Python操作Excel工作簿(\*.xlsx)

发表于 | 更新于 | 分类于 |
热度 ℃   |   字数统计: 3,314(字)   |   阅读时长: 13(分) |

前言

Excel 作为流行的个人计算机数据处理软件，混迹于各个领域，在程序员这里也是常常被处理的对象，可以处理 Excel 格式文件的 Python 库还是挺多的，比如 xlrd、xlwt、xlutils、openpyxl、xlwings 等等，但是每个库处理 Excel 的方式不同，有些库在处理时还会有一些局限性。

接下来对比一下几个库的不同，然后主要记录一下 xlwings 这个库的使用，目前这是个人感觉使用起来比较方便的一个库了，其他的几个库在使用过程中总是有这样或那样的问题，不过在特定情况下使用也是挺不错的。

EXCEL文件

Excel 被称为电子表格，其实际可以保存的格式分为很多种，但是“Excel 工作簿(*.xlsx)”和“Excel 97-2003 工作簿(*.xls)”是其中比较常用的两种，可以认为 .xls 格式的表格是 03版Excel 之前常用的格式，而 .xlsx 是 03版之后，一般指 07版Excel 之后常用的格式。

一般的 Excel 程序对于上述的两种格式都可以打开编辑，也可以相互转化存储，不过还是建议在没有特殊要求的情况下使用新版本的格式，一方面新的稳定版本可能会修复之前的一些BUG，同时也会带来进行一些优化。

我也是在写这篇总结之前才发现，一个空的 .xlsx 格式的文件大小有 7KB，而一个空的 .xls 格式的文件大小有 24KB，当我分别写入一个相同的汉字后，两个文件大小变成了 10KB 和 30KB，差距还是不小的，还有一个问题就是在将 .xlsx 格式的文件另存为 .xls 格式时还会有兼容性提示，提醒用户有些设置可能会丢失，所以能选新版本还是尽量用新版本吧。

测试环境

因为很多应用程序是不断迭代的，相对应的 Python 库也是不断迭代的，这里尽可能的给出版本号，不同的版本可能会有不同的问题：

  • 操作系统: Windows 10 随意版
  • Python: 3.75
  • xlrd: 1.2.0
  • xlwt: 1.3.0
  • xlutils: 2.0.0
  • openpyxl: 3.0.3
  • xlwings: 0.18.0

以上各个程序库使用之前自行安装就行，安装方法就不赘述了，不过可以提供一个可以快速安装镜像源，使用 pip install -i https://pypi.doubanio.com/simple 库名 可以尽可能解决下载安装缓慢的问题。

Excel具体操作

关于使用 Python 具体操作 Excel 的方法可以分为三组，配合使用 xlrd、xlwt、xlutils 操作作为第一组，使用库 openpyxl 作为第二组，而 xlwings 作为第三组，这篇总结重点总结 xlwings 的使用，其他两组简单了解。

xlrd、xlwt、xlutils

这一组操作 Excel 的库名字很形象，一个读、一个写、一个小工具，凑到一起就可以对 Excel 肆意妄为了，下面做个小练习，打开一个 Excel 文件然后修改第一个单元格的值，再另存为一个新文件，代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import xlrd
import xlwt
import xlutils.copy

def save_as_new_file(file_name, new_file_name):
    # 打开Excel文件
    rb = xlrd.open_workbook(file_name)
    # 创建一个可写入的副本
    wb = xlutils.copy.copy(rb)
    # 获得第一个sheet页签
    ws = wb.get_sheet(0)
    # 第一个单元格写入测试值
    ws.write(0, 0, 'test value')
    # 另存为一个新文件
    wb.save(new_file_name)

上述代码无论是操作 .xlsx 文件还是操作 .xls 文件都不会报错，但是另存为的 .xlsx 格式的文件会打不开，同时你会发现正常存储的 .xls 文件打开后格式全都没了，怎么办，改个参数试试，将打开文件的代码修改如下：

1
rb = xlrd.open_workbook(file_name, formatting_info=True)

其中参数 formatting_info=True 就表示打开Excel时保留原有的格式，但是这是相对于 .xls 格式的文件，对于 .xlsx 格式的文件直接跑出异常 raise NotImplementedError("formatting_info=True not yet implemented")，就因为处理不了 .xlsx 格式的文件，我暂时没有使用这几个库操作 Excel。

还有一点，这几个库操作单元格时，行和列的索引是从0开始的。

openpyxl

首先说这个库主要用来操作 .xlsx 格式的文件，对于 .xls 格式的文件无法打开，会报 openpyxl does not support the old .xls file format 这样的错误，但是可以存储成这样的格式，再次打开时会有格式不匹配的警告，但是基础的数据还在，所以还是优先用来操作 .xls 格式的文件吧。

