python学习-处理excel文档
本文介绍 python 如何处理
Excel
文档,包括安装依赖库、读取和写入 Excel 数据、表格的样式设置、图表创建、数据过滤和数据验证、大文件性能优化等功能。供自己以后查漏补缺,也欢迎同道朋友交流学习。
引言
上面俩篇文章,主要介绍了 Python
处理 word
和 PDF
的文档,我们平时工作中还经常与 excel
打交道。
因此,本章就主要介绍 Python 如何处理 Excel
文档,包括安装依赖库、读取和写入 Excel 数据、表格的样式设置、图表创建、数据过滤和数据验证、大文件性能优化等功能。
openpyxl库
openpyxl
是一个 Python
库,专门用于读写 Excel 相关文件。其特点如下:
- 兼容性:支持 Excel 的最新文件格式(
.xlsx
),这是旧版Excel
和其他一些库不支持的。 - 灵活性:允许对文件进行
细粒度
的操作,如单独编辑单元格
、行
、列
或整个工作表
。 - 功能丰富:除了基本的读写操作,还支持
样式设置
、图表创建
、数据过滤
和数据验证
等功能。 - 性能:对于较大的 Excel 文件,
openpyxl
提供了优化内存
使用的方法,使得处理大型数据
集成为可能。 - 易于使用:
API
设计直观,易于理解和使用,即使是初学者也能快速上手。 - 集成:可以轻松集成到 Python 的数据分析和自动化流程中,与其他数据处理库如
pandas
、numpy
等协同工作。
安装依赖库
pip install openpyxl
# or
pip3 install openpyxl
基本操作
- 创建工作簿:调用
Workbook
创建一个新的工作簿。 - 当前工作表:调用
active
属性可以设置当前工作表属性。 - 添加新工作表:调用
create_sheet
创建一个新的工作表,并可以指定其标题。 - 保存工作簿:调用
save
方法保存工作簿。
from openpyxl import Workbook
# 创建一个新的工作簿
wb = Workbook()
# 默认创建的工作表名为 "Sheet",我们可以重命名它
wb.active.title = "Active Sheet"
# 添加一个新的工作表
ws = wb.create_sheet(title="New Sheet")
# 保存工作簿
wb.save(filename="excel-1.xlsx")
创建新的工作簿成功后,表的内容为空,有俩个工作表如下:
读取Excel数据
本地新建了一个 excel-2.xlsx
文件,内容如下:
员工表如下:
水果价格表如下:
汽车库存表如下:
读取单个单元格
通过单元格坐标读取数据,以员工表举例:
from openpyxl import load_workbook
# 打开Excel文件
wb = load_workbook(filename='excel-2.xlsx')
ws = wb['员工表'] # 选择活动工作表
# wb.active 表示当前活动工作表
# 读取单个单元格的数据
print(ws['A1'].value) # 输出单元格A1的值
# 输出:员工表
print(ws['B2'].value) # 输出单元格B2的值
# 输出:年龄
print(ws['C3'].value) # 输出单元格C3的值
# 输出:前端
print(ws.cell(column=2, row=5).value) # 输出第二列第五行的值,也就是B5单元格的值
# 输出:26
# cell里参数不传column和row,默认输出第一个参数为row,第二个参数为column
print(ws.cell(4, 2).value) # 输出第4行第2列的值,也就是B4单元格的值
# 输出:33
读取行和列
# 切换到水果价格表
ws2 = wb['水果价格表']
# 读取整列的数据
row_data = [cell.value for cell in ws2['A']] # 读取第一行的数据
print(row_data)
# 输出:['水果', '香蕉', '苹果', '梨', '葡萄', '西瓜']
# 切片读取指定列数据
row_data2 = [cell[0].value for cell in ws2['A2:A6']]
print(row_data2)
# 输出:['香蕉', '苹果', '梨', '葡萄', '西瓜']
# 读取整行的数据
column_data = [cell.value for cell in ws2[1]]
print(column_data)
# 输出:['水果', '价格(元/斤)']
读取工作表
# 切换到汽车库存表
ws3 = wb['汽车库存表']
# 读取整个工作表的数据
for row in ws3.iter_rows(values_only=True):
for cell in row:
print(cell, end=' ')
print()
# 输出:
# 品牌 库存量
# 宝马3 200
# 奔驰C 190
# MG7 300
# 别克君威 234
写入数据
from openpyxl import Workbook
# 创建一个新的工作簿
wb = Workbook()
ws = wb.active # 激活默认的工作表
# 写入单个单元格的数据
ws['A1'] = '写入表格数据示例如下:'
# 写入一行数据
ws.append(['姓名', '年龄', '性别', '职业'])
# 写入整个表格数据
data = [
['张三', 25, '男'],
['李四', 30, '女'],
['王五', 28, '男'],
]
for row in data:
ws.append(row)
# 写入列数据
jobs = ['教师', '医生', '工程师']
for i in range(3, 6): # 从第3行开始写入数据
ws.cell(row=i, column=4).value = jobs[i - 3]
# 保存工作簿
wb.save(filename="excel-3.xlsx")
写入完成后,效果如下:
样式设置
在 openpyxl 中,可以设置单元格的字体、颜色、对齐等多种样式。
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
# 创建一个新的工作簿
wb = Workbook()
ws = wb.active
# 设置单元格数据
ws['A1'] = '单元格数据'
# 设置行高和列宽
ws.row_dimensions[1].height = 40
ws.column_dimensions['A'].width = 40
# 设置单元格样式
# 字体设置
font = Font(name='Arial', size=12, bold=True, color="FF0000")
# 文本对齐方式
alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 填充色
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
ws['A1'].font = font
ws['A1'].alignment = alignment
ws['A1'].fill = fill
# 保存工作簿
wb.save(filename="excel-4.xlsx")
写入完成后,效果如下:
高级功能
python 还可以实现写入图表、数据过滤、数据验证等高级功能:
图表
基于 openpyxl.chart
,可以实现柱状图、折线图、饼图等图表的绘制,下面简单写下柱状图的示例:
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
# 创建一个新的工作簿
wb = Workbook()
ws = wb.active
# 设置数据
data = [
['品牌', '每月销售量'],
['宝马3', 12000],
['奔驰C', 13000],
['MG7', 2000],
['别克君威', 5000],
['小米su7', 10000],
]
for row in data:
ws.append(row)
# 创建图表对象
chart = BarChart() # 柱状图
chart.title = "汽车销量图"
chart.x_axis.title = '品牌'
chart.y_axis.title = '每月销售量'
# 设置数据区域
data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=6)
xTitle = Reference(ws, min_col=1, min_row=2, max_row=6)
chart.add_data(data, titles_from_data=True)
chart.set_categories(xTitle)
# 将图表添加到工作表
ws.add_chart(chart, "D1")
# 保存工作簿
wb.save(filename="excel-5.xlsx")
写入完成后,效果如下:
数据过滤和数据验证
- 数据过滤:使用
auto_filter
方法启用数据过滤功能。 - 数据验证:调用
DataValidation
方法进行数据验证,制定单元格范围和允许的值。
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
# 创建一个新的工作簿
wb = Workbook()
ws = wb.active
# 添加一些数据
data = [
['姓名', '年龄', '性别'],
['张三', 25, '男'],
['李四', 30, '女'],
['王五', 28, '男'],
['朱六', 33, '女'],
['陈七', 19, '男'],
]
for row in data:
ws.append(row)
# 启用数据过滤
ws.auto_filter.ref = "A1:C6"
# 创建数据验证对象
dv = DataValidation(type="list", formula1='"男,女"', showDropDown=True, sqref="C2:C10")
ws.add_data_validation(dv)
# 保存工作簿
wb.save(filename="excel-6.xlsx")
写入完成后,效果如下:
处理大型文件
处理大型 Excel
文件时,需要注意以下几点:
- 使用
read_only
模式:使用只读 read_only 模式,这样可以减少内存的使用。 - 关闭不再需要的工作簿:使用
wb.close()
及时关闭不再需要的工作簿,可以释放内存。 - 使用
iter_rows
和iter_cols
迭代器处理数据:使用迭代器可以逐行处理数据,而不是一次性加载整个文件到内存中。 - 限制加载的行和列:在读取时,限制加载的行和列范围,只加载需要的数据。
- 使用优化读写模式:在创建工作簿时启用
wb = Workbook(optimized_write=True)
来优化读写。
python学习专栏系列
- python学习-基础学习1
- python学习-基础学习2
- python学习-基础学习3
- python学习-面向对象编程1
- python学习-面向对象编程2
- python学习-文件读写
- python学习-程序异常处理
- python学习-正则
- python学习-处理word文档
- python学习-处理pdf文档
- python学习-处理excel文档
- python学习-处理csv文档
- python学习-使用matplotlib绘制图表
- python学习-处理JSON数据
- python学习-SQLite数据库
- python学习-多线程处理
- python学习-网络爬虫