Sometimes, opening a large workbook with many formulas can take a long time, as Excel recalculates all formulas before opening the workbook, which leads to a longer processing time. Moreover, when exporting particular worksheets containing formulas or cross-worksheet formulas, Excel requires significant time because it calculates all formulas before completing the export process. To enhance the speed of opening or exporting a large Excel workbook with extensive formulas, DsExcel provides setCalculationMode method within the IFormulaOptions interface. This method allows you to choose from the CalculationMode enumeration options, providing control over how Excel calculates formulas before the workbook is opened or exported. CalculationMode enumeration provides the following three calculation modes:
| Calculation Mode | Description |
|---|---|
| Automatic | In this mode, Excel calculates everything and recalculates whenever something is changed every time a workbook is opened. |
| Semiautomatic | In this mode, Excel calculates everything except Data Tables and Python formulas. |
| Manual | In this mode, Excel calculates nothing; it recalculates only when the user explicitly requests it by pressing F9 or CTRL+ALT+F9 or when the workbook is saved. |
setCalculationMode method does not impact the functioning of the internal calculation engine of DsExcel, and it only affects the calculation mode settings in Excel and SpreadJS I/O. This method does not affect the runtime state of DsExcel. If you want to disable the calculation for the current workbook, use setEnableCalculation method of Workbook class and IWorkbook interface.
Refer to the following example code to set the setCalculationMode to ¡®Manual¡¯ for calculating the ¡®Total¡¯ value:
| Java |
Copy Code |
|---|---|
// Create a new workbook. Workbook workbook = new Workbook(); // Add data for the table. Object data = new Object[][]{ {"Name", "City", "Birthday", "Eye color", "Weight", "Height"}, {"Richard", "New York", new GregorianCalendar(1968, 5, 8), "Blue", 67, 165}, {"Nia", "New York", new GregorianCalendar(1972, 6, 3), "Brown", 62, 134}, {"Jared", "New York", new GregorianCalendar(1964, 2, 2), "Hazel", 72, 180}, {"Natalie", "Washington", new GregorianCalendar(1972, 7, 8), "Blue", 66, 163}, {"Damon", "Washington", new GregorianCalendar(1986, 1, 2), "Hazel", 76, 176}, {"Angela", "Washington", new GregorianCalendar(1993, 1, 15), "Brown", 68, 145} }; // Add data to the range. IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("A1:F7").setValue(data); worksheet.getRange("A:F").setColumnWidth(15); // Add table. worksheet.getTables().add(worksheet.getRange("A1:F7"), true); // Show totals. worksheet.getTables().get(0).setShowTotals(true); worksheet.getTables().get(0).getColumns().get(4).setTotalsCalculation(TotalsCalculation.Average); worksheet.getTables().get(0).getColumns().get(5).setTotalsCalculation(TotalsCalculation.Average); // Add comment to notify the user to calculate the formula manually. IComment comment = worksheet.getRange("F8").addComment("Please press F9 to calculate the formula."); comment.setVisible(true); // Set calculation mode to manual. workbook.getOptions().getFormulas().setCalculationMode(CalculationMode.Manual); // Save the Excel file. workbook.save("CalculationModeOptions.xlsx"); | |
