Detecting the max row/column in a worksheet that borders the content of the worksheet

Aspose.Cells Product Family
1

What properties should I use in order to detect the max row/column in an worksheet that basically cover all the content of a worksheet?

MaxDataRow/MaxDataColumn
MaxRow/MaxColumn
MaxDisplayRange

I noticed that none of these functions take into account sparklines.

Thanks

2

Hi,


Please note:

1) To get the last/farthest row/column index which has data in it, please use Cells.MaxDataRow/MaxDataColumn attribute.

2) To get the last/farthest row/column index which has either data or formattings in it, please use Cells.MaxRow/MaxColumn attribute.

3) To retrieve a Range object which is actually based on the 2), use MaxDisplayRange attribute.

Note: the row and column indices are always zero based.


Hope, this helps a bit.

Thank you.
3

Well, these functions don’t take into account other types of objects.

I was hoping that somehow there is an api that takes into account anything that can be included in the spreadsheet.

Thanks

4

Hi:

I attached a spreadsheet that you can test.

private static void TestSparkLine()
{
string fileName = @“C:\temp\spreadsheets\test_sparkline.xlsx”;
Workbook workbook = new Workbook(fileName);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
Console.WriteLine(“cells.MaxColumn:{0}, cells.MaxDataColumn:{1}, cells.MaxDisplayRange.FirstColumn + cells.MaxDisplayRange.ColumnCount:{2}”, cells.MaxColumn, cells.MaxDataColumn, cells.MaxDisplayRange.FirstColumn + cells.MaxDisplayRange.ColumnCount);
foreach (SparklineGroup g in worksheet.SparklineGroupCollection)
{
//Display the Sparklines group information e.g type, number of sparklines items
Console.WriteLine(“sparkline group: type:” + g.Type + “, sparkline items count:” + g.SparklineCollection.Count);
foreach (Sparkline s in g.SparklineCollection)
{
//Display the individual Sparkines and the data ranges
Console.WriteLine(“sparkline: row:” + s.Row + “, col:” + s.Column + “, dataRange:” + s.DataRange);

}
}
}


This function produces the following output:

cells.MaxColumn:0, cells.MaxDataColumn:0, cells.MaxDisplayRange.FirstColumn + cells.MaxDisplayRange.ColumnCount:1
sparkline group: type:Line, sparkline items count:1
sparkline: row:0, col:2, dataRange:Sheet1!A1:A10
sparkline group: type:Column, sparkline items count:1
sparkline: row:1, col:2, dataRange:Sheet1!A1:A10
Done!



5

Hi,


Well, MaxRow/MaxColumn does not include Sparklines, so you have to add a few lines of code to cover it, see the updated sample code.

Sample code:

string fileName = @“e:\test2\test_sparkline.xlsx”;
Workbook workbook = new Workbook(fileName);
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;

int maxRow = cells.MaxRow;
int maxColumn = cells.MaxColumn;
foreach (SparklineGroup g in worksheet.SparklineGroupCollection)
{
int endRow=0;
int endColumn=0;
//Display the Sparklines group information e.g type, number of sparklines items
Console.WriteLine(“sparkline group: type:” + g.Type + “, sparkline items count:” + g.SparklineCollection.Count);
foreach (Sparkline s in g.SparklineCollection)
{
//Display the individual Sparkines and the data ranges
Console.WriteLine(“sparkline: row:” + s.Row + “, col:” + s.Column + “, dataRange:” + s.DataRange);
endRow = s.Row;
endColumn = s.Column;

}

if (endRow > maxRow)
maxRow = endRow;
if (endColumn > maxColumn)
maxColumn = endColumn;

}

Console.WriteLine(“cells.MaxColumn:{0}, cells.MaxRow:{1}”, maxColumn, maxRow);//MaxRow: 9. MaxColumn: 2 - OK
6

Thanks, I actually used MaxDisplayRange instead of MaxRow/MaxColumn because it takes into account shapes as well.

In my mind I think MaxDisplayRange should take into account sparklines and any other content that can be placed in a worksheet.