Handling Currency and Locale in Generated Workbooks

The report goes to the German subsidiary and comes back with a question: why does revenue show 1,234.50 in the file and 1.234,50 on the call? Nothing is wrong. The workbook stored the number 1234.5 and a format code, and each reader's Excel drew it with their own conventions. That is the right behavior, and it surprises developers who tested on one machine. This article explains what a generated workbook stores, what the reader's locale controls, and how to write currency so it means the same thing to everyone. It builds on rounding and formatting numbers for finance readers and belongs to building Excel reports from SQL queries people trust.

What the file stores and what the reader supplies

An xlsx cell holds a value and a style. A numeric cell stores the number in a locale-independent form, with a period as the decimal point, whatever the writer's settings. The style references a number format code, also stored in a fixed notation: #,##0.00 always uses a comma for grouping and a period for decimals inside the file. When the reader opens it, Excel translates those separators into the ones their system uses.

So the file is stable and the display varies. Three things follow:

• Do not write numbers as text with the separators already in them, such as "1.234,50". A text cell is not a number, and it is parsed differently in each locale.

• Do not convert dates to strings in a local order, such as "03/04/2026", which is the 3rd of April or the 4th of March depending on the reader.

• Do use real numbers and real dates with formats. Excel does the localization.

Currency symbols and codes

A currency symbol is a display choice and a poor identifier. The dollar sign is used by the United States, Canada, Australia, Singapore and others. The yen and yuan share a glyph in many fonts. A report with $1,200,000 is ambiguous the moment it leaves its home market.

Better options, in order of reliability:

1. A currency column with the ISO 4217 code (USD, EUR, JPY) beside every amount that can vary.

2. The code in the header: "Net revenue (EUR)". Works when a whole column is one currency.

3. A format code with the code embedded: #,##0.00 "EUR". The text is quoted and displayed after the number.

4. A symbol format, such as [$€-407] #,##0.00, which includes a locale identifier and is the most fragile.

eur = wb.add_format({"num_format": '#,##0.00 "EUR"'})
usd = wb.add_format({"num_format": '#,##0.00 "USD"'})

For a sheet with mixed currencies, keep one column for the amount in the original currency, one for the currency code and, if conversion is needed, one for the amount in the reporting currency with the rate and its date. Never sum amounts that sit in different currencies, and let the generator refuse: group by currency before totalling, or convert first (handling multi currency columns covers the data side).

JPY has no minor unit and KWD has three decimals. If you store amounts as integer minor units, the divisor depends on the currency, so keep a small mapping from the ISO table rather than assuming 100.

Decimal and thousands separators

You cannot choose the separators for the reader, and you should not try. What you can do is choose a format code and test it. The standard grouped number format is #,##0.00. On a system set to German it shows 1.234,50; in French (France) with a narrow no-break space as the group separator, 1 234,50; in the United States 1,234.50. All three are the same cell.

There is one case where you do choose: a CSV. A CSV has no format codes and no cell types, so the separators in the text matter. Use a period for decimals and no thousands separator in CSV intended for machines, and expect that Excel in a comma-decimal locale will read the file with a semicolon delimiter or misread the numbers (Excel and CSV files shared with other tools). If the readers are people in Excel, prefer to give them an xlsx.

Dates, times and time zones

A date should be a date value with a format, such as yyyy-mm-dd. The ISO form is understood in every locale and unambiguous. If you want a friendlier display like 14 Mar 2026, use d mmm yyyy; the month name will follow the reader's language.

Be careful with format codes that vary by locale. dd/mm/yyyy writes a fixed order for everyone, which is what you want when the order matters. The built-in "short date" format codes in the [$-F800] family follow the reader's setting, which is what you want for a casual report and not for one that feeds a process.

Excel dates have no time zone. If the values are local timestamps, say which zone in the header ("Order time (Europe/Berlin)") or write UTC and name it. A workbook that crosses time zones silently moves orders between days (Excel stores dates as serial numbers explains the storage, and the date system in Mac and Windows date systems in Excel).

Language-dependent function names

If you write formulas, write them in the English function names and comma argument separators that the file format uses. Excel stores formulas in a canonical form (SUM, comma separated) and translates them for display, so a French reader sees SOMME(...) and ; separators. Do not write translated function names or semicolons into the file through a library, because the library writes your string as is. XlsxWriter's documentation covers this under working with formulas. Newer functions such as XLOOKUP need a _xlfn. prefix in the stored formula, which XlsxWriter can add with the use_future_functions option.

Text, sorting and encoding

Locale shapes text handling too. Sorting accented names differs by language: in Swedish, å sorts after z. A generator that sorts in Python and an Excel user who sorts the column will get different orders, which is fine until someone relies on the first. State the sort in the README, or sort in the database with an explicit collation.

Encoding is simpler: xlsx is Unicode throughout, so there is no code page problem, as there is in CSV. Check that your fonts cover the scripts in the data; a default font without CJK glyphs shows empty boxes on some machines. Names with right-to-left scripts are stored correctly and displayed by the reader's Excel.

Test under a second locale

A file that looks right on your laptop proves little. Test on a second configuration:

• Open the workbook in LibreOffice with the interface set to another language and the locale to German or French, and read the numbers and dates.

• Ask a colleague in another region to open it and screenshot the key sheet.

• Automate the stable checks: reload with openpyxl and assert that amount cells are numeric (cell.data_type == "n"), that date cells are datetime values, and that the number_format strings are the ones you set.

from openpyxl import load_workbook

ws = load_workbook("revenue.xlsx")["Sales"]
for row in ws.iter_rows(min_row=2, min_col=4, max_col=4):
    assert row[0].data_type == "n", "amount is not numeric"
    assert row[0].number_format.startswith("#,##0")

These checks catch the worst failure, which is amounts written as text. They do not replace one human look in another locale.

Document the conventions

Put the conventions on the README sheet: the currency and unit of each column, the rounding rule, the time zone of timestamps, the date format and the exchange rate source and date. A reader abroad who sees "All amounts in EUR thousands; dates are ISO 8601; times are UTC" has no questions. Ask what a stranger would misread, and write that down (putting a README sheet into every generated workbook).

When locale handling is not your job

Locale handling is lighter when the recipient is a program: write a CSV or JSON with plain numbers, ISO dates and a currency code field, and let the consumer format it. It is not your job to guess a reader's habits when they can set their own: give them correct values and clear labels. And it is out of scope when a single office reads the file on identical machines, though that situation changes the day someone travels. The Microsoft list of number format codes and the XlsxWriter working with formulas page are the two references worth bookmarking.