OpenXML spreadsheet created in .NET won't open in iPad

.net, ios, ipad, openxml, relative-path

Solution

I've spent a lot of time researching this and thought I'd share my results. It appears that OpenXML is doing two things. 1. The content_types.xml file is missing an entry for the workbook 2. The xl/_rels/workbook.xml.rels file is using a fullly relative path.

Excel itself opens the file fine but I've tried various apps on the iPad and they all fail. So I've had to manually fix the files myself using the following code. It assumes the entire content of the file is passed in as a stream and uses DotNetZip to open and manipulate. Hope this code helps others!

    private Stream ApplyOpenXmlFix(Stream input)
    {
        const string RELS_FILE = @"xl/_rels/workbook.xml.rels";
        const string RELATIONSHIP_ELEMENT = "Relationship";
        const string CONTENT_TYPE_FILE = @"[Content_Types].xml";
        const string XL_WORKBOOK_XML = "/xl/workbook.xml";
        const string TARGET_ATTRIBUTE = "Target";
        const string SUPERFLUOUS_PATH = "/xl/";
        const string OVERRIDE_ELEMENT = "Override";
        const string PARTNAME_ATTRIBUTE = "PartName";
        const string CONTENTTYPE_ATTRIBUTE = "ContentType";
        const string CONTENTTYPE_VALUE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";

        XNamespace contentTypesNamespace = "http://schemas.openxmlformats.org/package/2006/content-types";
        XNamespace relsNamespace = "http://schemas.openxmlformats.org/package/2006/relationships";
        XDocument xlDocument;
        MemoryStream memWriter;

        try
        {
            input.Seek(0, SeekOrigin.Begin);
            ZipFile zip = ZipFile.Read(input);

            //First we fix the workbook relations file
            var workbookRelations = zip.Entries.Where(e => e.FileName == RELS_FILE).Single();
            xlDocument = XDocument.Load(workbookRelations.OpenReader());

            //Remove the /xl/ relative path from all target attributes
            foreach (var relationship in xlDocument.Root.Elements(relsNamespace + RELATIONSHIP_ELEMENT))
            {
                var target = relationship.Attribute(TARGET_ATTRIBUTE);

                if (target != null && target.Value.StartsWith(SUPERFLUOUS_PATH))
                {
                    target.Value = target.Value.Substring(SUPERFLUOUS_PATH.Length);
                }
            }

            //Replace the content in the source zip file
            memWriter = new MemoryStream();
            xlDocument.Save(memWriter, SaveOptions.DisableFormatting);
            memWriter.Seek(0, SeekOrigin.Begin);
            zip.UpdateEntry(RELS_FILE, memWriter);

            //Now we fix the content types XML file
            var contentTypeEntry = zip.Entries.Where(e => e.FileName == CONTENT_TYPE_FILE).Single();
            xlDocument = XDocument.Load(contentTypeEntry.OpenReader());

            if (!xlDocument.Root.Elements().Any(e =>
                e.Name == contentTypesNamespace + OVERRIDE_ELEMENT &&
                e.Attribute(PARTNAME_ATTRIBUTE) != null &&
                e.Attribute(PARTNAME_ATTRIBUTE).Value == XL_WORKBOOK_XML))
            {
                //Add in the missing element
                var overrideElement = new XElement(
                    contentTypesNamespace + OVERRIDE_ELEMENT,
                    new XAttribute(PARTNAME_ATTRIBUTE, XL_WORKBOOK_XML),
                    new XAttribute(CONTENTTYPE_ATTRIBUTE, CONTENTTYPE_VALUE));

                xlDocument.Root.Add(overrideElement);

                //Replace the content
                memWriter = new MemoryStream();
                xlDocument.Save(memWriter, SaveOptions.DisableFormatting);
                memWriter.Seek(0, SeekOrigin.Begin);
                zip.UpdateEntry(CONTENT_TYPE_FILE, memWriter);
            }

            Stream output = new MemoryStream();

            //Save file
            zip.Save(output);

            return output;
        }
        catch
        {
            //Just in case it fails, return the original document
            return input;
        }
    }

Problem

Original source