Python - 操作Excel文件

2021-09-28 01:24:43

目录

about#

在Python中，对于Excel表的操作可以用xlrd和xlwt来做，一个用来读，一个用来写。
但后来，都使用openpyxl模块了...

关于excel表格，大家都不陌生了哈，这里它们在Python操作中的名词解释：

  • workbook，表示的是拿到的excel文件的文件句柄。通过workbook对象可以管理sheet。
  • sheet，workbook中可以创建很多sheet表格。通过sheet对象可以操作其中的单元格(cell)了，比如修改单元格内容、字体、样式、边框......
  • cell，sheet表格中，每一个单元格，都称为cell。
  • merge cell，表示该单元格被合并了。

openpyxl#

openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.0.9 documentation

openpyxl是Python的第三方库，专门用于读/写excel文件的，通常支持xlsx/xlsm/xltx/xltm结尾的excel文件。

pip install openpyxl
# pip install lxml   # 创建大文件时会用到
# pip install pillow  # 在excel中处理图片，会用到

读excel表格#

假如我有这样的一个excel表格：

正在上传…重新上传取消​

基础操作

from openpyxl import load_workbook

wb = load_workbook('./xxx.xlsx')

# 获取所有sheet
# print(wb.sheetnames)  # ['mysqld', 'mysqld_safe', 'client', 'Sheet']

# 获取已经存在的sheet

# 基于sheet名称获取sheet对象
# sheet = wb['mysqld']
# print(sheet)  # <Worksheet "mysqld">

# 基于索引获取sheet对象
sheet = wb.worksheets[0]

# 获取指定单元格的对象，有以下两种方式获取
# cell = sheet.cell(1, 1)  # 法1
# cell = sheet['A1']  # 法2

# 可以通过单元格对象来获取其内容、样式、字体等
# print(cell.value)  # 获取单元格的内容  key
# print(cell.alignment)  # 对齐方式  https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.alignment.html?highlight=alignment
# print(cell.font)  # 字体  https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.fonts.html?highlight=font#openpyxl.styles.fonts.Font
# print(cell.style)  # 字体样式


# 获取指定行
# print(sheet[1])  # (<Cell 'mysqld'.A1>, <Cell 'mysqld'.B1>)
# print(list(map(lambda x: x.value, sheet[1])))  # ['key', 'value']

# 获取所有行
# print(sheet.rows)  # 获取所有行，返回一个生成器 <generator object Worksheet._cells_by_row at 0x000001FBB9936DD0>
# for row in sheet.rows:
#     # 获取每一行的内容
#     print(list(map(lambda x: x.value, row)))
#     # 获取每行指定列的内容
#     print(row[0].value, row[1].value)  # 获取每行第一列、第二列内容

# 获取所有列
# print(sheet.columns)
# for col in sheet.columns:
#     # 获取每一列的内容
#     print(list(map(lambda x: x.value, col)))
#     # 以列的形式，获取指定行的内容
#     print(col[0].value)   # 获取第一行的数据

# 获取所有行，用的也比较多
# for row in sheet.iter_rows():
#     print(row)

# 也可以指定从哪行哪列循环到哪行哪列
# for row in sheet.iter_rows(min_row=3, min_col=3, max_row=5, max_col=5):
#     print(row)
#     print(row[0].row)  # 返回当前行的行号

读取合并单元格
如果遇到合并的单元格的话，就需要注意了：

  • 水平合并，以最左侧的单元格为主，即合并的单元格的内容来自于最左侧的单元格，其他被合并的单元格，内容为空，且特点是merge cell，表示该单元被合并了。
  • 垂直合并，内容来自与最上面的单元格，其他单元格内容为空，且特点是merge cell。

如有这样的一个表格：
正在上传…重新上传取消​

from openpyxl import load_workbook

wb = load_workbook('./xxx.xlsx')
sheet = wb.worksheets[-1]

# 先来看水平合并的单元格的特点
# 下面两个输出结果，非常正常，因为它是最左侧的单元格
# print(sheet['B1'])  # <Cell 'Sheet'.B1>
# print(sheet['B1'].value)  # 出版书籍

# 被合并的单元格,它是merge cell，且值为None
# print(sheet['C1'], sheet['C1'].value)  # <MergedCell 'Sheet'.C1> None
# print(sheet['D1'], sheet['D1'].value)  # <MergedCell 'Sheet'.D1> None

