none
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;
    OR
    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

    dimension/@ref
    sheetData/row/c/@r

    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" />
      </sheetView>
      </sheetViews>
      <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" />
      </cols>
    - <sheetData>
    - <row r="1" spans="1:13">
      <c r="A1" s="2" />
    Wednesday, September 16, 2009 2:16 AM