Issue with full recalc of workbook formulas when calculation chain is enabled

Aspose.Cells Product Family
1

We want to use the setEnableCalculationChain(true) for helping our performance in situation where we recalculate formulas multiple times. I am including a zip project which shows the issue we are facing. Basically, with the formulaSettings for calculationMode and calculationChain which we want to use - we are finding ourselves in a state where Workbook.calculateFormula() does not have the expected result of fully calculating all dependent formulas.

Can you help us to understand how we can save the workbook with all dependents fully calculated using the settings we have in our project demo zip attached?

Thank
asposetest.zip (114.5 KB)

you

2

@cwedwards92,
You need to call Workbook.calculateFormula() before retrieving the actual value. Please add a line to the specified method in your code segment, it will work fine.

public void validateAllFormulasFullyResolved(String outputArtifactPath) throws Exception {

        Workbook outputWb = loadWorkbook(Paths.get(outputArtifactPath));
        Worksheet outputSht = outputWb.getWorksheets().get(WS_NAME);

        String expectedValue = "400.0";       
         outputWb.calculateFormula();
        String actualValue = outputSht.getCells().get("B1").getValue().toString();  
         
         boolean testSucceeded = actualValue.equals(expectedValue);
         if (!testSucceeded) {
             throw new Exception("Test failed");
        }
 }
3

The validateAllFormulasFullyResolved is just there to illustrate the issue. Our actual codebase includes the preceeding code but not validateAllFormulasFullyResolved.

The problem is that the saved workbook (Book2) has dependent formulas which are not resolved/calculated, even though we set calculationMode to automatic and called calculateFormulas (lines 38-39) before we saved the file. This does not mimic Excel behavior.

4

@cwedwards92,

I am still not sure if this is an issue with Aspose.Cells for Java API. The reason is when we just save to Book2.xlsx file to file path and then open the Book2.xlsx file into MS Excel manually, the formula in B1 cells is not calculated automatically (even though Calculation Options is to “Automatic”) unless I click on B1 cell and then press enter to get “400” value. Anyways, could you please zip and attach your output Book2 file here for the code segment.

public String saveOutputWorkbook(Path path) throws Exception {
        var newoutputpath = path.toString().replace("Book1", "Book2");
        wb.save(newoutputpath);
        wb.dispose();
        return newoutputpath;
    }

Then, after a little evaluation, we may log appropriate ticket to resolve the issue.

5

@cwedwards92,

Nevermind. I am able to reproduce the issue now as you mentioned by using his template Excel file and sample code snippet. I found the issue with full recalculation of the workbook formulas when calculation chain is enabled. If we don’t enable the calculation chain, it works fine and we get expected results against formulas. Even the output Book2 is rendered with correct calculated results when we open the file into MS Excel manually.

We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-46324

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

6

What you describe is accurate, and this behavior is not parity with Excel.

In Excel, if I do the same thing: 1) edit in manual calculation mode, 2) switch calculation mode to automatic 3) calculate Workbook by clicking in ribbon and 4) save the file – the file is saved with all cells (including “B1”) fully calculated and reflecting the 400. In our aspose code, as you state the saved file’s B1 is not fully calculated and reflecting the 400.

Why do you want me to provide Book2? It is created at “asposetest/build/resources/main/Book2.xlsx” when you simply run the gradle project.

7

@cwedwards92,

Please refer to my previous reply/post as I have managed to replicate the issue now, so there’s no need to provide any files or additional information. We appreciate your patience as we will be addressing your issue soon. Once there is an update or progress regarding the issue, we will communicate it to you promptly. We aim to resolve this as soon as possible.

Apologies for any inconvenience this may have caused.

8

@cwedwards92

It seems you are using the Java version. So we created one ticket for Java version accordingly and updated this post with it.

9

@cwedwards92,

This is to inform you that your issue (Ticket ID: “CELLSJAVA-46324”) has been resolved. The fix will be included in our upcoming release (Aspose.Cells v25.4) that we plan to release in the first half of April 2025. You will be notified when the next version is released.

10

Thanks guys, we’ll look forward to using v25.4 when it’s released.

11

@cwedwards92,

You are welcome. We’ll ensure you’re promptly informed as soon as the new version is published.