python之操作Excel模块

字数统计: 3.9k字   |   阅读时长≈ 17分

摘要：python操作Excel文件，第三方模块汇总。

前言：本篇文章主要总结了一下利用python操作Excel文件的第三方库和方法。

常见库简介

1.xlrd

xlrd是一个从Excel文件读取数据和格式化信息的库，支持.xls以及.xlsx文件。
http://xlrd.readthedocs.io/en/latest/
    1、xlrd支持.xls，.xlsx文件的读
    2、通过设置on_demand变量使open_workbook()函数只加载那些需要的sheet，从而节省时间和内存（该方法对.xlsx文件无效）。
    3、xlrd.Book对象有一个unload_sheet方法，它将从内存中卸载工作表，由工作表索引或工作表名称指定（该方法对.xlsx文件无效）

2.xlwt

xlwt是一个用于将数据和格式化信息写入旧Excel文件的库（如.xls）。
https://xlwt.readthedocs.io/en/latest/
    1、xlwt支持.xls文件写。

3.xlutils

xlutils是一个处理Excel文件的库，依赖于xlrd和xlwt。
http://xlutils.readthedocs.io/en/latest/
    1、xlutils支持.xls文件。
    2、支持Excel操作。

4.xlwings

xlwings是一个可以实现从Excel调用Python，也可在python中调用Excel的库。
http://docs.xlwings.org/en/stable/index.html
    1、xlwings支持.xls读，支持.xlsx文件读写。
    2、支持Excel操作。
    3、支持VBA。
    4、强大的转换器可以处理大部分数据类型，包括在两个方向上的numpy array和pandas DataFrame。

5.openpyxl

openpyxl是一个用于读取和编写Excel 2010 xlsx/xlsm/xltx/xltm文件的库。
https://openpyxl.readthedocs.io/en/stable/
    1、openpyxl支持.xlsx文件的读写。
    2、支持Excel操作。
    3、加载大.xlsx文件可以使用read_only模式。
    4、写入大.xlsx文件可以使用write_only模式。

6.xlsxwriter

xlsxwriter是一个用于创建Excel .xlsx文件的库。
https://xlsxwriter.readthedocs.io/
    1、xlswriter支持.xlsx文件的写。
    2、支持VBA。
    3、写入大.xlsx文件时使用内存优化模式。

7.win32com

win32com库存在于pywin32中，是一个读写和处理Excel文件的库。
http://pythonexcels.com/python-excel-mini-cookbook/
    1、win32com支持.xls，.xlsx文件的读写，支持.xlsx文件的写。
    2、支持Excel操作。

8.DataNitro

DataNitro是一个内嵌在Excel中的插件。
https://datanitro.com/docs/ 
    1、DataNitro支持.xls，.xlsx文件的读写。
    2、支持Excel操作。
    3、支持VBA。
    4、收费

9.pandas

pandas通过对Excel文件的读写实现数据输入输出
http://pandas.pydata.org/
    1、pandas支持.xls，.xlsx文件的读写。
    2、支持只加载每个表的单一工作页。

win MAC Py2 Py3 .xls .xlsx 读 写 修改
xlrd √ √ √ √ √ √ √ χ χ
xlwt √ √ √ √ √ χ χ √ √
xlutils √ √ √ √ √ χ χ χ √
xlwings √ √ √ √ √ √ √ √ √
openpyxl √ χ √ √ χ √ √ √ √
xlswriter √ √ √ √ χ √ χ √ χ
win32com √ √ √ √ √ √ √ √ √
DataNitro √ χ √ √ √ √ — — —
pandas √ √ √ √ √ √ √ √ χ

提醒及注意：

xlutils 仅支持 xls 文件，即2003以下版本；
win32com 与 DataNitro 仅支持 windows 系统；
xlwings 安装成功后，如果运行提示报错“ImportError: no module named win32api”，请再安装 pypiwin32 或者 pywin32 包；
win32com 不是独立的扩展库，而是集成在其他库中，安装 pypiwin32 或者 pywin32 包即可使用；
DataNitro 是 Excel 的插件，安装需到官网下载。

基本功能：

由于设计目的不同，每个模块通常着重于某一方面功能，各有所长。

1.xlwings

可结合 VBA 实现对 Excel 编程，强大的数据输入分析能力，同时拥有丰富的接口，结合 pandas/numpy/matplotlib 轻松应对 Excel 数据处理工作。

2.openpyxl

简单易用，功能广泛，单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有，图表功能是其一大亮点，缺点是对 VBA 支持的不够好。

3.pandas

数据处理是 pandas 的立身之本，Excel 作为 pandas 输入/输出数据的容器。

4.win32com

从命名上就可以看出，这是一个处理 windows 应用的扩展，Excel 只是该库能实现的一小部分功能。该库还支持 office 的众多操作。需要注意的是，该库不单独存在，可通过安装 pypiwin32 或者 pywin32 获取。

5.xlsxwriter

拥有丰富的特性，支持图片/表格/图表/筛选/格式/公式等，功能与openpyxl相似，优点是相比 openpyxl 还支持 VBA 文件导入，迷你图等功能，缺点是不能打开/修改已有文件，意味着使用 xlsxwriter 需要从零开始。

6.DataNitro

作为插件内嵌到 Excel 中，可完全替代 VBA，在 Excel 中使用 python 脚本。既然被称为 Excel 中的 python，协同其他 python 库亦是小事一桩。然而，这是付费插件…

7.xlutils

基于 xlrd/xlwt，老牌 python 包，算是该领域的先驱，功能特点中规中矩，比较大的缺点是仅支持 xls 文件。

读写测试

测试用例

用例1. 读.xls文件的整个表（表有5个分页，每个分页有2000行1200列的整数）。
用例2. 读.xlsx文件的整个表（表有5个分页，每个分页有2000行1200列的整数）。
用例3. 读.xls文件的整个表（表有1个分页，页有2000行1200列的整数）。
用例4. 读.xlsx文件的整个表（表有1个分页，页有2000行1200列的整数）。
用例5. 写.xls文件的整个表（表有5个分页，每个分页有2000行1200列的整数）。
用例6. 写.xlsx文件的整个表（表有5个分页，每个分页有2000行1200列的整数）。
用例7. 写.xls文件的整个表（表有1个分页，页有2000行1200列的整数）。
用例8. 写.xlsx文件的整个表（表有1个分页，页有2000行1200列的整数）。

测试结果

测试模块 用例1 用例2 用例3 用例4 用例5 用例6 用例7 用例8
xlrd 5.77 98.06 1.28 19.72 — — —
xlwt — — — — 20.33 — 4.06 —
xlwings 1.57 11.91 2.01 3.09 — 44.00 — 9.30
openpyxl — 321.70/0.01 — 42.42/0.01 — 2109.93/135.5 — 31.84/25.0
xlsxwriter — — — — — 152.28 — 24.09
win32com 0.37 10.16 0.36 2.26 — 32.48 — 26.78
pandas 32.34 553.15 1.82 22.42 34.66 40.43 6.92 8.02

注：

1.xlwt和pandas每个工作页最多写入256列，因此测试用例修改为每页有2000行256列的整数.
2.xlutils读写依赖于xlrd和xlwt，不单独测试。
3.openpyxl测试两种模式，一是普通加载写入，二是read_only/write_only模式下的加载写入。
4.DataNitro要收费，且需依托Excel使用，本次不测试。

性能比较

单从读写的性能上考虑，win32com的性能是最好的，xlwings其次。

  openpyxl虽然操作Excel的功能强大，但读写性能过于糟糕，尤其是写大表时，会占用大量内存（把我的4G内存用完了），开启read_only和write_only模式后对其性能有大幅提升，尤其是对读的性能提升很大，使其几乎不耗时（0.01秒有点夸张，不过确实是加载上了）。pandas把Excel当作数据读写的容器，为其强大的数据分析服务，因此读写性能表现中规中矩，但其对Excel文件兼容性是最好的，支持读写.xls，.xlsx文件，且支持只读表中单一工作页。同样支持此功能的库还有xlrd，但xlrd只支持读，并不支持写，且性能不突出，需要配合xlutils进行Excel操作，并使用xlwt保存数据，而xlwt只能写入.xls文件（另一个可以写入.xls文件的库是pandas，且这两个写入的Excel文件最多只能有256列，其余库就我目前的了解均只能写入.xlsx文件），性能一般。xlsxwriter功能单一，一般用来创建.xlsx文件，写入性能中庸。win32com拥有最棒的读写性能，但该库存在于pywin32的库中，自身没有完善的文档，使用略吃力。xlwings拥有和win32com不相伯仲的读写性能，强大的转换器可以处理大部分数据类型，包括二维的numpy array和pandas DataFrame，可以轻松搞定数据分析的工作。

综合考虑，xlwings的表现最佳，正如其名，xlwings——Make Excel Fly！

便捷性比较

    本测试目前只是针对Excel文件的读写，并未涉及Excel操作，单从读写的便捷性来讲，各库的表现难分上下，但是win32com和xlwings这两个库可以在程序运行时实时在打开的Excel文件中进行操作，实现过程的可视化，其次xlwings的数据结构转换器使其可以快速的为Excel文件添加二维数据结构而不需要在Excel文件中重定位数据的行和列，因此从读写的便捷性来比较，仍是xlwings胜出。

