夜雨聆风一、图表概述
OpenPyXL支持在Excel文件中创建多种类型的图表，包括柱状图、折线图、饼图、散点图等。
from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referencewb = Workbook()ws = wb.active# 添加数据data = [ ['月份', '销售额'], ['1月', 100], ['2月', 120], ['3月', 140], ['4月', 160],]for row in data: ws.append(row)# 创建柱状图chart = BarChart()chart.title = '月度销售趋势'chart.x_axis.title = '月份'chart.y_axis.title = '销售额'# 数据范围data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)categories = Reference(ws, min_col=1, min_row=2, max_row=5)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)ws.add_chart(chart, 'E2')wb.save('chart_basic.xlsx')二、柱状图
2.1 基本柱状图
from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referencefrom openpyxl.chart.series import Serieswb = Workbook()ws = wb.active# 准备数据data = [ ['产品', 'Q1', 'Q2', 'Q3', 'Q4'], ['产品A', 100, 120, 110, 130], ['产品B', 80, 90, 100, 110], ['产品C', 120, 110, 130, 140],]for row in data: ws.append(row)# 创建柱状图chart = BarChart()chart.title = '季度销售对比'chart.x_axis.title = '产品'chart.y_axis.title = '销售额'chart.style = 13# 样式# 数据范围（从第2列开始）data_ref = Reference(ws, min_col=2, min_row=1, max_col=5, max_row=4)categories = Reference(ws, min_col=1, min_row=2, max_row=4)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)# 添加到工作表ws.add_chart(chart, 'F2')wb.save('bar_chart.xlsx')2.2 堆叠柱状图
from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referencewb = Workbook()ws = wb.active# 准备数据data = [ ['产品', '线上', '线下'], ['产品A', 60, 40], ['产品B', 70, 30], ['产品C', 50, 50], ['产品D', 80, 20],]for row in data: ws.append(row)# 创建堆叠柱状图chart = BarChart()chart.title = '销售渠道分布'chart.x_axis.title = '产品'chart.y_axis.title = '占比 (%)'chart.type = 'col'# 柱状图chart.grouping = 'stacked'# 堆叠chart.overlap = 100# 数据范围data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=5)categories = Reference(ws, min_col=1, min_row=2, max_row=5)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)ws.add_chart(chart, 'E2')wb.save('stacked_bar.xlsx')2.3 百分比堆叠柱状图
from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referencewb = Workbook()ws = wb.active# 准备数据（百分比）data = [ ['产品', '渠道A', '渠道B', '渠道C'], ['产品A', 40, 35, 25], ['产品B', 30, 40, 30], ['产品C', 50, 30, 20],]for row in data: ws.append(row)# 创建百分比堆叠柱状图chart = BarChart()chart.title = '销售渠道占比'chart.x_axis.title = '产品'chart.y_axis.title = '占比 (%)'chart.type = 'col'chart.grouping = 'percentStacked'chart.overlap = 100data_ref = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=4)categories = Reference(ws, min_col=1, min_row=2, max_row=4)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)ws.add_chart(chart, 'F2')wb.save('percent_stacked_bar.xlsx')三、折线图
3.1 基本折线图
from openpyxl import Workbookfrom openpyxl.chart import LineChart, Referencewb = Workbook()ws = wb.active# 准备数据data = [ ['月份', '产品A', '产品B', '产品C'], ['1月', 100, 80, 120], ['2月', 110, 90, 130], ['3月', 120, 100, 140], ['4月', 130, 110, 150], ['5月', 140, 120, 160],]for row in data: ws.append(row)# 创建折线图chart = LineChart()chart.title = '月度销售趋势'chart.x_axis.title = '月份'chart.y_axis.title = '销售额'chart.style = 12# 数据范围data_ref = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=6)categories = Reference(ws, min_col=1, min_row=2, max_row=6)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)# 添加数据标签for series in chart.series: series.marker.symbol = 'circle' series.marker.size = 5ws.add_chart(chart, 'H2')wb.save('line_chart.xlsx')3.2 带数据标记的折线图
from openpyxl import Workbookfrom openpyxl.chart import LineChart, Referencefrom openpyxl.chart.marker import Markerwb = Workbook()ws = wb.activedata = [ ['月份', '实际', '预测'], ['1月', 100, 95], ['2月', 120, 115], ['3月', 140, 135], ['4月', 160, 155], ['5月', 180, 175],]for row in data: ws.append(row)chart = LineChart()chart.title = '实际vs预测'chart.x_axis.title = '月份'chart.y_axis.title = '销售额'data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=6)categories = Reference(ws, min_col=1, min_row=2, max_row=6)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)# 设置系列样式for idx, series inenumerate(chart.series): series.marker.symbol = 'circle'if idx == 0else'diamond' series.marker.size = 6 series.graphicalProperties.solidFill = 'FF0000'if idx == 0else'0000FF' series.graphicalProperties.line.width = 20000# 2ptws.add_chart(chart, 'F2')wb.save('marked_line_chart.xlsx')四、饼图
4.1 基本饼图
from openpyxl import Workbookfrom openpyxl.chart import PieChart, Referencewb = Workbook()ws = wb.active# 准备数据data = [ ['产品', '销售额'], ['产品A', 100], ['产品B', 80], ['产品C', 60], ['产品D', 40], ['产品E', 20],]for row in data: ws.append(row)# 创建饼图chart = PieChart()chart.title = '产品销售分布'# 数据范围data_ref = Reference(ws, min_col=2, min_row=1, max_row=6)labels = Reference(ws, min_col=1, min_row=2, max_row=6)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(labels)chart.dataLabels.showVal = True# 显示数值chart.dataLabels.showCatName = True# 显示类别名ws.add_chart(chart, 'D2')wb.save('pie_chart.xlsx')4.2 圆环图
from openpyxl import Workbookfrom openpyxl.chart import PieChart, Referencefrom openpyxl.chart.piechart import PieChart3Dwb = Workbook()ws = wb.activedata = [ ['季度', '销售额'], ['Q1', 100], ['Q2', 120], ['Q3', 110], ['Q4', 130],]for row in data: ws.append(row)# 创建圆环图chart = PieChart()chart.title = '季度销售分布'chart.holeSize = 50# 圆环大小百分比data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)labels = Reference(ws, min_col=1, min_row=2, max_row=5)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(labels)ws.add_chart(chart, 'E2')wb.save('donut_chart.xlsx')五、散点图
5.1 基本散点图
from openpyxl import Workbookfrom openpyxl.chart import ScatterChart, Referencefrom openpyxl.chart.series import Serieswb = Workbook()ws = wb.active# 准备数据data = [ ['X', 'Y1', 'Y2'], [1, 2, 3], [2, 4, 5], [3, 6, 7], [4, 8, 9], [5, 10, 11],]for row in data: ws.append(row)# 创建散点图chart = ScatterChart()chart.title = '散点图示例'chart.x_axis.title = 'X轴'chart.y_axis.title = 'Y轴'chart.style = 13# 添加系列1xvalues = Reference(ws, min_col=1, min_row=2, max_row=6)yvalues = Reference(ws, min_col=2, min_row=2, max_row=6)series1 = Series(yvalues, xvalues, title="系列1")chart.series.append(series1)# 添加系列2yvalues2 = Reference(ws, min_col=3, min_row=2, max_row=6)series2 = Series(yvalues2, xvalues, title="系列2")chart.series.append(series2)ws.add_chart(chart, 'E2')wb.save('scatter_chart.xlsx')5.2 带趋势线的散点图
from openpyxl import Workbookfrom openpyxl.chart import ScatterChart, Referencefrom openpyxl.chart.series import Seriesfrom openpyxl.chart.trendline import Trendlinewb = Workbook()ws = wb.activedata = [ ['X', 'Y'], [1, 2], [2, 3.5], [3, 5], [4, 7], [5, 9],]for row in data: ws.append(row)chart = ScatterChart()chart.title = '带趋势线的散点图'xvalues = Reference(ws, min_col=1, min_row=2, max_row=6)yvalues = Reference(ws, min_col=2, min_row=2, max_row=6)series = Series(yvalues, xvalues, title="数据点")series.marker.symbol = 'circle'series.marker.size = 6# 添加趋势线trendline = Trendline()trendline.name = '趋势线'series.trendline = trendlinechart.series.append(series)ws.add_chart(chart, 'E2')wb.save('trendline_scatter.xlsx')请在微信客户端打开
六、图表样式自定义
6.1 颜色和样式
from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referencefrom openpyxl.chart.colors import Colorfrom openpyxl.chart.graphical import ShapePropertieswb = Workbook()ws = wb.activedata = [ ['类别', '值1', '值2'], ['A', 10, 20], ['B', 15, 25], ['C', 20, 30], ['D', 25, 35],]for row in data: ws.append(row)chart = BarChart()chart.title = '自定义样式'chart.x_axis.title = '类别'chart.y_axis.title = '值'chart.style = 10# 设置颜色colors = ['FF0000', '00FF00', '0000FF', 'FF9900']for idx, series inenumerate(chart.series): series.graphicalProperties.solidFill = colors[idx % len(colors)]data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=5)categories = Reference(ws, min_col=1, min_row=2, max_row=5)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)ws.add_chart(chart, 'F2')wb.save('styled_chart.xlsx')6.2 图表尺寸和位置
from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referencefrom openpyxl.utils.units import points_to_pixelswb = Workbook()ws = wb.activedata = [ ['月份', '销售额'], ['1月', 100], ['2月', 120], ['3月', 140],]for row in data: ws.append(row)chart = BarChart()chart.title = '调整尺寸'data_ref = Reference(ws, min_col=2, min_row=1, max_row=4)categories = Reference(ws, min_col=1, min_row=2, max_row=4)chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)# 设置尺寸（像素）chart.width = 15# 15cmchart.height = 10# 10cm# 设置位置chart.anchor = 'E2'ws.add_chart(chart)wb.save('sized_chart.xlsx')七、实战案例
7.1 综合报表图表
from openpyxl import Workbookfrom openpyxl.chart import BarChart, LineChart, PieChart, Referencefrom openpyxl.chart.series import SeriesclassReportCharts:"""报表图表生成器"""def__init__(self, filename):self.wb = Workbook()self.ws = self.wb.activeself.filename = filenameself._prepare_data()def_prepare_data(self):"""准备数据"""self.data = {'headers': ['月份', '销售额', '成本', '利润', '增长率'],'rows': [ ['1月', 100, 70, 30, '5%'], ['2月', 120, 80, 40, '8%'], ['3月', 140, 90, 50, '10%'], ['4月', 160, 100, 60, '12%'], ['5月', 180, 110, 70, '15%'], ['6月', 200, 120, 80, '18%'], ] }# 写入数据self.ws.append(self.data['headers'])for row inself.data['rows']:self.ws.append(row)defadd_bar_chart(self, position='G2'):"""添加柱状图""" chart = BarChart() chart.title = '月度销售趋势' chart.x_axis.title = '月份' chart.y_axis.title = '金额' chart.style = 10 data_ref = Reference(self.ws, min_col=2, min_row=1, max_col=4, max_row=7) categories = Reference(self.ws, min_col=1, min_row=2, max_row=7) chart.add_data(data_ref, titles_from_data=True) chart.set_categories(categories)self.ws.add_chart(chart, position)defadd_line_chart(self, position='G18'):"""添加折线图""" chart = LineChart() chart.title = '利润增长趋势' chart.x_axis.title = '月份' chart.y_axis.title = '利润' chart.style = 12 data_ref = Reference(self.ws, min_col=4, min_row=1, max_col=4, max_row=7) categories = Reference(self.ws, min_col=1, min_row=2, max_row=7) chart.add_data(data_ref, titles_from_data=True) chart.set_categories(categories)# 设置标记for series in chart.series: series.marker.symbol = 'circle' series.marker.size = 6self.ws.add_chart(chart, position)defadd_pie_chart(self, position='A15'):"""添加饼图""" chart = PieChart() chart.title = '成本构成' data_ref = Reference(self.ws, min_col=4, min_row=1, max_row=7) labels = Reference(self.ws, min_col=1, min_row=2, max_row=7) chart.add_data(data_ref, titles_from_data=True) chart.set_categories(labels)self.ws.add_chart(chart, position)defsave(self):"""保存文件"""self.wb.save(self.filename)# 使用report = ReportCharts('report_charts.xlsx')report.add_bar_chart()report.add_line_chart()report.add_pie_chart()report.save()7.2 销售仪表板
from openpyxl import Workbookfrom openpyxl.chart import BarChart, LineChart, PieChart, Referencefrom openpyxl.styles import Font, PatternFill, AlignmentclassSalesDashboard:"""销售仪表板"""def__init__(self):self.wb = Workbook()self.ws = self.wb.activeself.ws.title = '销售仪表板'self._setup_style()def_setup_style(self):"""设置样式"""# 标题样式self.title_style = Font(bold=True, size=16, color='FFFFFF')self.title_fill = PatternFill(start_color='1E90FF', fill_type='solid')# 表头样式self.header_style = Font(bold=True, size=11, color='FFFFFF')self.header_fill = PatternFill(start_color='4472C4', fill_type='solid')self.header_align = Alignment(horizontal='center', vertical='center')defcreate_dashboard(self):"""创建仪表板"""# 标题self.ws.merge_cells('A1:H1') cell = self.ws['A1'] cell.value = '2024年度销售仪表板' cell.font = self.title_style cell.fill = self.title_fill cell.alignment = Alignment(horizontal='center', vertical='center')self.ws.row_dimensions[1].height = 30# 数据区域 data = [ ['月份', '销售额', '订单数', '客单价', '增长率'], ['1月', 100, 50, 2000, '5%'], ['2月', 120, 55, 2182, '8%'], ['3月', 140, 60, 2333, '10%'], ['4月', 160, 65, 2462, '12%'], ['5月', 180, 70, 2571, '15%'], ['6月', 200, 75, 2667, '18%'], ]# 写入表头for col, header inenumerate(data[0], start=1): cell = self.ws.cell(row=3, column=col) cell.value = header cell.font = self.header_style cell.fill = self.header_fill cell.alignment = self.header_align# 写入数据for row_idx, row_data inenumerate(data[1:], start=4):for col_idx, value inenumerate(row_data, start=1):self.ws.cell(row=row_idx, column=col_idx, value=value)# 调整列宽for col inrange(1, 6):self.ws.column_dimensions[chr(64+col)].width = 15# 添加图表self._add_charts()def_add_charts(self):"""添加图表"""# 柱状图 chart1 = BarChart() chart1.title = '月度销售趋势' chart1.x_axis.title = '月份' chart1.y_axis.title = '销售额' data_ref = Reference(self.ws, min_col=2, min_row=3, max_col=2, max_row=9) categories = Reference(self.ws, min_col=1, min_row=4, max_row=9) chart1.add_data(data_ref, titles_from_data=True) chart1.set_categories(categories)self.ws.add_chart(chart1, 'G3')# 折线图 chart2 = LineChart() chart2.title = '订单量趋势' chart2.x_axis.title = '月份' chart2.y_axis.title = '订单数' data_ref = Reference(self.ws, min_col=3, min_row=3, max_col=3, max_row=9) categories = Reference(self.ws, min_col=1, min_row=4, max_row=9) chart2.add_data(data_ref, titles_from_data=True) chart2.set_categories(categories)self.ws.add_chart(chart2, 'G18')# 饼图 chart3 = PieChart() chart3.title = '客单价分布' data_ref = Reference(self.ws, min_col=4, min_row=3, max_row=9) labels = Reference(self.ws, min_col=1, min_row=4, max_row=9) chart3.add_data(data_ref, titles_from_data=True) chart3.set_categories(labels)self.ws.add_chart(chart3, 'A12')defsave(self, filename):"""保存文件"""self.wb.save(filename)# 使用dashboard = SalesDashboard()dashboard.create_dashboard()dashboard.save('sales_dashboard.xlsx')八、总结
# 快速参考# 1. 柱状图chart = BarChart()chart.title = '标题'chart.x_axis.title = 'X轴'chart.y_axis.title = 'Y轴'chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)# 2. 折线图chart = LineChart()chart.title = '标题'chart.add_data(data_ref, titles_from_data=True)chart.set_categories(categories)# 3. 饼图chart = PieChart()chart.title = '标题'chart.add_data(data_ref, titles_from_data=True)chart.set_categories(labels)# 4. 散点图chart = ScatterChart()chart.title = '标题'series = Series(yvalues, xvalues, title="系列名")chart.series.append(series)# 5. 图表样式chart.style = 10# 预设样式series.graphicalProperties.solidFill = 'FF0000'series.marker.symbol = 'circle'# 6. 添加图表ws.add_chart(chart, 'E2')OpenPyXL提供了完整的图表生成功能，支持多种图表类型和样式定制。通过组合不同的图表类型，可以创建丰富的数据报表和仪表板。掌握图表生成技术可以让Excel报表更加直观和专业。