Get Precedents returning the first precedent alone

Aspose.Cells Product Family
1

Hi,

In Aspose.Cells.Cell, when ever we are trying to get the precedents of the cell. It is giving the first item alone, not the entire item.

For example:

In the attached screenshot

cell K9 has the formula to sum K6, K7, K8. But

GetPrecedents() method

is getting only the K6. not all the k7 and k8

Regards,

Raj

2

Hi,


Please see the document/ article for your complete reference on how to get Precedents (complete range/list) for a given cell, please refer to the example there for your needs:


If you still have any issue, kindly give us your template Excel file, we will check it soon.

Thank you.
3

Hi,

Thanks for your support and response.

The reference you have shared is not handling in the case of sum of cells (or) elements.

For Example:

1. =(E100+E101)/((SUM(E68:E71)+SUM(D68:D71))/2)

2. =SUM(A1:B10)

Regards,

Rajkumar

4

Hi,

ReferredAreaCollection ret = cell.GetPrecedents();

This statement is not giving all the entire range of elements. It is giving only the first element of the range.

Please get back to us. As soon as possible with example

Regards,

Rajkumar

5

Hi,


What’s wrong with it. I used the following sample code with a simple template file (attached) which has a formula “=(E100+E101)/((SUM(E68:E71)+SUM(D68:D71))/2)” in A1 cell to get its relevant precedents, it works fine. It should give four areas/ranges which it does as per MS Excel. I am using our latest version of the product i.e. v7.7.0.x (Please download and try our latest version/fix : Aspose.Cells for .NET v7.7.0.4. ).
e.g
Sample code:

//Instantiating a Workbook object
Workbook workbook = new Workbook(“e:\test2\Book1_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());
}
}

Here is the output Console which is fine I think:
Sheet1!E100
Sheet1!E101
Sheet1!E68:E71
Sheet1!D68:D71


If you still have any issue or confusion, give us your template Excel file here, we will check it soon.

Thank you.


6

Hi,


Thanks for your immediate response.

I am also getting the same output. But expecting the range to be expand further.

For example:
Sheet1!E68:E71 is a range

My expectation is, It needs to divided into E68, E69,E70 and E71. Because this cells inside the range is precedents of another cell.

like
E68 is precedent of F70
E69 is precedent of F71
E70 is precedent of F72
E71 is precedent of F73

So my aim is to get the source data through continuous traverse of the precedents.

I have attached my excel file for your reference. (I need to get the C7 precedents to be B2, B3 and B4)

Kindly guide me to achieve this by any approach (or) any sample code
7

Hi,


Thanks for the details and sharing template file.

As you are demanding to get indirect precedents for a cell which has other inter dependent formulas to get its linked cells, I am afraid, we only support direct precedents and dependents only, I think MS Excel also does the same. Well, you have to write your own logic and codes to get precedents recursively.



Thank you.