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.
Reference that points at that block.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.
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.
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.titles_from_data=True when the first row of the reference is a series name rather than a value.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.
Libraries covered: openpyxl, xlrd, xlsxwriter, xlwt, and xlutils.