Formula Results

The rust_xlsxwriter library doesn't calculate the result of a formula and instead stores the value "0" as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened.

This works fine with the majority of spreadsheet applications. However, applications that don't have a facility to calculate formulas will only display the 0 results. Examples of such applications are Excel viewers, PDF converters, and some mobile device applications.

If required, it is also possible to specify the calculated result of the formula using the Worksheet::set_formula_result() method or the Formula::set_result() method:

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! The following example demonstrates manually setting the result of a formula.
//! Note, this is only required for non-Excel applications that don't calculate
//! formula results.

use rust_xlsxwriter::{Formula, Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    // Add a worksheet to the workbook.
    let worksheet = workbook.add_worksheet();

    // Using the formula string syntax.
    worksheet
        .write_formula(0, 0, "1+1")?
        .set_formula_result(0, 0, "2");

    // Or using a Formula type.
    worksheet.write_formula(1, 0, Formula::new("2+2").set_result("4"))?;

    workbook.save("formulas.xlsx")?;

    Ok(())
}

One common spreadsheet application where the formula recalculation doesn't work is LibreOffice (see the following issue report). If you wish to force recalculation in LibreOffice you can use the Worksheet::set_formula_result_default() method to set the default result to an empty string:

// SPDX-License-Identifier: MIT OR Apache-2.0
//
// Copyright 2022-2024, John McNamara, jmcnamara@cpan.org

//! The following example demonstrates manually setting the default result for
//! all non-calculated formulas in a worksheet.

use rust_xlsxwriter::{Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    // Add a worksheet to the workbook.
    let worksheet = workbook.add_worksheet();

    worksheet.set_formula_result_default("");

    workbook.save("formulas.xlsx")?;

    Ok(())
}