Excel : Not Loading expected number of columns for each row. RRS feed

  • Question

  • Hello,
    I have an worksheet with an expected number of columns (say 11), not all columns in the worksheet will have values for all the columns however I still need to know that no value was specified for that column.

    I am using the DocumentFormat.OpenXML library to interrogate the excel document using LINQ.

    What happens in my code is that when this worksheet is loaded, each row will have a different number of columns (relating to the number of columns with data in them). For example in one row, if columns 4, 5 and 7 don't have data specified then the total number of column objects that row would have is 8. The data that should have been at column position 6 would then be at column position 4.

    This is causing me a problem as I need to keep the indexes of the columns as I am reading them in, in order to know WHAT column the data that I loading in belongs to.

    Is there a way to either:

    Ensure the order of columns is maintained regardless of whether data is present or not;
    Find out column index (in relation to the 11 columns) a column belongs to.

    Hope someone can provide guidance.
    Tuesday, September 15, 2009 4:59 PM

All replies

  • Hello DavidChristiansen

    Before writing code, check structure of the XML representing your Excel document. Add .zip extension after your file name .xlsx, and doubleclick.

    There you will see


    That's it, column range and column number of each cells.

    Following is part of the XML
      <dimension ref="A1:M8" />
    - <sheetViews>
    - <sheetView tabSelected="1" workbookViewId="0">
      <selection activeCell="J4" sqref="J4" />
      <sheetFormatPr defaultRowHeight="17.399999999999999" />
    - <cols>
      <col min="1" max="1" width="8.796875" style="1" />
      <col min="2" max="2" width="28.5" style="1" customWidth="1" />
      <col min="3" max="9" width="8.796875" style="1" />
      <col min="10" max="10" width="10.8984375" style="1" bestFit="1" customWidth="1" />
      <col min="11" max="16384" width="8.796875" style="1" />
    - <sheetData>
    - <row r="1" spans="1:13">
      <c r="A1" s="2" />
    Wednesday, September 16, 2009 2:16 AM