写一个新文件的常见用法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font, Fill, Alignment, PatternFill

def write_new_excel(file_name):
    # 创建一个excel文档
    wb = Workbook()
    # 获得当前激活的sheet对象
    ws = wb.active
    # 给A2单元格赋值
    ws['A2'] = 'This is A2 cell'
    # 一行添加多列数据
    ws.append([1, 2, 'hello'])
    # 添加新的sheet
    ws = wb.create_sheet(title='NewInfo',index=0)
    # 设置单元格的值
    ws['A1'] = 'This is new sheet'

    # 保存excel
    wb.save(file_name)

读取和改写一个原有文件的常见用法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
def read_update_excel(file_name):
    # 加载Excel表
    wb = load_workbook(file_name)
    # 打印sheet数量
    print('sheet count:', len(wb.sheetnames))
    # 打印所有sheet名字
    print('sheet name list:', wb.sheetnames)
    # 获取第一个sheet对象
    ws = wb[wb.sheetnames[0]]
    # 打印sheet表行数和列数
    print('rows count:', ws.max_row, 'cols count:', ws.max_column)
    # 更新单元格A1的内容
    ws['A1'] = 'this is A1'
    # 在第二行位置插入一行
    ws.insert_rows(2)
    # 删除第五行
    ws.delete_rows(5)
    # 获取单元格对象，对应B2单元格
    cell = ws.cell(2,2)
    # 设置单元格内容
    cell.value = 'this is B2'
    # 修改字体格式为粗体
    cell.font = Font(bold=True)
    # 修改单元格格式
    cell.fill = PatternFill("solid", fgColor="F0CDCD")

    # 保存原文件或另存一个文件
    wb.save(file_name)

使用这个库遇到的情况，存储带有样式的数据没有发现问题，但是当加入一个计算公式后，另存为一个文件时明显文件尺寸变小了，但是数据和公式没有发现有问题。

有资料说处理速度真的很慢，因为我处理的文件比较小，但是没有发现这方面的问题，还有一个问题就是说Excel中的宏全部丢失，这个测试的时候确实是丢了，只不过这个好像和文件格式有关，要想保存宏需要存储为 .xlsm 格式，但是 openpyxl 使用来操作 .xlsx 文件的，存储时会导致宏丢失，强行存储为 .xlsm 格式会导致最终的文件打不开。

还有一点，这个库操作单元格时，行和列的索引是从1开始的。

xlwings

这个库在操作的首先要创建一个 App，通过这个创建出来的 App 对象来操作 Excel，非常像把 Excel 的各种操作 api 封装到一起，然后通过这个 App 对象来调用，如果在创建 App 的时候不设置隐藏参数，是会正常打开 Excel 程序的。

使用 xlwings 的基本方式：

1
2
3
4
5
6
7
8
9
10
11
import xlwings as xw

# 设置Excel程序不可见
app = xw.App(visible=False, add_book=False)

# 通过 app 操作 Excel文件
# app.bala bala bala .....
# app.bala bala bala .....

# 优雅的退出
app.quit()

创建一个新的 Excel 文件并写入数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
def write_new_excel(app, file_name):
    # 创建新的 Excel 表
    wb = app.books.add()
    # 获取当前活动的sheet
    ws = wb.sheets.active
    # 初始化二维区域的值
    arr_data = [[1, 2, 3], [4, 5, 6], [7, 8, 'end']]
    # 设置到新建的Excel中
    ws.range('A1:B3').value=arr_data
    # 设置单独一个单元格的值
    ws.range('A4').value='this is A4'
    # 设置单独一个单元格的值
    ws[3,1].value='this is B4'
    # 保存Excel文件
    wb.save(file_name)
    wb.close()

需要注意的是通过行索引和列索引修改单元格时，起始索引是0。

