python学习-处理excel文档

牛奶
329 阅读7分钟

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-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_rows 和 iter_cols迭代器处理数据：使用迭代器可以逐行处理数据，而不是一次性加载整个文件到内存中。
  • 限制加载的行和列：在读取时，限制加载的行和列范围，只加载需要的数据。
  • 使用优化读写模式：在创建工作簿时启用 wb = Workbook(optimized_write=True) 来优化读写。

python学习专栏系列

练习代码库地址

python-study

avatar
文章
阅读
粉丝