xlsxwriter: chart is removed from excel file if dot and underscore in category names

user_13041333 0
Viewed 96

there is something I don't understand:

import xlsxwriter

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

# Create a new Chart object.
chart = workbook.add_chart({'type': 'column'})

# Write some data to add to plot on the chart.
names_ok = ['A', 'B']
names_notok = ['1.1_1', '2.2_2']
data = [1, 2]

worksheet.write_row('A1', names_notok)
worksheet.write_row('A2', data)

chart.add_series({
    'values':     ['Sheet1', 1, 0, 1, 4],
    'categories': ['Sheet1', 0, 0, 0, 4],
})

# Insert the chart into the worksheet.
worksheet.insert_chart('A4', chart)

workbook.close()

running this script using names_ok in line 14 is fine, everything as expected. If using names_notok there is no error from python. But after opening with Office 2016, Excel reports an error and removes the chart from the sheet.

xlsxwriter python
1 Answers

This is a bug of sorts in XlsxWriter.

First off you can work around it by adding a empty category_data list property to the series:

import xlsxwriter

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

chart = workbook.add_chart({'type': 'column'})

names_notok = ['1.1_1', '2.2_2']
data = [1, 2]

worksheet.write_row('A1', names_notok)
worksheet.write_row('A2', data)


chart.add_series({
    'categories':      ['Sheet1', 0, 0, 0, 1],
    'values':          ['Sheet1', 1, 0, 1, 1],
    'categories_data': [],
})

worksheet.insert_chart('A4', chart)

workbook.close()

Output:

enter image description here

The issue occurs because '1.1_1' is a string but can evaluate as a float, since underscores are valid after PEP-0515:

>>> float('1.1_1')
1.11

Update: fixed on xlsxwriter main.

user_102380
Related