CODEWITHSUNDEEP
pythonpython-3.xopenpyxl
Sora Liu
Sora Liu

Reputation: 127

How to display date format in openpyxl chart

I have one excel file:

Excel content

I used openpyxl to draw chart, but displayed date format is not correct:

Chart

my code:

from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.chart import (
ScatterChart,
Reference,
Series,
)

wb = load_workbook(filename = 'cat-test.xlsx')
ws = wb.get_sheet_by_name('ACZ')
chart = ScatterChart()
chart.title = "Scatter Chart"
chart.style = 13
chart.x_axis.title = 'Date'
chart.y_axis.title = 'Count'
chart.x_axis.number_format ='yyyy/mm/dd'
xvalues = Reference(ws, min_col=2, min_row=2, max_row=9)
for i in range(1, 2):
    values = Reference(ws, min_col=i, min_row=1, max_row=9)
    series = Series(values, xvalues, title_from_data=True)
    chart.series.append(series)
ws.add_chart(chart, "A16")
wb.save("cat-test.xlsx")

Please help me to resolve it, why years are 1900, thank you!

Upvotes: 2

Views: 4107

Answers (1)

Sora Liu
Sora Liu

Reputation: 127

I think I resolve it. My reference is: http://openpyxl.readthedocs.io/en/default/charts/line.html#id1

My new code:

from openpyxl.chart.axis import DateAxis
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.chart import (
     LineChart,
     Reference,
     Series,
)
wb = load_workbook(filename = 'cat-test.xlsx')
ws = wb.get_sheet_by_name('ACZ')
chart = LineChart()
chart.title = "Line Chart"
chart.style = 13
chart.x_axis.title = 'Date'
chart.y_axis.title = 'Count'
chart.y_axis.crossAx = 500
chart.x_axis = DateAxis(crossAx=100)
chart.x_axis.number_format ='yyyy/mm/dd'
chart.x_axis.majorTimeUnit = "days"
data = Reference(ws, min_col=1, min_row=1, max_row=9)
chart.add_data(data, titles_from_data=True)
dates = Reference(ws, min_col=2, min_row=2, max_row=9)
chart.set_categories(dates)
ws.add_chart(chart, "A16")
wb.save("cat-test.xlsx")

my excel line chart

Upvotes: 2

Related Questions