Cell.GetPrecedents() and Tables

Aspose.Cells Product Family
1

Hello,


If I have the following formula in a cell:

=ROWS(Table1)

and I call the GetPrecedents() method on that cell, the result of the function is an empty collection. Table1 is a table created in Excel. However, if I go to Excel and do a trace precedents on the formula above, it correctly points to the cells in the table.

Could you guys look into whether it would be possible for the Cell.GetPrecedents() method to correctly identify the precedents in an Excel table?

Thanks!
2

Hi,


I have tested your scenario/ case using a simple template Excel file and I observed the issue as you mentioned. If a cell contains formula that involves table/ list object, Aspose.Cells does not return any referred area where as MS Excel does return the Table range. I used a simplest template Excel file which contains a Table and in a cell (e.g A1), it contains formula i.e. “=Rows(Table1)”, I used the following code.
e.g
Sample code:

//Instantiating a Workbook object
Workbook workbook = new Workbook(“e:\test2\Bk_Precedents.xlsx”);
Cells cells = workbook.Worksheets[0].Cells;
Aspose.Cells.Cell cell = cells[“A1”];
//Tracing precedents of the cell A1.
//The return array contains ranges and cells.
ReferredAreaCollection ret = cell.GetPrecedents();
//Printing all the precedent cells’ name.
if (ret != null)
{
for (int m = 0; m < ret.Count; m++)
{
ReferredArea area = ret[m];
StringBuilder stringBuilder = new StringBuilder();
if (area.IsExternalLink)
{
stringBuilder.Append("[");
stringBuilder.Append(area.ExternalFileName);
stringBuilder.Append("]");
}
stringBuilder.Append(area.SheetName);
stringBuilder.Append("!");
stringBuilder.Append(CellsHelper.CellIndexToName(area.StartRow, area.StartColumn));
if (area.IsArea)
{
stringBuilder.Append(":");
stringBuilder.Append(CellsHelper.CellIndexToName(area.EndRow, area.EndColumn));
}


Console.WriteLine(stringBuilder.ToString());
}
}


I have logged a ticket with an id “CELLSNET-42731” for your issue. We will look into it soon.

Once we have any update on it, we will let you know.

Thank you.
3

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.1.0.2 and let us know your feedback.