Problem with number formats

[Marta lop seb]

Hello,

I am doing an automation project for the company I work for in which I have some dataframes which I want to put in a specific excel format. My problem comes with the format of the numbers as I want to put it in ‘currency’ mode in euros but it only lets me do it in dollars (‘#,##0.00$’). Is there any way to do it in this format but with euros?

Any help would be very very welcome :) Thank you,

Marta López Sebastiá

[Charlie Clark]

Hola Marta,

unfortunately Excel elides a lot of information in such situations and the best thing to do is to look at the source code of a Workbook that you've created with the relevant style. In the case of number formats it's possible to do this in Openpyxl. Here's an example based on a file I just created

from openpyxl import load_workbook
wb = load_workbook("currency.xlsx")
ws = wb.active
ws["A1"].number_format
'#,##0.00\\ "€"'

As you can see the € has to be quoted.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Marta lop seb]

Hello Charlie,

It doesnt work for me either with the format that you propose. Is there a way to set it in accountancy mode? Even without the € sign at the end? 

Thank you very much for your help at both of my questions :)

Marta López Sebastiá

[Charlie Clark]

On 6 Jun 2024, at 11:18, Marta lop seb wrote:

> Hello Charlie,
>
> It doesnt work for me either with the format that you propose. Is there a
> way to set it in accountancy mode? Even without the € sign at the end?
> Thank you very much for your help at both of my questions :)

Marta,

I think you need to provide more information about what precisely doesn't work, what you've tried and what you don't understand about the documentation.

[Marta lop seb]

I am working on an automatization of a process to generate an excel with accountancy information. I have my datasets configurated and I want to put them on an especific format at Excel. I am from Spain so I want to put the money information in € format, such as 89.234,12€ but the number_format of openpyxl doesn't let me do that. I tried what you told me and this other way '#,##0.00"€"' and it doesn't write the euro sign. I have my code at coding latin-1.

```

    for row in ws["A16:K16"]:
        for cell in row:
            cell.font = Font(name='Calibri', size = 11, bold = True)
    letr = list(string.ascii_uppercase)[0:11]
    for j in letr:
        if j in list(string.ascii_uppercase)[0:7]:
            for i in ws[j]:
                i.alignment = Alignment(horizontal = "center")
        if j in ['C','E','G','H']:
            for i in ws[j]:
                i.number_format = '0.00%'
        if j in ['D','I','J','K']:
            for i in ws[j]:
                i.number_format = '#,##0.00'
        if j == 'F':
            for i in ws[j]:
                i.number_format = '#,##0_-'

```

Other issue, I'm also doing charts with openpyxl, and I'm trying to set labels just above the columns. I have two issues here, one is that the labels aren't the ones from the column (€) there are the variation of the ranking over the previous year.

The other issue is that I want to custom that label to put it green when the value is negative and red if the value is positive. Is that possible?

Thank you so much for your information.