Set the currency symbol when writing with xlsxwriter

Question

I'm having trouble writing the currency symbol I want in my currency values with xlsxwriter.

I followed the tutorial here and I'm able to write out currency values with the correct formatting and a dollar sign (whether this is from the tutorial or excel's default settings I'm not sure).

This works:

money = workbook.add_format({'num_format':'$#,##0.00'})

And it prints out a currency value with the dollar sign.

$1,000.00

But if I try to insert my own currency, let's say R:

money = workbook.add_format({'num_format':'R#,##0.00'})

I get this:

R1000

How can I set the currency symbol using xlsxwriter?

Hootman

What do you see if you remove the R? 1000 or 1,000.00? What if you put a space after the R, or place the R after the numbers? – Agnosticism

1,000.00, 1000, 1000, respectively. – Hootman

Yes, the R and formatting disappears, wierd. – Hootman

Answer 1

Please try:

num_format('"R" #,##0.00')  

format.set_num_format()

Herpetology

How to add a similar check for numbers with varying sizes like R 1,000 and R 1,24,546. I'm trying like this but it isn't working-<br /> num_format('"R" #,##0;[>=100000]"R" #,##,##0') Btw, you might want to update the link in your answer :) – Pyrophotometer

MWE would be nice :) – Systematism

Answer 2

Try something like the following:

import xlsxwriter

workbook = xlsxwriter.Workbook('money_format.xlsx')
worksheet = workbook.add_worksheet()

money_format = workbook.add_format({'num_format': '[$R]#,##0.00'})

worksheet.write('A1', 1234.56, money_format)

workbook.close()

The best way to determine the number format that you need is to format the cell in Excel and then edit it and look at what the custom format is.

It may be something like [$R-431]#,##0.00 or something quite complicated if positive and negative values are handled differently.

Update: See also the Number Format Categories section of the XlsxWriter Format docs.

Criss

Thanks for your answer, I haven't tested it with negative values, I'll have a look. – Hootman

Yes, it is a locale code. That's why I'd recommend getting the correct format string directly from Excel. – Criss

Answer 3

Perfectly working in all version

currency_id = fields.Many2one('res.currency', 'Currency',default=lambda self: self.env.user.company_id.currency_id)

style5 = xlwt.easyxf('font:bold True;', num_format_str=self.currency_id.symbol + '#,##0.00')

Rayleigh