Working with Formulas

13 Jul 202610 minutes to read

A formula is an expression in an Excel cell that calculates a value from cell references, constants, and functions. Syncfusion Flutter XlsIO supports writing formulas, evaluating them, and reading the calculated values.

The typical workflow is:

  1. Enable formula calculation for the worksheet through enableSheetCalculations(). This initializes the internal CalcEngine and makes calculatedValue return a result.
  2. Write a formula to a Range through the setFormula() method or the formula property. The expression must start with = (for example, =A1+A2).
  3. Read the calculated value through the calculatedValue property of Range if you need the result in code.

For prerequisites and installation steps, see the Flutter XlsIO Overview. For information on formatting calculated values, see Working with Cell Formatting.

NOTE

The code samples in this document use await workbook.save(). Always call workbook.dispose() after saving to release the XlsIO DOM memory, ideally inside a try/finally block.

Enable sheet calculation

The enableSheetCalculations() method of the Worksheet class initializes the internal CalcEngine so that formulas are evaluated. Call this method before reading calculatedValue on any range in the worksheet. Enabling sheet calculation may increase the time required to save the workbook because every formula in the worksheet is evaluated.

  • DART
    • import 'package:syncfusion_flutter_xlsio/xlsio.dart';

Future<void> enableCalculation() async {
  final Workbook workbook = Workbook();
  final Worksheet sheet = workbook.worksheets[0];

  // Enable formula calculation for the worksheet.
  sheet.enableSheetCalculations();

  // Add a formula so the calculation has a visible effect.
  sheet.getRangeByName('A1').setNumber(10);
  sheet.getRangeByName('A2').setNumber(20);
  sheet.getRangeByName('A3').setFormula('=A1+A2');

  // Read the calculated value as a string.
  final String result = sheet.getRangeByName('A3').calculatedValue;

  // ignore: avoid_print
  print(result);

  final List<int> bytes = await workbook.save();
  workbook.dispose();
}

    Apply a formula

    A formula can be written to a Range through the setFormula(String) method or by assigning a String to the formula property. Both require the expression to start with =. Cell references use the A1 reference style.

  • DART
    • import 'package:syncfusion_flutter_xlsio/xlsio.dart';

Future<void> applyFormula() async {
  final Workbook workbook = Workbook();
  final Worksheet sheet = workbook.worksheets[0];

  // Set numeric values in A1 and A2.
  sheet.getRangeByName('A1').setNumber(10);
  sheet.getRangeByName('A2').setNumber(20);

  // Set a formula in A3.
  sheet.getRangeByName('A3').setFormula('=A1+A2');

  final List<int> bytes = await workbook.save();
  workbook.dispose();
}

    Read a calculated value

    After enableSheetCalculations() has been called, the calculatedValue property of a Range returns the result of the formula as a String. If the formula has not been evaluated, the property returns an empty string.

  • DART
    • import 'package:syncfusion_flutter_xlsio/xlsio.dart';

Future<void> readCalculatedValue() async {
  final Workbook workbook = Workbook();
  final Worksheet sheet = workbook.worksheets[0];

  // Enable formula calculation.
  sheet.enableSheetCalculations();

  // Set values and a formula.
  sheet.getRangeByName('A1').setNumber(10);
  sheet.getRangeByName('A2').setNumber(20);
  sheet.getRangeByName('A3').setFormula('=A1+A2');

  // Read the calculated value as a string.
  final String result = sheet.getRangeByName('A3').calculatedValue;

  // ignore: avoid_print
  print(result);

  final List<int> bytes = await workbook.save();
  workbook.dispose();
}

    NOTE

    The calculatedValue property always returns a String. To use the result as a number or a date, parse the string with int.parse, double.parse, or DateTime.parse as appropriate.

    Use nested functions

    Using a function as one of the arguments of another function is known as a nested function. The arguments of each function are evaluated from the inside out. The following example uses IF, SUM, AVERAGE, MAX, COUNT, and MIN in a single formula.

  • DART
    • import 'package:syncfusion_flutter_xlsio/xlsio.dart';

Future<void> nestedFunctions() async {
  final Workbook workbook = Workbook();
  final Worksheet sheet = workbook.worksheets[0];

  // Team A data in column B.
  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);

  // Team B data in column D.
  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);

  // Enable formula calculation.
  sheet.enableSheetCalculations();

  // Average team A's scores, then add the larger of COUNT(B4,D4) and MIN(B5,D5).
  // If the total exceeds 50, the formula returns "PASS"; otherwise "FAIL".
  sheet
      .getRangeByName('B11')
      .setFormula(
        '=IF(SUM(AVERAGE(B4:B9), MAX(COUNT(B4,D4), MIN(B5,D5))) > 50, "PASS", "FAIL")',
      );

  final List<int> bytes = await workbook.save();
  workbook.dispose();
}

    Supported functions

    Syncfusion Flutter XlsIO supports range references and the functions listed below, grouped by category.

    General functions

    Function Description
    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.
    PRODUCT Multiplies its arguments.
    SUMPRODUCT Returns the sum of the products of corresponding array components.

    Logical functions

    Function Description
    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.

    Text functions

    Function Description
    CONCATENATE Joins several text items into one text item.
    TRIM Removes spaces from text.
    LOWER Converts text to lowercase.
    UPPER Converts text to uppercase.

    Time functions

    Function Description
    NOW Returns the serial number of the current date and time.
    TODAY Returns the serial number of today’s date.

    Lookup and reference functions

    Function Description
    INDEX Uses an index to choose a value from a reference or array.
    MATCH Looks up values in a reference or array.
    VLOOKUP Looks in the first column of an array and moves across the row to return the value of a cell.

    Conditional functions

    Function Description
    SUMIF Adds the cells specified by a given criteria.
    SUMIFS Adds all of its arguments that meet multiple criteria.
    COUNTIFS Counts the number of times all criteria are met.
    MAXIFS Returns the maximum value among cells specified by a given set of conditions.
    MINIFS Returns the minimum value among cells specified by a given set of conditions.

    See also