GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Iterative Calculation

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

The example demonstrates how to enable and use an iterative calculation in GemBox.Spreadsheet. By default, circular references are not allowed, so we first enable them by setting ExcelFile.CalculationOptions.EnableIterativeCalculation to true.

Column A contains a simple iterative calculation example where the calculation is limited by CalculationOptions.MaximumIterations. On calling the Calculate method, the formula will be calculated CalculationOptions.MaximumIterations 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.MaximumIterations, some iterative calculations may be terminated before the limit is reached if the difference between the previous and current calculation is less than CalculationOptions.MaximumChange value. The formulas in column B demonstrate such an iterative calculation process.

Notice that below you have Run options where you can choose output file format.

Screenshot
Iterative Calculation Screenshot

See the full code below, use Run Example to execute.

using System;
using GemBox.Spreadsheet;

class Sample
{
    [STAThread]
    static void Main(string[] args)
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        ExcelFile ef = new ExcelFile();

        // Set calculation options.
        ef.CalculationOptions.MaximumIterations = 10;
        ef.CalculationOptions.MaximumChange = 0.05;
        ef.CalculationOptions.EnableIterativeCalculation = true;

        // Add new worksheet
        ExcelWorksheet ws = ef.Worksheets.Add("Iterative Calculation");

        // Some column formatting.
        ws.Columns[0].SetWidth(50, LengthUnit.Pixel);
        ws.Columns[1].SetWidth(100, LengthUnit.Pixel);

        // Simple example of circular reference limited by MaximumIterations in column A.
        ws.Cells["A1"].Formula = "=A2";
        ws.Cells["A2"].Formula = "=A1 + 1";

        // Simple example of circular reference limited by MaximumChange in column B.
        ws.Cells["B1"].Value = 100000.0;
        ws.Cells["B2"].Formula = "=B3 * 0.03";
        ws.Cells["B3"].Formula = "=B1 + B2";

        // Calculate all cells.
        ws.Calculate();

        ef.Save("Iterative Calculation.xlsx");
    }
}
Imports System
Imports GemBox.Spreadsheet

Module Samples

    Sub Main()

        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim ef As ExcelFile = New ExcelFile

        ' Set calculation options.
        ef.CalculationOptions.MaximumIterations = 10
        ef.CalculationOptions.MaximumChange = 0.05
        ef.CalculationOptions.EnableIterativeCalculation = True

        ' Add new worksheet.
        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Iterative Calculation")

        ' Some column formatting.
        ws.Columns(0).SetWidth(50, LengthUnit.Pixel)
        ws.Columns(1).SetWidth(100, LengthUnit.Pixel)

        ' Simple example of circular reference limited by MaximumIterations in column A.
        ws.Cells("A1").Formula = "=A2"
        ws.Cells("A2").Formula = "=A1 + 1"

        ' Simple example of circular reference limited by MaximumChange in column B.
        ws.Cells("B1").Value = 100000.0
        ws.Cells("B2").Formula = "=B3 * 0.03"
        ws.Cells("B3").Formula = "=B1 + B2"

        ' Calculate all cells.
        ws.Calculate()
        ef.Save("Iterative Calculation.xlsx")

    End Sub

End Module

Check next sample.