Creating and Customizing Line Charts in Excel Using Python

#python #programming #excel #chart

Line charts are one of the most commonly used chart types for data visualization, particularly suitable for displaying trends in data over time. In data analysis, financial reporting, and market research, line charts can clearly present data fluctuation patterns and growth trends. This article introduces how to create line charts in Excel workbooks using Python and perform various customizations.

Environment Setup

First, install the Spire.XLS for Python library:

pip install Spire.XLS

This library provides a complete Excel file operation API, supporting creation, reading, and modification of Excel documents, including chart generation functionality.

Creating a Basic Line Chart

The basic process for creating a line chart includes: preparing data, adding a chart object, setting the data range, and configuring chart properties.

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

# Create workbook and worksheet
workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Line Chart"

# Prepare sample 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 = "Jun"
sheet.Range["B2"].NumberValue = 3300
sheet.Range["B3"].NumberValue = 2300
sheet.Range["B4"].NumberValue = 4500
sheet.Range["B5"].NumberValue = 6700

sheet.Range["C1"].Value = "Jul"
sheet.Range["C2"].NumberValue = 7500
sheet.Range["C3"].NumberValue = 2900
sheet.Range["C4"].NumberValue = 2300
sheet.Range["C5"].NumberValue = 4200

sheet.Range["D1"].Value = "Aug"
sheet.Range["D2"].NumberValue = 7400
sheet.Range["D3"].NumberValue = 6900
sheet.Range["D4"].NumberValue = 7800
sheet.Range["D5"].NumberValue = 4200

sheet.Range["E1"].Value = "Sep"
sheet.Range["E2"].NumberValue = 8000
sheet.Range["E3"].NumberValue = 7200
sheet.Range["E4"].NumberValue = 8500
sheet.Range["E5"].NumberValue = 5600

# Set header row style
sheet.Range["A1:E1"].Style.Color = Color.get_DarkGray()
sheet.Range["A1:E1"].Style.Font.Color = Color.get_White()
sheet.Range["A1:E1"].Style.HorizontalAlignment = HorizontalAlignType.Center

# Add line chart
chart = sheet.Charts.Add()
chart.ChartType = ExcelChartType.Line

# Set chart data range
chart.DataRange = sheet.Range["A1:E5"]

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

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

# Configure axes
chart.PrimaryCategoryAxis.Title = "Month"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryValueAxis.Title = "Sales (in Dollars)"
chart.PrimaryValueAxis.HasMajorGridLines = False

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

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

Result document preview:

The code above first populates the worksheet with sales data for four countries over six months, then creates a chart object using the Charts.Add() method. The ChartType property is set to ExcelChartType.Line to specify a line chart. The data range is bound to the cell range A1:E5 through the DataRange property.

Adding Data Markers

In certain scenarios, markers need to be added to each data point on the line chart to more clearly identify specific value positions. This effect can be achieved by setting the MarkerStyle property.

# ...

# Change line chart to line chart with markers
chart.ChartType = ExcelChartType.LineMarkers

# ...

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

# Add circular markers to each data series
for cs1 in chart.Series:
    cs = ChartSerie(cs1)
    cs.Format.Options.IsVaryColor = True
    cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
    cs.DataFormat.MarkerStyle = ChartMarkerType.Circle

chart.Legend.Position = LegendPositionType.Top
workbook.SaveToFile("Line_Circle.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Result document preview:

By setting MarkerStyle to ChartMarkerType.Circle, each data point is displayed as a circular marker. In addition to circles, other marker styles such as squares, triangles, or diamonds can also be selected.

Creating 3D Line Charts

For presentation scenarios requiring stronger visual impact, 3D line charts can be used. The 3D effect enhances the立体感 and层次感 of the chart.

# ...

# Create 3D line chart
chart.ChartType = ExcelChartType.Line3D

# ...

# Set axis title rotation angle
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90
chart.PrimaryValueAxis.MinValue = 1000

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

chart.PlotArea.Fill.Visible = False
workbook.SaveToFile("Line_3D.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Result document preview:

The creation method for 3D line charts is similar to 2D charts; simply set ChartType to ExcelChartType.Line3D. Additionally, the angle of axis titles can be rotated by adjusting the TextRotationAngle property to make the chart layout more reasonable.

Customizing Chart Styles

The appearance of line charts can be customized in various ways, including colors, fonts, gridlines, and legend positions.

# Customize chart style
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# Set primary category axis
chart.PrimaryCategoryAxis.Title = "Month"
chart.PrimaryCategoryAxis.Font.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.IsBold = True

# Set primary value axis
chart.PrimaryValueAxis.Title = "Sales (in Dollars)"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.MinValue = 1000

# Hide plot area background
chart.PlotArea.Fill.Visible = False

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

Result document preview:

By accessing the PrimaryCategoryAxis and PrimaryValueAxis objects, the titles, fonts, and gridlines of the horizontal and vertical axes can be configured separately. The PlotArea.Fill.Visible property controls the visibility of the plot area background; setting it to False makes the chart cleaner.

Practical Tips

In practical applications, the following tips can enhance the usability and readability of line charts:

Data Label Display: Display specific values next to each data point by setting DataLabels.HasValue = True, making it easier for readers to quickly obtain information
Multi-Series Color Differentiation: Set IsVaryColor = True to assign different colors to different data series, improving distinguishability
Gridline Control: Decide whether to display gridlines based on data density to avoid cluttering the chart
Legend Position Adjustment: Place the legend at the top or bottom to leave more space for the data area

Summary

This article introduced the complete process of creating line charts in Excel using Python, including implementation methods for basic line charts, line charts with markers, and 3D line charts. By adjusting chart types, data markers, axis properties, and style settings, professional-grade data visualization charts that meet various requirements can be generated.

Line charts are suitable for scenarios such as displaying time series data, trend analysis, and comparative analysis. After mastering these techniques, they can be applied to practical work such as financial report generation, sales data analysis, and market trend research.