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

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.

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.

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

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");

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

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");

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)

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).

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.

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.

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?

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.

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 <filename unknown>: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 <filename unknown>:0
at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Load () [0x00000] in <filename unknown>:0

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 <filename unknown>: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 <filename unknown>:0
at DocumentFormat.OpenXml.Packaging.OpenXmlPackage.Load () [0x00000] in <filename unknown>:0

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

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

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.