Django/python and isues with xlwt locale formatting
Question: I am using django-excel-view which uses django-excel-response which in turn
uses xlwt. I have a scenario where users can switch locale which lets them
view floats with a standard decimal point or in some languages a comma decimal
point.

When exporting the view as an xls with standard decimal point locale it works
just fine, but using comma decimals makes the xls file store the float as text
and adds an apostrophe before the number (e.g. '123,45). I have a feeling that
ExcelResponse (<https://djangosnippets.org/snippets/1151/>) is not handling
the float correctly (see line 43 onwards of the snippet).

What would be the correct xlwt style to apply for the xls to be saved
correctly with comma decimals and what is a good way to check whether a value
is a comma decimal and should have that style applied? In other words:

    
    
    styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
              'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
              'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
              'default': xlwt.Style.default_style,
              'comma_decimal': xlwt.easyxf('????????')}
    
    for rowx, row in enumerate(data):
        for colx, value in enumerate(row):
            if isinstance(value, datetime.datetime):
                cell_style = styles['datetime']
            elif isinstance(value, datetime.date):
                cell_style = styles['date']
            elif isinstance(value, datetime.time):
                cell_style = styles['time']
            elif isinstance(value, ?????????????):
                cell_style = styles['comma_decimal']
            else:
                cell_style = styles['default']
            sheet.write(rowx, colx, value, style=cell_style)
    

SOLUTION:

I ended up adding an extra check in django-excel-response that does some regex
check for comma float values (which are added by django locale as they should
be) and then replaces the commas with decimal points.

    
    
    elif (re.compile("^[0-9]+([,][0-9]+)?$")).match(u"{}".format(value)):
        value = float(value.replace(',', '.'))
    

jmcnamara helped point me in this direction instead of messing with locales
and xlwt formatting.


Answer: > I have a scenario where users can switch locale which lets them view floats
> with a standard decimal point or in some languages a comma decimal point.

The important thing to note here is that the the thousands/decimal separators
for a locale are a Windows setting and not an Excel setting. It is set in
Control Panel/Regional and Language Options/Format or similar.

So if in one locale the number format is displayed in Excel as `1,234.56` and
in another locale it is displayed as `1.234,56` in both cases **the format is
stored** in Excel as the US style: `0,000.00`.

So, when using xlwt, or any other spreadsheet writing module, you should use
the US style formatting options (comma for thousand and . for decimal). It
will then be displayed in the users locale according to their Windows
settings.

