Openpyxl: How to Create Combined Bar and Line Charts in Excel

To create a combined bar and line chart, you can utilize the openpyxl library to interact with Excel files and leverage its charting capabilities. This guide provides an example code demonstrating the process.

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

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Add data
data = [
    ['Month', 'Sales', 'Profit'],
    ['Jan', 1500, 300],
    ['Feb', 1800, 400],
    ['Mar', 1200, 200],
    ['Apr', 2000, 500],
    ['May', 2400, 600],
    ['Jun', 1800, 400]
]

for row in data:
    ws.append(row)

# Create the bar chart
bar_chart = BarChart()
bar_chart.title = 'Sales and Profit'
bar_chart.y_axis.title = 'Value'
bar_chart.x_axis.title = 'Month'

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
categories = Reference(ws, min_col=1, min_row=2, max_row=7)

bar_chart.add_data(data, titles_from_data=True)
bar_chart.set_categories(categories)

# Create the line chart
line_chart = LineChart()
line_chart.title = 'Sales and Profit'
line_chart.y_axis.title = 'Value'
line_chart.x_axis.title = 'Month'

line_data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
line_categories = Reference(ws, min_col=1, min_row=2, max_row=7)

line_chart.add_data(line_data, titles_from_data=True)
line_chart.set_categories(line_categories)

# Combine the line chart with the bar chart
bar_chart += line_chart

# Add the chart to the Excel sheet
ws.add_chart(bar_chart, 'E5')

# Save the Excel file
wb.save('chart.xlsx')

Running this code generates an Excel file named 'chart.xlsx' containing the combined bar and line chart. This approach allows for effective visualization of multiple datasets on a single chart.