使用Python XlsxWriter设置折线图X轴无效问题排查

The issue here is that when your categories range contains date values, XlsxWriter (and Excel by default) automatically switches the X-axis to a date axis instead of a discrete category axis. This means it’s treating your dates as continuous numerical values (based on Excel’s internal date numbering) rather than using the formatted date strings you’ve defined as discrete labels.

To resolve this, explicitly set the X-axis type to category after adding your series. Here’s how to modify your code:

Report_Chart = workbook.add_chartsheet('Report Numbers')
chart = workbook.add_chart({'type' : 'line'})

# Add your series as before
chart.add_series({'name': 'Report Source 1', 'categories' : '=Daily Report!$B$1:$W$1', 'values': '=Daily Report!$B$2:$W$2'})
chart.add_series({'name': 'Report Source 2', 'categories' : '=Daily Report!$B$1:$W$1', 'values': '=Daily Report!$B$3:$W$3'})

# Force the X-axis to use discrete category labels
chart.set_x_axis({'type': 'category'})

Report_Chart.set_chart(chart)

Why this works: #

• A category axis treats each entry in your categories range as a separate, unordered label (even if they’re dates), displaying exactly the formatted text from your worksheet cells.
• A default date axis plots dates based on chronological spacing, which can skip or reorder labels if there are gaps, and won’t respect your custom "01 April 2020" format unless explicitly configured on the axis.

As an extra check, ensure the cells in Daily Report!$B$1:$W$1 are formatted with your desired date style in the worksheet (using worksheet.set_column() or cell-level formatting) to guarantee the labels appear correctly once the axis type is fixed.

内容的提问来源于stack exchange，提问作者John Hill