Calculated value from linked workbook is not getting updated

Aspose.Cells Product Family
1

Hi,

We are using Aspose for sometiem now , I am having few issue with linked workboos

I have two workbook wbA & wbB .

Cell B1 in wbB is linked to Cell A1 in wbA . While processing these workbooks Cell B1 always return me old value from cell A1 .

1. Is this possible in Aspose.Cell to calculated linked workbook formula without physically opening the template ? I tried to user CalculateFormula() but that doesn't support external links.

2. Do I have to set some explicit property to invoke this calculation ?

Regards

Pankaj Singh

2

Hi Pankaj,

How could you even possibly do it in MS Excel without opening the files manually.
Aspose.Cells for .NET only supports to set/get the external referenced formula in the workbook.


Thank you.

3

Amjad,

My point is can't I do something like call calculate formula on wbA & then comes back an call the same on wbB .Then read that calculated value like using method CalculaetFormyula which does this without opening files.

I was able to do it with NPOI but thats a open source , I don't want to use it as we already have license for Aspose so I want to continue with that.

Regards

Pankaj Singth

4

Hi Pankaj,

We will get back to you soon.

Thank you.

5

Hi,

Please try the attached version (v4.8.1.9).

We have supported this feature in it.
Please try the following sample code:

Workbook workbook = new Workbook();
workbook.Open(@“F:\FileTemp\Book1.xls”);
workbook.UpdateLinkedDataSource();
workbook.CalculateFormula();
…


Thank you.

6

Hi Amjad,

Thanks for the reply but somehow this didn't work out for me .

Following is the sample code that I have used , I am attaching the template I have used for the following code :

Workbook book = new Workbook();

Workbook book2 = new Workbook();

book.Open("c:\\Demo.xls");

book2.Open("c:\\Demo2.xls");

book.Worksheets[2].Cells[1, 2].PutValue(5000);//This will trigger change is cell value in both the sheets after formulae calculation.

book.CalculateFormula();

book.UpdateLinkedDataSource();

book2.CalculateFormula();

book2.UpdateLinkedDataSource();

book.CalculateFormula();

book2.CalculateFormula();

book.Save("yahoo.xls", SaveType.OpenInExcel, FileFormatType.Excel2003, HttpContext.Current.Response);

I feel the issue is :

Demo.xls is linked to Demo2.xls but during runtime it creates a temp/virtual copy of Demo2 which is located C:\Documents and Settings\sinpank\Local Settings\Temporary Internet Files\Content.IE5\WN4NU1GJ\

but as I am operating on copy available at C:\ it fails to update the virtual copy somehow.

Or maybe syntax I used is wrong ?

Regards

Pankaj Singh

7

Hi Pankaj,

I found the issue after an initial test, we will look into it and get back to you soon. Your issue has been logged into our issue tracking system with an issue id: CELLSNET-12762.

Thank you.

8

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells and refer the following sample code as per your requirement:

Workbook book = new Workbook();
Workbook book2 = new Workbook();
book.Open(@"F:\FileTemp\Demo.xls");
book2.Open(@"F:\FileTemp\Demo2.xls");
book.Worksheets[2].Cells[1, 2].PutValue(5000);
book.CalculateFormula();
Console.WriteLine(book.Worksheets[0].Cells["A2"].DoubleValue);
book2.UpdateLinkedDataSource(new Workbook[] { book });
book2.CalculateFormula();
book.UpdateLinkedDataSource(new Workbook[] { book2 });
book.CalculateFormula();
Console.WriteLine(book.Worksheets[0].Cells["A4"].DoubleValue);

BTW, the method Workbook.UpdateLinkedDataSource is used to get the data from external workbook. We have supported to get the data from the Workbook object in this fix. In the old fix, we just get the data form the file though it’s opened by a Workbook Object.

Thank You & Best Regards,