Excel API Library for .NET - Sample Browser | Document Solutions | Calculation mode options
[
フレーム]
Refer to the following example code to learn how to modify the calculation mode option. The calculation mode option changes the behavior of MS Excel and SpreadJS. Setting the CalculationMode as "Manual" will prevent MS Excel or SpreadJS from automatically calculating. Additionally, setting CalculationMode to "Manual" improves the speed of opening workbooks that contain a large number of formulas in MS Excel and SpreadJS. The example code demonstrates changing the calculation mode to "Manual," where formulas are not automatically calculated when opening exported XLSX files in MS Excel.
//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
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}
};
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;
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 to an excel file
workbook.Save("CalculationOptions.xlsx");
' Create a new Workbook
Dim workbook As New Workbook
Dim data(,) As 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}
}
Dim worksheet As IWorksheet = 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
Dim 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 to an excel file
workbook.Save("CalculationOptions.xlsx")