Generate Professional Bar Charts in Excel Using Python

In data analysis and report generation scenarios, data visualization is a key means to enhance information delivery efficiency. As one of the most commonly used chart types, bar charts can intuitively display numerical comparisons between different categories. By automatically generating Excel bar charts with Python, not only can large amounts of data be processed in batches, but also the consistency and professionalism of chart formatting can be ensured.

This article introduces how to use the Spire.XLS for Python library to create bar charts in Excel worksheets and customize chart elements such as titles, axes, data labels, and legends. This solution is suitable for scenarios that require dynamic report generation, automated data analysis workflows, or batch export of visualization results.

Environment Setup

First, install the Spire.XLS for Python library:

pip install Spire.XLS

This library provides a complete Excel document operation API, supporting creating, reading, modifying, and converting Excel files without requiring Microsoft Excel application installation.

Core Implementation

Creating Workbook and Populating Data

The first step in creating a bar chart is to prepare the data source. Categorical data and corresponding numerical data need to be organized in the Excel worksheet.

from spire.xls.common import *
from spire.xls import *

def CreateChartData(sheet):
    # Set headers and data
    sheet.Range["A1"].Value = "Country"
    sheet.Range["A2"].Value = "Cuba"
    sheet.Range["A3"].Value = "Mexico"
    sheet.Range["A4"].Value = "France"
    sheet.Range["A5"].Value = "Germany"

    sheet.Range["B1"].Value = "June"
    sheet.Range["B2"].NumberValue = 6000
    sheet.Range["B3"].NumberValue = 8000
    sheet.Range["B4"].NumberValue = 9000
    sheet.Range["B5"].NumberValue = 8500

    sheet.Range["C1"].Value = "August"
    sheet.Range["C2"].NumberValue = 3000
    sheet.Range["C3"].NumberValue = 2000
    sheet.Range["C4"].NumberValue = 2300
    sheet.Range["C5"].NumberValue = 4200

    # Set cell styles
    sheet.Range["A1:C1"].RowHeight = 15
    sheet.Range["A1:C1"].Style.Color = Color.get_DarkGray()
    sheet.Range["A1:C1"].Style.Font.Color = Color.get_White()
    sheet.Range["A1:C1"].Style.VerticalAlignment = VerticalAlignType.Center
    sheet.Range["A1:C1"].Style.HorizontalAlignment = HorizontalAlignType.Center
    sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0"

# Create workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Sales Data"

# Populate chart data
CreateChartData(sheet)

The above code creates a data table with three columns: the first column contains country names (categories), and the second and third columns contain sales data for June and August respectively. By setting cell styles, the header has a dark background with white font, and the value area uses currency format display.

Creating Basic Bar Chart

After data preparation is complete, a bar chart object can be created based on these data ranges.

# Add chart to worksheet
chart = sheet.Charts.Add()

# Set chart data range
chart.DataRange = sheet.Range["A1:C5"]
chart.SeriesDataFromRange = False

# Set chart position and size
chart.LeftColumn = 1
chart.TopRow = 6
chart.RightColumn = 11
chart.BottomRow = 29

# Set chart type to clustered column chart
chart.ChartType = ExcelChartType.ColumnClustered

The Charts.Add() method creates a new chart object in the worksheet. The data range used by the chart is specified through the DataRange property, and SeriesDataFromRange is set to False to indicate manual series data configuration. The chart position is defined by row and column indices, placing the chart below the data here.

Customizing Chart Title and Axes

Chart readability largely depends on the clarity of titles and axes.

# Set chart title
chart.ChartTitle = "Sales Market Analysis by Country"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Set category axis (X-axis)
chart.PrimaryCategoryAxis.Title = "Country"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True

# Set value axis (Y-axis)
chart.PrimaryValueAxis.Title = "Sales Amount (USD)"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.MinValue = 1000
chart.PrimaryValueAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90

PrimaryCategoryAxis represents the horizontal axis, used to display category labels; PrimaryValueAxis represents the vertical axis, used to display numerical scales. Setting HasMajorGridLines = False hides major gridlines, making the chart cleaner. The TextRotationAngle property allows rotation of the axis title angle, set to 90 degrees for vertical display here.

Configuring Data Series and Labels

The visual effect of data series directly impacts the chart's information delivery.

# Configure data series
for cs in chart.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

IsVaryColor = True makes each data point use different colors, enhancing visual distinction. DataLabels.HasValue = True displays specific value labels above each bar, allowing readers to quickly obtain precise data.

Setting Legend Position

The legend helps readers understand what different colors represent.

# Set legend position
chart.Legend.Position = LegendPositionType.Top

Placing the legend at the top of the chart avoids obscuring the data area while maintaining a compact layout.

