Use ClosedXml to convert an XML document in OpenXML format

Question

I have an XmlDocument in OpenXML format. Looks like this:

<?xml version="1.0" encoding="utf-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook>
    <o:DocumentProperties>
        [snip]
    </o:DocumentProperties>
    <Styles>
        [snip]
    </Styles>
    <Worksheet ss:Name="Contract">
        <Table>
            <Row>
                <Cell ss:StyleID="s38">
                    <Data ss:Type="String"/>
                </Cell>
                <Cell ss:StyleID="s38">
                    <Data ss:Type="String">System Number</Data>
                </Cell>
                <Cell ss:StyleID="s38">
                    <Data ss:Type="String">Contract Number</Data>
                </Cell>
                <Cell ss:StyleID="s38">
                    <Data ss:Type="String">Customer Name(s)</Data>
                </Cell>
                <Cell ss:StyleID="s38">
                    <Data ss:Type="String">Customer System Number(s)</Data>
                </Cell>
                [snip]
            </Row>
            <Row>
                [snip]
            </Row>
            [etc]
        </Table>
        <x:WorksheetOptions>
            [snip]
        </x:WorksheetOptions>
    </Worksheet>
</Workbook>

I need to present this to the user from an internal company website. However, the code to directly present it is not working:

MemoryStream strm = new MemoryStream();
XmlWriter xmlWriter = XmlWriter.Create(strm);

System.Xml.XPath.XPathNavigator xPathNavigator = xPartCollection.OwnerDocument.CreateNavigator();
xslt.Transform(xPathNavigator, xmlWriter);
strm.Position = 0;

Response.Clear();
Response.Charset = "utf-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
Response.AddHeader("content-disposition", "attachment;filename=" + PartDefGuid.InternalName + Start + ".xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Charset = "";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.BinaryWrite(strm.ToArray());
Response.Flush();
Response.SuppressContent = true;
Response.End();

Excel throws an error "Excel cannot open this file because the file format or file extension is not valid." I presume it has to do with the Company's internal security regarding downloading Excel files from the web. They have it set extremely high.

However, in another spot on the website I have ClosedXML working great. I can create ClosedXML workbooks and response.write the workbook and Excel will open them.

I was wondering if there was a way to have ClosedXML read this OpenXML style XML Document. It would save me from having to redo all the code that was used to create the OpenXML document in the first place.

I tried converting it to Datasets because ClosedXML is great at translating Datasets to Excel workbook data. That brought up more problems because it treated each section as a different table. The Dataset ended up with 17 tables. <Worksheet> <Table> <Row> <Cell> became four separate tables.

I could merge all the tables and work to get it properly formatted but I thought it might be easier to start from the OpenXML source and directly convert that to ClosedXML.

Answer 1

It doesn't look like there are any easy "XML to ClosedXML" converters out there. Plus it also seems like old code wasn't outputting OpenXML it was outputting SpreadsheetML (thanks @Francois Botha!)

So I ended up going back to the original XSL stylesheet that's used to create that SpreadsheetML and rewrote it in C# to create a ClosedXML workbook instead.