Refresh Formula linking to other external workbooks

Aspose.Cells Product Family
1

Hi, I have a workbook with formula linking to other external workbooks, how do I refresh or update the links so that it can show actual values from the other workbooks? I’m using asposecells module for python running with jpype, currently I’m using the below codes to refresh the formula calculation but this only calculates values within the current workbook and not formula that are linked to external workbooks.

My current python code

import asposecells
jpype.startJVM()
from asposecells.api import Workbook

workbook = Workbook(Filepath)
workbook.calculateFormula()
workbook.save(Filepath)

jpype.shutdownJVM()

Appreciate your assistance, thanks!

2

@myDAN,

Yes, by default, Aspose.Cells for Python API does not support to calculate formulas whose source is external workbooks. You may try to use Workbook.updateLinkedDataSource() method for the task if it works. You need to load/open the source (external) file(s) via Workbook object separately. See the example code in the thread for your reference, the sample code is in .NET but you may easily convert it for Python(Java) accordingly.

1 Like
4

Thank you, will try the link you shared

5

Hi I encountered a syntax problem when entering the code into python

invalid syntax (, line 14)
Traceback (most recent call last):
File “”, line 14
workbook.updateLinkedDataSource(Workbook[] { wbdata})
^
SyntaxError: invalid syntax

6

@myDAN,

Could you please share complete sample code with sample Excel files (if any) to reproduce the issue, we will check it soon.

PS. please try latest version/fix (Aspose.Cells for Python via Java v23.1).

7

@myDAN

Please change the code to:

workbook.updateLinkedDataSource([wbdata])
1 Like
8

@myDAN

Also, here is a working example for your reference.
Cell B1 in Book1 is linked to Cell A1 in Book 2:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook

wb1 = Workbook("Book1.xlsx")
wb1.calculateFormula()
print("Book1 origin value: %s" % wb1.getWorksheets().get(0).getCells().get("B1").getStringValue())

wb2 = Workbook("Book2.xlsx")

# updateLinkedDataSource to Book2
wb1.updateLinkedDataSource([wb2])
wb1.calculateFormula()
print("Book1 value after updateLinkedDataSource Book2: %s" % wb1.getWorksheets().get(0).getCells().get("B1").getStringValue())

# change Book2 value
wb2.getWorksheets().get(0).getCells().get("A1").putValue(10)
wb1.calculateFormula()
print("Book1 value after Book2 value changed: %s" % wb1.getWorksheets().get(0).getCells().get("B1").getStringValue())


jpype.shutdownJVM()

Book1Book2.zip (13.0 KB)

1 Like
9

Thanks, this worked in my desktop, but not in the Linux Server environment

10

@myDAN,

What is the issue you got on linux server environment, please elaborate with example code and template files. We will check your issue soon.

1 Like
11

Hi @Amjad_Sahi, I’ve put them in the same folder, now it’s working in the Linux Server

12

@myDAN,
I’m glad your issue has been solved. If you have any questions, please contact us.

1 Like