代码示例

最后，附上一些演示代码，大家可自行体会下不同模块的使用。

6.1 xlwings基本代码

1
2
3
4
5
6
7
8
9
import xlwings as xw
#连接到excel
workbook = xw.Book(r'path/myexcel.xlsx')
#连接到指定单元格
data_range = workbook.sheets('Sheet1').range('A1')
#写入数据
data_range.value = [1,2,3]
#保存
workbook.save()

6.2 xlsxwriter基本代码

1
2
3
4
5
6
7
8
9
import xlsxwriter as xw
#新建excel
workbook  = xw.Workbook('myexcel.xlsx')
#新建工作薄
worksheet = workbook.add_worksheet()
#写入数据
worksheet.write('A1',1)
#关闭保存
workbook.close()

6.3 xlutils基本代码import xlrd #读取数据

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
import xlwt #写入数据
import xlutils #操作excel
#----xlrd库
#打开excel文件
workbook = xlrd.open_workbook('myexcel.xls')
#获取表单
worksheet = workbook.sheet_by_index(0)
#读取数据
data = worksheet.cell_value(0,0)
#----xlwt库
#新建excel
wb = xlwt.Workbook()
#添加工作薄
sh = wb.add_sheet('Sheet1')
#写入数据
sh.write(0,0,'data')
#保存文件
wb.save('myexcel.xls')
#----xlutils库
#打开excel文件
book = xlrd.open_workbook('myexcel.xls')
#复制一份
new_book = xlutils.copy(book)
#拿到工作薄
worksheet = new_book.getsheet(0)
#写入数据
worksheet.write(0,0,'new data')
#保存
new_book.save()

6.4 win32com基本代码

1
2
3
4
5
6
7
8
9
10
import win32com.client as wc
#启动Excel应用
excel_app = wc.Dispatch('Excel.Application')
#连接excel
workbook = excel_app.Workbooks.Open(r'e:/myexcel.xlsx' )
#写入数据
workbook.Worksheets('Sheet1').Cells(1,1).Value = 'data'
#关闭并保存
workbook.SaveAs('newexcel.xlsx')
excel_app.Application.Quit()

6.5 openpyxl基本代码

1
2
3
4
5
6
7
import openpyxl
# 新建文件
workbook = openpyxl.Workbook()
# 写入文件
sheet = workbook.activesheet['A1']='data'
# 保存文件
workbook.save('test.xlsx')

6.6 DataNitro基本代码

1
2
3
4
#单一单元格赋值
Cell('A1').value = 'data'
#单元区域赋值
CellRange('A1:B2').value = 'data'

openpyxl具体使用

1、 创建一个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
# -*- coding: utf-8 -*-
from openpyxl import Workbook
import datetime
import time

# 创建文件对象
wb = Workbook()
# 获取第一个sheet
ws = wb.active

# 在第1行第A列，写入数字23333
ws['A1'] = 23333
# 写入中文（unicode中文也可以）
ws['B1'] = "你好！"+"欢迎使用openpyxl"

# 在下一行，写入多个单元格
ws.append([1, 2, 3])

# 写入一个当前时间
ws['A2'] = datetime.datetime.now()

# 写入一个自定义的时间格式
ws['A3'] = time.strftime('%Y{y}%m{m}%d{d}%H{h}%M{f}%S{s}', time.localtime()).format(y='年', m='月', d='日', h='时', f='分', s='秒')

# 保存文件，注意文件覆盖
wb.save("test.xlsx")
# 关闭流
wb.close()

2、创建sheet

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
from openpyxl import Workbook

wb = Workbook()

# 创建一个sheet
ws1 = wb.create_sheet("sheet1")
# 设定一个sheet的名字
ws1.title = "sheet1 Title"
# 设定sheet的插入位置 默认插在后面
ws2 = wb.create_sheet("Mysheet", 0)
ws2.title = "Mysheet"
# 设定sheet的标签的背景颜色
ws1.sheet_properties.tabColor = "1072BA"

# 获取某个sheet对象
print(wb["sheet1 Title"])
print(wb["Mysheet"])

# 获取全部sheet的名字，遍历sheet名字
print(wb.sheetnames)
for i in wb.sheetnames:
    print(i)

print("*"*50)

for sheet in wb:
    print(sheet.title)

# 将sheet1中的A1单元格赋值为zeke
wb["sheet1 Title" ]["A1"] = "zeke"
# 复制一个sheet
source = wb["sheet1 Title"]
target = wb.copy_worksheet(source)

wb.save("test2.xlsx")
wb.close()

