logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

Python操作Excel文件（1）：花式大师pyexcel | silaoA的博客

 看见就非常 2022-02-24

pyexcel是开源的Excel操作库，项目地址为https://github.com/pyexcel/pyexcel。它包装了一套API用于读和写文件数据，这一套API接受的参数包括2个关键字集合，一个指定数据来源，另一个指定目的文件，每个集合里都有很多关键字参数控制读写细节。pyexcel包还实现了工作簿、表单类型，用于访问、操作和保存数据，读写操作十分花式。

本文评述pyexcel库用于读写Excel文件的方法，并做总结。

0x00 读文件

API

读文件包括pyexcel包级别get系列封装函数：get_array、get_dict、get_record、get_book、get_book_dict、get_sheet，可以将文件内容转换为array、dict、sheet/book等多种类型，屏蔽了文件介质是csv（comma separated values）/tsv（tab separated values）文本、xls/xlsx表格文件、dict/list类型、sql数据库表等的细节。同时还有一套同等的iget系列函数，唯一的不同是返回生成器，以提高效率。

示例

假使当前路径下，样例文件名称是data.csv（也可以是data.xls,但需要安装pyexcel-xls插件包），其内容如下:

1
2
3
4
5
col1	col2	col3
v11	v12	v13
v21	v22	v23
v31	v32	v33
v41		v43

可按下述示例代码读入数据内容，推荐首选get_sheet函数读Excel文件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 导入库
import pyexcel as pe

In [47]: sh = pe.get_sheet(file_name='./data.csv', delimiter='\t')
# csv全称是comma separated values，默认用逗号（comma）作列分割符，其他分割符要指定，否则会被当成同一列

In [48]: type(sh)
Out[48]: pyexcel.sheet.Sheet #没有看错，直接就转成了Sheet类型

In [49]: sh # 查看sh内容
Out[49]:
data.csv: # 第一行显示sheet名称，然后是数据
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| v11  | v12  | v13  |
+------+------+------+
| v21  | v22  | v23  |
+------+------+------+
| v31  | v32  | v33  |
+------+------+------+
| v41  |      | v43  |
+------+------+------+

也可以仅读取文件的一部分，传入更多参数控制行列范围，注意行列序号从0开始！

1
2
3
4
5
6
7
8
9
10
11
12
13
# 范围控制：第2行开始限定最多3行、第1列开始限定最多3列
In [57]: sh_mini = pe.get_sheet(file_name='./data.csv', delimiter='\t', start_row=2, row_limit=3,start_column=1, column_limit=3)

In [58]: sh_mini
Out[58]:
data.csv:
+-----+-----+
| v22 | v23 |
+-----+-----+
| v32 | v33 |
+-----+-----+
|     | v43 |
+-----+-----+

get_sheet函数接收sheet_name参数，对于有多个sheet的Excel表格，用于指定要读取的sheet，如果缺省，则读取第1个sheet。get_sheet函数还接收name_columns_by_row/name_rows_by_column参数，用于将指定的行/列作为列/行名称，默认值为0，代表第1行，pyexcel.sheet.Sheet类有同名的方法进行同样的操作。

