python xlsxwriter - wrong category axis in column chart in Excel 2013

Question

I'm using xlsxwriter to generate a chart. I'm trying to use texts for the category axis, it works in almost every excel version (2007, 2010). But not in excel 2013, and not in Microsoft Excel Online (which seems to be like excel 2013).

The problem: the category axis is displayed as sequence numbers (1,2,3..), instead of the actual text in the cells. This is the relevant part of my the code, which writes the data (a list of 2-sized tuples), and inserts a column chart based on that data.

    xlsxwriter.Workbook('a.xlsx', {'default_date_format': 'dd/mm/yyyy'})
    sheet = workbook.add_worksheet(sheet_name)
    sheet.write_row(0, 0, headers) # Write header row
    sheet.set_column(0, 1, 25) # Column width
    rowCount = 1

    # Write the data
    for text, total in data:
        sheet.write_row(rowCount, 0, (text, total))
        rowCount += 1

    column_chart = workbook.add_chart({'type': 'column'})
    column_chart.set_size({'width': 850, 'height': 600})

    column_chart.set_x_axis({'text_axis': True})
    column_chart.add_series({
        'name': sheet_name,
        'categories': [sheet_name, 1, 0, rowCount, 0], # row, col row, col
        'values': [sheet_name, 1, 1, rowCount, 1],
        'data_labels': {'value': True}
    })
    sheet.insert_chart('D10', column_chart)
    workbook.close()

As I've said, the code outputs good xlsx, that works in every excel besides 2013. The category axis shows the line number in the sheet (1,2,3..) instead of the text value which is assign to the chart in the categories option.

Thanks in advance

Answer 1

There isn't any reason that the XlsxWriter output would work in Excel 2007 and not in Excel 2013. The file format is the default 2007 file format and Excel is very good at backward compatibility.

Also, I don't see the issue you describe. I modified your example to add some sample input data:

import xlsxwriter

workbook = xlsxwriter.Workbook('a.xlsx', 
                               {'default_date_format': 'dd/mm/yyyy'})

sheet_name = 'Data'

sheet = workbook.add_worksheet(sheet_name)
sheet.set_column(0, 1, 25)
rowCount = 1

data = [
    ['Foo', 4],
    ['Bar', 5],
    ['Baz', 6],
]

# Write the data
for text, total in data:
    sheet.write_row(rowCount, 0, (text, total))
    rowCount += 1

column_chart = workbook.add_chart({'type': 'column'})
column_chart.set_size({'width': 850, 'height': 600})

column_chart.set_x_axis({'text_axis': True})
column_chart.add_series({
    'name': sheet_name,
    'categories': [sheet_name, 1, 0, rowCount, 0], # row, col row, col
    'values': [sheet_name, 1, 1, rowCount, 1],
    'data_labels': {'value': True}
})
sheet.insert_chart('D10', column_chart)
workbook.close()

And the output looks correct in Excel 2013: