Generating Excel-compatible XML files from Mathematica

Question

Excel can save its Workbooks in the "XML Table" format preserving all the formatting and styling information and then open and edit them as native Excel workbooks. I wish to generate such files by Mathematica for Excel programmatically adding formatting information for rows, columns, cells etc.

The first stumbling problem is that even when I Import Excel-generated file in Mathematica and then just Export it backward to "XML" the resulting file cannot be opened by Excel. Opening it in a text editor shows that this file contains apostrophes instead of quotes. Adding the "AttributeQuoting" -> "\"" option to Export fixes this but the resulting file still cannot be opened by Excel. More careful comparison shows that Export also drops the most of namespace prefixes in the generated file.

How to generate Excel-compatible XML files from Mathematica?

P.S. This question comes from the previous question: "How to export to Excel numbers as text fields from Mathematica?"

Answer 1

I have found the solution. From the Documentation,

Some documents use names in a non-namespace-compliant fashion, because the XML namespace recommendation, which extends XML, was made after the initial XML recommendation. "IncludeNamespaces"->"Unparsed" is provided to allow parsing of these documents. The name is always represented as the exact single string that appears in the XML file. Unless absolutely necessary, this option value should not be used.

With the option "IncludeNamespaces"->"Unparsed" everything is nice and even the "AttributeQuoting" -> "\"" option is not needed:

XMLData = Import["test.xml", "IncludeNamespaces" -> "Unparsed"];
Export["test-from MMa.xml", XMLData]

The exported file is opened by Excel without warnings.