其他几个函数与get_sheet较相似，接受的参数也相同。

  • get_array函数将文件数据转换为数组，即嵌套的列表，列表每个元素对应表格一行。

    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
    In [65]: arr = pe.get_array(file_name='./data.csv', delimiter='\t')

    In [66]: type(arr)
    Out[66]: list

    In [67]: arr
    Out[67]:
    [['col1', 'col2', 'col3'],
     ['v11', 'v12', 'v13'],
     ['v21', 'v22', 'v23'],
     ['v31', 'v32', 'v33'],
     ['v41', '', 'v43']]
    ``` 
    - `get_dict`函数将文件数据转换为有序字典，用第1行的字段作为key，后续行值组成列表作为value。
    ```python
    In [68]: d = pe.get_dict(file_name='./data.csv', delimiter='\t')

    In [69]: type(d)
    Out[69]: collections.OrderedDict

    In [70]: d
    Out[70]:
    OrderedDict([('col1', ['v11', 'v21', 'v31', 'v41']),
                 ('col2', ['v12', 'v22', 'v32', '']),
                 ('col3', ['v13', 'v23', 'v33', 'v43'])])

    In [71]: d['col2']
    Out[71]: ['v12', 'v22', 'v32', '']

  • get_record函数将文件数据转换为有序字典形成的列表，每行数据对应一个有序字典，字典将文件首行字段作为key、数据行作为value。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    In [72]: rec = pe.get_records(file_name='./data.csv', delimiter='\t')

    In [73]: type(rec)
    Out[73]: list

    In [74]: rec
    Out[74]:
    [OrderedDict([('col1', 'v11'), ('col2', 'v12'), ('col3', 'v13')]),
     OrderedDict([('col1', 'v21'), ('col2', 'v22'), ('col3', 'v23')]),
     OrderedDict([('col1', 'v31'), ('col2', 'v32'), ('col3', 'v33')]),
     OrderedDict([('col1', 'v41'), ('col2', ''), ('col3', 'v43')])]

    In [75]: rec[2]
    Out[75]: OrderedDict([('col1', 'v31'), ('col2', 'v32'), ('col3', 'v33')])

  • get_book函数将文件转换为pyexcel.book.Book对象，如果从csv文件读取，则只包含1个sheet，名称就是文件名；如果从xls文件读取，则包含xls文件中所有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
    37
    38
    39
    40
    41
    42
    In [79]: bk = pe.get_book(file_name='./data.csv', delimiter='\t')

    In [80]: type(bk)
    Out[80]: pyexcel.book.Book

    In [81]: bk
    Out[81]:
    data.csv: #仅有这一个sheet
    +------+------+------+
    | col1 | col2 | col3 |
    +------+------+------+
    | v11  | v12  | v13  |
    +------+------+------+
    | v21  | v22  | v23  |
    +------+------+------+
    | v31  | v32  | v33  |
    +------+------+------+
    | v41  |      | v43  |
    +------+------+------+

    In [3]: bk2 = pe.get_book(file_name='./data.xls')

    In [4]: type(bk2)
    Out[4]: pyexcel.book.Book

    In [5]: bk2
    Out[5]:
    Sheet1:
    +------+------+------+
    | col1 | col2 | col3 |
    +------+------+------+
    | v11  | v12  | v13  |
    +------+------+------+
    | v21  | v22  | v23  |
    +------+------+------+
    | v31  | v32  | v33  |
    +------+------+------+
    | v41  |      | v43  |
    +------+------+------+
    Sheet2: # Sheet2是空的

    Sheet3: # Sheet3是空的

  • get_book_dict函数将文件数据转换为多sheet的有序字典，sheet名称作为key，sheet数据以嵌套列表形式作为value，这在含有多sheet的Excel表格中较有用；对于csv文件，由于只有1个sheet，返回的有序字典仅有1项。

    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
    In [36]: bd = pe.get_book_dict(file_name='./data.xls')

    In [37]: type(bd)
    Out[37]: collections.OrderedDict

    In [38]: bd2 = pe.get_book_dict(file_name='./data.csv')

    In [39]: bd
    Out[39]:
    OrderedDict([('Sheet1',
                  [['col1', 'col2', 'col3'],
                   ['v11', 'v12', 'v13'],
                   ['v21', 'v22', 'v23'],
                   ['v31', 'v32', 'v33'],
                   ['v41', '', 'v43']]),
                 ('Sheet2', []),
                 ('Sheet3', [])])

    In [40]: bd2
    Out[40]:
    OrderedDict([('data.csv',
                  [['col1\tcol2\tcol3'],
                   ['v11\tv12\tv13'],
                   ['v21\tv22\tv23'],
                   ['v31\tv32\tv33'],
                   ['v41\t\tv43']])])

上述示例都是指定file_name参数从文件中读取数据，事实上pyexcel的get系列函数还支持指定其他参数从嵌套列表、字典等类型变量甚至sql连接中获得数据，不在本文示例范围之内。

0x01 数据访问

Book和Sheet

pyexcel中实现了pyexcel.book.Book和pyexcel.book.Sheet类型，和Excel表格文件的book、sheet概念相对应，可以通过上述get系列函数得到book/sheet对象，也可以通过pyexcel.Book()/pyexcel.Sheet()函数创建。

拿到book对象后，下一步就是访问book中的sheet。pyexcel.book.Book类对象可以按序号索引对应的sheet，也可以调用sheet_by_index和sheet_by_name方法获得指定的sheet内容，调用sheet_names方法返回book对象包含的所有sheet名称。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
In [45]: bk2[1]  # 通过序号访问book包含的sheet
Out[45]: Sheet2:

In [46]: bk2[0] # 通过序号访问book包含的sheet
Out[46]:
Sheet1:
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| v11  | v12  | v13  |
+------+------+------+
| v21  | v22  | v23  |
+------+------+------+
| v31  | v32  | v33  |
+------+------+------+
| v41  |      | v43  |
+------+------+------+

In [47]: bk2.sheet_by_index(1) # 调用方法访问book包含的sheet，和序号索引等效
Out[47]: Sheet2:

pyexcel.sheet.Sheet类对象有个texttable属性，即表示文本，除了sheet名称，还有绘制表格边框的虚线符，直接打印变量sh和打印sh.texttable效果相同。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
In [62]: sh0 = bk2[0]

