We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Total Rows in Sheet Function?

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

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!

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

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

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.