sheet 1: Has raw data, it is “A,B,C”
sheet 2: Use formula to lookup data in sheet 1 (it is “A,B,C”)
sheet 3: Use TEXTSPLIT to split the data in sheet 2 (supposed cell A1=A, B1 = B, C1 = C)
sheet 4: Use TEXTJOIN to join the data in sheet 3
Then use the code below to evaluate this Excel, the result is incorrect (Only A1 of sheet 3 have value while B1 and C1 are blank, refer as incorrect_result.PNG)
But we found that if use the version that call the “refreshDynamicArrayFormulas” and “calculateFormula” functions multiple times, the result is correct now (refer as correct_result.PNG)
package com.ura.cms.api.test;
import com.ura.cms.api.TestApplication;
import com.aspose.cells.*;
import com.ura.cms.api.model.*;
import com.ura.cms.api.service.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.stereotype.Component;
import java.io.ByteArrayInputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.Date;
@Component
@Slf4j
public class TestAspose {
public static void main(String[] args) throws Exception {
System.setProperty("spring.devtools.restart.enabled", "false");
System.setProperty("app_mode", "test");
ConfigurableApplicationContext context = SpringApplication.run(TestApplication.class, args);
try {
context.getBean(TestAspose.class).test();
} catch (Exception e) {
e.printStackTrace();
}
context.close();
}
public void test() throws Exception {
String dir = "/Users/wilson.li/Temp/20241129";
String in_path = Paths.get(dir, "in.xlsx").toString();
byte[] file_content = Files.readAllBytes(Paths.get(in_path));
Workbook wb = new Workbook(new ByteArrayInputStream(file_content));
Worksheet ws = wb.getWorksheets().get(0);
ws.getCells().get(3, 0).setValue("TEST");
ws.getCells().get(3, 1).setValue("A,B,C");
wb.calculateFormula();
wb.refreshDynamicArrayFormulas(true);
// This version does not work. But if uncomment below, it works
//wb.calculateFormula();
//wb.refreshDynamicArrayFormulas(true);
//wb.calculateFormula();
//wb.refreshDynamicArrayFormulas(true);
//wb.calculateFormula();
//wb.refreshDynamicArrayFormulas(true);
Date currentDate = new Date();
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String timestamp = formatter.format(currentDate);
String out_path = Paths.get(dir, "out" + "_" + timestamp + ".xlsx").toString();
wb.save(out_path);
}
}
It seems that you are experiencing issues with the TEXTSPLIT function not refreshing properly in your Excel file when evaluated using Aspose.Cells. Based on your description, the problem arises when you evaluate the Excel file with TestAspose_call_once.java, resulting in incorrect output, while using TestAspose_call_many.java with multiple calls to refresh the dynamic array formulas yields the correct result.
To address this issue, it is important to understand how Aspose.Cells handles formula calculations. The library has an embedded formula calculation engine that can calculate formulas imported from a designer file as well as those added at runtime. When you call the CalculateFormula method on the Workbook or Worksheet, it processes all formulas in the file.
In your case, it appears that the TEXTSPLIT function requires multiple refreshes to yield the correct output. This can be due to the way dynamic array formulas are handled in Aspose.Cells. To ensure that your formulas are calculated correctly, you might want to enable the calculation chain, which can improve performance when recalculating formulas multiple times.
Here’s a brief outline of what you can do:
Enable Calculation Chain: Before performing calculations, set FormulaSettings.EnableCalculationChain to true. This allows the library to optimize the calculation process for subsequent calls.
Call Refresh Methods: Use refreshDynamicArrayFormulas and calculateFormula multiple times as you did in TestAspose_call_many.java. This approach seems to resolve the issue in your case.
Direct Calculation: If you need to calculate specific formulas without adding them to the worksheet, you can use the Calculate method on the Worksheet class.
Here is a sample code snippet to illustrate how you might set this up:
// Load the workbook
Workbook workbook = new Workbook("input.xlsx");
// Enable calculation chain
workbook.getSettings().getFormulaSettings().setEnableCalculationChain(true);
// Perform calculations
workbook.calculateFormula();
// Refresh dynamic array formulas multiple times
for (int i = 0; i < 3; i++) {
workbook.getWorksheets().get(2).refreshDynamicArrayFormulas();
workbook.calculateFormula();
}
// Save the output
workbook.save("output_call_many.xlsx");
This code snippet demonstrates how to enable the calculation chain and perform multiple refreshes to ensure that the formulas are calculated correctly.
If you continue to experience issues, consider checking the specific implementation of the TEXTSPLIT function and ensure that all dependencies are correctly set up in your Excel file.
Thanks for your advise. But the issue is that the Excel provided is just one case. We do not know exactly the number of reference of the dynamic formula the Excel will have.
Or if we assume the maximum number of reference of the dynamic formula, said 100, will this cause performance degrade?
After initial testing, I am able to reproduce the issue as you mentioned by using your template Excel file and sample code snippet. I found if I call the “refreshDynamicArrayFormulas” and “calculateFormula” functions multiple times, the results are correct but there should not be any need for calling the functions multiple times. To investigate 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-46188
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.
For refreshDynamicArrayFormulas(boolean calculate), as its api doc describes, it does not calculate the referenced formulas recursively. In your sample case, the dynamic array formula is at Sheet3!A1 and it references to Sheet2!A1 which is a formula too. When you refreshing Sheet3!A1, Sheet2!A1 will not be re-calculated, so the value used by the dynamic array formula is the original value instead of the new one you updated by
ws.getCells().get(3, 1).setValue("A,B,C");
To get the correct result, one way is just like the commented part in your code, calculating the workbook before refreshing dynamic array formulas, and after the refreshing, re-calculate the workbook again.
Another way is using CalculationOptions with true value for Recursive property(in fact it is the default value of this property):
CalculationOptions copts = new CalculationOptions();
copts.setRecursive(true); //the default value is true, so this line is needless in fact
wb.refreshDynamicArrayFormulas(true, copts);
wb.calculateFormula();
wb.save(...);
By adding the CalculationOptions option, we can now automatically refresh the Excel for TEXTSPLIT. But still found this works only for case that split horizontally like TEXTSPLIT(A1,“,”), while case that split vertically like TEXTSPLIT(A1, , “,”) still not work.
If you still find the issue, kindly do share your complete sample (runnable) code and template Excel file (if any) to reproduce the issue on our end, we will check it soon.