处理Excel

在日常的工作中，我们或多或少的都要和 Excel 打交道。在 Python 中有许多处理 Excel 的模块（或者说叫第三方库）主要如下：

• Xlrd：xlrd 支持 .xls、.xlsx Excel文件的读，并不支持 .xls、.xlsx 文件的写。
• Xlwt：xlwt 仅支持 .xls 文件的写。
• Xlsxwriter：xlswriter 支持 .xlsx 文件的写，另外此模块还支持 VBA 操作。
• Win32com：win32com 支持 Excel 的 .xlsx 和 .xls，安装 pypiwin32 即可使用该库，该模块现在只支持 Windows 系统。
• Openpyxl：openpyxl 支持 Excel2010 多种文件的操作，read_only 和 write_only 两个参数值得注意，该模块对 VBA 的支持不好，不支持 .xls 文件的操作。
• Xlwings：xlwings 实现了 Excel 中调用 Python，Python 中调用 Excel 的操作，支持 .xls 文件的读，支持 .xlsx 文件的读写，支持 VBA 的操作，另外还支持和 Numpy、Pandas 结合进行操作，在很大程度上扩展了应用。
• Pandas：Pandas 不用多说了，数据分析领域最为重要的库，支持 .xls 和 .xlsx 读写。

本节我们将使用 Xlwings 来处理 Excel

安装 Xlwings

pip install xlwings   # 使用 pip 安装
conda install xlwings # 使用 conda 安装

基本概念

excel基本结构分为 Application ——> Workbooks ——> Worksheets ——> Range.

即应用程序 ——> 工作簿 ——> 工作表 ——> 单元格。

img

在 Xlwings 中

• Excel程序用 App 来表示，多个 Excel 程序集合用 Apps 表示；
• 单个工作簿用 Book 表示，工作簿集合用 Books 表示；
• 单个工作表用 Sheet 表示，工作表集合用 Sheets 表示；
• 区域用 Range 表示，既可以是一个单元格，也可以是一片单元格区域。

明确几个概念：

1. 新建：创建一个不存在的工作薄或者工作表
2. 打开：打开一个已经存在的工作薄
3. 引用：就是告诉程序，你要操作哪个对象。比如你打开了 A、B、C 三个工作薄，现在你想操作B工作薄，就要先引用B
4. 激活：我们可以同时打开多个工作薄，但是一次只能操作一个工作簿，我们正在操作的这个工作薄称为当前活动工作薄，激活的意思就是将某一个对象（工作薄或工作表等）变成当前活动对象

基础操作

import xlwings as xw
# 应用->工作簿->工作表->范围

# 应用
# visible用于设置xlwing操作excel的过程是否显示，默认True表示显示
# add_book表示xlwings操作excel的时候是否新增一个excel文件，默认是true表示增加
app = xw.App(visible = False, add_book = False)

# 工作簿
wb = app.books.add()

# 工作表
sht = wb.sheets["sheet1"]

# 范围
sht.range("a2").value = "a2"

# 保存excel
wb.save("demo.xlsx")

# 关闭excel程序
wb.app.quit()

操作工作簿

wb = app.books.add()                   # 新建工作簿。
wb = app.books.open(r'file_path')      # 打开现有的工作簿
wb = app.books.active                  # 获取当前活动的工作簿

操作工作表

sht = wb.sheets.active                 # 获取当前活动的工作表
sht = wb.sheets[0]                     # 按索引获取工作表
sht = wb.sheets['Sheet1']              # 按表名获取工作表
sht1 = wb.sheets.add()                 # 新建工作表，默认新建的放在最前面。
sht1 = wb.sheets.add('新建工作表', after=sht)   # 新建工作表，放在sht工作表后面。

写入

以下表为例

	A	B	C	D
1	1	2	3	4
2	5	6	7	8
3	9	10	11	12
4	13	14	15	16

指定单元格来写入：

sht.range("A1").value = "1"

插入一行：直接列表即可

sht.range("A1").value = [1, 2, 3, 4]

