Calculation engine

xlsgen > overview > Calculation engine

xlsgen has a built-in calculation engine, which updates formulas in real-time or on-demand. A number of scenarios are enabled.

The page related to formulas in the documentation has a special column which marks all built-in Excel functions that are supported by xlsgen so far. A subset of the over 200 built-in Excel functions, and support for other functions is being added on a case by case basis.

If, upon closing a workbook in Excel that was generated by xlsgen, Excel brings a prompt saying "Microsoft Excel recalculates formulas when opening files last saved by an earlier version of Excel (than the one currently running)", then you may avoid this behavior by setting the appropriate Excel target version. See here.

In any case, xlsgen does not currently provide calculation support for the following :

user-defined formulas such as VBA macros or external XLL addins.

Whenever a cell is updated by code

By default, formulas referencing that cell are not automatically updated. If the retained mode is enabled however, formulas are automatically updated. If the formulas are not automatically updated, the user can still get the updated formula values by making an explicit CalculateFormulas() call (see below).

Whenever a formula is updated by code

Same behavior than regular cells.

Whenever the workbook is closed

The formulas are automatically calculated. Any formula containing a function not currently supported by xlsgen is marked dirty so that it gets automatically recalculated by Excel next time the workbook is opened.

Whenever the user would like to update a formula

The granularity of explicit recalculation calls is threefold :

at the cell level, the worksheet object exposes a CalculateFormula(row, col) method which applies to a particular cell.
at the worksheet level, the CalculateFormulas() method recalculates all the formulas of that worksheet. Note that any formula stored in a different worksheet being dependent from the formulas in the worksheet are also recalculated.
at the workbook level, the CalculateFormulas() method recalculates all the formulas of the entire workbook.

Whenever a formula is recalculated

A formula error interface is available to the user. See IXlsFormulaError. The IXlsFormulaError interface can be accessed from the LastFormulaError property of the IXlsCalculationOptions interface, which itself is exposed by the workbook.

The calculation engine can be configured to throw an error when a formula calculation error occurs, or remain silent. By default, formula errors are kept silent. See the TreatErrorsAsCodeErrors property exposed by the IXlsCalculationOptions interface, which itself is exposed by the workbook.

How to enable/disable the auto-recalc mode (retained mode)

The AutoRecalc property, set to false by default, is available from the IXlsCalculationOptions interface, which itself is exposed by the workbook.

To show how all of this works, a sample code is provided :

Java code


XlsEngine engine = new XlsEngine("./../../../xlsgen.dll");

// we open a regular Excel file, and calculate it
//  then we duplicate the worksheet, make a change, and recalculate it

XlsWorkbook workbook = engine.New("calculation_engine.xls");

// make sure that there will be no prompt when this document gets closed from Excel
workbook.putExcelTargetVersion(xlsgen.excelversion_2003);



XlsWorksheet wksht001 = workbook.AddWorksheet("calculations");

wksht001.putRowHeight(10, 13);
wksht001.putRowHeight(11, 13);
wksht001.putLabel(4,2, "");
wksht001.putNumber(4,3, 2003);
wksht001.putNumber(4,4, 2004);

wksht001.putLabel(5,2, "January");
wksht001.putNumber(5,3, 100);
wksht001.putNumber(5,4, 105);

wksht001.putLabel(6,2, "February");
wksht001.putNumber(6,3, 80);
wksht001.putNumber(6,4, 120);

wksht001.putLabel(7,2, "March");
wksht001.putNumber(7,3, 120);
wksht001.putNumber(7,4, 140);

wksht001.putLabel(8,2, "Total");
wksht001.putFormula(8,3, "=SUM(C5:C7)"); // = C5 + C6 + C7 = 100 + 80 + 120 = 300
wksht001.putFormula(8,4, "=SUM(D5:D7)"); // = D5 + D6 + D7 = 105 + 120 + 140 = 365




XlsWorksheet wksht002 = wksht001.Duplicate("calculations 2");

// number1 should be 0 since the formula is not calculated yet

int number1 = wksht002.getNumber(8, 3); 


wksht002.putNumber(7,3, 150);

// number2 should be 0 since auto recalc is off and we haven't explicitely asked for calculations

int number2 = wksht002.getNumber(8, 3);


wksht002.CalculateFormulas();

// number3 should be 330 (= C5 + C6 + C7 = 100 + 80 + 150)

int number3 = wksht002.getNumber(8, 3);


workbook.getCalculationOptions().putAutoRecalc(true);

wksht002.putNumber(7,3, 180);

// number3 should be 360 (= C5 + C6 + C7 = 100 + 80 + 180)

int number4 = wksht002.getNumber(8, 3);

workbook.Close();

VB code


' we open a regular Excel file, and calculate it
'  then we duplicate the worksheet, make a change, and recalculate it

Dim engine As xlsgen.CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim workbook As IXlsWorkbook
Set workbook = engine.New("calculation_engine.xls")

' make sure that there will be no prompt when this document gets closed from Excel
workbook.ExcelTargetVersion = xlsgen.enumExcelTargetVersion.excelversion_2003


Dim wksht001 As IXlsWorksheet
Set wksht001 = workbook.AddWorksheet("calculations")

wksht001.RowHeight(10) = 13
wksht001.RowHeight(11) = 13
wksht001.Label(4, 2) = ""
wksht001.Number(4, 3) = 2003
wksht001.Number(4, 4) = 2004

wksht001.Label(5, 2) = "January"
wksht001.Number(5, 3) = 100
wksht001.Number(5, 4) = 105

wksht001.Label(6, 2) = "February"
wksht001.Number(6, 3) = 80
wksht001.Number(6, 4) = 120

wksht001.Label(7, 2) = "March"
wksht001.Number(7, 3) = 120
wksht001.Number(7, 4) = 140

wksht001.Label(8, 2) = "Total"
wksht001.Formula(8, 3) = "=SUM(C5:C7)" ' = C5 + C6 + C7 = 100 + 80 + 120 = 300
wksht001.Formula(8, 4) = "=SUM(D5:D7)" ' = D5 + D6 + D7 = 105 + 120 + 140 = 365



Dim wksht002 As IXlsWorksheet
Set wksht002 = wksht001.Duplicate("calculations 2")

' number1 should be 0 since the formula is not calculated yet

Dim number1 As Integer
number1 = wksht002.Number(8, 3)


wksht002.Number(7, 3) = 150

' number2 should be 0 since auto recalc is off and we haven't explicitely asked for calculations

Dim number2 As Integer
number2 = wksht002.Number(8, 3)


wksht002.CalculateFormulas

' number3 should be 330 (= C5 + C6 + C7 = 100 + 80 + 150)

Dim number3 As Integer
number3 = wksht002.Number(8, 3)


workbook.CalculationOptions.AutoRecalc = True

wksht002.Number(7, 3) = 180

' number3 should be 360 (= C5 + C6 + C7 = 100 + 80 + 180)

Dim number4 As Integer
number4 = wksht002.Number(8, 3)

workbook.Close

C# code


IXlsEngine engine = Start();

// we open a regular Excel file, and calculate it
//  then we duplicate the worksheet, make a change, and recalculate it

IXlsWorkbook workbook = engine.New( "calculation_engine.xls");

// make sure that there will be no prompt when this document gets closed from Excel
workbook.ExcelTargetVersion = xlsgen.enumExcelTargetVersion.excelversion_2003;



IXlsWorksheet wksht001 = workbook.AddWorksheet("calculations");

wksht001.set_RowHeight(10, 13);
wksht001.set_RowHeight(11, 13);
wksht001.set_Label(4,2, "");
wksht001.set_Number(4,3, 2003);
wksht001.set_Number(4,4, 2004);

wksht001.set_Label(5,2, "January");
wksht001.set_Number(5,3, 100);
wksht001.set_Number(5,4, 105);

wksht001.set_Label(6,2, "February");
wksht001.set_Number(6,3, 80);
wksht001.set_Number(6,4, 120);

wksht001.set_Label(7,2, "March");
wksht001.set_Number(7,3, 120);
wksht001.set_Number(7,4, 140);

wksht001.set_Label(8,2, "Total");
wksht001.set_Formula(8,3, "=SUM(C5:C7)"); // = C5 + C6 + C7 = 100 + 80 + 120 = 300
wksht001.set_Formula(8,4, "=SUM(D5:D7)"); // = D5 + D6 + D7 = 105 + 120 + 140 = 365




IXlsWorksheet wksht002 = wksht001.Duplicate("calculations 2");

// number1 should be 0 since the formula is not calculated yet

int number1 = wksht002.get_Number(8, 3); 


wksht002.set_Number(7,3, 150);

// number2 should be 0 since auto recalc is off and we haven't explicitely asked for calculations

int number2 = wksht002.get_Number(8, 3);


wksht002.CalculateFormulas();

// number3 should be 330 (= C5 + C6 + C7 = 100 + 80 + 150)

int number3 = wksht002.get_Number(8, 3);


workbook.CalculationOptions.AutoRecalc = 1;

wksht002.set_Number(7,3, 180);

// number3 should be 360 (= C5 + C6 + C7 = 100 + 80 + 180)

