真正的勇士,敢于直面银行卡上的余额,敢于正视磅秤上的数字。表格数据是最常见的数据类型,Excel是日常办公中最常接触的文件。
上一章提过Python可以通过
openpyxl
模块处理Excel文件。其实Python处理Excel的三方模块主要有这几个:
-
xlrd
、xlwt
和xlutils
,支持读写Excel文件。
-
openpyxl
,支持读写和修改OpenXML格式的Excel文件。
-
xlswriter
,支持写文件,以及更多Excel功能特性。
-
pandas
,底层依赖xlrd
和xlwt
读写Excel文件,处理数据神器。
-
pywin32
,调用Win32 API操作文件,只适用于Windows。
-
xlwings
,可理解为pywin32
的多平台加强版,有商业版支持。
- 第1类中3个模块,属于同一作者的开源项目,目前已停更,不建议直接使用。
pywin32
和xlwings
基于原生Excel软件提供服务,需提前安装软件,读写性能和功能最佳,xlwings
还有商业版本支持。pandas
侧重数据分析,Excel文件读写只是其数据输入和输出部分。openpyxl
不需要依赖Excel功能,直接读写OpenXML内容,兼容性好。xlswriter
侧重写Excel文件,支持VBA等更多特性。
- 如果企业内大量使用Excel,并用了很多如VBA宏的特性,建议用xlwings。一方面功能全,另一方面可以采买商业付费版支持。
- 对于普通Excel文件自动处理,建议用
openpyxl
,大部分情况下够用。
- GB级以下的数据分析,建议用
pandas
,后续会单独出一个数据分析系列来介绍它。
openpyxl
的使用,它足够应付大部分人日常的表格自动化处理场景。日常处理Excel文件,最常见的应用场景有3类:
- 读写Excel文件
- 合并、分割文档数据
- 报表自动化,批量生成图表
pip install openpyxl
基本使用 【自动化办公|Python处理Excel文件的实用姿势】Excel表格文件中主要元素有这几个:
- sheet(工作表):一个Excel文件可以有多个sheet。
- row/col(行和列),
iter_rows()
和iter_cols()
可以返回所有行和列。 - cell(单元格):和Excel一样用
sheet['A1']
表示,注意行列都以1开始。 - formulae(公式):可以为单元格设置计算公式,和Excel概念一致。
- charts(图表):比如饼图、柱状图等。
- style(样式):支持自定义样式,以及条件格式化等。
- image(图片),支持
pillow
的图像处理。
import pathlib
import datetime
from openpyxl import load_workbookpath = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
in_path = path.joinpath('input.xlsx')
wb = load_workbook(in_path, read_only=True)
for name in wb.sheetnames:
ws = wb[name]
print(f'{name} 表有 {ws.max_row-ws.min_row+1} 行, {ws.max_column-ws.min_column+1} 列.')
读取Excel文件内的数据
import pathlib
import datetime
from openpyxl import load_workbookpath = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
in_path = path.joinpath('input.xlsx')
wb = load_workbook(in_path)
ws = wb[wb.sheetnames[0]] # 第一个工作表
# 操作单列
for cell in ws["A"]:
print(cell.value)
# 操作单行
for cell in ws["1"]:
print(cell.value, end='\t')
# 操作多列
for column in ws['A:C']:
for cell in column:
print(cell.value)
# 操作多行
for row in ws['1:3']:
for cell in row:
print(cell.value, end='\t')
print()
# 指定范围
for row in ws['A1:C3']:
for cell in row:
print(cell.value)
# 所有行
for row in ws.iter_rows():
for cell in row:
print(cell.value)
# 所有列
for column in ws.iter_cols():
for cell in column:
print(cell.value)
创建Excel文件
import pathlib
import datetime
from openpyxl import Workbookpath = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
out_path = path.joinpath('004excel_basic.xlsx')
sheet_path = path.joinpath('004excel_sheet.xlsx')
wb = Workbook()
ws = wb.active # 找当前Sheet
ws['A1'] = 42
ws.append([1, 2, 3]) # 添加行
ws['A2'] = datetime.datetime.now()
wb.save(out_path)
# 创建第二个sheet
wb.create_sheet('sheet2')
ws = wb['sheet2']
ws.title = '第二个工作表'
ws.sheet_properties.tabColor = "FF0000"
# 增加一整行数据
ws.append([1, 2, 3, datetime.datetime.now()])
ws.cell(row=1, column=3, value=https://www.it610.com/article/10)
# 获取cell方式一
d1 = ws.cell(row=1, column=4)
# 获取cell方式二
d2 = ws['D1']
print(d1==d2)
print(type(d2.value))
wb.save(sheet_path)
设置单元格样式 单元格样式可以用NumberFormat(数据格式)、Alignment(对齐)、Font(字体)、Border(边框)、PatternFill(填充)、Protection(保护)等来设置。
也可以通过
NamedStyle
自定义样式,重复应用到单元格。此外,通过
row_dimensions
和column_dimensions
可以设置整行/整列的样式。import pathlib
import datetime
from openpyxl import Workbook
from openpyxl.styles import numbers, Font, GradientFill, PatternFill, Border, Side, Alignment, Protection
from openpyxl.styles import NamedStylepath = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
out_path = path.joinpath('004excel_style.xlsx')
wb = Workbook()
ws = wb.active
ws.cell(row=1, column=1, value='https://www.it610.com/article/微软雅黑').font = Font(name='微软雅黑', size=18, bold=True, color='123456')
ws.cell(row=2, column=1, value='https://www.it610.com/article/居中对齐').alignment = Alignment(horizontal='center', vertical='center')
ws.cell(row=3, column=1, value='https://www.it610.com/article/填充渐变色').fill = GradientFill(stop=['FF0000', '0000FF'])
ws.cell(row=4, column=1, value='https://www.it610.com/article/设置边线').border = Border(left=Side(border_style='thin', color='123456'), right= Side(border_style='thick', color='FF0000'))
ws.cell(row=5, column=1, value='https://www.it610.com/article/受保护的').protection = Protection(locked=True, hidden=True)
ws.cell(row=6, column=1, value=https://www.it610.com/article/0.88).number_format =numbers.FORMAT_PERCENTAGE
ws.column_dimensions['A'].width = 80 # 修改列宽
# 设置整行
row = ws.row_dimensions[4]
row.font = Font(name='宋体', size=30, italic=True, color='FF0000')
# 设置整列
column = ws.column_dimensions["B"]
column.fill = PatternFill("solid", fgColor="1874CD")
# 自定义样式
my_style = NamedStyle(name='my_style',
font=Font(name='宋体',size=12, color='FF000000'),
fill=PatternFill(fill_type='solid', fgColor="FFDAB9"),
border=Border(top=Side(border_style='thin', color='FF000000')),
alignment=Alignment(horizontal='left',vertical='center'))
ws['B4'] = '自定义样式'
ws['B4'].style=my_style
wb.save(out_path)
合并单元格
- 合并单元格:
merge_cells()
- 解除合并:
unmerge_cells()
unmerge_cells()
会出错;此外合并的单元格顺序是从左上角到右下角。import pathlib
import datetime
from openpyxl import Workbook
from openpyxl.styles import numbers, Font, GradientFill, Border, Side, Alignment, Protectionpath = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
out_path = path.joinpath('004excel_cell_merge.xlsx')wb = Workbook()
ws = wb.active
# 合并,必须是从左上角,到右下角的顺序
ws.merge_cells('A3:D5')
ws.merge_cells('A7:D10')
# 合并后以起点为代表读写值,其他的cell都被设置为只读
ws['A3']='test'
# 解除合并
ws.unmerge_cells('A7:D10')
# 也可以用起止行列数字来选取合并区域
ws.merge_cells(start_row=12,start_column=11,end_row=15,end_column=14)
ws['K12']='合并过'
ws.unmerge_cells(start_row=12,start_column=11,end_row=15,end_column=14)
wb.save(out_path)
图表和公式应用
openpyxl
支持的图表都在openpyxl.chart
包中,比如直方图、饼图、折线图等。可以直接把单元格的值设置为公式,在Excel软件中可以获得结果,但
openpyxl
并不支持公式的计算。如果生成的
xlsx
文件被Excel软件打开过,这时公式的计算结果会缓存在文件中,这时用openpyxl
打开文件时候,增加一个data_only=True
参数,也可以读取到计算结果。import pathlib
import datetime
from openpyxl import Workbook
from openpyxl.chart import BarChart, PieChart, LineChart, Referencepath = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
out_path = path.joinpath('004excel_graph.xlsx')
wb = Workbook()
ws = wb.active
# 装载数据
data = https://www.it610.com/article/[
('月份', '产品A销量', '产品B销量'),
(1, 100, 200),
(2, 106, 100),
(3, 120, 200),
(4, 180, 100),
(5, 200, 300),
(6, 280, 400),
(7, 400, 200),
(8, 500, 800),
(9, 600, 1000),
(10, 800, 300),
]
for d in data:
ws.append(d)
# 用Python做些统计
ws['A12'] = '汇总累加'
ws['B12'] = sum([d[1] for i, d in enumerate(data) if i > 0])
ws['C12'] = sum([d[2] for i, d in enumerate(data) if i > 0])
# 用公式统计
ws['A13'] = '公式累加'
ws['B13'] = '=SUM(B2:B11)'
ws['C13'] = '=SUM(C2:C11)'
# 增加一列总销量
ws['D1'] = '当月总销量'
for i in range(2, 13):
ws[f'D{i}'] = ws[f'C{i}'].value + ws[f'B{i}'].value
# 列状图
ct_bar= BarChart()
ct_bar.type = "col" # 列状图
ct_bar.style = 10
ct_bar.title = "销量柱状图"
ct_bar.y_axis.title = '销量'
ct_bar.x_axis.title = '月份'
d_ref = Reference(ws, min_col=2, min_row=1, max_row=11, max_col=3)
series = Reference(ws, min_col=1, min_row=2, max_row=11)
ct_bar.add_data(d_ref, titles_from_data=https://www.it610.com/article/True)
ct_bar.set_categories(series)
ws.add_chart(ct_bar,'A15')
# 饼图
ct_pie = PieChart()
ct_pie.title = "产品销量占比"
d_ref = Reference(ws, min_col=2, min_row=12, max_col=3)
series = Reference(ws, min_col=2, min_row=1, max_col=3)
ct_pie.add_data(d_ref, titles_from_data=https://www.it610.com/article/True)
ct_pie.set_categories(series)
ct_pie.splitType='val'
ws.add_chart(ct_pie, 'I15')
# 一个图两个轴
ct_bar = BarChart()
d_ref = Reference(ws, min_col=2, min_row=1, max_row=11, max_col=3)
ct_bar.add_data(d_ref, titles_from_data=https://www.it610.com/article/True)
ct_bar.x_axis.title ='月份'
ct_bar.y_axis.title = '产品AB销量'
ct_bar.y_axis.majorGridlines = None
ct_bar.title = '销量分析'
ct_line = LineChart()
d_ref = Reference(ws, min_col=4, min_row=1, max_row=11)
ct_line.add_data(d_ref, titles_from_data=https://www.it610.com/article/True)
ct_line.y_axis.axId = 200 # 不为空即可
ct_line.y_axis.title ='总销量'
# 让线条和第一图的最大值相交
ct_line.y_axis.crosses = 'max'
ct_bar += ct_line # 只支持+=赋值,不能直接+
ws.add_chart(ct_bar, 'A31')
wb.save(out_path)
插入图像
openpyxl
的图像处理基于PIL.Image
。PIL.Image
的宽和高单位默认使用Pixel(像素),如果需要映射到Excel中的位置,可以用pixels_to_EMU()
转换。import pathlib
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
from openpyxl.drawing.spreadsheet_drawing import AbsoluteAnchor
from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU as p2epath = list(pathlib.Path.cwd().parents)[1].joinpath('data/automate/004excel')
out_path = path.joinpath('004excel_image.xlsx')
img_path = path.joinpath('image.jpg')
wb = Workbook()
ws = wb.active
img = Image(img_path)
img.width /= 2
img.height /= 2
h, w = img.height, img.width
# 绝对定位图片
position = XDRPoint2D(p2e(800), p2e(800))
size = XDRPositiveSize2D(p2e(h), p2e(w))
img.anchor = AbsoluteAnchor(pos=position, ext=size)
ws.add_image(img)
# 单元格定位
img = Image(img_path)
size = (90, 90)
img.width, img.height = size
ws.row_dimensions[3].height=90
ws.column_dimensions['B'].width=90
ws.add_image(img, 'B3')
wb.save(out_path)
总结 本文主要介绍了使用
openpyxl
模块处理Excel文件,包括基本的Excel文件读写、单元格样式处理、图表生成和图像结合等。用Excel应对日常工作中的小规模数据足以。
比如实践应用中有一个“多门店加盟品牌数据统计”的案例,其中正好用到了上面的各项知识点。
文章图片
案例说明:
- 以
Python
和openpyxl
应用实践为主。 - 提供解决问题思路,实战中可直接应用。
- 后续“数据分析专题”会介绍
Pandas
等数据分析工具,一个实战应用更灵活的模块。
文章图片
推荐阅读
- 推荐系统论文进阶|CTR预估 论文精读(十一)--Deep Interest Evolution Network(DIEN)
- Python专栏|数据分析的常规流程
- Python|Win10下 Python开发环境搭建(PyCharm + Anaconda) && 环境变量配置 && 常用工具安装配置
- Python绘制小红花
- Pytorch学习|sklearn-SVM 模型保存、交叉验证与网格搜索
- OpenCV|OpenCV-Python实战(18)——深度学习简介与入门示例
- python|8. 文件系统——文件的删除、移动、复制过程以及链接文件
- 爬虫|若想拿下爬虫大单,怎能不会逆向爬虫,价值过万的逆向爬虫教程限时分享
- 分布式|《Python3网络爬虫开发实战(第二版)》内容介绍
- java|微软认真聆听了开源 .NET 开发社区的炮轰( 通过CLI 支持 Hot Reload 功能)