Python xlsxwriter VLOOKUP in German Excel

user_18664246 0
Viewed 45

I trying for hours to get my VLOOKUP working.

This is an example for an working VLOOKUP before.

=SVERWEIS(E3;Tabelle2!$C$1:$ZZ$5000;117;0)

I generated the xlsx File with Python XLSWriter. With the following options:

        worksheet.protect(
            # password=PASSWORD_BLATTSCHUTZ,
            options={
                "objects": False,
                "scenarios": False,
                "format_cells": True,
                "format_columns": True,
                "format_rows": False,
                "insert_columns": False,
                "insert_rows": False,
                "insert_hyperlinks": True,
                "delete_columns": False,
                "delete_rows": False,
                "select_locked_cells": True,
                "sort": True,
                "autofilter": True,
                "pivot_tables": False,
                "select_unlocked_cells": True,
            }
        )
xlsxwriter excel-formula excel python
2 Answers

You will need to use the English language version of the function name and comma instead of semi-colon.

This is explained in the Working with Formulas section of the XlsxWriter docs in the subsection on Non US Excel functions and syntax:

Excel stores formulas in the format of the US English version, regardless of the language or locale of the end-user’s version of Excel. Therefore all formula function names written using XlsxWriter must be in English:

    worksheet.write_formula('A1', '=SUM(1, 2, 3)')    # OK
    worksheet.write_formula('A2', '=SOMME(1, 2, 3)')  # French. Error on load.

Also, formulas must be written with the US style separator/range operator which is a comma (not semi-colon). Therefore a formula with multiple values should be written as follows:

    worksheet.write_formula('A1', '=SUM(1, 2, 3)')   # OK
    worksheet.write_formula('A2', '=SUM(1; 2; 3)')   # Semi-colon. Error on load.

If you have a non-English version of Excel you can use the following multi-lingual formula translator to help you convert the formula. It can also replace semi-colons with commas.

Using the translator from the link in the previous paragraph it looks like the formula you should use is:

=VLOOKUP(E3,Tabelle2!$C$1:$ZZ$5000,117,0)

This assumes that you have a sheet called "Tabelle2". If not change that too.

user_102380

The problem was not with the german or english excel version.

The type of the cell must be "STANDARD" not "Text" or anything else. Otherwise it won't detect your excel commands.

To do this you just need to overwrite anycell with the following option:

text_format = workbook.add_format()
text_format.set_num_format("@")

worksheet.write(i + STARTROW, j, content, text_format)...
user_186642460
Related