Excel not opening my file when I try to write formulas using python

[AN513]

I have an portuguese language Excel (don't know if that matters) and when I write an simple =IF(x;y;z) formula or (in portuguese) =SE(x,y,z) using openpyxl I cant open the file, I receive an error and the excel sheet clears that cell that I wrote the formula.

Sorry bad english.

Please help me.

[AN513]

Forgot to mention that if I copy paste the formula (in case of being in portuguese) the formula works just fine but when my python program does that it doesn't work. (also tried write in english but doesn't work).

[Conny Söderholm]

Can you post your code?

Have you used this syntax to insert a value? 

 "=SUM(1, 1)"

Remember that you need to use English locale in the formulas you insert with OpenPyXL

[AN513]

Thank you in advance for you're help!

=IF(F40>2; Constantes!E13 * F39 * (F38/100); IF(F40>1; Constantes!E12 * F39 * (F38/100); IF(F40>0; Constantes!E11 * F39 * (F38/100); -1)))

program:

formula1 = 'IF(' + turnos_cell + '>0; ' + turno1 + ' * ' + main_formula + '; -1)'
formula2 = 'IF(' + turnos_cell + '>1; ' + turno2 + ' * ' + main_formula + '; ' + formula1 + ')'
formula3 = '=IF(' + turnos_cell + '>2; ' + turno3 + ' * ' + main_formula + '; ' + formula2 + ')'

escrever_celula(novo_documento, sheet_names[0], 'F42', formula3) # A function made by me to write to a cell  using openpyxl

this is what is written to the cell is 3 nested ifs (I also tryed the same formula but in portuguese but I stil couldn't open the file after writing with python)4

Thank you

[Conny Söderholm]

You need to use english locale in the formula inserted with openpyxl. Replace ; with , and translate the function names to english.

--
You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/Koe5aAGL2hQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/fa9cf030-7953-418e-b841-062505d9b5ecn%40googlegroups.com.

[AN513]

Ohhh in portuguese we must use ;  I've been with this bug for a few days... thank you very much :) 

A happy portuguese student,

Afonso Nóia

[Charlie Clark]

On 18 Mar 2021, at 11:42, AN513 wrote:

> Ohhh in portuguese we must use ; I've been with this bug for a few
> days...
> thank you very much :)

See: https://openpyxl.readthedocs.io/en/stable/usage.html#using-formulae

Microsoft's attempt to localise everything was maybe a nice idea but, as
with the best of intentions, ends up causing all kinds of problems. The
German translations in my local copy are often quite baffling to me.

Charlie

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

[The Tester Boy]

I am trying to insert this formula : =@FILTER(CP!$B$5:$B$13,(TDS_Conso_Summary!E2=CP!$A$5:$A$13)*(TDS_Conso_Summary!F2=CP!$C$5:$C$13))

but I am unable to, the code is executed but the excel removes it automatically.

Instead of using @ operator I tried using index, even that didn't work 
Code:

for row in range(2,ws_TDS_Conso_Summary.max_row+1):

formula = f"=INDEX(FILTER(CP!$B$5:$B$13, (TDS_Conso_Summary!E{row}=CP!$A$5:$A$13)*(TDS_Conso_Summary!F{row}=CP!$C$5:$C$13)), 1)"
ws_TDS_Conso_Summary[f'K{row}'] = formula

Error in excel:
Refer attachment