Diagnosing Invalid XLSX

Matt Binsfeld 21 Reputation points
2021-11-16T18:32:50.967+00:00

Hi,
Our software generated an xlsx. Trying to open it in excel results in an error and Excel offers to repair the file, and the repaired file appears to be fine.

The repair log indicates that the problem was something in sheet3.xml, so I tried performing a diff between the repaired sheet 3 and the original. However, I discovered nearly every element had been changed. I also attempted to use the Open XML SDK Productivity tool to diagnose what could possibly be wrong with the document, but I didn't have any luck with that either.

What's wrong with the attached xlsx? How can I discover this error on my own in the future? This won't allow me to upload an xlsx, so I've uploaded sheet3.xml from the xlsx instead. Let me know if there is a way to include the whole xlsx. 149857-sheet3.xml

Office Open Specifications
Office Open Specifications
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Open Specifications: Technical documents for protocols, computer languages, standards support, and data portability. The goal with Open Specifications is to help developers open new opportunities to interoperate with Windows, SQL, Office, and SharePoint.
127 questions
Accepted answer
  1. Mike Bowen 1,516 Reputation points Microsoft Employee
    2021-12-01T16:54:54.497+00:00

    Hi @Matt Binsfeld ,

    After digging further into the .xlsx, I found that the issue with sheet3 is that it has several cells with the cached formula results of #CIRCULAR! like the snippet below. #CIRCULAR! is not a valid error and so on load we convert it to a string value. Immediately after load though, recalc runs so the formulas end up recomputing anyway. I replaced all the #CIRCULAR! with #VALUE! (and repaired the two xfids from my first answer) and the file loads fine. 

       <c r="D41" t="e" s="17">  
       <f>SUM(D33:D40)</f>  
       <v>#CIRCULAR!</v>  
   </c>

    In summary, to fix the file, replace the invalid xfid values from styles.xml and replace all the instances of #CIRCULAR! with #VALUE! in sheet3.xml.

    Let me know if that resolves your issue.

    -Michael

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mike Bowen 1,516 Reputation points Microsoft Employee
    2021-11-17T00:01:09.497+00:00

    Hi @Matt Binsfeld ,

    In the styles.xml part from the file you sent there are 2 <xf /> elements with invalid values for the xfId attribute. In ISO-29500 18.8.45 xf (Format) the xfId attribute is defined:
    “For xf records contained in cellXfs this is the zero-based index of an xf record contained in cellStyleXfs corresponding to the cell style applied to the cell.”

    If you look at the xf elements with xpaths
    • /x:styleSheet1/x:cellXfs1/x:xf[151]
    • /x:styleSheet1/x:cellXfs1/x:xf[149]
    You can see that they have indexes of 84 and 85, but if you look at the <cellStyleXfs /> element, it only contains 6 elements, so 84 and 85 are invalid indexes.

    Both full errors are:

    Id Sem_MissingIndexedElement
    Description Element 'DocumentFormat.OpenXml.Spreadsheet.CellFormat' referenced by 'xf@xfId' does not exist in part '/xl/styles.xml'. The index is '84'.
    XPath /x:styleSheet1/x:cellXfs1/x:xf[151]
    Part URI /xl/styles.xml
    NamespacesDefinitions xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"

    Id Sem_MissingIndexedElement
    Description Element 'DocumentFormat.OpenXml.Spreadsheet.CellFormat' referenced by 'xf@xfId' does not exist in part '/xl/styles.xml'. The index is '85'.
    XPath /x:styleSheet1/x:cellXfs1/x:xf[149]
    Part URI /xl/styles.xml
    NamespacesDefinitions xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"

    To find these errors I used an extension for VS Code the OOXML Validator, which validates OOXML files and log the errors.
    I edited the xfId attributes to be valid indexes with this VS Code extension (OOXML Viewer), this fixed the validation error, but there is still an issue with the file. I think it may be because I just picked valid, but random indexes to test with and not what the xfId values should be.
    Can you get your software to replace the invalid indexes and see if this fixes the issue?

    Thanks,
    Michael Bowen
    Escalation Engineer - Microsoft Open Specifications

    1 person found this answer helpful.