none
Why does Excel complain about the table in this workbook? RRS feed

  • Question

  • The enclosed Workbook is valid OOXML as far as I can tell but Excel claims that there is a problem with it and in the error message points at the table XML file. As the author of the library that created the file I would very much like to know if there is anything that I can do to improve compatibility with MS Excel.

    Wednesday, February 27, 2019 1:23 PM

Answers

  • Just following up from our conversation offline. The issue Excel was complaining about was that the <tableColumn> name= attribute was not matching exactly the contents of the cell that represented the header for that column. Excel expects these to be identical. Excel will remove leading and trailing spaces in the name attribute but in the cell value, it didn't. Therefore the mismatch. 

    A defect report to the ISO body has been submitted to add a description that these two must match. 

    Tom

    Tuesday, March 19, 2019 8:52 PM
    Moderator

All replies

  • Hi Charlie, 

    As the author of the library, can you try to narrow down the problem to some more specific area of the file format that you think is causing an issue with Excel? We don't typically take corrupt documents (allegedly corrupt) and try to figure out what is wrong. And it's up to the implementer to be more specific about the file format question. 

    Best regards,
    Tom Jebo
    Sr Escalation Engineer
    Microsoft Open Specifications

    Wednesday, February 27, 2019 5:26 PM
    Moderator
  • Hi Tom,

    the OOXML Productivity Tool thinks the file is valid. Excel complains about the table but comparing the source with the processed version indicates very little difference.

    This is the source of the table.

    <x:table id="1" name="Table11" displayName="Table11" ref="B6:J11" headerRowCount="1" totalsRowShown="0" headerRowDxfId="10" dataDxfId="9" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
      <x:autoFilter ref="B6:J11" />
      <x:tableColumns count="9">
        <x:tableColumn id="33" name="TW Asset ID" dataDxfId="8" />
        <x:tableColumn id="1" name="Asset manufacturer company full name" dataDxfId="7" />
        <x:tableColumn id="4" name="Subsiduary Company / Subsiduary Unit " dataDxfId="6" />
        <x:tableColumn id="34" name="Asset series name or brand name" dataDxfId="5" />
        <x:tableColumn id="13" name="Asset Model and Sub-Model" dataDxfId="4" />
        <x:tableColumn id="3" name="model year" dataDxfId="3" />
        <x:tableColumn id="26" name="Asset unit customization code / ID" dataDxfId="2" />
        <x:tableColumn id="15" name="equipment classification" dataDxfId="1" />
        <x:tableColumn id="19" name="equipment sub-classification" dataDxfId="0" />
      </x:tableColumns>
      <x:tableStyleInfo name="THStyle 4" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0" />
    </x:table>

    Thursday, February 28, 2019 9:27 AM
  • What changes do you make to get it to work? I.e. do a diff to see what changes.

    What version of Excel are you testing with?

    Are your OOXML parts UTF-8 or 16?

    Tom

    Thursday, February 28, 2019 3:02 PM
    Moderator
  • The diff is minimal. I think it possibly has more to do with what's in the worksheet itself. For instance, I've seen Excel happily create tableColumn with name="" but this only works if the referenced cell is using whitespace and you can only preserve this if you use an optional sharedStrings part. I'll post a diff later. Unfortunately I'm not allowed to post the source workbook because I haven't been verified.

    @@ -1,5 +1,5 @@
    -<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"  id="21" name="Table11" displayName="Table11" ref="B6:J11" totalsRowShown="0" headerRowDxfId="10" dataDxfId="9">
    -  <autoFilter ref="B6:J11" />
    +<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" dataDxfId="9" displayName="Table11" headerRowCount="1" headerRowDxfId="10" id="1" name="Table11" ref="B6:J11" totalsRowShown="0">
    +  <autoFilter ref="B6:J11"/>
       <tableColumns count="9">
         <tableColumn id="33" name="TW Asset ID" dataDxfId="8"/>
         <tableColumn id="1" name="Asset manufacturer company full name" dataDxfId="7"/>
    @@ -11,5 +11,5 @@
         <tableColumn id="15" name="equipment classification" dataDxfId="1"/>
         <tableColumn id="19" name="equipment sub-classification" dataDxfId="0"/>
       </tableColumns>
    -  <tableStyleInfo name="THStyle 4" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
    -</table>
    +  <tableStyleInfo name="THStyle 4" showColumnStripes="0" showFirstColumn="0" showLastColumn="0" showRowStripes="1"/>
    +</table>

    • Edited by Charlie Clark Thursday, February 28, 2019 5:09 PM Included diff
    Thursday, February 28, 2019 3:38 PM
  • Please narrow this down to specific change that causes the sheet to load or not load. There are potentially many reasons why Excel wouldn't load a worksheet that may or may not be related to your implementation. 

    Which library are you implementing? 

    Tom

    Saturday, March 2, 2019 7:08 PM
    Moderator
  • The library is, FWIW, openpyxl. I'm aware that the issue probably isn't with the table itself, but that is the report I get from Excel. I have a minimal example (single worksheet with a single table) but I am prevented from the forum from posting it.

    As per https://social.msdn.microsoft.com/Forums/en-US/b37a1c97-7ded-47e8-b5eb-617ad32892c6/impossible-to-add-a-predefined-chart-style-to-charts-where-no-ltapprgt-tag-is-present-in-the?forum=os_binaryfile I'm aware that Excel has requirements above and beyond the OOXML specification. It is these which I'm trying to identify so that I can work with them.

    Monday, March 4, 2019 11:45 AM
  • Hi Charlie, 

    Is it the same document as the one in the other post? If not, please create the smallest possible Excel workbook to reproduce the problem and then if you don't mind either post a link where I can download the file (if it's ok to share publicly) or send to dochelp at Microsoft dot com, referencing the URL of this thread and my name. Once you provide the document, I'll see if I can identify what Excel is complaining about. 

    Best regards,
    Tom Jebo
    Sr Escalation Engineer
    Microsoft Open Specifications
    Monday, March 4, 2019 8:11 PM
    Moderator
  • Just following up from our conversation offline. The issue Excel was complaining about was that the <tableColumn> name= attribute was not matching exactly the contents of the cell that represented the header for that column. Excel expects these to be identical. Excel will remove leading and trailing spaces in the name attribute but in the cell value, it didn't. Therefore the mismatch. 

    A defect report to the ISO body has been submitted to add a description that these two must match. 

    Tom

    Tuesday, March 19, 2019 8:52 PM
    Moderator