MaxDataRow returns wrong results

Aspose.Cells Product Family
1

Dear,


I am using 8.6.1.6 version of aspose.cells. I am using MaxDataRow property to get the rows containing some data. There are 7 rows that contains some data and the result should be 6 as per documentation. But it returns 8 in maxdatarow. Please find the attached excel and code snippet i m using.

var wb = new Workbook(“D:/AsposeTest/201805.xlsx”);
var sheet = wb.Worksheets[0];
int totalRowsToCheck = (sheet.Cells).MaxDataRow;
Console.Write(totalRowsToCheck);

Kind Regards
Naveed Anjum
2

Hi,


Thanks for providing us template file and sample code.

Well, I have tested your scenario/ case using your sample code with template file a bit. Aspose.Cells rightly gives you “8” as farthest row index for your code. If you could open your template file “201805.xlsx” into MS Excel manually, you will see/confirm that the cell “O9” has a space char in it and is not null/empty, so Aspose.Cells gives you “8” for Cells.MaxDataRow attribute.

Hope, this sorts out your doubt/confusion on Aspose.Cells APIs part.

Thank you.

3

Oh i see. Well i only want to read the excel that matches the pattern of row number 7 and want to ignore rest of the rows that comes after. Can you please tell me the code snippet that i can use to do that. Your help is highly appreciated.


Kind Regards
Naveed Anjum
4

Hi Naveed,


You can create a range of cells and iterate over it to read all cells from the range. Please check following piece of code for elaboration.

C#

var book = new Workbook(dir + “201805.xlsx”);
//Get enumerator from an object of Range
IEnumerator rangeEnumerator = book.Worksheets[0].Cells.CreateRange(“A1:U7”).GetEnumerator();
//Traverse cells in the range
while (rangeEnumerator.MoveNext())
{
var cell = rangeEnumerator.Current as Aspose.Cells.Cell;
Console.WriteLine(cell.Name + " " + cell.Value);
}
5

Dear Baber,


The issue is that rows in this excel can vary. it will not be 7 always as there can be more employees in the excel. so i just want to read it until it matches the pattern of row that has the totals which in above case is row number 7. So i am looking for some pattern that should read until row having total is found excluding first five rows as they will always be the same(values can vary).

Kind Regards
Naveed Anjum
6

Hi Naveed,


Thank you for the clearification.

Please check the following piece of code which uses the Aspose.Cells’ Find & Search Data option to look for the string TOTAL and creates a range based on the row at which the specified string is found.

C#

var book = new Workbook(dir + “201805.xlsx”);
//Get Cells collection
Cells cells = book.Worksheets[0].Cells;
//Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();
//Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = 0;
ca.EndRow = cells.MaxDataRow + 1;
ca.EndColumn = cells.MaxDataColumn + 1;
//Set cells area for find options
findOptions.SetRange(ca);
//Set searching properties
findOptions.SearchNext = true;
findOptions.SeachOrderByRows = true;
//Set the lookintype, you may specify, values, formulas, comments etc.
findOptions.LookInType = LookInType.Values;
//Set the lookattype, you may specify Match entire content, endswith, starwith etc.
findOptions.LookAtType = LookAtType.EntireContent;
//Find the cell with value
int rowIndex = cells.Find(“TOTAL”, null, findOptions).Row;
Range range = book.Worksheets[0].Cells.CreateRange(“A1:U” + (rowIndex+1)); //Added 1 to rowIndex because it is zero based
Console.WriteLine(range.RefersTo);
//Get enumerator from an object of Range
IEnumerator rangeEnumerator = range.GetEnumerator();
//Traverse cells in the range
while (rangeEnumerator.MoveNext())
{
var cell = rangeEnumerator.Current as Aspose.Cells.Cell;
Console.WriteLine(cell.Name + " " + cell.Value);
}

7

Hi Baber,


Thanks for prompt reply. i checked the above code and cells.MaxDataRow returns 8 although it should return 6. it returns 8 because there is one space in O-9 column and i think that is done by mistake from our customers. I just want to ignore the cells that has spaces that we have in above case. What you suggest should we trim the spaces and then read the property maxDataRow? If then how we can do that in code other then the cells that have data.

Kind Regards
Naveed Anjum
8

Hi Naveed,


Thank you for writing back.

Please note, in a real time scenario, a customer may put one empty space (or more) by mistake that will consequently change the MaxDataRow property. It will not be appropriate to check all cells & remove spaces as you have to tackle the case with all possible number of spaces that can be inserted. Moreover, as you already have a few cells with no values so you definitely wish to skip them. I think my previously provide approach is more doable as you can get the exact number of row containing the string TOTAL which is expected to appear at the bottom of all data.
9

Hi Baber,


Yes you are right. i will use the first one to read the values until total. Thank you so much for your kind help.

Kind Regards
Naveed Anjum
10

Hi Naveed,


Thank you for your understanding. Please feel free to contact us back in case you need our further assistance with Aspose APIs.