python学习-处理excel文档

119 阅读7分钟

python学习-处理excel文档

本文介绍 python 如何处理 Excel 文档,包括安装依赖库、读取和写入 Excel 数据、表格的样式设置、图表创建、数据过滤和数据验证、大文件性能优化等功能。

供自己以后查漏补缺,也欢迎同道朋友交流学习。

引言

上面俩篇文章,主要介绍了 Python 处理 wordPDF 的文档,我们平时工作中还经常与 excel 打交道。

因此,本章就主要介绍 Python 如何处理 Excel 文档,包括安装依赖库、读取和写入 Excel 数据、表格的样式设置、图表创建、数据过滤和数据验证、大文件性能优化等功能。

openpyxl库

openpyxl 是一个 Python 库,专门用于读写 Excel 相关文件。其特点如下:

  • 兼容性:支持 Excel 的最新文件格式(.xlsx),这是旧版 Excel 和其他一些库不支持的。
  • 灵活性:允许对文件进行细粒度的操作,如单独编辑单元格或整个工作表
  • 功能丰富:除了基本的读写操作,还支持样式设置图表创建数据过滤数据验证等功能。
  • 性能:对于较大的 Excel 文件,openpyxl 提供了优化内存使用的方法,使得处理大型数据集成为可能。
  • 易于使用API 设计直观,易于理解和使用,即使是初学者也能快速上手。
  • 集成:可以轻松集成到 Python 的数据分析和自动化流程中,与其他数据处理库如 pandasnumpy 等协同工作。

安装依赖库

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-1

读取Excel数据

本地新建了一个 excel-2.xlsx 文件,内容如下:

excel-2

员工表如下:

excel-2-1

水果价格表如下:

excel-2-2

汽车库存表如下:

excel-2-3

读取单个单元格

通过单元格坐标读取数据,以员工表举例:

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")

写入完成后,效果如下:

excel-3

样式设置

在 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")

写入完成后,效果如下:

excel-4

高级功能

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")

写入完成后,效果如下:

excel-5

数据过滤和数据验证

  • 数据过滤:使用 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-6

处理大型文件

处理大型 Excel 文件时,需要注意以下几点:

  • 使用 read_only 模式:使用只读 read_only 模式,这样可以减少内存的使用。
  • 关闭不再需要的工作簿:使用 wb.close() 及时关闭不再需要的工作簿,可以释放内存。
  • 使用iter_rowsiter_cols迭代器处理数据:使用迭代器可以逐行处理数据,而不是一次性加载整个文件到内存中。
  • 限制加载的行和列:在读取时,限制加载的行和列范围,只加载需要的数据。
  • 使用优化读写模式:在创建工作簿时启用 wb = Workbook(optimized_write=True) 来优化读写。

python学习专栏系列

练习代码库地址

python-study