Cells.MaxDataRow > Cells.Rows.Count?

While parsing through our test spreadsheet, I was dumping various values as a sanity check and noticed the following:

07/15/08 09:48:40 Processing Overview worksheet, 88 rows, 96 max data

This came from the following code:

sMsg = string.Format("Processing {0} worksheet, {1} rows, {2} max data",m_obCurrentWS.Name,m_obCurrentWS.Cells.Rows.Count,m_obCurrentWS.Cells.MaxDataRow+1);

How is it possible for the MaxDataRow index to be greater than the Rows.Count value for the same worksheet? There are, in fact, 96 rows containing data in the worksheet.

Hi,

Well, you need to understand the difference b/w Cells.MaxDataRow and Rows.Count properties.

MaxDataRow: It actually returns the index of the farthest row filled with data.

Count: It actually counts the total number of rows which is initialized in a worksheet. It is to be noted that all the rows with default settings / formattings (standard formattings) are not included in the collection.

To explain you better i give you an example here, attached is the template input file.

Sample code:

Following is the code with some comments. If any one wants to determine: last row index which contains data / formattings, last column index which contains data / formattings, countings of cols / rows filled with data / styles other than those rows / cols with standard settings, total no. of cells filled with data/ styles in a worksheet. Kindly check the template file attached and then utilize the code for better understanding:

Workbook wb = new Workbook();

wb.Open("d:\\test\\tstbk1.xls");

Worksheet ws = wb.Worksheets[0];

int colcount = ws.Cells.Columns.Count;

int rowcount = ws.Cells.Rows.Count;

int maxcol = ws.Cells.MaxColumn;

int maxdatacol = ws.Cells.MaxDataColumn;

int maxdatarowincol = ws.Cells.MaxDataRowInColumn(3);

int maxrow = ws.Cells.MaxRow;

int maxdatarow = ws.Cells.MaxDataRow;

int noofcells = ws.Cells.Count;

// Returns 8 ok... since the columns with standard (default) settings / formatting are not included.

MessageBox.Show("Cols count: " + colcount.ToString());

// Returns 13 ok... since all the rows with default settings / formattings are not included in the collection.

MessageBox.Show("Rows count: " + rowcount.ToString());

// Returns 10 ok... since maximum data/styled column is K and its index is 10.

MessageBox.Show("Max Column index which contains data / style attribute: " + maxcol.ToString());

// Returns 10 ok... since maximum data column is also K and its index is 10.
MessageBox.Show("Max Column index which contains data only: " + maxdatacol.ToString());

// Returns 10 ok...since in the D column the data is filled up to 11th row and row index is 10.

MessageBox.Show("Max row index which contains data in the fourth (D) column: " + maxdatarowincol.ToString());

// Returns 23 ok... since the data / style is filled up to the 24th rows only so the last row index is 23.

MessageBox.Show("Max Row index which contains data / style attribute:" + maxrow.ToString());

// Returns 23 ok... since the data is filled up to the 24th rows only so the last row index is 23.

MessageBox.Show("Max Row index which contains data only" + maxdatarow.ToString());

// Returns 87, since there are total 87 cells filled with data / styles.
MessageBox.Show("Total no. of cells: " + noofcells.ToString());

Hopefully it will give you some insight regarding all the properites mentioned above.

Thank you.

Got it, thanks! Looks like MaxDataRow is what we need, everything else falls into place after that.