In [63]: sh0.texttable
Out[63]: 'Sheet1:\n+------+------+------+\n| col1 | col2 | col3 |\n+------+------+------+\n| v11  | v12  | v13  |\n+------+------+------+\n| v21  | v22  | v23  |\n+------+------+------+\n| v31  | v32  | v33  |\n+------+------+------+\n| v41  |      | v43  |\n+------+------+------+'

In [64]: print(sh0.texttable)
Sheet1:
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| v11  | v12  | v13  |
+------+------+------+
| v21  | v22  | v23  |
+------+------+------+
| v31  | v32  | v33  |
+------+------+------+
| v41  |      | v43  |
+------+------+------+

此外，pyexcel.sheet.Sheet类还有几个十分有用的属性。

  • content属性，与直接显示sheet相比，就少了第一行的sheet名称。
  • csv属性，sheet数据的csv形式，没有表格框线。
  • array属性，sheet数据的array形式（嵌套列表），与get_array函数返回结果一样。
  • row/column属性，非常类似嵌套list，支持通过下标访问指定行/列，序号从0起始。

行和列

拿到pyexcel.sheet.Sheet对象后，除了用row/column属性获得所有行/列对象集合，进一步迭代遍历，也可以通过序号索引任意行/列，序号从0起始。当序号超出表格行/列范围时便抛出IndexError错误，可以用sheet对象的row_range/column_range方法查看行/列范围。pyexcel.sheet.Sheet对象的row_at/column_at方法，与直接通过row/column属性序号索引等效。

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
In [57]: sh0.row
Out[57]: <pyexcel.internal.sheets.row.Row at 0x2cd5e69bcc0>

In [58]: sh0.column
Out[58]: <pyexcel.internal.sheets.column.Column at 0x2cd5e5ef6d8>

In [59]: r1 =sh0.row[1] # 序号索引

In [60]: r1
Out[60]: ['v11', 'v12', 'v13']

In [61]: c3 = sh0.column[3] #超出范围报错
---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
<ipython-input-61-0f107a9c4c17> in <module>

In [62]: sh0.column_range() #查看列范围
Out[62]: range(0, 3)

In [63]: c2 = sh0.column[2] # 序号索引

In [64]: c2
Out[64]: ['col3', 'v13', 'v23', 'v33', 'v43']

In [65]: sh0.row_range() #查看行范围
Out[65]: range(0, 5)

In [66]: sh0.row_at(1)
Out[66]: ['v11', 'v12', 'v13']

单元格

pyexcel.sheet.Sheet对象支持二元序号索引任意单元格，或者用行/列名称替代序号（请注意下述代码注释）。也可以整体用Excel表单元格地址形式索引，无需任何转换。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
In [72]: c = sh0[3,1]  # 行列序号索引

In [73]: c
Out[73]: 'v32'

In [75]: sh0['B4'] # 单元格地址索引
Out[75]: 'v32'

# 设定第1行内容作为列名称，行序号0则定位到第1个数据行，行头被跳过，使用中注意！
In [78]: sh0.name_columns_by_row(0) 

In [79]: sh0[3,"col2"] #3定位到第4个数据行，对应表格的第5行
Out[79]: ''

In [80]: sh0[3,"col1"] #3定位到第4个数据行，对应表格的第5行
Out[80]: 'v41'

0x02 改写文件

改写文件包括改写变量值、将变量对象写入文件两个步骤，推荐通过pyexcel.book.Sheet或pyexcel.book.Book类对象进行。

API

对于pyexcel.book.Sheet类对象，row、column属性支持像list那样增删改操作，而且均有save_as方法用于将对象写入文件。此外，pyexcel提供了save系列封装函数：save_as、save_book_as写入文件，指定目的文件时，使用的参数名称与get系列相比较多了”dest_”前缀。如，get系列用file_name指定数据文件来源，save系列用dest_file_name指定目的文件路径；get系列用delimiter参数指定csv分隔符，save系列用dest_delimiter指定写入到csv文件时使用的分隔符。

改写示例

pyexcel.book.Sheet类对象可以整行/列地增、删、改，也可以定位到具体单元格赋值，使用列表整行/列赋值时要注意元素个数与列/行数一致。对于pyexcel.book.Book类对象，既可以把整个book像操作列表那样整体扩展，也可以只索引出某些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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
In [85]: sh0.column[1] = ['v12_new','v22_new',0.0,128]

In [86]: sh0
Out[86]:
Sheet1: #注意将首行当作列名称后，首行框线发生变化
+------+---------+------+
| col1 |  col2   | col3 |
+======+=========+======+
| v11  | v12_new | v13  |
+------+---------+------+
| v21  | v22_new | v23  |
+------+---------+------+
| v31  | 0.0     | v33  |
+------+---------+------+
| v41  | 128     | v43  |
+------+---------+------+