# 再来看垂直合并的单元格
# 先来看最上面的单元格，也是非常正常的
# print(sheet['A2'])  # <Cell 'Sheet'.A2>
# print(sheet['A2'].value)  # 机械工业出版社

# 被合并的单元格,它是merge cell，且值为None
# print(sheet['A3'], sheet['A3'].value)  # <MergedCell 'Sheet'.A3> None

# 在循环中也能看到这个现象
for row in sheet.rows:
    print(row)
"""
(<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <MergedCell 'Sheet'.C1>, <MergedCell 'Sheet'.D1>, <Cell 'Sheet'.E1>)
(<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.D2>, <Cell 'Sheet'.E2>)
(<MergedCell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.E3>)
(<Cell 'Sheet'.A4>, <Cell 'Sheet'.B4>, <Cell 'Sheet'.C4>, <Cell 'Sheet'.D4>, <Cell 'Sheet'.E4>)
"""

写excel表格#

注意：在Windows平台，只要牵扯到保存修改的操作，都必须保证要操作的excel表格是关闭的，否则操作失败。提示权限问题： PermissionError: [Errno 13] Permission denied: 'xxx.xlsx'

写excel表格，就分为两种，文件存在，然后改写；文件不存在，打开文件写。
原excel表格写内容

from openpyxl import load_workbook

file_path = './xxx.xlsx'
wb = load_workbook(file_path)
sheet = wb.worksheets[0]
# 拿到单元格对象
cell = sheet['A1']
# 有了单元格对象，就可以一顿操作了，改内容、字体、边框.....
cell.value = 'new_key'

# 最后要保存修改
wb.save(file_path)

新建excel表格，写内容

from openpyxl.workbook import Workbook

file_path = './x1.xlsx'
# Workbook会自动创建一个excel文件，然后默认也会创建一个sheet，sheet名叫Sheet，注意，是Sheet不是sheet，这俩不是一回事
wb = Workbook()

# 拿到sheet
sheet = wb['Sheet']

# 拿到单元格对象
cell = sheet['A1']
# 有了单元格对象，就可以一顿操作了，改内容、字体、边框.....
cell.value = 'new_key'

# 最后要保存修改
wb.save(file_path)

更多细节操作#

在来研究下关于sheet和cell的更多细节操作。
sheet的操作

from openpyxl.workbook import Workbook

file_path = './x1.xlsx'
# Workbook会自动创建一个excel文件，然后默认也会创建一个sheet，sheet名叫Sheet，注意，是Sheet不是sheet，这俩不是一回事
wb = Workbook()
sheet = wb['Sheet']

# 修改sheet名
sheet.title = "new_sheet"

# 设置sheet颜色
sheet.sheet_properties.tabColor = "ffcc66"  # rgb颜色：http://tools.jb51.net/static/colorpicker/

# 创建新的sheet
# 0表示索引，相当于新创建的sheet插入到原有(多个)sheet中的那个位置，0就是新创建的sheet放在最左边
s2_sheet = wb.create_sheet('s2', 0)

# 当你打开excel时，指定默认打开的sheet，默认是打开最左侧的sheet
wb.active = 1


# 拷贝sheet
s3_sheet = wb.copy_worksheet(wb['s2'])  # sheet s2也必须存在
s3_sheet.title = 's3'

# 删除sheet
del wb['new_sheet']   # 不存在则报错

# 最后都要记得保存
wb.save(file_path)

cell操作

from openpyxl.workbook import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill

file_path = './x1.xlsx'
# Workbook会自动创建一个excel文件，然后默认也会创建一个sheet，sheet名叫Sheet，注意，是Sheet不是sheet，这俩不是一回事
wb = load_workbook(file_path)
sheet = wb['Sheet']

# 修改单元格的值
# 法1
# cell = sheet['A1']
# cell.value = '单元格A1'
# 法2
# sheet['A1'] = '单元格A1'


# for row in sheet['A1': 'B3']:
#     # 每一行的row，相当于有两个值，是元组类型 ('A1', 'B1')
#     row[0].value, row[1].value = 'x', 'y'

"""
上面的for循环结果，表格中的效果如下
    A   B
1   x   y
2   x   y
3   x   y
"""

