openpyxl不仅提供了将数据一行列的形式对应到Excel表格中,以及相关的添加,删除,获取等操作; 还提供了将数据进行图形化展示的相关组件(类),使得数据图形化在excel表格中的展示变得更加简单。
将数据在excel中进行图形化展示(例:公司近几年销售额和利润的数据图形化):
相关代码如下:
openxls_chart.py:
- # -*- coding: utf-8 -*-
-
- import os
- import sys
- from openpyxl import Workbook
- from openpyxl.chart import BarChart, Series, Reference
- from copy import deepcopy
-
- #初始化Workbook
- wb = Workbook(write_only=True)
- ws = wb.create_sheet()
-
- #创建数据
- rows = [
- ('年份', '营业额', '利润'),
- (2016, 20, 8),
- (2017, 30, 12),
- (2018, 50, 20),
- (2019, 60, 25),
- (2020, 70, 35),
- (2021, 80, 40),
- ]
-
- #添加数据到excel
- for row in rows:
- ws.append(row)
-
- #chart图的生成1
- chart1 = BarChart()
- chart1.type = "col"
- chart1.style = 10
- chart1.title = "近几年营业额/利润对比图"
- chart1.y_axis.title = '数据'
- chart1.x_axis.title = '年份'
-
- data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)
- cats = Reference(ws, min_col=1, min_row=2, max_row=7)
- chart1.add_data(data, titles_from_data=True)
- chart1.set_categories(cats)
- chart1.shape = 4
- ws.add_chart(chart1, "A10")
-
- #chart图的生成2
- chart2 = deepcopy(chart1)
- chart2.style = 11
- chart2.type = "bar"
- chart2.title = "近几年营业额/利润对比图"
- ws.add_chart(chart2, "J10")
-
- #chart图的生成3
- chart3 = deepcopy(chart1)
- chart3.type = "col"
- chart3.style = 12
- chart3.grouping = "stacked"
- chart3.overlap = 100
- chart3.title = "近几年营业额/利润对比图"
- ws.add_chart(chart3, "A27")
-
- #chart图的生成4
- chart4 = deepcopy(chart1)
- chart4.type = "bar"
- chart4.style = 13
- chart4.grouping = "percentStacked"
- chart4.overlap = 100
- chart4.title = "近几年营业额/利润对比图"
- ws.add_chart(chart4, "J27")
-
- #保存文件
- wb.save("bar.xlsx")
运行结果:
% python3 openxls_chart.py
生成了bar.xlsx,打开该excel文件,内容如下:
说明:
(1)BarChart:用于创建柱状图;
(2)deepcopy: 用于深度copy,即将已存在的对象的内容全部复制到新的同类型的对象中;
(3)Workbook相关操作:
wb = Workbook(write_only=True)
ws = wb.create_sheet()wb.save("bar.xlsx"):保存excel表格。