We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Loading multiple workbooks for dynamic formula calculation in .NET

i have two workbooks. one workbook depends on another workbook for data used in its formula. how can we load both workbooks and dynamically pass on values to do calculation using Aspose.cells features

@janepamela,

See the following sample code for your reference with comments:
e.g.
Sample code:

Workbook book = new Workbook("e:\\test2\\DataBook1.xlsx");
Workbook book2 = new Workbook("e:\\test2\\MyCustomReports.xlsx");
//Suppose you need to update some value in a cell e.g. M2. This cell/data is referenced in some formula in other workbook (book2) sheet.
book.Worksheets[0].Cells["M2"].PutValue(400);
book.CalculateFormula();

book2.UpdateLinkedDataSource(new Workbook[] { book });//these lines are important
book2.CalculateFormula();

//Get the calculated value.
string val1 = book2.Worksheets[0].Cells["G16"].StringValue;//this cell has formula which refers to source workbook's cell (e.g. M2)

Hope, this helps a bit.

I have two excel workbooks, database.xlsx and tool.xlsx. Tool excel depends on database excel data for calculations internally. so i tried as below.:

Workbook book1 = new Workbook(“D:\test\DataBase.xlsx”);
Workbook book2 = new Workbook(“D:\test\Tool.xlsx”);
book2.UpdateLinkedDataSource(new Workbook[] { book1 });
book2.Worksheets[0].Cells[“A2”].PutValue(400);
book2.CalculateFormula();
string val1 = book2.Worksheets[0].Cells[“C2”].StringValue;

when i executed, i could not get the expected output.

but i tried keeping all the data in one excel(tool excel) and i can able to get the expected result.
where as, whether i mention UpdateLinkedDataSource to link the database excel or no,t am getting the wrong result, meaning tool excel is not getting the required information from database excel .
let me know if i miss anything in the above snippet.

@janepamela,
Please make sure the file path(includes name) of your data source workbook is same with the external link in your Tool.xlsx. If not, please reset the data source workbook’s FileName:

Workbook book1 = new Workbook(“D:\test\DataBase.xlsx”);
Workbook book2 = new Workbook(“D:\test\Tool.xlsx”);
book1.FileName="…"; //the external link path used by formulas in Tool.xlsx
book2.UpdateLinkedDataSource(new Workbook[] { book1 });

If you still get incorrect result with this modification, please send us your template files and we will look into it to figure out the issue.