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 CalculationMode property within the IFormulaOptions interface. This property 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. |
CalculationMode property 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 property does not affect the runtime state of DsExcel. If you want to disable the calculation for the current workbook, use EnableCalculation property.
Refer to the following example code to set the CalculationMode to ‘Manual’ for calculating the ‘Total’ value:
| C# |
Copy Code |
|---|---|
// Create a new workbook. var workbook = new Workbook(); // Add data for the table. object[,] data = new object[,]{ {"Name", "City", "Birthday", "Eye color", "Weight", "Height"}, {"Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165}, {"Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134}, {"Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180}, {"Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163}, {"Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176}, {"Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145} }; // Add data to the range. IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["A1:F7"].Value = data; worksheet.Range["A:F"].ColumnWidth = 15; // Add table. worksheet.Tables.Add(worksheet.Range["A1:F7"], true); // Show totals. worksheet.Tables[0].ShowTotals = true; worksheet.Tables[0].Columns[4].TotalsCalculation = TotalsCalculation.Average; worksheet.Tables[0].Columns[5].TotalsCalculation = TotalsCalculation.Average; // Add comment to notify the user to calculate the formula manually. var comment = worksheet.Range["F8"].AddComment("Please press F9 to calculate the formula."); comment.Visible = true; // Set calculation mode to manual. workbook.Options.Formulas.CalculationMode = CalculationMode.Manual; // Save the Excel file. workbook.Save("CalculationModeOptions.xlsx"); | |
