Number Formats in different Locales

As shown in the previous section the format.set_num_format() method is used to set the number format for rust_xlsxwriter formats. A common use case is to set a number format with a "grouping/thousands" separator and a "decimal" point:

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

//! The following example demonstrates setting a number format that appears
//! differently in different locales.

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("number_format.xlsx")?;

    Ok(())
}

In the US locale (and some others) where the number "grouping/thousands" separator is "," and the "decimal" point is "." which would be shown in Excel as:

In other locales these values may be reversed or different. They are generally set in the "Region" settings of Windows or Mac OS. Excel handles this by storing the number format in the file format in the US locale, in this case "#,##0.00", but renders it according to the regional settings of the host OS. For example, here is the same, unmodified, output file shown above in a German locale:

And here is the same file in a Russian locale. Note the use of a space as the "grouping/thousands" separator:

In order to replicate Excel's behavior all XlsxWriter programs should use US locale formatting which will then be rendered in the settings of your host OS.