Problem

While Microsoft Excel is available in different language versions, ExcelWriter is US-English based. What this means is that while ExcelWriter can generate spreadsheets in any language, there is no French, Russian, Chinese, etc. version of ExcelWriter. This requires special considerations when creating number formats for other languages in ExcelWriter.

You must understand what symbols are used as “separators,” “decimal placeholders,” and what remaining symbols will be interpreted as “literals.” This article will help you understand how ExcelWriter does this, and how this will be interpreted by a non-English versions of Excel (example, French, Chinese, Russian, etc). This post will use French as the non-English language example.

Solution

What is a number separator?

A number separator is a symbol or space that is used to group numbers so that they are easier to read. In English(US) and many other languages, separators occur between the thousands position and the hundreds position, and then again for every three numbers moving left of the decimal placeholder. The decimal placeholder may also change from language to language.

Compare these values for English (United States) and French:

Specifying Number Formats for non-English Spreadsheets

Since ExcelWriter is only available in US-English, you must specify your number formats according to US-English standards. This will allow ExcelWriter to correctly identify the separators and decimal place holders. When the spreadsheet is opened in a non-English version of Microsoft Excel, those separators and placeholders will be correctly translated according to the language and regional settings in that version of Microsoft Excel.

ExcelWriter code sample


//--- Declare variables
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create();
Worksheet ws = wb.Worksheets[0];

ws.Cells["A1"].Value = 1234567
ws.Cells["A1"].Format.Number = "#,###.##;-#,###.##;;"
...[rest of code]

Here is how Cell A1 will display its number:

Follow

Related posts: