xlsxwriter - Creating Table Formulas using Structural References

I am trying to create a table in Excel using XLSX writer where a lot of the data is precomputed, but a few columns need running formulas.
I am trying to use structural references (headers as reference) to improve readability of the formulas in the table.
However, upon opening the generated file, I get a warning that the file has to be repaired and the formula is zero'd out.

Here's code that has the same idea as my actual code and recreates my problem:

import xlsxwriter

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

data = [
    ['SOH', 'SOO', 'Actual Order', 'Total'],  # Headers
    [10, 5, 3, None],
    [20, 10, 7, None],
    [15, 8, 6, None]
]

for row, row_data in enumerate(data):
    worksheet.write_row(row, 0, row_data)

worksheet.add_table('A1:D4', {
    'name': 'orderTable',
    'columns': [
        {'header': 'SOH'},
        {'header': 'SOO'},
        {'header': 'Actual Order'},
        {'header': 'Total'}
    ]
})

for row in range(1, 4):
    formula = '=orderTable[@[SOH]] + orderTable[@[SOO]] + orderTable[@[Actual Order]]'
    worksheet.write_formula(row, 3, formula)

workbook.close()

What I have tried so far:

• Confirmed that the table is created and named correctly in Excel
• Pasting the formula as is in the cell it would be placed -> in this case the formula works as intended
• Using direct cell references (E.g. B1, B2 etc) -> This actually works, but I prefer using structural references instead for readability
• I tried both with and without table name in front (e.g. [@[SOH]] and orderTable[@[SOH]]
• I tried removing/changes inner brackets and using quotation marks (' ') around the variable names
• I confirmed that the headers do not contain any odd characters that could cause issues in creating the references
• I tried using write_array_formula and some other formats without any luck

Thanks in advance!

The structured formula that Excel displays in a Table isn't the formula that it stores. In addition to this Excel has changed the syntax of referring to table elements from [#This Row],[Column] to @[Column] over time but it still uses the former in the stored formula.

XlsxWriter tries to account for this and in most cases it will modify and store the required formula correctly. For example the program would generate the required output if the formula was added to the table column parameters like this:

import xlsxwriter

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

data = [
    ['SOH', 'SOO', 'Actual Order', 'Total'],  # Headers
    [10, 5, 3, None],
    [20, 10, 7, None],
    [15, 8, 6, None]
]

for row, row_data in enumerate(data):
    worksheet.write_row(row, 0, row_data)

worksheet.add_table('A1:D4', {
    'name': 'orderTable',
    'columns': [
        {'header': 'SOH'},
        {'header': 'SOO'},
        {'header': 'Actual Order'},
        {'header': 'Total',
         'formula': 'orderTable[@[SOH]] + orderTable[@[SOO]] + orderTable[@[Actual Order]]'}
    ]
})

workbook.close()

Output:

This is explained, to some extent, in the XlsxWriter docs on table columns.

If you wish to explicitly set the formula, like in the for() loop of your example you will need to explicitly expand the formula yourself to the following:

for row in range(1, 4):
    formula = 'orderTable[[#This Row],[SOH]] + orderTable[[#This Row],[SOO]] + orderTable[[#This Row],[Actual Order]]'
    worksheet.write_formula(row, 3, formula)