In [87]: sh0.row[-1] = [] #注意将首行当作列名称后，行索引将忽略首行，易造成混乱

In [88]: sh0
Out[88]:
Sheet1:
+------+---------+------+
| col1 |  col2   | col3 |
+======+=========+======+
| v11  | v12_new | v13  |
+------+---------+------+
| v21  | v22_new | v23  |
+------+---------+------+
| v31  | 0.0     | v33  |
+------+---------+------+
|      |         |      |
+------+---------+------+

In [101]: sh0.column['col3'] = [1,2,3,4] # 对列命名后，尽量使用字典的索引方式

In [103]: sh0[3,'col3'] = '4C' # 对第3数据行、'col3'列单元格赋值

In [104]: sh0['C4'] # 使用Excel单元格地址索引
Out[104]: '4C'

In [107]: sh0[3,2] # 使用行列序号索引
Out[107]: '4C'

In [118]: del sh0.column[2] # 删除指定列

In [121]: bk3 = bk2+bk2[-1] # 对book整体赋值

In [124]: bk3 = bk2+bk2  # 对book整理赋值

In [125]: bk3.sheet_names()
Out[125]: ['Sheet1', 'Sheet1_2', 'Sheet2', 'Sheet2_3', 'Sheet3', 'Sheet3_4']

pyexcel.book.Sheet还实现了表单转置（transpose）、截取（region）、剪切（cut）、粘贴（paste）、map应用（map）、行列筛选（filter）、格式化（format）等花式操作方法，本文均不一一论述。

pyexcel各种花式操作简化了代码逻辑，让用户专注于处理数据，但对于表格边框风格/字体/颜色等均不支持，对公式也不支持。

保存示例

写入文件，即可用pyexcel.book.Sheet或pyexcel.book.Book类对象的save_as方法，操作简单直接，操作Excel推荐首选。也可以用pyexcel包级别的save系列封装函数，更适合进行文件类型转换，同时还有一套同等的isave系列函数，主要的不同是只在写时读入变量，以提高效率。
以上这些save方法/函数会根据目的文件扩展名自动判别格式类型。

1
2
3
4
5
6
7
8
9
10
In [128]: sh0.save_as("./工作簿sh0.xls") #xls文件中含1个sheet

In [130]: bk2.save_as("./工作簿bk2.xls")

In [131]: bk3.save_as("./工作簿bk3.xls")

In [132]: bk2.save_as("./工作簿bk2.xlsx") #需要 pyexcel-xlsx,pyexcel-xlsxw支持

# 直接进行类型换行，省去读、写的繁琐操作，需要 pyexcel-xlsx,pyexcel-xlsxw支持
In [136]: pe.save_as(file_name="./data.xls", dest_file_name="./data.xlsx")

pyexcel.book.Sheet或pyexcel.book.Book类还实现save_to系列函数，将对象写入数据库、ORM、内存等。

0x03 总结

  • pyexcel包封装了get系列函数用于从文件中读取、转换数据，均能灵活支持多种读文件方式，对于操作Excel文件，推荐首选get_sheet函数。
  • pyexcel包对不同格式文件的支持依赖不同的插件包。
  • pyexcel包内部实现了pyexcel.book.Sheet或pyexcel.book.Book类型，与Excel文件的工作簿、表单概念对应，提供了多种灵活的数据访问、删改的方法，以及可视化的方法，让用户专注于处理数据，但很遗憾不支持Excel公式，也不支持图表、表格边框/字体/颜色等风格设定。
  • pyexcel.book.Sheet或pyexcel.book.Book类型有save系列方法将对象变量写入文件、数据库、内存等，推荐首选；同时pyexcel包级别的save_as系列封装函数，对于转换文件类型十分方便；这些写入文件的方法/函数，根据目的文件扩展名自动判别格式类型。
  • pyexcel.cookbook包封装了一些实用工具函数，如多类型文件合并、表格分拆;
  • pyexcel.book.Sheet和pyexcel.book.Book等类的方法并未实现全，调用时有的会抛出错误，要注意这个大坑，本文在ipython环境写示例时错误的未给出，这也是提示符编号不连续的原因。

参考

更多阅读

如本文对你有帮助，或内容引起极度舒适，欢迎分享转发与留言交流


►本文为原创文章，如需转载请私信知乎账号silaoA或联系公众号伪码人（We_Coder）。

都看这里了，不妨点个赞再走呗

    本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。
    转藏 分享 献花（0）

    来自： 看见就非常 > 《pmr》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多