none
OpenXML generated file wont open in MS Spreadsheet Compare tool and Excel "Open and Repair" also reports a problem

    Question

  • We are trying to build new process to auto generate Excel files using OpenXML (2.8.1.0). The very basic XLSX file will open in Excel, but when I try to use the MS tool Spreadsheet Compare tool, it can't open the file. The error we are getting is

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

    If I open the OpenXML generated file in Excel and save it first, the file can now be opened by the Spreadsheet Comparison tool. Excel is fixing/adding something to the file that is missing from OpenXML generated file.

    I used this MSDN sample code as a very basic starting point to help demonstrate the problem
    https://msdn.microsoft.com/en-us/library/office/ff478153.aspx

    The goal is to use the Spreadsheet comparison tool to make sure what we are generating is matching the old process that created the same file.

    I ran the file the "Open and Repair" in the Excel file open dialog, and it gives me this report on the original file generated from the above sample.

    <?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>

    Thank you 

    vendredi 1 juin 2018 16:18

Toutes les réponses

  • Hi NerdBrick,

    The issue seems that the Spreadsheet Compare tool not full compatible with Open XML.

    A easy workaround for this is you can consider using Microsoft Interop to automate Excel file instead of Open XML when it is possible. Or we can also using Microsoft Interop to save the spreadsheet again after you generate the spreadsheets. Here is the code for your reference:

    CreateSpreadsheetWorkbook(@"c:\heet2.xlsx");
    Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook workbook=app.Workbooks.Open(@"c:\Sheet2.xlsx");
    workbook.Save();
    app.Quit();
    I also suggest that you contact the developer team for Spreadsheet Compare tool team via the bottom contact in the below link:

    Compare two versions of a workbook by using Spreadsheet Compare

    Hope it is helpful.

    Regards & Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    lundi 4 juin 2018 03:26
    Modérateur
  • I appreciate you taking your time to reply to my question. However, this solution is not an option for us since we are running this process on a website, and we don't want to install Office on the sever to take advantage of Office Interop. This is why we went with the solution of OpenXML/ClosedXML.

    The reason I don't feel this is completely a problem with Spreadsheet Compare tool, is because Excel was also secretly not pleased with the OpenXML generated file when opened it through the "Open and Repair" feature. I think Excel is silently skipping over the error while opening the file.  See my note at the end of my post above. 





    lundi 4 juin 2018 15:05
  • Hi NerdBrick,

    Thanks for the detail info for this issue. 

    What's version of Excel are you using? The document generated by the sample code could be opened well in the Excel 2016 on my side. 

    Regards & Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    mardi 5 juin 2018 01:52
    Modérateur
  • I'm using Excel 2016 version 1708 Build 8431.2250

    Yes, it will open just fine when you open it in Excel directly. The issue I'm noting is you see something interesting when you do a File->Open->Open and Repair (the little down arrow on the "Open" button). You will see the notice that "Errors were detected in file" in the Error XML that will result (See above). 


    Once you open the file in Excel and hit save, the file will be "repaired" and it will be happy in the Spreadsheet compare tool, and the "Open and Repair" wont complain either. 

    Thank you

    mardi 5 juin 2018 02:18
  • Hello NerdBrick,

    How do you generate the file? Do you generate the file from scratch or generate the file based on a workbook?

    As far as I know, if we use OpenXML SDK to generate the file from scratch, we only used limited content to create an "Excel-readable" workbook. Once the workbook is opened in Excel, Excel application will help to finish other part content to make the workbook a "true" workbook.

    I also tried to use code in your shared link for testing. I could use it to create a workbook which size is 1.73KB and after saving it in Excel, the size grows up to 9.24KB. So if the Spreadsheet Compare tool are trying to read content that has not been added, it will cause error.

    For a workaround for this, I would suggest you try to generate the file based on a blank workbook which is generated by Excel.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    jeudi 7 juin 2018 06:34
    Modérateur
  • Hello NerdBrick,

    What's the state of the thread? Is your original issue resolved? If it is, I would suggest you mark ANY helpful reply to close the thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    vendredi 22 juin 2018 06:26
    Modérateur
  • I took this topic over to GitHub, and I believe they are looking at fixing the issue in OpenXML

    https://github.com/OfficeDev/Open-XML-SDK/issues/509

    Thank you
    vendredi 22 juin 2018 15:01