# 对齐方式
# cell = sheet['A1']
# horizontal，水平方向对齐方式："general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed"
# vertical，垂直方向对齐方式："top", "center", "bottom", "justify", "distributed"
# text_rotation，旋转角度。
# wrap_text，是否自动换行。
# cell.alignment = Alignment(horizontal='center', vertical='distributed', text_rotation=45, wrap_text=True)


# 边框
# side的style有如下：dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin'
# cell = sheet['A1']
# cell.border = Border(
#     top=Side(style="thin", color="FFB6C1"),
#     bottom=Side(style="dashed", color="FFB6C1"),
#     left=Side(style="dashed", color="FFB6C1"),
#     right=Side(style="dashed", color="9932CC"),
#     diagonal=Side(style="thin", color="483D8B"),  # 对角线
#     diagonalUp=True,  # 左下 ~ 右上
#     diagonalDown=True  # 左上 ~ 右下
# )


# 字体
# cell = sheet['A1']
# cell.font = Font(name="微软雅黑", size=45, color="ff0000", underline="single")


# 背景色
# cell = sheet['A1']
# cell.fill = PatternFill("solid", fgColor="99ccff")


# 渐变背景色
# cell = sheet['A1']
# cell.fill = GradientFill("linear", stop=("FFFFFF", "99ccff", "000000"))  # stop：从左到右依次进行渐变


# 宽高设置，在sheet中，序号从1开始
# sheet.row_dimensions[1].height = 50
# sheet.column_dimensions["E"].width = 100


# 合并单元格
# sheet.merge_cells("B2:D8")
# sheet.merge_cells(start_row=15, start_column=3, end_row=18, end_column=8)


# 写入公式
# sheet["D2"] = "=B2*C2"
# sheet["D3"] = "=SUM(B3,C3)"

# 删除
# idx：删除行或者列的起始位置
# amount：总共删amount行/列，默认是1行/列
# sheet.delete_rows(idx=1, amount=2)   # 从第一行开始，总共删除2行
# sheet.delete_cols(idx=1, amount=2)  # 从第一列开始，总共删除2列


# 插入
# sheet.insert_rows(idx=1, amount=2)  # 插入两行
# sheet.insert_cols(idx=1, amount=2)  # 插入两列


# 移动
# 将指定范围内的单元格进行移动
# row 正值表示向下移动；负值表示向上移动
# col 正值表示向有移动；负值表示向左移动
# translate:True表示公式自动调整，默认为False
# sheet.move_range("B1:D3", rows=3, cols=3, translate=True)  # 将 B1到B3范围内的单元格整体向下移动3行向右移动3列


# 打印区域
# sheet.print_area = "A1:D8"

# 打印时，每个页面都固定表头
# sheet.print_title_cols = 'A:D'
# sheet.print_title_rows = '1:2'


# 最后都要记得保存
wb.save(file_path)

xlrd#

下载# 

pip install xlrd==1.2.0

PS：之前还没发现，截至到我编辑这段说明的时候，xlrd这个模块最新已经到了2.0.1版本，但最新版本跟我下面的示例中的代码不兼容，所以，大家如果不愿意研究最新的模块操作，就按照上面的下载指定版本，保证下面的示例能跑通。

useage#

  • 有两种方式获取到Excel中的sheet：
    • 通过索引：sheet_by_index(0)。
    • 通过sheet名称：sheet_by_name('自动化')。
import xlrd

# 首先拿到book对象
book = xlrd.open_workbook('./a1.xlsx')
# sheet_by_index = book.sheet_by_index(0)
sheet_by_name = book.sheet_by_name('自动化')
  • 获取行数和列数
rows = sheet_by_name.nrows
cols = sheet_by_name.ncols
  • 读取每行的内容
for row in range(rows):
    # 使用 row方法读取
    # print(sheet_by_name.row(row))
    # 也可以使用row_values读取
    print(sheet_by_name.row_values(row))
  • 读取每列的内容
for col in range(cols):
    # 下面两种方法都可以
    # print(sheet_by_name.col(col))
    print(sheet_by_name.col_values(col))
  • 读取固定列的内容
print(sheet_by_name.cell(0, 0))
print(sheet_by_name.cell_value(0, 0))
  • 将每行都和首行组成字典，存放在一个列表中
l = []
title = sheet_by_name.row_values(0)
# print(title)
for row in range(1, rows):
    l.append(dict(zip(title, sheet_by_name.row_values(row))))
print(l)
上一篇：链路追踪：SkyWalking


下一篇：Minio path 坑