How to count rows per worksheet in OpenXML

Question

I switched from Interop library to OpenXML, because I need to read large Excel files. Before that I could use:

worksheet.UsedRange.Rows.Count

to get the number of rows with data on the worksheet. I used this information to make a progressbar. In OpenXML I do not know how to get the same information about the worksheet. What I have now is this code:

 using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(path, false))
{
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
    int row_count = 0, col_count;
    // here I would like to get the info about the number of rows
    foreach (Row r in sheetData.Elements<Row>())
    {
        col_count = 0;
        if (row_count > 10)
        {
            foreach (Cell c in r.Elements<Cell>())
            {
                // do some stuff  
                // update progressbar  
            }
        }
        row_count++;
    }
}

Kavindu Dodanduwa · Accepted Answer · 2015-03-06T10:41:26.267

15

It's not that hard (When you use LINQ),

 using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open("PATH", true))
 {
   //Get workbookpart
   WorkbookPart workbookPart = myDoc.WorkbookPart;

   //then access to the worksheet part
   IEnumerable<WorksheetPart> worksheetPart = workbookPart.WorksheetParts;

   foreach (WorksheetPart WSP in worksheetPart)
   {
      //find sheet data
      IEnumerable<SheetData> sheetData = WSP.Worksheet.Elements<SheetData>();       
      // Iterate through every sheet inside Excel sheet
      foreach (SheetData SD in sheetData)
      {
           IEnumerable<Row> row = SD.Elements<Row>(); // Get the row IEnumerator
           Console.WriteLine(row.Count()); // Will give you the count of rows
      }
}

Edited with Linq now it's straight forward.

edited Mar 06 '15 at 10:41

answered Mar 06 '15 at 10:28

Kavindu Dodanduwa

10,909
3
31
44

So, I had to do this looping beforehand? Just to count rows? I ask this, because I in the main loop where I process data I update progressbar, but before that I had to set its Maximum property (which is the number of rows on the worksheet) – Jacobian Mar 06 '15 at 10:39
Sorry posted in a rush... try the newsest update :) Uses LINQ – Kavindu Dodanduwa Mar 06 '15 at 10:42
For your answer.. no you will not need :) My answer provide number of rows available per all the sheets in worksheet.. simply utilize the code provided :) – Kavindu Dodanduwa Mar 06 '15 at 10:43
Ok. I now see. I will check in a minute this aprroach in my code. – Jacobian Mar 06 '15 at 10:46
Only difference in your code and mine is that I iterate through all the sheets :) It would work fine – Kavindu Dodanduwa Mar 06 '15 at 10:54
What if i want to get row count of a single sheet instead of all? – Abhishek Puri Sep 16 '20 at 06:13

How to count rows per worksheet in OpenXML

1 Answers1