C#: Getting the number of rows/columns with ExcelPackage

Question

I need to read and write data from an Excel spreadsheet. Is there a method to finding out how many rows/columns a certain worksheet has using ExcelPackage? I have the following code:

        FileInfo newFile = new FileInfo(@"C:\example.xlsx");
        using (ExcelPackage xlPackage = new ExcelPackage(newFile)) 
        {
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
        }

I need to iterate through each cell this worksheet has and spit it into a fairly big table, but I don't want to print out blank cells or get an exception. Is there a method resembling worksheet.rowNum or colNum?

Answer 1

I was using sheet.UsedRange on its own but noticed that some cells at the end of the range were blank but still included in the range.

This works, however for efficiency you might be better staring from the last row in the range and counting back to see where your data ends (as opposed to this snippet which starts from the first row!)

        int count = 0;
        E.Range excelRange = sheet.UsedRange;
        object[,] valueArray = (object[,])excelRange.get_Value(E.XlRangeValueDataType.xlRangeValueDefault);
        if (valueArray.GetUpperBound(0) > 1)
        {
            for (int i = 0; i < valueArray.GetUpperBound(0) + 2; i++)
            {
                if (valueArray[i + 2, 1] == null)
                    break;
                else
                    count++;
            }
        }

Answer 2

I have looked up a few websites using ExcelPackage library.
Also, the page on codeplex has a question on - how to get the number of rows/columns?

It seems there isn't a support of it. Sorry, the documentation is not available as well.
You will have to iterate on rows/columns (keeping in mind the max rows/columns the spreadsheet can hold) and check whether the cell contains any value.

See this link - http://nayyeri.net/use-excelpackage-to-manipulate-open-xml-excel-files

Answer 3

I would start with the UsedRange property and then for each Cell in the final Row of the UsedRange do Cell.End(xlUp). This should get you the final cell for each column, the cell which has the maximum row index is the last cell in your true used range.

The UsedRange property can appear wrong because when cells are cleared but not deleted, the UsedRange property is not updated. To make the UsedRange property valid again simply select all the rows and columns after your last cell (so all the blank cells) and go edit->delete.

Answer 4

I just did the following loop to solve the problem. It works fine only if you know how many columns there will be beforehand. Otherwise it would take another loop iteration.

                int totalCells = 0;
                int totalRows = -1;

                do
                {
                    totalRows++;
                } while (worksheet.Cell(totalRows + 1, 1).Value != @"");
                totalCells = totalRows * 12;