1.简介:
openpyxl不仅提供了将数据一行列的形式对应到Excel表格中,以及相关的添加,删除,获取等操作; 还提供了将数据进行图形化展示的相关组件(类),使得数据图形化在excel表格中的展示变得更加简单。
2.举例:
将数据在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表格。