none
Corrupted Excel 2007 Sheets Through Office Primary Interop Assemblies RRS feed

  • Question

  • Hello,

    I am looking for some help investigating an issue where some sheets within an Excel 2007 file are corrupted when created or written to using .Net and the Office Primary Interop Assemblies. Below are notes on the configuration and questions I am trying to answer. Any input/tips would be greatly appreciated. Thank you.

    What else should I be looking for???

    Configuration notes:

    1. Windows 7, Office 2007, Visual Studio 2012, Office 2010 Primary Interop Assemblies installed
    2. Error when corrupted sheets exist
    3. Allowing Excel to fix the sheets is successful
    4. This is an intermittent issue and thus far there is not a way to replicate it consistently

    Questions with notes:

    1. Does it matter that the Office PIA version is newer than the installed Office?
    2. How can I find the sheet names from within the XML?
      • Message: 'Repaired Records: View from /xl/worksheets/sheet63.xml part'
      • The XML within the ‘sheet63.xml’ does not appear to have the name of the sheet itself
        • The ‘codeName’ within ‘sheet63.xml’ states ‘Sheet75’ instead of ‘63’

    3. How can I tell which PIA version is being referenced if multiple are installed on the computer?
      • Visual Studio 2012 with Office PIA’s are installed: C:\Program Files (x86)\Microsoft Visual Studio 11.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Excel.dll
        • A search on the drive found this as the only occurrence
        • This is for Office 2010
      • The GAC shows the PIA for Excel 2007:
        • Microsoft.Office.Interop.Excel, Version=12.0.0.0
        • Microsoft.Office.Tools.Excel.v9.0, Version=9.0.0.0
        • Microsoft.VisualStudio.Tools.Office.Excel.AddInAdapter.v9.0, Version=9.0.0.0
        • Microsoft.VisualStudio.Tools.Office.Excel.AddInProxy.v9.0, Version=9.0.0.0
        • Microsoft.VisualStudio.Tools.Office.Excel.HostAdapter.v10.0, Version=10.0.0.0
        • Policy.11.0.Microsoft.Office.Interop.Excel, Version=12.0.0.0
      • ‘Microsoft Visual Studio 2010 Tools for Office Runtime (x64)’ is listed within ‘Programs and Features’
      • The visual studio project references:
        • Path = C:\Windows\assembly\GAC\Microsoft.Office.Interop.Excel\12.0.0.0
        • Runtime Version = v1.1.4322
        • Version = 12.0.0.0

    • Edited by sammye99 Friday, February 3, 2017 12:52 PM
    Thursday, February 2, 2017 11:42 PM

Answers

  • To close the loop on this... There appears to be an issue with the Office PIA's and Excel 2007 when multiple/all cells are selected within a sheet and the workbook is saved. All of the corruption errors pointed to sheets (within the same workbook) which had multiple cells selected. I resolved this by ensuring only a single cell is selected within each sheet and saving the workbook. Disclaimer: the corrupted files were intermittent so this may not be the true issue and resolution.
    • Marked as answer by sammye99 Friday, March 24, 2017 2:37 PM
    Friday, March 24, 2017 2:37 PM

All replies

  • Hi sammye,

    Your image is broken, we could not see it.

    Could you share us your code to crate and write? When you get this error again, before you repairing it, I would suggest you mark a copy of it, and then repair. After that, comparing them by Open XML SDK 2.5 for Microsoft Office to check the difference.

    For getting sheet names, you could check /xl/workbook.xml-><>x:workbook(Workbook)-><>x:sheets(Sheets)  by Open XML SDK 2.5 Tools.

    Here is a screen shot.

    Best Regards,

    Edward


    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.

    Friday, February 3, 2017 5:56 AM
  • I uploaded the screenshot again. Thanks for the OpenXML SDK link. I will give it a try and report back.
    Friday, February 3, 2017 12:53 PM
  • Below or some screenshots of original (unfixed, left side) and fixed (right) differences. Some notes:

    1. 'sheetPr' and 'cols' sections are removed in the fixed, along with a number of other sections/properties (highlighted in red)

    2. 'customFormat' property is removed in the fixed

    3. 'pageSetup' and 'headerFooter' are removed in the fixed

    Top of file:

    Bottom of file:

    Friday, February 3, 2017 6:52 PM
  • Hi sammye,

    Have you set sheet property, custom format, pageSetup or header in your code?

    I think it would be better if you could share us your code to create such a corrupted file, and then we could try to reproduce your issue.

    Best Regards,

    Edward


    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.

    Monday, February 6, 2017 6:27 AM
  • Below is the code to create a new sheet within the existing Excel workbook.Please note that an existing Excel file is always started with, then sheets copied within it.I assume the format, pageSetup, and header should all be copied from the previous sheet. Correct? How can I verify this?

    Note: Only some of the sheets within the workbook are becoming corrupted, not all of them.

    I have been looking through the error message and matching up the names to sheets (using the Open XML tool, thanks!!!) and the issue may be isolated to a few specific sheets. I am going to look at a couple other corrupted files to see if the same sheets pop up.

                    try
                    {
                        sheet = (Excel.Worksheet)sheets.get_Item(selectedSheet);
                        sheet.Activate();
                        sheet.Copy(Type.Missing, sheets.get_Item(selectedSheet));
                        string newSheetCopyName = selectedSheet + " (2)";
                        sheet = ((Excel.Worksheet)sheets[sheet.Index + 1]);
                        sheet.Activate();

                        // sheet name cannot be greater than 31 char's long
                        if (selectedSheet.Length > 26)
                        {
                            selectedSheet = selectedSheet.Remove(26);
                        }
                        sheet.Name = newSheetName;
                        sheet = (Excel.Worksheet)sheets.get_Item(newSheetName);

                        sheet.Activate();

    Monday, February 6, 2017 3:06 PM
  • Hi sammye,

    As you found, it seems your issue is related with specific sheets. It would be much helpful if you could share us the excel file with this sheet, and then we could try to make a test.

    Best Regards,

    Edward


    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.

    Tuesday, February 7, 2017 6:03 AM
  • To close the loop on this... There appears to be an issue with the Office PIA's and Excel 2007 when multiple/all cells are selected within a sheet and the workbook is saved. All of the corruption errors pointed to sheets (within the same workbook) which had multiple cells selected. I resolved this by ensuring only a single cell is selected within each sheet and saving the workbook. Disclaimer: the corrupted files were intermittent so this may not be the true issue and resolution.
    • Marked as answer by sammye99 Friday, March 24, 2017 2:37 PM
    Friday, March 24, 2017 2:37 PM