Iterative Calculation in Excel using C# and VB.NET

The following example shows how you can use the GemBox.Spreadsheet calculation engine to calculate cell formulas with circular references, using C# and VB.NET.

By default, circular references are not allowed. To enable and use iterative calculation, you'll need to set the CalculationOptions.EnableIterativeCalculation property.

The following example shows how to create an Excel workbook with circular references and enable iterative calculation in C# and VB.NET.

Excel formula with circular reference calculated with GemBox.Spreadsheet
Screenshot of Excel formula with circular reference calculated with GemBox.Spreadsheet
using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();

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

        // Add new worksheet
        var worksheet = workbook.Worksheets.Add("Iterative Calculation");

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

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

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

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

        workbook.Save("Iterative Calculation.%OutputFileType%");
    }
}
Imports GemBox.Spreadsheet

Module Program

    Sub Main()

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

        Dim workbook As New ExcelFile()

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

        ' Add new worksheet.
        Dim worksheet = workbook.Worksheets.Add("Iterative Calculation")

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

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

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

        ' Calculate all cells.
        worksheet.Calculate()

        workbook.Save("Iterative Calculation.%OutputFileType%")

    End Sub
End Module

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 the CalculationOptions.MaximumChange value. The formulas in column B show such an iterative calculation process.

See also


Next steps

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.

Download Buy