插入一列：设置options中的transpose为true：

sht.range("A1").options(transpose = True).value = [1, 5, 9, 13]

插入行列：传二维列表即可：

sht.range("A1").value = [[1, 2, 3, 4], [5, 6, 7, 8], [9, 10, 11, 12], [13, 14, 15, 16]]

读取

读取某个位置的值

print(sht.range("B3").value)

## 10.0

读取行

print(sht.range("B4:D4").value)

# [14.0, 15.0, 16.0]

读取列

print(sht.range("B2:B4").value)

# [6.0, 10.0, 14.0]

读取一个范围

print(sht.range("C3:D4").value)

# [[11.0, 12.0], [15.0, 16.0]]

xlwings 还提供了另外一种更加方便的方式来操作一个区域块，通过 expand 或 options 中的 expand 参数，expand 使用的是当前已获取的区域对象，而 options 中的 expand 参数在调用时才计算区域对象，推荐使用 options 中的 expand 参数，是你可以在更改区域后及时获取区域的变化。下面的代码，可以清楚的表达两种方式的不同。

sheet.range('A1').value = [[1,2], [3,4]]
rng1 = sheet.range('A1').expand('table')
rng2 = sheet.range('A1').options(expand='table')

print(rng1.value)
# [[1.0, 2.0], [3.0, 4.0]]
print(rng2.value)
# [[1.0, 2.0], [3.0, 4.0]]

sheet.range('A3').value = [5, 6]

print(rng1.value)
# [[1.0, 2.0], [3.0, 4.0]]
print(rng2.value)
# [[1.0, 2.0], [3.0, 4.0], [5.0, 6.0]]

格式设置

使用xlwings模块进行excel表格操作时，难免会用到对单元格进行格式设置，比如常用到的对单元格设置为文本格式、日期时间格式、小数和百分数，下面列出常用：
使用range().api.NumberFormat = XXX即可修改格式

比如：

range('A1').api.NumberFormat = "@"  #设置为文本格式
range('A2').api.NumberFormat = "0.0"  #设置为小数格式
range('A3').api.NumberFormat = "yyyy-mm-dd"  #设置为"-"连接的日期格式
range('A4').api.NumberFormat = "0%"  #设置为百分比

设置单元格大小

sht.autofit()    # 自动调整单元格大小。注：此方法是在单元格写入内容后，再使用，才有效。
sht.range(1,4).column_width = 5    # 设置第4列 列宽。（1,4）为第1行第4列的单元格
sht.range(1,4).row_height = 20     # 设置第1行 行高

设置单元格 字体格式

cell.color = 255,200,255         # 设置单元格的填充颜色
cell.api.Font.ColorIndex = 3     # 设置字体的颜色，具体颜色索引见下方。
cell.api.Font.Size = 24          # 设置字体的大小。
cell.api.Font.Bold = True        # 设置为粗体。
cell.api.HorizontalAlignment = -4108    # -4108 水平居中。-4131 靠左，-4152 靠右。
cell.api.VerticalAlignment = -4130      # -4108 垂直居中（默认）。-4160 靠上，-4107 靠下，-4130 自动换行对齐。
cell.api.NumberFormat = "0.00"          # 设置单元格的数字格式。

单元格操作

合并拆分单元格

sht.range('C8:D8').api.merge()      # 合并单元格 C8 到 D8
sht.range('C8:D8').api.unmerge()    # 拆分单元格。

插入 、删除 一行

sht1.range('a3').api.EntireRow.Delete()     # 会删除 A3 单元格所在的行。
sht1.api.Rows(3).Insert()                   # 会在第 3 行插入一行，原来的第3行下移。

插入 、删除 一列

sht1.range('c2').api.EntireColumn.Delete()  # 会删除 C2 单元格所在的列。
sht1.api.Columns(3).Insert()                # 会在第3列插入一列，原来的第3列右移。(也可以用列的字母表示)

参考：python读写excel利器：xlwings 从入门到精通 - chengjon - 博客园 (cnblogs.com)