读入已有 Excel 表格并修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
def read_update_excel(app, file_name):
    # 加载已有的表格
    load_wb = app.books.open(file_name)
    # 获取Excel表中第一个sheet
    load_ws = load_wb.sheets[0]
    # 打印sheet的名字
    print(load_ws.name)
    # 根据sheet名字获取sheet对象
    load_ws = load_wb.sheets[load_ws.name]
    # 获取当前活动的sheet
    load_ws = load_wb.sheets.active

    # 获取存在数据的行数和列数
    rows = load_ws.api.UsedRange.Rows.count
    cols = load_ws.api.UsedRange.Columns.count
    print('rows count:', rows, 'cols count:', cols)

    # 修改指定单元格数据（A1单元格）
    load_ws[0,0].value='this is A1'

    # 有空行或空列时获取准确的行列数量
    print(load_ws.used_range.shape)

    # 从A1单元格开始扩展到非空行空列，最后的行数和列数
    print((load_ws.range('A1').expand().last_cell.row,
        load_ws.range('A1').expand().last_cell.column))

    # 从A1单元格开始扩展到非空行空列，最后的行数和列数
    print((load_ws.range('A1').expand().last_cell.row,
        load_ws.range('A1').expand().last_cell.column))

    # 从A1单元格开始扩展到非空行空列，最后形状
    print(load_ws.range(1,1).expand().shape)

    # 从A1单元格开始扩展到非空行空列，最后的行数和列数
    print((load_ws.range('A1').expand('table').rows.count,
        load_ws.range('A1').expand('table').columns.count))

    # 保存修改后的Excel
    load_wb.save(file_name)
    load_wb.close()

Excel 增加删除行和列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
def insert_delete_rowscols(app, file_name):
    # 加载已有的表格
    load_wb = app.books.open(file_name)
    # 获取当前活动的sheet
    load_ws = load_wb.sheets.active

    # 从第2行开始插入4行，也就是说2-5行变成新插入的空行
    load_ws.api.rows('2:5').insert
    # 删除第6行和第7行
    load_ws.api.rows('6:7').delete
    # 插入一个单元格，实际测试效果是B列从B2开始向下移动，B2为新添加的单元格
    load_ws.range('B2').api.insert
    # 插入新的一列
    load_ws.api.columns('B').insert
    # 删除一列
    load_ws.api.columns('C').delete

    # 保存修改后的Excel
    load_wb.save(file_name)
    load_wb.close()

单元格宽高查询设置与合并

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
def cell_operation(app, file_name):
    # 加载已有的表格
    load_wb = app.books.open(FILE_PATH_ROOT + file_name)
    # 获取当前活动的sheet
    load_ws = load_wb.sheets.active

    # 合并单元格
    load_ws.range('A2:A3').api.merge

    #获取单元格
    cell = xw.Range('B2')
    # 打印单元格所在的行和列
    print("row is:", cell.row, "col is:", cell.column)

    # 打印当前格子的高度和宽度
    print("cell.width:", cell.width, "cell.height:", cell.height)

    # 设置当前格子的高度和宽度
    cell.row_height = 32
    cell.column_width = 64

    # 指定单元格的高度和宽度自适应
    cell.columns.autofit()
    cell.rows.autofit()

    # 再次打印当前格子的高度和宽度
    print("cell.width:", cell.width, "cell.height:", cell.height)

    # 保存修改后的Excel
    load_wb.save(file_name)
    load_wb.close()

几个库支持情况对比

虽然前面写了这么多方法，但是遇到一个实际的问题时还是会犹豫，到底用哪种方式呢？下面做一个简单的对比，只是根据我做的实验来简单对比，如果有不准确甚至是错误的地方，欢迎大家指出来，我会尽快改正的。

情景/库 xlrd、xlwt、xlutils openpyxl xlwings
读取.xls 可以带有样式读取 不支持 可以读取
保存.xls 可以带有样式保存 可以保存，但是提示文件扩展名不匹配，可以看到原始数据 可以保存，但是提示文件扩展名不匹配，可以看到原始数据
读取.xlsx 可以读取，但没有样式 可以带有样式读取 可以带有样式读取
保存.xlsx 保存后打不开 可以带有样式保存 可以带有样式保存
读取.xlsm 可以读取，但没有样式和宏 可以读取，但没有宏 可以读取包含宏的表格
保存.xlsm 保存后打不开，存成 .xls 格式宏丢失 保存后打不开，存成 .xls想 格式宏丢失 存储后宏还在
增删行和列 没有直接方法 支持 支持
另存后大小 .xls 文件没有变化 .xlsx 文件会变小 .xls、.xlsx 文件没有变化
使用建议 只操作.xls文件可以考虑 只操作.xlsx文件可以考虑，不能带有宏 一个比较好的选择，使用时感觉速度稍微有点慢

总结

  1. Excel 表格程序经过版本的更替发生了很大的变化，出现了相同内容时 .xls 比 .xlsx 格式的文件大很多的情况
  2. 基于上一点考虑，如果能使用的新版的表格，那么就放弃旧的格式的吧
  3. 还有一个神奇的情况，一个带有少量数据的 .xlsx 格式的表格要比一个空表格还要小，这是什么情况，暂时没弄明白怎么回事，求知道的大神告知一二
Albert Shi wechat
欢迎您扫一扫上面的微信公众号，订阅我的博客