Generated package does not include certain required parts

[NerdBrick]

Description
Started on MSDN support first.
https://social.msdn.microsoft.com/Forums/office/en-US/fa395b57-ab12-4fe8-9fa1-0726c18fe0cd/openxml-generated-file-wont-open-in-ms-spreadsheet-compare-tool-and-excel-quotopen-and?forum=oxmlsdk

The basic problem is when a new file is created by OpenXML, it appears to load in Excel, but if you use the Excel File Open tool for "Open and Repair" you can see there are some hidden errors being reported. My assumption is the tool Spreadhsheet Compare tool from MS Office can't open this file because of teh same underlying error. If the file is opened and then saved in Excel, the tool now open the file.

Information

• .NET Target: 4.7.1
• DocumentFormat.OpenXml Version: 2.8.1.0

Repro

public static void CreateSpreadsheetWorkbook(string filepath)
        {
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "mySheet"
            };
            sheets.Append(sheet);

            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
        }

Observed

Spreadsheet Comparison tool will report
https://support.office.com/en-us/article/Basic-tasks-in-Spreadsheet-Compare-F2B20AF8-A6D3-4780-8011-F15B3229F5D8

"Error opening workbook. Attempted to read past the end of the stream"

If you attempt to open the file through Excel "Open and Repair" option, you will get this XML error.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error260080_01.xml</logFileName><summary>Errors were detected in file 'C:\TEMP\testing.xlsx'</summary>
<additionalInfo><info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info></additionalInfo>
</recoveryLog>

Expected
File should open without errors.

Please add your expected behavior here.

[twsouthwick]

This repo is for issues in the SDK, while this error appears to be with the comparer. In order to identify if this is an issue with the SDK, can you try reopening the file you created with the SDK and see if there are any issues opening?

[NerdBrick]

Yes, the SDK can reopen the file without any errors. I understand this might be a fault with Spreadsheet Compare tool, but what about Excel itself is reporting a problem with the OpenXML generated file when "Open and Repair" feature is used?

 static void Main(string[] args)
        {
            var testFile = @"c:\temp\NewReOpen.xlsx";
            CreateSpreadsheetWorkbook(testFile);
            OpenSpreadsheetWorkbook(testFile);

            Console.WriteLine("Hit any key");
            Console.ReadLine();
        }

        public static void CreateSpreadsheetWorkbook(string filepath)
        {
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "mySheet"
            };
            sheets.Append(sheet);

            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
        }

        public static void OpenSpreadsheetWorkbook(string filepath)
        {
            // Open the document
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);
            spreadsheetDocument.WorkbookPart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
        }

[tarunchopra]

@NerdBrick : Do you mean that above pasted code will create a corrupted excel file?

[tarunchopra]

@NerdBrick : I just used the code you pasted and Excel is able open the file just fine. What did I miss?

[NerdBrick]

Yes, it will open directly in Excel; however, if on the file open dialog you chose the "Open and Repair" option, you can see there was an unreported error as Excel opens the file. My thought is this error might be what is stopping Spreadsheet compare from opening the file.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error260080_01.xml</logFileName><summary>Errors were detected in file 'C:\TEMP\NewReOpen.xlsx'</summary>
<additionalInfo><info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info></additionalInfo>
</recoveryLog>

[tarunchopra]

Thank you. So what happens is that when we overwrite the file with openxmlsdk then it removes lot of parts which were created by Excel, as default. Like this, all these Red parts were removed by openxmlsdk when the file was overwritten. After that when we run 'open and repair', Excel add these removed parts along with other data back to package as a part of repair.

@tomjebo : What shall we do ? Shall the above code of CreateSpreadsheetWorkbook be fixed to add parts added as default by Excel?

[twsouthwick]

@NerdBrick Can you open the corrected file in the comparer?

@tarunchopra @tomjebo If these items are supposed to be included per the spec, I vote we should add it.

[NerdBrick]

@twsouthwick , when you mean "corrected file" are you saying after I open it and save it in Excel? Yes, once the OpenXML file has been opened and saved in Excel, Spreadsheet Compare will be happy.

[twsouthwick]

@NerdBrick Yes, that's what I was referring to. This identifies that it is most likely due to the change @tarunchopra mentioned. Next step is to identify why the package doesn't have those lines.

@tarunchopra Were the files pointed to by those parts removed or did they not even exist?

[tarunchopra]

Taylor - The files got removed; they did exist initially but openxmlsdk code removed them as it recreated that file and added only the parts requested by user in the above code.

[twsouthwick]

We should identify why it's being removed. Are there a list of items that are required for a well-formed document that we should ensure are there?

[twsouthwick]

@tarunchopra @tomjebo Can you guys comment as to whether there is anything in the standard that specifies which parts are required for a functional package?

[tomjebo]

@NerdBrick (@twsouthwick @tarunchopra) I spent some time today testing this scenario. I found that a blank, newly created Excel workbook (no SDK involved) behaves the same way when using the "Open and Repair" option in Excel. So I focused on the Spreadsheet Compare tool.

After whittling down the Excel genned file to the same structure and content as the SDK genned one (tediously), I found that the one thing that the Spreadsheet Compare tool didn't like was the "x:" ns prefix in this line in the sheet.xml part:

<x:sheetData/>

The SDK uses a xmlns declaration attribute that specifies "x:" as the prefix like this:

<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

Excel uses other prefixes but doesn't on this first declaration as it is the default spreadsheetml/2006/main namespace.

The "x:sheetData" is being emitted as part of schema generated code and this would be a little tricky to change in the SDK, not to mention special case. Also, from everything I can discern, the XML emitted by the SDK is completely legal, the Spreadsheet Compare tool just doesn't like it. I think this would be a bug in the tool. Our team can file a bug with the Excel tools folks.

[twsouthwick]

@tomjebo Yeah, that seems to be an issue with the compare tool as it is valid XML. Can you close this once you have a bug opened with the Excel tools folks?

[twsouthwick]

@tomjebo Have you been able to open a bug with the compare tool?

[maflleje1983]

@NerdBrick, where you able to get an answer to this issue? I just recently took on helping resolve this issue in an application. The end user will not want to open and save every time an Excel file is generated.

When the file is first generated, I noticed the size of the application is small. When I open up and save the file in Excel, the file almost doubles in size with all of the corrections needed.

[github-actions]

Stale issue message

[dontbyte]

Just found this issue after I've created an issue @ClosedXML repo, see #1436 (related)

[NerdBrick]

@NerdBrick, where you able to get an answer to this issue? I just recently took on helping resolve this issue in an application. The end user will not want to open and save every time an Excel file is generated.

When the file is first generated, I noticed the size of the application is small. When I open up and save the file in Excel, the file almost doubles in size with all of the corrections needed.

I'm sorry for the long delay replying. I no longer work for the company that I was using OpenXML for.

Sorry I wasn't of more help.