Iterative Calculation

The following example shows how you can use the GemBox.Spreadsheet calculation engine to calculate cell formulas with circular references.

The example shows how you can enable and use an iterative calculation in GemBox.Spreadsheet. By default, circular references are not allowed, so we first enable them by setting CalculationOptions.setEnableIterativeCalculation.

Column A contains a simple iterative calculation example where the calculation is limited by CalculationOptions.getMaximumIterations. On calling the calculate method, the formula will be calculated ("CalculationOptions.getMaximumIterations", "/spreadsheet-java/docs/com.gembox.spreadsheet.calculationengine.CalculationOptions.html#com_gembox_spreadsheet_calculationengine_CalculationOptions_getMaximumIterations__") times before the calculation is terminated and the last calculation result is set as the cell value.

Although the calculation of all circular references is limited by CalculationOptions.getMaximumIterations, some iterative calculations may be terminated before the limit is reached if the difference between the previous and current calculation is less than CalculationOptions.getMaximumChange value. The formulas in column B demonstrate such an iterative calculation process.

Excel formula with circular reference calculated with GemBox.Spreadsheet for Java
Screenshot of Excel formula with circular reference calculated with GemBox.Spreadsheet for Java
import com.gembox.spreadsheet.*;

class Program {

    public static void main(String[] args) throws java.io.IOException {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.setLicense("FREE-LIMITED-KEY");

        ExcelFile workbook = new ExcelFile();

        // Set calculation options.
        workbook.getCalculationOptions().setMaximumIterations(10);
        workbook.getCalculationOptions().setMaximumChange(0.05);
        workbook.getCalculationOptions().setEnableIterativeCalculation(true);

        // Add new worksheet
        ExcelWorksheet worksheet = workbook.addWorksheet("Iterative Calculation");

        // Some column formatting.
        worksheet.getColumn(0).setWidth(50, LengthUnit.PIXEL);
        worksheet.getColumn(1).setWidth(100, LengthUnit.PIXEL);

        // Simple example of circular reference limited by MaximumIterations in column A.
        worksheet.getCell("A1").setFormula("=A2");
        worksheet.getCell("A2").setFormula("=A1 + 1");

        // Simple example of circular reference limited by MaximumChange in column B.
        worksheet.getCell("B1").setValue(100000.0);
        worksheet.getCell("B2").setFormula("=B3 * 0.03");
        worksheet.getCell("B3").setFormula("=B1 + B2");

        // Calculate all cells.
        worksheet.calculate();

        workbook.save("Iterative Calculation.%OutputFileType%");
    }
}

Want more?

Next example GitHub

Check the next example or select an example from the menu. You can also download our examples from the GitHub.


Like it?

Download Buy

If you want to try the GemBox.Spreadsheet for Java yourself, you can download the free version. It delivers the same performance and set of features as the professional version, but with some operations limited. To remove the limitation, you need to purchase a license.