Calculate a cell, range or sheet

The following example shows you how to recalculate only a single cell, a range or one worksheet instead of the whole workbook. Pass a reference to CalcSession.Calculate, or pass the Worksheet object itself.

The reference is one or more comma separated areas. All of these are valid:

"Sheet1" — the whole worksheet
"Sheet1!B2" — a single cell
"Sheet1!A1:D100" — a range
"'My Sheet'!A:A" — a whole column, sheet name in quotes because it contains a space
"Sheet1!3:7" — whole rows
"Sheet1!A1:A10,Sheet2!B5" — several areas at once

An area with no sheet name refers to the first worksheet, unless a sheet of that name exists.

Whatever the referenced formulas depend on is calculated too, across sheets, and written back. Cells that depend on them are not: they keep their cached values. A targeted run therefore answers "what are these cells worth" rather than refreshing the whole workbook before a save — use Calculate(workbook) for that. CalculatedCellCount, FailedCellCount and VolatileCells of the result describe the calculated cells only.

C# example

using System;
using Independentsoft.Calc;
using Independentsoft.Office.Spreadsheet;

namespace Sample
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook book = new Workbook("c:\\test\\input.xlsx");

            CalcSession session = new CalcSession();

            //Only the formulas in B2:B500 of "Sales", plus whatever they depend on,
            //on this and on any other sheet.
            CalcResult result = session.Calculate(book, "Sales!B2:B500");

            Console.WriteLine(result);

            Worksheet sales = null;

            foreach (Worksheet sheet in book.Sheets)
            {
                if (sheet.Name == "Sales")
                {
                    sales = sheet;
                }
            }

            Console.WriteLine("B2 = " + sales["B2"].Value);

            //A single cell.
            session.Calculate(book, "Sales!B2");

            //Several areas at once.
            session.Calculate(book, "Sales!B2:B500,Summary!C1:C20");

            //A whole worksheet, given as an object rather than as a reference.
            CalcResult sheetResult = session.Calculate(book, sales);

            Console.WriteLine(sheetResult);

            book.Save("c:\\test\\output.xlsx", true);
        }
    }
}

VB example

Imports System
Imports Independentsoft.Calc
Imports Independentsoft.Office.Spreadsheet

Module Module1
    Sub Main(ByVal args As String())

        Dim book As New Workbook("c:\test\input.xlsx")

        Dim session As New CalcSession()

        'Only the formulas in B2:B500 of "Sales", plus whatever they depend on,
        'on this and on any other sheet.
        Dim result As CalcResult = session.Calculate(book, "Sales!B2:B500")

        Console.WriteLine(result)

        Dim sales As Worksheet = Nothing

        For Each sheet As Worksheet In book.Sheets
            If sheet.Name = "Sales" Then
                sales = sheet
            End If
        Next

        Console.WriteLine("B2 = " & sales("B2").Value)

        'A single cell.
        session.Calculate(book, "Sales!B2")

        'Several areas at once.
        session.Calculate(book, "Sales!B2:B500,Summary!C1:C20")

        'A whole worksheet, given as an object rather than as a reference.
        Dim sheetResult As CalcResult = session.Calculate(book, sales)

        Console.WriteLine(sheetResult)

        book.Save("c:\test\output.xlsx", True)

    End Sub
End Module

The same overloads exist for OpenDocument spreadsheets: pass an Independentsoft.Office.Odf.Spreadsheet and a reference, or an Independentsoft.Office.Odf.Table.

Calc .NET > Tutorial > Calculate a cell, range or sheet

Need help? Ask our developers: