Generating Charts in Excel with openpyxl

Generating charts in Excel using the openpyxl library in Python is a powerful way to automate the creation of visual data representations. openpyxl supports various chart types such as bar charts, line charts, pie charts, scatter plots, and more. This article will guide you through the process of generating charts in Excel files with practical examples and explanations.

Understanding openpyxl Charting Capabilities

openpyxl allows you to create Excel files and manipulate their content programmatically. Among its features, chart creation is crucial for representing data visually. It supports multiple chart classes under the openpyxl.chart module, including:

• BarChart

• LineChart

• PieChart

• ScatterChart

• AreaChart

• Reference (used to define data ranges for charts)

• Series (used to define individual data series)

Basic Steps to Generate a Chart in Excel with openpyxl

1. Install openpyxl
   Make sure you have the openpyxl library installed:

   bash
   CopyEdit
   pip install openpyxl
   

2. Create or Load a Workbook
   You can start with a new workbook or load an existing one:

   python
   CopyEdit
   from openpyxl import Workbook
   
   wb = Workbook()
   ws = wb.active
   

3. Add Data to Worksheet
   Charts need data to visualize, so you’ll first populate the worksheet:

   python
   CopyEdit
   data = [
       ['Month', 'Sales'],
       ['Jan', 40],
       ['Feb', 30],
       ['Mar', 50],
       ['Apr', 70],
       ['May', 60],
   ]
   
   for row in data:
       ws.append(row)
   

4. Create a Chart Object
   Choose the type of chart you want. For example, a bar chart:

   python
   CopyEdit
   from openpyxl.chart import BarChart, Reference
   
   chart = BarChart()
   

5. Define Data Range for the Chart
   Use the Reference class to specify the data to plot:

   python
   CopyEdit
   data_ref = Reference(ws, min_col=2, min_row=1, max_row=6)
   categories_ref = Reference(ws, min_col=1, min_row=2, max_row=6)
   
   chart.add_data(data_ref, titles_from_data=True)
   chart.set_categories(categories_ref)
   

6. Add Chart to Worksheet
   Position the chart on the worksheet:

   python
   CopyEdit
   ws.add_chart(chart, "E5")
   

7. Save the Workbook
   Save your Excel file with the embedded chart:

   python
   CopyEdit
   wb.save("sales_chart.xlsx")
   

Complete Example: Creating a Bar Chart

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

wb = Workbook()
ws = wb.active

# Data
data = [
    ['Month', 'Sales'],
    ['Jan', 40],
    ['Feb', 30],
    ['Mar', 50],
    ['Apr', 70],
    ['May', 60],
]

for row in data:
    ws.append(row)

# Create chart
chart = BarChart()
data_ref = Reference(ws, min_col=2, min_row=1, max_row=6)
categories_ref = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)
chart.title = "Monthly Sales"
chart.y_axis.title = "Sales"
chart.x_axis.title = "Month"

# Add chart to worksheet
ws.add_chart(chart, "E5")

# Save file
wb.save("sales_chart.xlsx")

Other Chart Types and Customizations

• Line Chart:

python
CopyEdit
from openpyxl.chart import LineChart

chart = LineChart()
# Use the same data and categories references as before
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)
chart.title = "Monthly Sales Trend"

• Pie Chart:

python
CopyEdit
from openpyxl.chart import PieChart

chart = PieChart()
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories_ref)
chart.title = "Sales Distribution"

• Scatter Chart:

python
CopyEdit
from openpyxl.chart import ScatterChart, Reference, Series

chart = ScatterChart()
xvalues = Reference(ws, min_col=1, min_row=2, max_row=6)  # For scatter, typically numeric x-axis
yvalues = Reference(ws, min_col=2, min_row=2, max_row=6)
series = Series(yvalues, xvalues, title="Sales Data")
chart.series.append(series)
chart.title = "Sales Scatter Plot"

Customizing Charts

You can further customize charts with properties such as colors, styles, gridlines, legend positioning, and axis formatting. For example:

• Change Chart Style:

  python
  CopyEdit
  chart.style = 10  # Excel predefined style number
  

• Modify Axis Properties:

  python
  CopyEdit
  chart.x_axis.majorGridlines = None
  chart.y_axis.minorGridlines = None
  chart.x_axis.title = "Custom X Axis"
  

• Adjust Legend:

  python
  CopyEdit
  chart.legend.position = 'b'  # Bottom
  

Tips for Effective Chart Generation

• Always verify your data ranges using Reference to avoid including empty or unwanted cells.

• Use meaningful titles and axis labels to improve readability.

• For large datasets, consider filtering or summarizing data before charting.

• Combine multiple series to compare datasets within the same chart.

• Save your file and open it in Excel to confirm that the chart renders correctly.

Conclusion

Automating Excel chart creation with openpyxl can streamline reporting and data visualization tasks. By following the simple steps of preparing data, selecting chart types, setting data references, and adding charts to worksheets, you can generate professional charts programmatically. Whether it’s a bar chart, line chart, pie chart, or scatter plot, openpyxl provides a flexible API to tailor charts to your exact needs.

Mastering this technique can save time and enhance data presentation for business reports, dashboards, or any Excel-based project.