Adding dollar symbol in Python - pyexcelerator/ xlwt & Gae

[Nijin Narayanan]

Hi,

I'm trying to add '$' Symbol in Excel file using python. I have code the number format as :'"$"#,##0.00'(Code below).

Run the code and Download the Excel file. But When i open the excel file it shows pound symbol.

Please note: My default accounting format symbol is "£ English UK". I Need to Show the Currency symbol as $ using python. How ?

Code:

wb = Workbook()



    ws0 = wb.add_sheet('Sheet 1')




    currency_style = XFStyle()        



    currency_style.num_format_str =  '"$"#,##0.00'




    for i in range(1,5):           



        ws0.write(i, 0, 125.05, currency_style) 




    self.response.headers['Content-Type'] = 'application/ms-excel'



    self.response.headers['Content-Transfer-Encoding'] = 'Binary'



    self.response.headers['Content-disposition'] = 'attachment; filename="myfile.xls"'



    wb.save(self.response.out)

Cheers! ,  

NN

[John Machin]

On Friday, 4 November 2011 16:14:56 UTC+11, Nijin Narayanan wrote:

Hi,

I'm trying to add '$' Symbol in Excel file using python. I have code the number format as :'"$"#,##0.00'(Code below).

Run the code and Download the Excel file. But When i open the excel file it shows pound symbol.

Please note: My default accounting format symbol is "£ English UK". I Need to Show the Currency symbol as $ using python. How ?

For a start, your problem is (or should be) nothing to do with Google App Engine. Try to reproduce your problem (I can't) by a simple script (see below) that creates a file directly.

Aside: I would have thought that displaying a $ sign instead of the 3-letter currency name in an international environment was not a Good Idea.. One HKD differs wildly in value from one USD or CAD or AUD or NZD. Confusing two of  those 4 is still not a Good Idea.

When I run this script:

import xlwt
wb = xlwt.Workbook()
ws = wb.add_sheet("s1")
fmts = [
    '$#,##0.00',
    '"$"#,##0.00',
    r'\$#,##0.00',
    '[$$]#,##0.00',
    # next 2 are suggested work-arounds for use if Excel is not treating $ as a constant
    u'\uFE69#,##0.00', # SMALL DOLLAR SIGN
    u'\uFF04#,##0.00', # FULL-WIDTH DOLLAR SIGN
    '"USD "#,##0.00', # even better ideas
    '"HKD "#,##0.00',
    ]
for rowx, fmt in enumerate(fmts):
    xf = xlwt.easyxf('', fmt)
    ws.write(rowx, 0, fmt)
    ws.write(rowx, 1, 1234.56, xf)
wb.save('xlwt_dollar.xls')

and  change my regional settings to English (United Kingdom), I see this locale output:

>>> import locale
>>> locale.setlocale(locale.LC_ALL, '')
'English_United Kingdom.1252'
>>> locale.localeconv()['currency_symbol']
'\xa3'

and in Excel (2003 and 2007) I see dollar signs (not pound signs) in B1:B4.

When I repeat the exercise with French (France), I see

>>> locale.setlocale(locale.LC_ALL, '')
'French_France.1252'
>>> locale.localeconv()['currency_symbol']
'\x80'
>>> _.decode('cp1252')
u'\u20ac'
>>> # EURO

and in Excel that the thousands separator has become a space instead of a comma, and the decimal point is now a comma (as expected), but there are still dollar signs in B1:B4.

It would be great if you (and others whose Windows systems don't have $ by default, like mine) would run that script and report back.

HTH

John