int number4 = wksht002.get_Number(8, 3);

workbook.Close();

C/C++ code


xlsgen::IXlsEnginePtr engine = xlsgen.Start();


// we open a regular Excel file, and calculate it
//  then we duplicate the worksheet, make a change, and recalculate it

xlsgen::IXlsWorkbookPtr workbook = engine->New(L"calculation_engine.xls");

// make sure that there will be no prompt when this document gets closed from Excel
workbook->ExcelTargetVersion = xlsgen::excelversion_2003;



xlsgen::IXlsWorksheetPtr wksht001 = workbook->AddWorksheet(L"calculations");

wksht001->RowHeight[10] = 13;
wksht001->RowHeight[11] = 13;
wksht001->Label[4][2] = L"";
wksht001->Number[4][3] = 2003;
wksht001->Number[4][4] = 2004;

wksht001->Label[5][2] = L"January";
wksht001->Number[5][3] = 100;
wksht001->Number[5][4] = 105;

wksht001->Label[6][2] = L"February";
wksht001->Number[6][3] = 80;
wksht001->Number[6][4] = 120;

wksht001->Label[7][2] = L"March";
wksht001->Number[7][3] = 120;
wksht001->Number[7][4] = 140;

wksht001->Label[8][2] = L"Total";
wksht001->Formula[8][3] = L"=SUM(C5:C7)"; // = C5 + C6 + C7 = 100 + 80 + 120 = 300
wksht001->Formula[8][4] = L"=SUM(D5:D7)"; // = D5 + D6 + D7 = 105 + 120 + 140 = 365




xlsgen::IXlsWorksheetPtr wksht002 = wksht001->Duplicate(L"calculations 2");

// number1 should be 0 since the formula is not calculated yet

int number1 = wksht002->Number[8][3]; 


wksht002->Number[7][3] = 150;

// number2 should be 0 since auto recalc is off and we haven't explicitely asked for calculations

int number2 = wksht002->Number[8][3];


wksht002->CalculateFormulas();

// number3 should be 330 (= C5 + C6 + C7 = 100 + 80 + 150)

int number3 = wksht002->Number[8][3];


workbook->CalculationOptions->AutoRecalc = TRUE;

wksht002->Number[7][3] = 180;

// number3 should be 360 (= C5 + C6 + C7 = 100 + 80 + 180)

int number4 = wksht002->Number[8][3];

workbook->Close();

Calculating external workbook references

By default, xlsgen will try to calculate external workbook references, i.e. formulas containing references to one or more external workbooks. This behavior can be disabled by setting the workbook AutoOpenExternalWorkbookReference property appropriately.

If, during the calculation, the external workbook being referenced is not opened already, xlsgen will try to open it. Excel internally stores relative or fully qualified filepaths to Excel spreadsheets, therefore xlsgen may need additional help in order to open an external workbook. It works as follows :

if the reference is fully qualified, xlsgen will be able to open the workbook at least in principle. The workbook filepath can be stored using any of the regular filepaths conventions (drive, network, ...). Of course, if the file does not exist, the calculation will be aborted. In order to know if the reference to the workbook is fully qualified or not, you can use the automatic source code generation tool (xlscodegen.exe) and take a look at the formula in the cell. The formula should contain the corresponding filepaths.
if the reference is not fully qualified, i.e. relative or a filename with no filepath at all, xlsgen can be helped to find said workbook by calling a method exposed by the workbooks collection called AddFilepath. For instance, if you know the workbook in question is stored in C:\tmp\sales.xls, make sure to call engine.Workbooks.AddFilepath("C:\tmp") before starting the calculations.

Multithreaded calculation engine

xlsgen has a multithreaded calculation engine that can be taken advantage of to accelerate calculations or just avoid having many CPU cores staying idle. Here is a snapshot of a calculation scenario using single thread calculations :

Single thread calculations (notice the 24% overall usage and the bottom right chart)

And here is the same calculation scenario with all of the 6 CPU cores of that i5-9400 taken to handle the calculations :

Multithread calculations (notice the 100% overall usage and all charts)

The amount of worker threads is up to the client application. Of course, if you are running a CPU with n cores, it makes sense to create no more than n worker threads otherwise threads will be waiting one another, not doing work in parallel.

If you don't know how many cores the CPU where xlsgen runs has, it can be queried very easily :

C/C++ code


int nbCores = workbook->CalculationOptions->MultithreadCores;

And then, creating n worker threads for next calculations is just as simple :

C/C++ code


workbook->CalculationOptions->MultithreadCores = n;

workbook->CalculateFormulas();