[SOLVED] How to write formulas in openpyxl in german excel version

How to write formulas in openpyxl in german excel version

I have the following german formula in Excel which works perfectly fine:

=WENN(UND(SVERWEIS(B2; '\\path\path\path\path\[filename.xlsx]sheetname'!$A:$C; 3; FALSCH) = 0,01; SVERWEIS(B2; '\\path\path\path\path\[filename.xlsx]sheetname'!$A:$D; 4; FALSCH) = DATUM(9999; 12; 31); XODER(RECHTS(LINKS(SVERWEIS(B2; '\\path\path\path\path\[filename.xlsx]sheetname'!$A:$E; 5; FALSCH); 8); 3) = "150"; RECHTS(LINKS(SVERWEIS(B2; '\\path\path\path\path\[filename.xlsx]sheetname'!$A:$E; 5; FALSCH); 8); 3) = "020")); "Muss neuen Preis bekommen!"; "")

Now I tried to automate the process for all the data I have with python and my englisch formula looks like this:

formula = (
   f'=IF(AND(VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$C, 3, FALSE) = 0.01, '
   f'VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$D, 4, FALSE) = DATE(9999, 12, 31), '
   f'XOR(RIGHT(LEFT(VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E, 5, FALSE), 8), 3) = "150", '
   f'RIGHT(LEFT(VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E, 5, FALSE), 8), 3) = "020")), '
   '"Muss neuen Preis bekommen!", "")'
)

When writing the formula in the cells it translates everything correctly except the XOR-Condition. After that I tried to simply write the german command for XOR (XODER) in my python code which resulted in me getting the "#VALUE" error in excel. I just need to go into the cell and press enter and the formula gets calculated correctly.I tried to force excel to recalculate all formulas in my worksheet with win32 or changing how i write the the formula in my excel sheet. For example I tried this:

formulager1 = (
     f'=WENN(UND('
     f'SVERWEIS(B{row_num}; '
     f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$C; 3; FALSCH) = 0,01; '
     f'SVERWEIS(B{row_num}; '
     f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$D; 4; FALSCH) = DATUM(9999; 12; 31); '
     f'XODER('
     f'RECHTS(LINKS(SVERWEIS(B{row_num}; '
     f\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E; 5; FALSCH); 8); 3) = \"150\"; '
     f'RECHTS(LINKS(SVERWEIS(B{row_num}; '
     f'\'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E; 5; FALSCH); 8); 3) = \"020\"'
     f')); \"Muss neuen Preis bekommen!\"; \"\")'
)

I also tried adding the "_xlfn." prefix which is mentioned in this question:

Nothing I tried seems to work so my question is:

Does anyone of you have an idea how i could correctly write my formula in my excel ? Whether it is changing how I write the formula through the python script or adjusting the code so that all formulas get recalculated or even a way that excel translates the XOR condition correctly, which would be the easiest solution in my estimation.

Solution

For anyone wondering: I found the solution. I used the _xlfn. prefix at the wrong spot.

I tried adding it at the front of the whole formula, but it needed to be added in front of the XOR which wouldnt be translated correctly.

formula = (
f'=IF(AND(VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$C, 3, FALSE) = 0.01, '
f'VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$D, 4, FALSE) = DATE(9999, 12, 31), '
f'_xlfn.XOR(RIGHT(LEFT(VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E, 5, FALSE), 8), 3) = "150", '
f'RIGHT(LEFT(VLOOKUP(B{row_num}, \'\\\\path\\path\\path\\path\\[filename.xlsx]sheetname\'!$A:$E, 5, FALSE), 8), 3) = "020")), '
'"Muss neuen Preis bekommen!", "")'
)