Decrease the Calculation Time of Cell.Calculate method
Possible Usage Scenarios
Normally, we recommend users to call Workbook.CalculateFormula() method once and then get the calculated values of the individual cells. But sometimes, users do not want to calculate entire workbook. They just want to calculate a single cell. Aspose.Cells provides CalculationOptions.Recursive property which you can set false and it will decrease the calculation time of individual cell significantly. Because when the recursive property is set to true, then all the dependents of cells are recalculated on each call. But when the recursive property is set to false, then dependent cells are calculated only once and are not calculated again on subsequent calls.
Decrease the Calculation Time of Cell.Calculate() method
The following sample code illustrates the usage of CalculationOptions.Recursive property. Please execute this code with the given sample excel file and check its console output. You will find that setting the recursive property to false has decreased the calculation time significantly. Please also read the comments for a better understanding of this property.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
public static void main(String[] args) throws Exception { | |
// Test calculation time after setting recursive true | |
TestCalcTimeRecursive(true); | |
// Test calculation time after setting recursive false | |
TestCalcTimeRecursive(false); | |
} | |
// -------------------------------------------------- | |
static void TestCalcTimeRecursive(boolean rec) throws Exception { | |
String dataDir = Utils.getDataDir(DecreaseCalculationTime.class); | |
// Load your sample workbook | |
Workbook wb = new Workbook(dataDir + "sample.xlsx"); | |
// Access first worksheet | |
Worksheet ws = wb.getWorksheets().get(0); | |
// Set the calculation option, set recursive true or false as per parameter | |
CalculationOptions opts = new CalculationOptions(); | |
opts.setRecursive(rec); | |
// Start calculating time in nanoseconds | |
long startTime = System.nanoTime(); | |
// Calculate cell A1 one million times | |
for (int i = 0; i < 1000000; i++) { | |
ws.getCells().get("A1").calculate(opts); | |
} | |
// Calculate elapsed time in seconds | |
long second = 1000000000; | |
long estimatedTime = System.nanoTime() - startTime; | |
estimatedTime = estimatedTime / second; | |
// Print the elapsed time in seconds | |
System.out.println("Recursive " + rec + ": " + estimatedTime + " seconds"); | |
} |
Console Output
This is the console output of the above sample code when executed with the given sample excel file on our machine. Please note, your output may differ but the elapsed time after setting the recursive property to false will always be less than setting it to true.
Recursive true: 51 seconds
Recursive false: 16 seconds