3、操作单元格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# -*- coding: utf-8 -*-
from openpyxl import Workbook

wb = Workbook()
# 创建一个sheet
ws1 = wb.create_sheet("Sheet1")

# 将A1单元格赋值为123.11
ws1["A1"] = 123.11
# 将B2单元格赋值为你好
ws1["B2"] = "你好"
# 将第4行第2列的单元赋值为10
temp = ws1.cell(row=4, column=2, value=10)

print(ws1["A1"].value)
print(ws1["B2"].value)
print(temp.value)

wb.save("test3.xlsx")
wb.close()

4、操作已存在的文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook

# 打开test5.xlsx文件
wb = load_workbook('test5.xlsx')
# 猜测格式类型
wb.guess_types = True
ws = wb.active
ws["A1"] = "12%"
print(ws["A1"].value)

# 注意如果原文件有一些图片或者图标，则保存的时候可能会导致图片丢失
wb.save("test5.xlsx")
wb.close()

5、操作批量的单元格

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
# -*- coding: utf-8 -*-
from openpyxl import Workbook

wb = Workbook()
# 创建一个sheet
ws1 = wb.create_sheet("Sheet")

ws1["A1"] = 1
ws1["A2"] = 2
ws1["A3"] = 3

ws1["B1"] = 4
ws1["B2"] = 5
ws1["B3"] = 6

ws1["C1"] = 7
ws1["C2"] = 8
ws1["C3"] = 9

# 操作单列
print(ws1["A"])
for cell in ws1["A"]:
    print(cell.value)

# 从A列到C列,获取每一个值
print(ws1["A:C"])
for column in ws1["A:C"]:
    for cell in column:
        print(cell.value)

# 从第1行到第3行，获取每一个值
row_range = ws1[1:3]
print(row_range)
for row in row_range:
    for cell in row:
        print(cell.value)

print("*"*50)

# 从第1行到第3行，从第1列到第3列
for row in ws1.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3):
    for cell in row:
        print(cell.value)

# 获取所有行
print(ws1.rows)
for row in ws1.rows:
    print(row)

print("*"*50)

# 获取所有列
print(ws1.columns)
for col in ws1.columns:
    print(col)

wb.save("test4.xlsx")
wb.close()

6、获取所有的行(列)对象：

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
42
43
44
45
46
# coding=utf-8
from openpyxl import Workbook
from openpyxl import load_workbook

# 打开test5.xlsx文件
wb = load_workbook('test5.xlsx')
ws = wb.active
rows = []
for row in ws.iter_rows():
    rows.append(row)

# 所有行
print(rows)
# 获取第一行
print(rows[0])
# 获取第一行第一列的单元格对象
print(rows[0][0])
# 获取第一行第一列的单元格对象的值
print(rows[0][0].value)
# 获取最后行 print rows[-1]
print(rows[len(rows) - 1])
# 获取第后一行和最后一列的单元格对象
print(rows[len(rows) - 1][len(rows[0]) - 1])
# 获取第后一行和最后一列的单元格对象的值
print(rows[len(rows) - 1][len(rows[0]) - 1].value)

cols = []
for col in ws.iter_cols():
    cols.append(col)
# 所有列
print(cols)
# 获取第一列
print(cols[0])
# 获取第一列的第一行的单元格对象
print(cols[0][0])
# 获取第一列的第一行的值
print(cols[0][0].value)
print("*" * 30)
# 获取最后一列
print(cols[len(cols) - 1] )
# 获取最后一列的最后一行的单元格对象
print(cols[len(cols) - 1][len(cols[0]) - 1])
# 获取最后一列的最后一行的单元格对象的值
print(cols[len(cols) - 1][len(cols[0]) - 1].value)

wb.close()

7、使用公式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')
ws1 = wb.active

ws1["A1"] = 1
ws1["A2"] = 2
ws1["A3"] = 3

ws1["A4"] = "=SUM(1, 1)"
ws1["A5"] = "=SUM(A1:A3)"

# 打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值
print(ws1["A4"].value)
# 打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值
print(ws1["A5"].value)

wb.save("test.xlsx")

8、合并单元格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# -*- coding: utf-8 -*-
from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('test.xlsx')
ws = wb.active

ws.merge_cells('A2:D2')
# 合并后的单元格，脚本单独执行拆分操作会报错，需要重新执行合并操作再拆分
ws.unmerge_cells('A2:D2')

ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=2, end_column=4)

wb.save("test.xlsx")

参考文章

https://www.cnblogs.com/zeke-python-road/p/8986318.html

扫一扫，分享到微信

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia-plus根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

教练，我想学技术！
我主要是以后端技术为主，偶尔看看网络安全相关的内容，或者编写一些有趣的脚本！