How to have localized style when writing cell with xlwt

Question

I'm writing an Excel spreadsheet with Python's xlwt and I need numbers to be formatted using "." as thousands separator, as it is in brazilian portuguese language.

I have tried:

style.num_format_str = r'#,##0'

And it sets the thousands separator as ','. If I try setting num_format_str to '#.##0', I'll get number formatted as 1234.000 instead of 1.234. And if I open document in OpenOffice and format cells, I can set the language of the cell to "Portuguese (Brazil)" and then OpenOffice will show the format code as being "#.##0", but I don't find a way to set the cell's language to brazilian portuguese.

Any ideas?

Answer 1

The thousands separator (and the decimal "point" etc) are recorded in the XLS file in a locale-independent fashion. The recorded thousands separator is a comma. How it is displayed depends on the user's locale. OpenOffice calc allows the user to override the default locale (Tools / Options / Languages / Locale setting).

With xlwt, write your num_format_str as "#,###.00" and your data as float("1234567.89"), like this:

import xlwt
b = xlwt.Workbook()
s = b.add_sheet('x')
style = xlwt.easyxf("", "#,###.00")
s.write(0, 0, 1234567.89, style)
b.save("locale_fmt_demo.xls")

Open the output file with OO Calc and try various locale settings. I got these results:

English (Australia): 1,234,567.89  
Portuguese (Brazil): 1.234.567,89
French (France):     1 234 567,89