2
Vote

Error opening generated sheet on Mac

description

Hi,
 
Thank you for a great library.
 
Im experiencing a issue though.
 
When I load a sheet into the XLWorkbook constructor - fill out a couple of cells - save the sheet and then open the newly generated sheet on a Mac, the sheet is corrupted in the associated styles.xml.
 
My research led me to believe, that the issue origins in the Calibri font, which does not exists on Mac.
I then tried the above procedure with an empty sheet and with Arial selected as the default font, but that did not solved the issue.
 
The sheet was still corrupted, and I could see in the Open XML SDK 2.0 Productivity Tool, that the Calibri font was still present.
 
If I compare the empty "template" sheet with the generated corrupted sheet. The only real difference is the extra font (Calibri) in styles.xml, apparently automatically provided by the ClosedXML code.
 
A possible fix for this issue would be very welcome.

file attachments

comments

MDeLeon wrote Aug 1, 2012 at 3:56 PM

Attach a file that opens up correctly on Mac but gets corrupted by ClosedXML. Make it an empty one if possible.

marle1 wrote Aug 2, 2012 at 8:29 AM

Thank you for your quick reply MDeLeon.

I have attached a xlsx file, which works before being manipulated by ClosedXML.

The issue only gets apparent when you edit the manipulated sheet and save it again in Excel on Mac.
Thats when the file is corrupted and asked to repair.

I tried to manually remove the Calibri font from the styles.xml file and reopening the file in Excel, but that did not solve the problem.

MDeLeon wrote Aug 2, 2012 at 2:54 PM

I took your file opened it with ClosedXML, then saved it, and the saved version doesn't have Calibri. It leaves the font as Arial.

marle1 wrote Aug 3, 2012 at 7:22 AM

Well... Even if I load the empty sheet with ClosedXML and saves it to a new destination without inserting any text the Calibri font is added to the sheet.

I have printed out the style of the Workbook when the sheet was loaded and the result shows that Calibri is the default font:

Style Font:False-False-None-False-Baseline-False-11-FF000000-Calibri-Swiss Fill:Color Index: 64-None-Color Index: 64 Border:None-FF000000-None-FF000000-None-FF000000-None-FF000000-None-FF000000-False-False NumberFormat: 0- Alignment: General-Bottom-0-False-ContextDependent-0-False-0-False- Protection: Locked

The code:

using(var wb = new XLWorkbook(System.AppDomain.CurrentDomain.BaseDirectory + @"data\test2.xlsx"))
    {
        Response.Write("Style " + wb.Style.ToString());

        var ws = wb.Worksheets.First();

        //ws.Cell(7, 1).Value = "Some text from code";

        System.IO.Stream stream = new System.IO.MemoryStream();

        wb.SaveAs(stream);

        using (System.IO.Stream md = stream)
            md.SaveAs(@"E:\Temp\test2.xlsx");
    }
Im opening the sheet on a iMac OSX Mountain Lion 10.8 with Excel 2011 v14.2.3.

Im using the latest version of ClosedXML v0.66.1 with .NET 4.0 on a Win Server 2008 RC.

I have attached the sheet that was loaded and saved with ClosedXML and nothing more. It does have Calibri as a font when I debug it with Open XML SDK 2.0 Productivity Tool.

Thanks

MDeLeon wrote Aug 3, 2012 at 9:53 AM

heh, I had to go to my own documentation to figure this one. I'll try to make it load the default style from the file.

See: http://closedxml.codeplex.com/wikipage?title=Using%20Default%20Styles&referringTitle=Documentation

In the meantime you can do the following:
        XLWorkbook.DefaultStyle.Font.FontName = "Arial";
        var wb = new XLWorkbook("test2.xlsx");
        wb.SaveAs("test2_saved.xlsx");

marle1 wrote Aug 3, 2012 at 2:40 PM

Hi again MDeLeon,

I did quite a lot of research regarding the Mac issue today, because the Calibri font situation was actually not the source of the issue after all.

What I was able to discover, was that the styles.xml file cannot contain a namespace reference called xmlns:x.
This was apparently not an issue with the sheet1.xml file, which is allowed to contain the xmlns:x reference.

Therefor a solution to the issue is to not reference the namespace with x in styles.xml. When x is omitted, the sheet loads just fine - both before and after editing.

I hope this makes sense for you and that you are able to patch a fix in the very near future, as this is a vital piece of my current project.

Thanks

MDeLeon wrote Aug 3, 2012 at 3:25 PM

I'm very doubtful of that explanation. Please do a hello world program and open the output on your Mac:

