Total Rows in Sheet Function?

Aspose.Cells Product Family
1

Hi,
I am looking for a function that returns the last possible row number in a sheet.
I do not need in the last row that has data in it, rather I want the actual last row. Is there a function that does this?

Thanks,

Shane

2

Hi,

Well, you should use Cells.MaxRow attribute if you need to get the farthest row index (zero based) of the cell with data or only formatting/style applied.

e.g

Workbook workbook = new Workbook();
workbook.Open(@“e:\test\Book1.xlsx”);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;

//Get the last row index which contains data or style.
int mrow = cells.MaxRow;
//Get the farthest column index which contains data or formatting
//Suppose the last column which contains data is U but there is some background color applied in //Z10 column (and there is no data in it). It is to be noted here the MaxColumn would give you 25 value here.
int mcol = cells.MaxColumn;


By the way if you want to know how many rows are possibly there exactly in a worksheet, please check the stats below:

1) In a worksheet for an XLS file (MS Excel 97 - 2003), there would be 65536 rows
2) In a worksheet for an XLSX file (MS Excel 2007), it has maximum of 1048576 rows.


Thanks for your understanding!

3

Thanks for the reply - but I already use MaxRow elsewhere, and this is not what I need in this case - and I don’t really want to hardcode the values unless I have to.

For the maximum rows I can achieve the desired result by using CreateRange(0,1,TRUE).RowCount.
But I cannot find a way to do the same thing for columns.

In the Microsoft Excel Interop, you can use the function Range(“A1”).End(xlDown/xlToRight) to get the last column or row, but the same End function in Aspose does not do this - rather it seems to return the same result as MaxColumn or MaxRow. Is this intentional, or should it follow the same behaviour as the Microsoft API?

Thanks,

Shane

4

Further to this, if I use CreateRange(0,1,TRUE) to hide most of the rows on a worksheet, the resultant size of the saved file is almost 3MB, compared to about 100KB when you perform the same procedure using the Microsoft Interop.

Furthermore, even if I manually clear and delete all the cells in the worksheet, the file size does not reduce. This looks like a problem in the way that Aspose.Cells is working to me. Could you try this yourself and let me know what you think about it.

Thanks,

Shane

5

Hi,

Well, the CreateRange(0,1,TRUE).RowCount should be the limitation of MS Excel. Do you need to get the limitation, here is the list of internal constants used for your reference:

internal const int MaxRow = 0x100000 - 1;//0xFFFFF
internal const int MaxColumn = 16383;//0x3FFF
internal const int MaxRow2003 = ushort.MaxValue;
internal const int MaxColumn2003 = byte.MaxValue;
internal const string MaxColumnName = “XFD”;

The MaxRow and MaxColumn are the limitation of MS Excel 2007. MaxRow2003 and MaxColumn2003 are the limitation of MS Excel 2003.

Thank you.