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++;
    }
}

score 17 · Accepted Answer · edited Oct 12 '22 at 07:34

17

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 Oct 12 '22 at 07:34

Hossein Golshani

1,847
5
16
27

answered Mar 06 '15 at 10:28

Kavindu Dodanduwa

12,193
3
33
46

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