CODEWITHSUNDEEP
pythonaxisexcel-2013xlsxwriter
user3599803
user3599803

Reputation: 7034

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

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

Upvotes: 3

Views: 749

Answers (2)

user3599803
user3599803

Reputation: 7034

Problem actually solved by upgrading my version of the library. My version was 0.5.1 and now is 0.9. I wonder if it was a bug in the earlier version. Works great now, too bad it took me some time to figure out

Upvotes: -1

jmcnamara
jmcnamara

Reputation: 41644

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:

enter image description here

Upvotes: 2

Related Questions