That count could be (and probably will be) inaccurate. It indicates the number of rows or columns which have been lazy loaded. A row could be accessed and have nothing changed on it. Just getting a row instance will force it to be created, and that will increment the count of the collection. Or a row can have something changed on it, then the change can be reset, and the count will still include that row.

I'm really keen to not have to create 16 million WorksheetCell objects, and iterate over 16 million cells in order to get the few cells that have values... But fortunately there is a solution.

Cheers,

After loading the rows and columns collection will have entries for all rows and columns that has data. However we can just look at the index property to determine the row and column positions. To get the number of rows and columns look at the index of the last entry.

And in my example I have omitted the check whether the Rows and Columns collections are in fact empty...

Sorry for the formatting...

Martin

jonesmar said:

int numColumns = sheet.Columns[((ICollection<WorksheetColumn>)sheet.Columns).Count - 1].Index + 1;int numRows = sheet.Rows[((ICollection<WorksheetRow>)sheet.Rows).Count - 1].Index + 1; foreach(WorksheetRow row in sheet.Rows) {    foreach(WorksheetCell cell in row.Cells) {        int rowNo = row.Index;        int colNo = col.Index;        object value = cell.Value;        // Process cell...    }}

 I don't see why you would need to get the full number of rows or columns in this code example (maybe you are using those numbers elsewhere though), but getting the count in this was may also be inaccurate, especially if some rows or columns are skipped in the worksheet. Try this instead:

int numRows = 0;
Dictionary<int,object> validColumns = new Dictionary<int,object>();

foreach(WorksheetRow row in sheet.Rows)
{
    bool rowHasData = false;

    foreach(WorksheetCell cell in row.Cells)
    {
        int colNo = cell.ColumnIndex;
        object value = cell.Value;

        if(value != null)
        {
            rowHasData = true;

            // Add an entry to the dictionary if the column had no cells before
            // or overwrite the entry for the column if it already has cells
            validColumns[colNo] = null;
        }

        // Process cell...
    }

    if(rowHasData)
        numRows++;
}

int numColumns = validColumns.Count;

However, this will ignore rows and cells with no data that have formatting. If you would like to get the count that included those rows and cell as well, you would need to modify this slightly.

I just wonder why column's property "HasData" was made private :(

This property is only initialized and used when saving a file, so it would not be useful at run-time. Also, it just indicates whether the column has a non-default width or a non-default cell format, so I don't think it would help you that much. What are you trying to do with the columns?

Thanks, but it can be tricky due to possibly very large file dimensions. If only it was calculated at runtime...

Will try to find a solution :)

You can just loop over a row's Cells collection with a foreach loop. This will give you all cells which have a value or a non-default cell format.

Yes, you're right with a columns, but there is a property for WorksheetRow named "HasCellBlocks", which also isn't accessible.

My problem is when cell is empty but, for example, resized. Then it exists in the Cells collection, but it's Value is null. These cells are often at the bounds, and what I need is to know when I find an empty row: this row is empty at the middle of data (count as error) or it is "end of the document". A property like "HasCellBlocks" could easily tell me if the row have any values.