python xlsxwriter @ symbol added to formula

user_19748537 0
Viewed 34

I'm using the xlsxwriter python module..

# Calculate Sum of Column
for column in range(11, 12):
    # Determine where we will place the formula
    cell_location = xl_rowcol_to_cell(number_rows+2, column)
    # Get the range to use for the sum formula
    start_range = xl_rowcol_to_cell(1, column)
    print(start_range)
    end_range = xl_rowcol_to_cell(number_rows, column)
    print(end_range)
    # Construct and write the formula
    formula = "=SUM(ABS({:s}:{:s}))".format(start_range, end_range)
    print(formula)
    sheet.write_formula(cell_location, formula, fmt)

However, when I execute this code, an @ symbol is added which causes the formula to not work:

=SUM(ABS(@L2:L16))

it should be =SUM(ABS(L2:L16))

Has anyone experienced something alike?

xlsxwriter python
1 Answers

This indicates that the formula is a "Dynamic Formula" and you need to write it with the write_dynamic_array_formula() method:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write_dynamic_array_formula('A1', '=SUM(ABS(L2:L16))')

workbook.close()

Output:

enter image description here

This is explained in the XlsxWriter docs in the section on Dynamic Arrays - The Implicit Intersection Operator "@".

The important part is here:

If you are encountering the Implicit Intersection Operator “@” for the first time then it is probably from a point of view of “why is Excel/XlsxWriter putting @s in my formulas”. In practical terms if you encounter this operator, and you don’t intend it to be there, then you should probably write the formula as a CSE or dynamic array function using write_array_formula() or write_dynamic_array_formula() (see the previous section on Dynamic Array support).

user_102380
Related