This project has moved and is read-only. For the latest updates, please go here.

Excel 2013 - 2010 compatibility

Nov 19, 2012 at 2:35 PM
Edited Nov 19, 2012 at 2:36 PM

First I want to thank all contributors for creating this great project. Before I got to know ClosedXML I used OpenXML directly. In comparison, ClosedXML is a huge step forward as it's incredibly easy to use.

However I recently switched to Office 2013 and after editing an existing template and saving it in Excel 2013, one of our customers got the following error after saving and reopening the resulting file in Excel 2010:

"Excel found unreadable content in 'tmp1234.xlsx'. Do you want to recover the contents of this workbook?"

The file was temporarily saved and then opened on the client's computer without any problems. The error occurred only when the client saved a copy of the file and then reopened the copy.

On Excel 2013 I can't reproduce this error and when I try to open a copy saved with Excel 2013  in Excel 2010 and create another copy of it, no error is thrown on reopening it.

Has anyone else tried to use ClosedXML in combination with a Excel 2013 template and got any compatibility issues with Excel 2010?

Thank you

Nov 27, 2012 at 6:42 PM
Edited Nov 28, 2012 at 9:40 AM

Hello

I can confirm this problem. I encountered it myself today.

Last year I wrote a little program for my boss to list some data about our customers in an Excel sheet. Last week my boss wanted me to restructure the table, so he could print it more easily and add the possibility to print only the data for one single customer. In addition to making the required changes I downloaded the latest ClosedXML DLL, version 0.68.1 (.Net 4.0), to use it. The last time I updated this project was about a year ago, so the last ClosedXML version used, was probably 0.60.

Now the Excel file is produced as desired and I can do whatever I like with it in Excel 2013. But my boss has not found the time to install Office 2013 yet. So when he creates the file and opens it everything ist fine. As soon as he changes it in Excel 2010 and saves it, he gets the error Ceten reported.

When we agree to recover the content as the dialog suggest, the data seems to still be there, but all colors, text orientations and other style elements seem to be gone.

I will analyse it tomorrow and report my findings.

 

OK, I tried a whole lot of things now, unfortunately without much success.

When I create a new document with ClosedXML it works fine and can be saved and reopened in Excel 2010.

When I open an existing Excel document in ClosedXML and save just save it under a different name the problem occurs.

So I thought it must be something about the document I open. There was no problem with a document that is completely empty. So I tried the next best thing. I created a new Excel file an just set the background color of cell A1 to red in Excel 2013 and saved the file (I also tried that in Excel 2010, with the same result.). Then I just opened it with ClosedXML and saved it again without doing anything to it:

 

Dim sTemplate As String = "C:\Template.xlsx"
Using wb As New XLWorkbook(sTemplate)
    wb.Save()
End Using

 

When I open it in Excel 2010 everything is just fine. When I save it and try to it again in Excel 2010 it is completely broken. It does show the error message, but it can't even repair it, as it could when I used my original Excel file with more text , colors etc. in it.

I unpacked the file after I saved it with Excel 2010 and tried to open the styles.xml file with XMLNotepad. That program refused to open the file telling me that 'x' was an undeclared namespace in line 2, position 1642. Opening the file (styles.xml) I found the following at said position:

<x:ext uri="{9260A510-F301-46a8-8635-F512D64BE5F5}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"><x15:timelineStyles defaultTimelineStyle="TimeSlicerStyleLight1"/></x:ext>

Looking at the file created by ClosedXML I found, that the styles.xml file is considerably bigger. It is only one line, but when I put a linebreak after the XML-declaration I have 3347 symbols in the file created by ClosedXML and only 1863 in the file after it has been saved with Excel 2010.

The first part where the namespaces are declared looks like this in the file created by ClosedXML:

<x:styleSheet xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" mc:Ignorable="x14ac">

and like this in the file after saving it with Excel 2010:

<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">

 

I hope this helps to fix the problem. If you need any more information let me know.

Dec 18, 2012 at 4:36 PM
Edited Dec 18, 2012 at 4:42 PM

Unluckily I can confirm the problem. The following are the steps that I perform:

  1. Created an Excel file with Excel 2013 (under Windows 8) which serves as a template for further ClosedXml operations
  2. Opening the Excel file with ClosedXml 0.68.1 -> new XLWorkbook(MemoryStream, XLEventTracking.Disabled)
  3. Saving the Workbook (without performing any changes using ClosedXml) -> workbook.Save()
  4. Retrieving the modified content of the workbook by getting the bytes from the underlying MemoryStream
  5. Saving a file using the content

The template file itself seems to be fine (can be opened and saved multiple times with Excel 2007, 2010 and 2013).
The generated file can be flawlessly accessed, edited and saved with Excel 2007 and 2013.

But if I open such a generated file with Excel 2010, edit anything, save it and then try to reopen it, I am getting the "unreadable content" error.
The file that Excel 2010 produces when saving is completely broken, it can't even be opened with Excel 2013.

If the file that I am using as a template is needed (I will have to alter it before I can provide it), just let me know.

Thanks beforehand, I really appreciate your work!

Update: Just to be complete, I am using the version for .Net 3.5 because I generate the Excel in SharePoint 2010 context.

Jul 7, 2013 at 7:46 PM
Hello!

I have the same problem.

I also tried with native OpenXML -> SpreadsheetDocument.Open method (Stream, Boolean) see http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.packaging.spreadsheetdocument.open.aspx (open excel file from file or stream), but problem stil exists.

So, in anyone found solution, please write post here.

best regards
Grzegorz Pawluch
Oct 17, 2013 at 2:33 PM
Hi
Did you get any solution for this issue. We are also facing the same issue. We have created Excel file using OPENXML and it is working fine in Windows 8 + Office 2013. But the same file if i try to open in Office 2010, first time it opens fine and if i modifiy any data and saves the file and tries to open again it shows unreadable content error and all the formattings are gone. Please reply if you have found any solution for this issue.

Thanks,
Prasad.
Oct 29, 2013 at 2:26 PM
Sorry for the late reply.
We still haven't found a solution for this problem.
As a workaround we still edit our template using Excel 2010.
Jun 12, 2014 at 11:24 AM
Any updates, I'm facing the same problem
Jun 18, 2014 at 10:54 PM
long/old thread ataziz, How can I reproduce your error?
Oct 28, 2014 at 7:39 AM
Hello
I reproduced.

1) Create an Excel file "Book1.xlsx" with Excel 2013 / Windows8.1.
2) Execute below with Excel 2010 / Windows 8:
            XLWorkbook xl = new XLWorkbook(@"c:\AnyPath\Book1.xlsx");
            xl.Save();
3) Open Book1.xlsx with Excel 2010 / Windows 8.
4) Input any string and save.
5) Open Book1.xlsx with Excel 2010 , "unreadable content" error occured.
6) Execute 2) again, " 'x' was an undeclared namespace" error occured.

ClosedXML version is 0.75.0.
I hope this helps to fix the problem.
Nov 11, 2014 at 7:49 AM
Sorry my mistake.
ClosedXML version was 0.71.0 when I reproduced it.
Version 0.75.0 is fine.

This problem seems to reproduce using OpenXML 2.0 or not applying KB2889836(Update for Microsoft Excel 2010).
Nov 19, 2014 at 1:49 AM
Hi first time posting, I figured out how to fix this after reading your posts above and this here
http://support.microsoft.com/kb/296492

TLDR see below for solution if you get the x' was an undeclared namespace problem that I did

1) Download notepad++ so you can edit your XML file easier, you can also choose to use regular notepad
2) For the noobs out there like me when I started this process - unpack the xlsx file by changing .xlsx extension to .zip, then unzip the zip to a folder since you can't edit files inside the zip folder (you will rezip later)
3) Open up the styles.xml file inside the xl folder using notepad++
3) Control find for "x:" to find instances where x is used as namespace

In my case, I found this:
<x:ext uri="{9260A510-F301-46a8-8635-F512D64BE5F5}" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"><x15:timelineStyles defaultTimelineStyle="TimeSlicerStyleLight1"/></x:ext>

Since there is an x15 after xmlns: I know the name space here should be x15 and not x (according to the microsoft.com link at the beginning of this post)
So the <x:ext... should be change to <x15:ext... and the </x:ext> should be changed to </x15:ext>

4) Make these changes to all instances of x: to the correct namespace and hit save on styles.xml
5) Rezip the excel folder and change .zip to .xlsx
6) Open the file in excel - this worked for me but no guarantee it would work for you

Message me if you have any questions - thanks for the clues
Aug 25, 2015 at 2:39 PM
I had the same problem happening apparently only on not very updated Office 2010 Standard installations.

The (very dirty) workaround I found (deducing it from this useful post) is removing those extensions completely, as I didn't need them.

After saving the workbook with ClosedXML I used the OOXML library directly doing this (in both workbook.xml and styles.xml):
using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(ms, true))
{
    if (excelDoc.WorkbookPart.Workbook.Descendants<WorkbookExtensionList>().Any())
    {
        excelDoc.WorkbookPart.Workbook.RemoveAllChildren<WorkbookExtensionList>();
        excelDoc.WorkbookPart.Workbook.Save();
    }

    if (excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Descendants<StylesheetExtensionList>().Any())
    {
        excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.RemoveAllChildren<StylesheetExtensionList>();
        excelDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
    }
}
As a note, it appears that fully updated Office 2010 Professional installation do not corrupt the files after saving, in my experience only Office 2010 Standard did this.

I hope this can be useful.
Sep 20, 2016 at 7:07 AM
I know this is old, but just in case it helps someone, I got around this using the following code;
Private Sub FixWorkBook(ByVal ms As MemoryStream)
    Try
        Dim owb As SpreadsheetDocument = SpreadsheetDocument.Open(ms, True)

        For Each element In owb.WorkbookPart.Workbook.ChildElements
            If element.LocalName = "extLst" Then element.Remove()
        Next

        For Each element In owb.WorkbookPart.WorkbookStylesPart.Stylesheet.ChildElements
            If element.LocalName = "extLst" Then element.Remove()
        Next

        owb.Close()

    Catch ex As Exception
        LogError(ex, "FixWorkBook")
    End Try
End Sub