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. The calculated cell count, failed cell count and volatile cells of the result describe the calculated cells only.

import com.independentsoft.calc.CalcResult;
import com.independentsoft.calc.CalcSession;
import com.independentsoft.office.spreadsheet.Sheet;
import com.independentsoft.office.spreadsheet.Workbook;
import com.independentsoft.office.spreadsheet.Worksheet;

public class Example {

    public static void main(String[] args)
    {
        try
        {
            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");

            System.out.println(result);

            Worksheet sales = null;

            for (Sheet sheet : book.getSheets())
            {
                if (sheet instanceof Worksheet && sheet.getName().equals("Sales"))
                {
                    sales = (Worksheet)sheet;
                }
            }

            System.out.println("B2 = " + sales.get("B2").getValue());

            //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);

            System.out.println(sheetResult);

            book.save("c:\\test\\output.xlsx", true);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}

The same overloads exist for OpenDocument spreadsheets: pass a com.independentsoft.office.odf.Spreadsheet and a reference, or a com.independentsoft.office.odf.Table.

JCalc > Tutorial > Calculate a cell, range or sheet

Need help? Ask our developers: