I used Excel Save As to generate XML but some rows in the XML aremissing

[Roger L Costello <costello@mitre.org>]

Hi Folks,

I have an Excel spreadsheet. If has 16 columns. I did a File >> Save As "XML Spreadsheet 2003 (*.xml)" to generate XML.

I opened the generated XML document. It has a <Row> for the header. That <Row> has 16 child elements, corresponding to the 16 columns in the spreadsheet. And, there is a <Row> corresponding to each row in the spreadsheet. Most of the <Row> elements have 16 child elements, but some have only 15 child elements. 

Yikes!

For some reason, Excel dropped cells in the conversion to XML. But which cell (column) did it drop? Below I show one of the <Row> elements. If you were to count, you would find that there are 15 child <Cell> elements.  One <Cell> element is missing (dropped). Which one?

I noticed ss:Index="9" in one of the <Cell> elements and not any of the others. My guess is that it is trying to tell me that, "Hey, this <Cell> corresponds to column 9 (column I) of the spreadsheet, the previous <Cell> (column H) was dropped". Is that what it's saying? More generally, my question is: How do I determine which column Excel dropped in the conversion to XML? Does it always drop the same column, or does it drop different columns in different rows?

/Roger

<Row>
    <Cell ss:StyleID="s19"><Data ss:Type="Number">0</Data></Cell>
    <Cell ss:StyleID="s20"><Data ss:Type="DateTime">2013-12-18T00:00:00.000</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">3-ROUTINE</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">NOT USED</Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"></Data></Cell>
    <Cell ss:Index="9" ss:StyleID="s22"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"></Data></Cell>
    <Cell ss:StyleID="s22"><Data ss:Type="String">ABC</Data></Cell>
</Row>