var wb = new XLWorksheet();
wb.AddWorksheet("Sheet1").FirstCell().SetValue("Hello World");
wb.SaveAs("HelloWorld.xlsx");

marle1 wrote Aug 4, 2012 at 10:05 AM

Obviously that was not the entire explanation, while the x namespace declaration is working just fine when the sheet is created from ClosedXML. Im just stating that obitting the x namespace and x: reference infront of all the nodes, solved the issue with corrupt files on Office 2011 for Mac.

There must be some kind of link between the x declaration and the corrupt sheets in Office 2011.

As a note:
I have tried creating the template .xlsx files in both Office 2010 (Win) and Office 2011 (Mac)

MDeLeon wrote Aug 6, 2012 at 4:08 PM

I don't know what to do here since I have no way of testing it and no way to know what's really causing your Office to reject the file (I mean, the Open XML SDK says the file OK).

marle1 wrote Aug 7, 2012 at 8:28 AM

I understand.

I will dig a little deeper into the differences in the hope of finding the cause for the corrupt files and hopefully get back to you with a more thorough explanation.

marle1 wrote Aug 9, 2012 at 1:51 PM

Hi again MDeLeon,

I have attached a compressed file for you to look at when you have the time.

The file contains two .xml files. Both files originates from the same Excel sheet.
  • styles_working.xml has not yet been through ClosedXML code.
  • styles_notworking.xml has been through ClosedXML code.
What I was able to find was that removing the last lines in styles_notworking.xml, describing a extLst (extensionList), solved the issue with the corrupt files on Mac.

It seems to me that somehow the format is not recognizing the extLst after been "touched" by ClosedXML.

I am not sure why, but I figured that you could help me find the answer.

MDeLeon wrote Aug 9, 2012 at 4:54 PM

I don't even touch the SlicerStyles. The modifications you see (adding x:) are all OpenXML SDK's doing. It has to be some other value in conjunction of that one that's making the file fail. That's because the two xml portions are identical (save for x:).

Can't you just save your template without the SlicerStyles?

marle1 wrote Aug 10, 2012 at 10:26 AM

It is actually incredible difficult to remove the default SlicerStyle in Excel.
Well I mean, I cannot find any documentation describing how to remove the styles.

What I am able to do is to remove the SlicerStyle lines from styles.xml manually for each template created.
But that is just not the most elegant of solutions.

One might be in trouble if that person actually need SlicerStyles in a template.

Thank you for you help - I will leave this issue hanging.

tomwinans wrote Feb 6, 2013 at 10:02 PM

Here is a zip of a file generated by the following code:
        var wb = new XLWorkbook();          wb.AddWorksheet("Sheet1").FirstCell().SetValue("Hello World");          wb.SaveAs("/Users/tommy/Desktop/HelloWorld.xlsx");
This file, called HelloWorld0.xlsx in the zip, is untouched - just as generated on OSX. The second xlsx file in the zip, called HelloWorld1.xlsx, is a file I opened using Excel 2011 - Excel complained the file was corrupted and offered to fix the file, which I permitted. I saved the corrected file - there is a log file Excel generated.

I attempted to open the fixed file with the following code - unsuccessfully ...
            using (FileStream fs = new FileStream("/Users/tommy/Desktop/HelloWorld1.xlsx", FileMode.Open)) {                    XLWorkbook wb = (XLWorkbook) new XLWorkbook(fs);

                Console.WriteLine ("Opened XL document");

                IXLWorksheets sheets = wb.Worksheets;

                Console.WriteLine ("There are {0} worksheets in the Excel file just opened", sheets.Count);             }
The workbook creation failed with the following exception as generated by Mono:

