Using Formulas

OExport supports from the version 2.4 also basic formulas in XLS format along with formula recalculation.

Export formats:

  • XLSX, ODS: All valid formulas are exported.
  • XLS: Formulas supported by OExport recalculation engine are exported.
  • CSV/TXT etc.: Formulas supported by OExport recalculation engine are evaluated and their result values are exported.

OExport recalculation engine supports by now these functions and operators:

  • 15.5: Constant number values.
  • A4: Cell addresses (from the same sheet).
  • A4:B5: Cell ranges (from the same sheet).
  • + - * / ^: Mathematical operators: ADD, SUBTRACT, MULTIPLY, DIVIDE, POWER.
  • SUM(A4:B5): Sum function with one argument.

See this code demo with supported formulas:

procedure TForm1.BtnFormulaTestClick(Sender: TObject);
var
  xExport: TOExport;
  I: Integer;
begin
  xExport := TOExport.Create;
  try
    with xExport.AddWorkSheet('formula test') do begin
      //create 3 rows
      for I := 0 to 2 do
        AddRow;
      //create 4 columns in the first 2 rows with some numbers
      for I := 0 to 3 do begin
        Rows[0].AddCellNumber(I+1);
        Rows[1].AddCellNumber(10-I);
      end;

      with Rows[2] do begin
        //A3 = A1*A2 + 5
        AddCellNumber.
          SetFormula('A1*A2 + 5').

        //B3 = B1/(-B2)
        AddCellNumber.
          SetFormula('B1/(-B2)').

        //C3...empty
        AddCellEmpty;

        //D3 = SUM(C1:D2)
        AddCellNumber.
          SetFormulaSum(2, 0{C1}, 3, 1{D1});
      end;
    end;

    xExport.SaveToFileWithDialog('formula-test', '', True);
  finally
    xExport.Free;
  end;
end;
Categories