Saving the File

After completing all settings, save the workbook to a file.

# Save file
workbook.SaveToFile("ClusteredColumn.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Chart Effect Preview

Below is the bar chart effect created by the above operations:

Advanced Customization Features

Creating 3D Bar Charts

In addition to 2D bar charts, 3D bar charts with立体 effects can be created to enhance visual impact.

# Create workbook
workbook_3d = Workbook()
sheet_3d = workbook_3d.Worksheets[0]
sheet_3d.Name = "Sales Data"

# Populate data
CreateChartData(sheet_3d)

# Add 3D bar chart
chart_3d = sheet_3d.Charts.Add()
chart_3d.DataRange = sheet_3d.Range["A1:C5"]
chart_3d.SeriesDataFromRange = False

# Set position and size
chart_3d.LeftColumn = 1
chart_3d.TopRow = 6
chart_3d.RightColumn = 11
chart_3d.BottomRow = 29

# Set chart type to 3D clustered column chart
chart_3d.ChartType = ExcelChartType.Column3DClustered

# Set title and axes (same as 2D chart)
chart_3d.ChartTitle = "Sales Market Analysis by Country"
chart_3d.ChartTitleArea.IsBold = True
chart_3d.ChartTitleArea.Size = 12

chart_3d.PrimaryCategoryAxis.Title = "Country"
chart_3d.PrimaryCategoryAxis.Font.IsBold = True
chart_3d.PrimaryCategoryAxis.TitleArea.IsBold = True

chart_3d.PrimaryValueAxis.Title = "Sales Amount (USD)"
chart_3d.PrimaryValueAxis.HasMajorGridLines = False
chart_3d.PrimaryValueAxis.MinValue = 1000
chart_3d.PrimaryValueAxis.TitleArea.IsBold = True
chart_3d.PrimaryValueAxis.TitleArea.TextRotationAngle = 90

for cs in chart_3d.Series:
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True

chart_3d.Legend.Position = LegendPositionType.Top

# Save 3D chart file
workbook_3d.SaveToFile("ClusteredColumn_3D.xlsx", ExcelVersion.Version2010)
workbook_3d.Dispose()

Effect preview:

3D bar charts are implemented by setting ChartType to Column3DClustered. This chart type is particularly useful when displaying multi-level data or emphasizing depth perception.

Adjusting Chart Size and Position

In practical applications, it may be necessary to dynamically adjust the chart size and position according to page layout.

# Adjust chart position
chart.LeftColumn = 2
chart.TopRow = 8

# Adjust chart size
chart.RightColumn = 12
chart.BottomRow = 30

By modifying the LeftColumn, TopRow, RightColumn, and BottomRow properties, the position and dimensions of the chart in the worksheet can be precisely controlled.

Customizing Data Label Format

Data labels can be further customized to display richer information.

# Get the first data series
series = chart.Series[0]

# Customize data labels
for dataPoint in series.DataPoints:
    dataPoint.DataLabels.HasValue = True
    dataPoint.DataLabels.HasPercentage = False
    dataPoint.DataLabels.Font.Size = 10
    dataPoint.DataLabels.Font.Color = Color.get_Blue()

By iterating through data points, font size, color, and display content can be set individually for each data label.

Practical Tips

Batch Creating Multiple Charts

When multiple charts need to be created for different datasets, loop structures can be used to simplify the code.

datasets = [
    {"name": "Q1", "range": "A1:D5"},
    {"name": "Q2", "range": "F1:I5"},
    {"name": "Q3", "range": "A10:D14"}
]

for i, dataset in enumerate(datasets):
    chart = sheet.Charts.Add()
    chart.DataRange = sheet.Range[dataset["range"]]
    chart.ChartType = ExcelChartType.ColumnClustered
    chart.ChartTitle = dataset["name"]
    chart.TopRow = 6 + i * 25  # Arrange charts vertically

This method can quickly generate a series of uniformly formatted charts, suitable for quarterly reports or monthly analysis scenarios.

Exporting Charts as Images

Generated charts can be exported as image formats for embedding into other documents or web pages.

# Save chart as image
chart.SaveToImage("chart.png")

This feature allows charts to be reused in PowerPoint presentations, PDF reports, or web pages.

Summary

This article introduced the complete process of creating and customizing bar charts in Excel using Python. Through the Spire.XLS for Python library, the following functions can be easily implemented:

• Create 2D and 3D bar charts based on worksheet data
• Customize chart titles, axes, legends, and data labels
• Adjust chart position, size, and visual styles
• Batch generate multiple charts and export as images

These techniques can be applied to automated report generation, data visualization pipelines, and enterprise-level document processing systems. By combining different chart types and customization options, various complex data presentation requirements can be met.