How to modify Excel [Content_Types].xml file generated by C# OpenXML

0
votes

I am generating Excel files in C# using OpenXML SDK library. I have noticed that generated content file ([Content_Types].xml) differs from what Excel is generating when saving files.

For some reason my OpenXML generated files lacks 

<Default ContentType="application/xml" Extension="xml"/>

element and places the

<Default Extension="xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml" />

element instead.

The OpenXML generated file looks as follows:

<?xml version="1.0" encoding="utf-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml" />
<Default Extension="xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml" />
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml" />
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml" />
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" />
<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml" />
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml" />
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml" />
</Types>

and I would like to achieve the following:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="bin" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings"/>
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Default Extension="xml" ContentType="application/xml"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>
<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>
<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>
</Types>

The is also one missing element, which I would like to add

<Default Extension="bin" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings"/>

Is there a way of accessing the content types using the OpenXML library? I would like to avoid the approach of manual unpacking the xlsx file and modifying the source of the [Content_Types].xml

2
c#excelopenxmlcontent-typeopenxml-sdk
Why is it important to change these? Which version of Excel is involved? The Open XML SDK default is for Office 2007 compatibility. A newer version of Excel might generate different content for this reason. The Open XML SDK does not enable accessing these things directly. You might try opening such a file in Excel, making a small change to make it "dirty", then save to a new file name. Open the original in the Open XML SDK Productivity Tool, then use the Compare feature to open the changed version. That should show you the code to "convert" the first to the second. - Cindy Meister
I am generating Excel files with OpenXML SDK, which should later be read by OleDB. This fails for some reason with ''External table is not in the expected format." error. I know that with EPPlus Lib the files are parsed OK by OLE. Maybe out of curiosity or maybe I am just stubborn I want to do this with OpenXML. The idea was to generate exactly the same as EPPlus or Excel does. - Sebastian Widz

2 Answers

0
votes
    using (var archive = ZipFile.Open(filePath, ZipArchiveMode.Update))
    {
        var entry = archive.GetEntry("[Content_Types].xml");

        //Replace the content
        StringBuilder sb = new StringBuilder();
        sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ?>");
        sb.Append("<Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\">");
        //sb.Append("<Default Extension=\"bin\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings\"/>");
        sb.Append("<Default Extension=\"rels\" ContentType=\"application/vnd.openxmlformats-package.relationships+xml\"/>");
        sb.Append("<Default Extension=\"xml\" ContentType=\"application/xml\"/>");
        sb.Append("<Override PartName=\"/xl/workbook.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\" />");
        sb.Append("<Override PartName=\"/xl/worksheets/sheet1.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml\" />");
        sb.Append("<Override PartName=\"/xl/theme/theme1.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.theme+xml\" />");
        sb.Append("<Override PartName=\"/xl/styles.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml\" />");
        sb.Append("<Override PartName=\"/xl/sharedStrings.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml\" />");
        sb.Append("<Override PartName=\"/docProps/core.xml\" ContentType=\"application/vnd.openxmlformats-package.core-properties+xml\" />");
        sb.Append("<Override PartName=\"/docProps/app.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.extended-properties+xml\" />");
        sb.Append("</Types>");

        entry.Delete();
        entry = archive.CreateEntry([Content_Types].xml);
        using (StreamWriter writer = new StreamWriter(entry.Open()))
        {
            writer.Write(sb);
        }
    }
0
votes

The easiest solution would be to use an appropriate Excel workbook with the desired content types as the starting point.

Using the Open XML SDK, you can create workbooks from an Excel template (.xslt) or clone existing Excel workbooks (.xlsx, .xlsm). You can clone a workbook by either calling the Clone() method on an existing SpreadsheetDocument object or by reading the data into a MemoryStream, opening the SpreadsheetDocument on that MemoryStream, and then saving it to a different file (or database or whatever) at the end.

The following example creates an Excel workbook from a template (or workbook):

SpreadsheetDocument doc = SpreadsheetDocument.CreateFromTemplate("Template.xlst");

If you look at how the CreateFromTemplate() method is implemented, you'll see that it always clones the workbook or template. For templates, it then changes the document type (using the ChangeDocumentType() method) so that you will always get a workbook rather than a template. Thus, if your starter template or workbook is stored in the file system, this would be the most straightforward approach.

Depending on how and where your "template" is stored, there are also other ways to achieve the same result. However, you will generally produce a Stream (e.g., a MemoryStream) with the template or workbook data and then open the SpreadsheetDocument on that Stream.