WorkbookProtection corrupts Spreadsheet for Excel 2019

[DizTortion]

Spreadsheets saved with Excel 2019 get corrupted as soon as you add a WorkbookProtection.

Information

• .NET Target: .NET 3.5
• DocumentFormat.OpenXml Version: 2.9.1, 2.10.0-beta0001

Repro

Create an empty spreadsheet in Excel 2019. Perform the following operation:

using (var document = SpreadsheetDocument.Open("...", true) {
  document.WorkbookPart.Workbook.WorkbookProtection = new WorkbookProtection();
}

Open the spreadsheet in Excel 2019.

Observed

Excel fails to open and repair the file.

The resulting workbook.xml contains the newly added x:workbookProtection followed by the x:revisionPtr.

<x:workbookProtection />
<xr:revisionPtr revIDLastSave="0" documentId="13_ncr:1_{4BF1E7C4-2F1B-4756-81DA-B102ED8D212D}" xr6:coauthVersionLast="36" xr6:coauthVersionMax="36" xr10:uidLastSave="{00000000-0000-0000-0000-000000000000}" />

Manually changing the order (unzip/edit/zip) repairs the file.

<xr:revisionPtr revIDLastSave="0" documentId="13_ncr:1_{4BF1E7C4-2F1B-4756-81DA-B102ED8D212D}" xr6:coauthVersionLast="36" xr6:coauthVersionMax="36" xr10:uidLastSave="{00000000-0000-0000-0000-000000000000}" />
<x:workbookProtection />

Expected

File should not get corrupted.

[twsouthwick]

@DizTortion Thanks for the report. I'm a little confused by which version of excel you are referring to throughout. Can you update your repro to be explicit as to the version?

This is probably from the new 2016 features that have been brought online.

[DizTortion]

@twsouthwick Hi, I updated the issue and removed references to versions other than Excel 2019. I just tried to emphasize that the code worked while we used Excel 2010. Now that we upgraded to Excel 2019 we are seeing corrupted files.

[twsouthwick]

Thanks @DizTortion for the feedback. @tomjebo this seems a bit more schema related - can you check if the ordering has changed between 2010 and 2019?

[S1r-Lanzelot]

Any update on this @twsouthwick ? We ran into the same issue, ended up building our document with openxml and then using com to do the protection. 🤦‍♂

[DizTortion]

@S1r-Lanzelot Maybe give this workaround a try. Make your Spreadsheet modifications and close the stream. Then pass that stream (variable memoryStream) to the using block below.

It checks if there is a workbook protection and swaps its position with the revisionPtr. No COM interop required.

// TODO: workaround (see https://github.com/OfficeDev/Open-XML-SDK/issues/601)
using (var package = Package.Open(memoryStream, FileMode.Open))
{
    var xmlDocument = new XmlDocument();

    var part = package.GetPart(new Uri("/xl/Workbook.xml", UriKind.Relative));

    using (var oldPartStream = part.GetStream())
    {
        xmlDocument.Load(oldPartStream);

        var xmlNamespaceManager = new XmlNamespaceManager(xmlDocument.NameTable);
        xmlNamespaceManager.AddNamespace(
            "x",
            "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
        xmlNamespaceManager.AddNamespace(
            "xr",
            "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");

        var workbook = xmlDocument.DocumentElement
            .SelectSingleNode("/x:workbook", xmlNamespaceManager);
        var revisionPtr = xmlDocument.DocumentElement
            .SelectSingleNode("/x:workbook/xr:revisionPtr", xmlNamespaceManager);
        var workbookProtection = xmlDocument.DocumentElement
            .SelectSingleNode("/x:workbook/x:workbookProtection", xmlNamespaceManager);

        if (revisionPtr != null)
        {
            workbook.RemoveChild(revisionPtr);
            workbook.InsertBefore(revisionPtr, workbookProtection);

            using (var newPartStream = part.GetStream(FileMode.Create))
            {
                xmlDocument.Save(newPartStream);
            }
        }
    }
}

[github-actions]

Stale issue message

[DizTortion]

Any progress on this one? I'd be happy to provide more information if required.