Python Excel

Python Excel Charts

openpyxl can add a bar, line, scatter, or pie chart to a worksheet from the cells that already hold the data. Excel draws the chart when the file is opened. The steps are the same for each chart type.

  1. Put the numbers in a rectangular block of cells.
  2. Build a Reference that points at that block.
  3. Create a chart object and add the reference as its data.
  4. Set the chart size if you want something other than the default.
  5. Anchor the chart on the worksheet and save the workbook.

Older openpyxl releases used openpyxl.charts and Series objects. That package was removed. The examples below use openpyxl.chart, which is what current openpyxl installs.

Bar chart

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
sheet = wb.active

for i in range(1, 11):
    sheet["A" + str(i)] = i

chart = BarChart()
chart.title = "Sample values"
data = Reference(sheet, min_col=1, min_row=1, max_row=10)
chart.add_data(data, titles_from_data=False)
chart.shape = 4
chart.width = 15
chart.height = 8
sheet.add_chart(chart, "C2")
wb.save("example.xlsx")

The loop writes 1 through 10 down column A. Reference describes that range: column 1, rows 1 to 10. add_chart(chart, "C2") places the top-left of the chart at cell C2. Width and height are in centimetres, roughly.

The reference

Reference needs the worksheet and the corners of the data.

  • min_col and max_col are column numbers. Column A is 1.
  • min_row and max_row are row numbers.
  • Set titles_from_data=True when the first row of the reference is a series name rather than a value.

Line, scatter, and pie

Swap the chart class. The reference and the add_chart call stay the same.

from openpyxl.chart import LineChart, ScatterChart, PieChart

line_chart = LineChart()
scatter_chart = ScatterChart()
pie_chart = PieChart()

A pie chart expects one series of values. A scatter chart is more useful with two columns, one for x and one for y. Pass the x values with chart.set_categories(Reference(...)) or, for a true scatter, chart.x_axis together with a series xVal reference. For a first chart, the bar example above is the one to get saving and opening in Excel.