Number Format Categories

The set_num_format() method is used to set the number format for numbers used with worksheet.write_number(). For example the following formats a number with the Excel number format "$#,##0.00".

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

//! The following example demonstrates setting a currency format for a worksheet
//! cell. This example doesn't actually set a currency format, for that see the
//! followup example in doc_format_currency2.rs.

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

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

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

    // Add a format.
    let currency_format = Format::new().set_num_format("$#,##0.00");

    worksheet.write_number_with_format(0, 0, 1234.56, &currency_format)?;

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

    Ok(())
}

Which give the following output with the category displayed:

The number category is shown as "Number" but it is also possible to have it correspond to one of the other number categories such as "Currency" (which we might have expected in this case).

The Excel number categories are:

• General
• Number
• Currency
• Accounting
• Date
• Time
• Percentage
• Fraction
• Scientific
• Text
• Custom

If we wanted to have the number format display as a different category, such as "Currency", then would need to match the number format string used by Excel with the number format used by in our code. The easiest way to do this is to open the Number Formatting dialog in Excel and set the required format:

Then, while still in the dialog, change to Custom. The format displayed is the format used by Excel.

If we put the format that we found ("[$$-409]#,##0.00") into our previous example and rerun it we will get a number format in the Currency category:

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

//! The following example demonstrates setting a currency format for a worksheet
//! cell.

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

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

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

    // Add a format.
    let currency_format = Format::new().set_num_format("[$$-409]#,##0.00");

    worksheet.write_number_with_format(0, 0, 1234.56, &currency_format)?;

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

    Ok(())
}

That give us the following updated output. Note that the number category is now shown as Currency:

The same process can be used to find format strings for "Date" or "Accountancy" formats.