Search code examples
pythonexcelxlsxwriter

How to prevent Excel/XlsxWriter randomly adding @ character in my formulas

Context

I use to write some data into an xlsx file from a code.
I need to fill a cell with a formula that calculates the average of a specific column (that contains time values).

Problem Description

I noticed that when I try to open my document with Excel, it automatically adds an '@' symbol right before my range which makes my formula calculation fail...
If I manually remove this extra '@', the formula works perfectly as expected.

It is really annoying to have to manually fix my formula like that since I have a lot of similar average calculations that are broken because of this.
I read that it is Excel that adds it so it seems to be no way to prevent this undesired behaviour programmatically from my python code with the xlsxwriter API.

Question

Does someone know if there is a way to prevent Excel to perform any modification of my formulas ?

What I have tried so far

I already tried to "protect" all of my sheets (make them read-only) but it seems to apply only for the user, not for Excel...

Example

Here is an example of my issue (to make it more understandable that this load of plain text).

From my python code, if I write the following formula:

=AVERAGE(TIMEVALUE(B2:B8))

After opening the document, Excel automatically changes the formula as:

=AVERAGE(TIMEVALUE(@B2:B8))

Which make the result of the calculation become:

#VALUE!

If I undo what Excel does by manually restoring my formula (manually edit the cell and remove the '@'), I get the desired calculated average value as I expected it to be.

Solution

  • In general if Excel inserts a @ in a formula written by XlsxWriter it means that formula is an array formula and should have been written with worksheet.write_array_formula() (although there are some caveats, see below).

    So with a variant of your example:

    import xlsxwriter

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

worksheet.write_array_formula('A1', '=AVERAGE(TIMEVALUE(B1:B6))')

worksheet.write_column('B1', ['2:30'] * 6)

workbook.close()

    Output:

    enter image description here

    However, recent versions of Excel have introduced dynamic formulas and dynamic arrays. I think what you are seeing here is the Implicit intersection operator: @. I am currently working on support for dynamic formulas and arrays in XlsxWriter but it is still under investigation.

    Update

    As of release 1.4.1, May 2021, XlsxWriter supports dynamic arrays so the above example could be changed to use write_dynamic_array_formula() like this:

    worksheet.write_dynamic_array_formula('A1', '=AVERAGE(TIMEVALUE(B1:B6))')

    Which would give the following output in Excel 365 (note that it no longer has @ or the array formula braces {}):

    enter image description here