XlsxWriter adding random character (@) when writing formula

Question

I am trying to write formulas to an xlsx document using XlsxWriter in Python.

What I am trying to write to a cell is:

=SUM(IF($F$5:$F$130=$F$4,I$5:I$130))

But when I try to use XlsxWriter to insert the formula it writes this:

=SUM(IF(@$F$5:$F$130=$F$4,I$5:I$130))

As you can see, there is a random @ from XlsxWriter which causes the #VALUE error. Once I manually remove the @ the formula works as intended.

I printed the formula string to the terminal and it printed as the following:

=SUM(IF($F$5:$F$130=$F$3,I$5:I$130))

How can I fix this?

Relevant snippets of the code are included below...variables are censored to generic

--- Snippet 1 ---

for j in range(0,len(LIST_A)):

    if VAR_A == LIST_VAR_A[1]:
        formula = formula_function(row, col, row_start, LIST_B, LIST_C, j)

        print(formula) # this is where I printed the formula to check for an @

        worksheet.write_formula(row, col, formula, LIST_FORMAT[0])

    else: worksheet.write(row, col, CLASS.LIST_MEMBER[j], LIST_FORMAT[0]) 

    col += 1

--- Snippet 2 ---

def formula_function(row, col, row_start, LIST_B, LIST_C, j):
    
    # rctc ... from xlsxwriter.utility import xl_rowcol_to_cell as rctc

    CELL_A = rctc(row, col-3-spread_depth, row_abs=True, col_abs=True)
     
    CELL_B1 = rctc(row_start+1+len(LIST_B), col-3-j, row_abs=True, col_abs=True)
    CELL_B2 = rctc(row_start-1+len(LIST_C), col-3-j, row_abs=True, col_abs=True)

    CELL_C1 = rctc(row_start+1+len(LIST_B), col, row_abs=True)
    CELL_C2 = rctc(row_start-1+len(LIST_C), col, row_abs=True)
    
    formula = '=SUM(IF(' + CELL_B1  + ':' + CELL_B2 + '=' + CELL_A + ',' + \
              CELL_C1 + ':' + CELL_C2  + '))'

    return formula

@ is the implicit Intersection operator, introduced in Excel 365. It will be included in a formula when you write to .Formula or .Value. To avoid it you must write to .Formula2. Does your library support that? — chris neilsen, Mar 11 '22 at 01:21
You probably want [write_dynamic_array_formula](https://xlsxwriter.readthedocs.io/worksheet.html#write_dynamic_array_formula) — chris neilsen, Mar 11 '22 at 01:29

score 0 · Accepted Answer · answered Mar 11 '22 at 07:39

XlsxWriter isn't inserting @ into the formulas, Excel 365 is displaying it like that to indicate a position in a formula that is implicitly returning a single value when a range or an array could be returned.

From the XlsxWriter docs on Dynamic Arrays - The Implicit Intersection Operator "@":

...

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 (Ctrl+Shift+Enter) or dynamic array function using write_array_formula() or write_dynamic_array_formula().

If your case you should use a dynamic array formula:

import xlsxwriter

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

worksheet.write_dynamic_array_formula(0, 0, 0, 0, '=SUM(IF($F$5:$F$130=$F$4,I$5:I$130))')

workbook.close()

Output:

Thank you so much! This worked like a charm...at first it could not find write_dynamic_array_formula and I realized I was on XlsxWriter version 1.3.3. I updated to the newest version, tried your solution, and everything worked as intended! — HSo, Mar 11 '22 at 09:51

XlsxWriter adding random character (@) when writing formula

1 Answers1