Working with Formulas

14 Mar 20234 minutes to read

Formulas are entries in Excel that have equations, by which values are calculated. A typical formula might contain cell references, constants, and even functions.

Enable Calculation

To perform calculation in an Excel workbook, it is recommended to invoke enableSheetCalculations() method of Worksheet. Enabling this method will initialize CalcEngine objects and retrieves calculated values of formulas in a worksheet.

The following code sample illustrates on how to enable worksheet formula calculations.

  • DART
    • // Create a new Excel Document.
final Workbook workbook = Workbook();

// Accessing sheet via index.
final Worksheet sheet = workbook.worksheets[0];

//Formula calculation is enabled for the sheet
sheet.enableSheetCalculations();

// Save and dispose workbook
final List<int> bytes = workbook.saveSync();
workbook.dispose();

File('Output.xlsx').writeAsBytes(bytes);

    Apply Formula

    In a worksheet, formulas can be entered by using the setFormula() method of the Range instance.
    Following code example illustrates on how to write a formula.

  • DART
    • // Create a new Excel document.
final Workbook workbook = Workbook();

//Accessing worksheet via index.
final Worksheet sheet = workbook.worksheets[0];

//set the value to the cell.
sheet.getRangeByName('A1').setNumber(10);
sheet.getRangeByName('A2').setNumber(20);

//Setting formula in the cell.
sheet.getRangeByName('A3').setFormula('=A1+A2');

//Save and dispose a workbook.
final List<int> bytes = workbook.saveSync();
workbook.dispose();

File('Formula.xlsx').writeAsBytes(bytes);

    Accessing a Calculated value

    To evaluate formula, it is must to enable sheet calculation in prior. After enabling the sheet calculation, the formula can be evaluated using calculatedValue of Range, which returns a string value.

    The following code shows how to access a calculated value.

  • DART
    • // Create a new Excel document.
final Workbook workbook = Workbook();

// Accessing worksheet via index.
final Worksheet sheet = workbook.worksheets[0];

// Formula calculation is enabled for the sheet.
sheet.enableSheetCalculations();

// set the value to the cell.
sheet.getRangeByName('A1').setNumber(10);
sheet.getRangeByName('A2').setNumber(20);

// Setting formula in the cell.
sheet.getRangeByName('A3').setFormula('=A1+A2');

// Returns the calculated value of a formula using the most current inputs
String calculatedValue = sheet.getRangeByName('A3').calculatedValue;

// Save and dispose a workbook.
final List<int> bytes = workbook.saveSync();
workbook.dispose();

File('Formula.xlsx').writeAsBytes(bytes);

    Formula with Nested Functions

    Using a function as one of the arguments in a formula is known as Nested Function.

    The following code shows how to use nested functions.

  • DART
    • // Create a new Excel Document.
final Workbook workbook = Workbook();

// Accessing sheet via index.
final Worksheet sheet = workbook.worksheets[0];

// set the value to the cell.
sheet.getRangeByName('B3').setText('Team A');
sheet.getRangeByName('B4').setNumber(47);
sheet.getRangeByName('B5').setNumber(43);
sheet.getRangeByName('B6').setNumber(40);
sheet.getRangeByName('B7').setNumber(51);
sheet.getRangeByName('B8').setNumber(53);
sheet.getRangeByName('B9').setNumber(50);

sheet.getRangeByName('D3').setText('Team B');
sheet.getRangeByName('D4').setNumber(72);
sheet.getRangeByName('D5').setNumber(43);
sheet.getRangeByName('D6').setNumber(84);
sheet.getRangeByName('D7').setNumber(90);
sheet.getRangeByName('D8').setNumber(42);
sheet.getRangeByName('D9').setNumber(56);

// Formula calculation is enabled for the sheet.
sheet.enableSheetCalculations();

// Setting formula in the cell.
final Range range = sheet.getRangeByName('B11');
range.setFormula(
  '=IF(SUM(AVERAGE(B4:B9), MAX(COUNT(B4,D4), MIN(B5,D5))) > 50, \"PASS\", \"FAIL\")');

// Save and dispose workbook.
final List<int> bytes = workbook.saveSync();
File('NestedFunction.xlsx').writeAsBytes(bytes);
workbook.dispose();

    Supported Formulas

    In flutter XlsIO, we have support for Range reference and basic function formula listed below:

    SUM Adds its arguments
    AVERAGE Returns the average of its arguments
    MAX Returns the maximum value in a list of arguments
    MIN Returns the minimum value in a list of arguments
    COUNT Counts how many numbers are in the list of arguments
    IF Specifies a logical test to perform
    AND Returns TRUE if all of its arguments are TRUE
    OR Returns TRUE if any argument is TRUE
    NOT Reverses the logic of its argument
    CONCATENATE Joins several text items into one text item
    TRIM Removes spaces from text
    LOWER Converts text to lowercase
    UPPER Converts text to uppercase
    NOW Returns the serial number of the current date and time
    TODAY Returns the serial number of today's date
    INDEX Uses an index to choose a value from a reference or array
    MATCH Looks up values in a reference or array
    PRODUCT Multiplies its arguments
    SUMPRODUCT Returns the sum of the products of corresponding array components
    SUMIF Adds the cells specified by a given criteria
    VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell
    COUNTIFS Counts the number of times all criteria are met.
    MAXIFS Returns the maximum value among cells specified by a given set of conditions or criteria
    MINIFS Returns the minimum value among cells specified by a given set of conditions or criteria
    SUMIF Adds the cells specified by a given criteria
    SUMIFS Adds all of its arguments that meet multiple criteria