Operation not valid when package is read-only at System.IO.Packaging.Package.CheckIsReadOnly () [0x00017] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/WindowsBase/System.IO.Packaging/Package.cs:114 at System.IO.Packaging.PackagePart.CreateRelationship (System.Uri targetUri, TargetMode targetMode, System.String relationshipType, System.String id, Boolean loading) [0x00000] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/WindowsBase/System.IO.Packaging/PackagePart.cs:128 at System.IO.Packaging.PackagePart.LoadRelationships (System.Collections.Generic.Dictionary2 relationships, System.IO.Stream stream) [0x0008f] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/WindowsBase/System.IO.Packaging/PackagePart.cs:168 at System.IO.Packaging.PackagePart.get_Relationships () [0x00048] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/WindowsBase/System.IO.Packaging/PackagePart.cs:50 at System.IO.Packaging.PackagePart.GetRelationships () [0x00016] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/WindowsBase/System.IO.Packaging/PackagePart.cs:192 at DocumentFormat.OpenXml.Packaging.OpenXmlPart.Load (DocumentFormat.OpenXml.Packaging.OpenXmlPackage openXmlPackage, DocumentFormat.OpenXml.Packaging.OpenXmlPart parent, System.Uri uriTarget, System.String id, System.Collections.Generic.Dictionary2 loadedParts) [0x00000] in :0 at DocumentFormat.OpenXml.Packaging.OpenXmlPartContainer.LoadReferencedPartsAndRelationships (DocumentFormat.OpenXml.Packaging.OpenXmlPackage openXmlPackage, DocumentFormat.OpenXml.Packaging.OpenXmlPart sourcePart, System.IO.Packaging.PackageRelationshipCollection relationshipCollection, System.Collections.Generic.Dictionary`2 loadedParts) [0x00000] in :0 at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Load () [0x00000] in :0

tomwinans wrote Feb 6, 2013 at 10:08 PM

By the way ... attempting to open the corrupted file yielded the following Mono stack trace:

Text node cannot appear in this state. Line 1, position 681. at Mono.Xml2.XmlTextReader.ReadText (Boolean notWhitespace) [0x0000c] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/XmlTextReader.cs:1651 at Mono.Xml2.XmlTextReader.ReadContent () [0x0015c] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/XmlTextReader.cs:1359 at Mono.Xml2.XmlTextReader.Read () [0x00141] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/XmlTextReader.cs:626 at System.Xml.XmlTextReader.Read () [0x0006b] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/XmlTextReader2.cs:564 at Mono.Xml.EntityResolvingXmlReader.Read () [0x0009b] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/EntityResolvingXmlReader.cs:393 at Mono.Xml.Schema.XsdValidatingReader.Read () [0x00027] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/Mono.Xml.Schema/XsdValidatingReader.cs:1585 at System.Xml.XmlValidatingReader.Read () [0x00104] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/XmlValidatingReader.cs:507 at System.Xml.XmlDocument.ReadNodeCore (System.Xml.XmlReader reader) [0x00363] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/XmlDocument.cs:1021 at System.Xml.XmlDocument.ReadNode (System.Xml.XmlReader reader) [0x00047] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/XmlDocument.cs:874 at System.Xml.XmlDocument.Load (System.Xml.XmlReader xmlReader) [0x00019] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/XmlDocument.cs:738 at System.Xml.XmlDocument.Load (System.IO.Stream inStream) [0x0002e] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/System.XML/System.Xml/XmlDocument.cs:696 at System.IO.Packaging.PackagePart.LoadRelationships (System.Collections.Generic.Dictionary2 relationships, System.IO.Stream stream) [0x00006] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/WindowsBase/System.IO.Packaging/PackagePart.cs:158 at System.IO.Packaging.PackagePart.get_Relationships () [0x00048] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/WindowsBase/System.IO.Packaging/PackagePart.cs:50 at System.IO.Packaging.PackagePart.GetRelationships () [0x00016] in /Volumes/work/macports/var/macports/build/_Volumes_work_mports_dports_devel_mono/mono/work/mono-2.10.9/mcs/class/WindowsBase/System.IO.Packaging/PackagePart.cs:192 at DocumentFormat.OpenXml.Packaging.OpenXmlPart.Load (DocumentFormat.OpenXml.Packaging.OpenXmlPackage openXmlPackage, DocumentFormat.OpenXml.Packaging.OpenXmlPart parent, System.Uri uriTarget, System.String id, System.Collections.Generic.Dictionary2 loadedParts) [0x00000] in :0 at DocumentFormat.OpenXml.Packaging.OpenXmlPartContainer.LoadReferencedPartsAndRelationships (DocumentFormat.OpenXml.Packaging.OpenXmlPackage openXmlPackage, DocumentFormat.OpenXml.Packaging.OpenXmlPart sourcePart, System.IO.Packaging.PackageRelationshipCollection relationshipCollection, System.Collections.Generic.Dictionary`2 loadedParts) [0x00000] in :0 at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Load () [0x00000] in :0

MDeLeon wrote Feb 6, 2013 at 10:20 PM

I'm going to close this one for now because I honestly don't know how to troubleshoot this issue (I don't have a way to test things).


** Closed by MDeLeon 8/12/2012 10:53 AM

MDeLeon wrote Feb 6, 2013 at 10:20 PM

Tom has provided more info. I'll see what I can do.

tomwinans wrote Feb 8, 2013 at 3:51 PM

Thanks much for taking your time to look at this. Appreciate the help. Please let me know if you need any additional information as I can sort through this with you. Use of a tool like this in an OSX/Linux/Mono context would prove quite useful to many, I am sure.