Update dependent connections cell value

Aspose.Cells Product Family
1

Hi,

I’m working on a case where, I have 3 excels
E1 → A1(cell) → 1
E2 → A1(cell) → E1:A1
E3 → A1(cell) ->E2:A1

If I update E1 → A1 → 2, How can I see the change in E3 → A1.

Is there a option to do so?

Thanks.

2

Can you please provide more details about the programming language you are using and how you are currently updating the cell values in the Excel files?

3

We’re using .NET and we don’t have an implementation on updating values

4

@sitaram.bulusu
Dependency updates can be implemented based on formulas. Would you like to provide a detailed description of your needs? If you could manually create sample files and take screenshots to illustrate the specific situation, it would be very helpful for us to locate the issue and provide a solution. We will check it soon.

5

Hi @John.He

Here are the files,
image.png (10.5 KB)

image.png (5.6 KB)

image.png (11.1 KB)

If you see second excel having a reference to first excel and third excel is having a reference to the second excel.

If I update the value in first excel, the third excel should recompute with the updated value.

6

@sitaram.bulusu
By using the provided image information to create three empty sample files and testing them on the latest version v24.8, we can implement the requirements through the following sample code. Please refer to the attachment. emptyfiles.zip (15.7 KB)

The sample code as follows:

Cell c3 = null;
Workbook book1 = new Workbook(filePath + "1.xlsx");
book1.FileName = "1.xlsx";
c3 = book1.Worksheets[0].Cells["C3"];
c3.Value = 989;
Console.WriteLine("c3 value in 1.xlsx: " + c3.Value);


Workbook book2 = new Workbook(filePath + "2.xlsx");
book2.FileName = "2.xlsx";
c3 = book2.Worksheets[0].Cells["C3"];
c3.Formula = "='[1.xlsx]Sheet1'!$C$3 + 1";
book2.UpdateLinkedDataSource(new Workbook[] { book1 });
book2.CalculateFormula(new CalculationOptions() { LinkedDataSources = new Workbook[] { book1 } });
Console.WriteLine("c3 value in 2.xlsx: " + c3.Value);


Workbook book3 = new Workbook(filePath + "3.xlsx");
book3.FileName = "3.xlsx";
c3 = book3.Worksheets[0].Cells["C3"];
c3.Formula = "='[2.xlsx]Sheet1'!$C$3 + 1";
book3.UpdateLinkedDataSource(new Workbook[] { book1, book2 });
book3.CalculateFormula(new CalculationOptions() { LinkedDataSources = new Workbook[] { book1, book2 } });
Console.WriteLine("c3 value in 3.xlsx: " + c3.Value);

Console.WriteLine("update value==========");
//update value
book1.Worksheets[0].Cells["C3"].Value = 2000;
Console.WriteLine("new c3 value in 1.xlsx: " + book1.Worksheets[0].Cells["C3"].Value);

//get values
book2.UpdateLinkedDataSource(new Workbook[] { book1 });
book2.CalculateFormula(new CalculationOptions() { LinkedDataSources = new Workbook[] { book1 } });
Console.WriteLine("new c3 value in 2.xlsx: " + book2.Worksheets[0].Cells["C3"].Value);

book3.UpdateLinkedDataSource(new Workbook[] { book1, book2 });
book3.CalculateFormula(new CalculationOptions() { LinkedDataSources = new Workbook[] { book1, book2 } });
Console.WriteLine("new c3 value in 3.xlsx: " + book3.Worksheets[0].Cells["C3"].Value);

The ouput result:

c3 value in 1.xlsx: 989
c3 value in 2.xlsx: 990
c3 value in 3.xlsx: 991
update value==========
new c3 value in 1.xlsx: 2000
new c3 value in 2.xlsx: 2001
new c3 value in 3.xlsx: 2002

Hope helps a bit.

7

Thanks for the code and the explanation. I’ll try it out and get back if I have any other questions

8

@sitaram.bulusu
Please take your time to try the suggested solutions. Hopefully, your issue will be sorted out. Please let us know your feedback. If you have any questions, please feel free to contact us at any time.