Bug: Issue with @ in table formulas

[nick-schultz]

Current behavior

in a table formula, the string '@ is getting converted to '[#This Row],

This is happening here (line 3466): https://github.com/jmcnamara/XlsxWriter/blob/172211873cd2fabe67876722a523b9bf9771d613/xlsxwriter/worksheet.py#L3457-L3472

Expected behavior

would expect the '@ to be left alone as it is escaped.

In the documentation: See the Microsoft documentation on Using structured references with Excel tables for details.

Use an escape character for some special characters in column headers Some characters have special meaning and require the use of a single quotation mark (') as an escape character. For example: =DeptSalesFYSummary['#OfItems]

Here’s the list of special characters that need an escape character (') in the formula:

Left bracket ([)

Right bracket (])

Pound sign(#)

Single quotation mark (')

At sign (@)

Sample code to reproduce

import xlsxwriter
from xlsxwriter.utility import xl_range

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

data = [[1],[2]]

columns = [
          {"header": "CL"},
          {"header": "p4web", "formula": '=HYPERLINK(CONCAT("http://myweb.com:1677/\'@md=d&path/to/sour/...\'@/",[@CL],"?ac=10"),[@CL])'},              
         ]

# Add a table to the worksheet.
cell_range = xl_range(0, 0, len(data), len(columns))
worksheet.add_table(cell_range,
    {
        'header_row': True,
        "data": data,            
        "columns": columns,
    },
)
workbook.close()

Environment

- XlsxWriter version:
- Python version:
- Excel version:
- OS:

Any other information

No response

OpenOffice and LibreOffice users

• [X] I have tested the output file with Excel.

[jmcnamara]

Thanks for the detailed report. I'll look into it.

[jmcnamara]

Thanks for the detail report. That is now fixed on main. The output from your sample code now looks like this: