4

I have a Python 3 script that is loading some data into an Excel file on a Windows machine. I need the cell not just the number to be formatted as Currency.

I can use the following format to set the Number format for a cell:

sheet['D48'].number_format = '#,##0'

However, when I try a similar approach using the number format for Currency:

sheet['M48'].number_format = '($#,##0.00_);[Red]($#,##0.00)'

I get this for the custom format. Notice the extra backslashes, they are being added to the format so it does not match with the pre-defined Currency style.

(\$#,##0.00_);[Red](\$#,##0.00)

I have seen this question and used it to get this far. However the answer does not solve the extra backslash issue I am seeing. Set openpyxl cell format to currency

Community
  • 1
  • 1
analyticsPierce
  • 2,979
  • 9
  • 57
  • 81
  • Who is "they"? Where do you see this? Number formats are just strings to openpyxl. – Charlie Clark Sep 08 '16 at 06:23
  • @CharlieClark I updated the question to be more clear. I see the format with the backslashes in Excel. Is there something about a dollar sign that would cause it to be escaped? Other formats work as expected. – analyticsPierce Sep 08 '16 at 06:27
  • Excel does the escaping automatically presumably because it has poor string handling. You'll see it with things like date formats but it normally doesn't affect the output. There is a mini-language for number formats where the dollar sign plays a role in things like `[$-404]e/m/d` which might explain this. – Charlie Clark Sep 08 '16 at 09:10
  • 1
    @CharlieClark do you know of a way to get around it? Can I specify just "Currency" as the format? – analyticsPierce Sep 08 '16 at 14:22
  • There is nothing to get around, it's just the way Excel works. openpyxl certainly does not this kind of escaping and neither do the various XML libraries. – Charlie Clark Sep 08 '16 at 18:22
  • 1
    @CharlieClark I love openpyxl and know that it is not the problem. But there should be someway to format the cell as Currency. Otherwise all the values are left as text which is unacceptable. The focus of the question is on some kind of solution to make this work. – analyticsPierce Sep 08 '16 at 18:27
  • @analyticsPierce did you ever find a solution to this? I'm also trying to format cell values such that they appear as "Currency" in Excel rather than "Custom", but haven't found any way to achieve this. – Jordan Apr 23 '20 at 01:28

2 Answers2

0

I just formatted before placing into the cell.

"${:10,.2f}".format(7622086.82)

'$7,622,086.82'

rabkaman
  • 121
  • 1
  • 6
0

I formatted the cell in Excel, and then copied the format.

This worked for me

.number_format = '[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00'
Bruno
  • 623
  • 4
  • 6