openpyxl库备忘录

最近工作中用到次表格的读写，用python写的脚本，用到的库是openpyxl，这里记录一下常见的使用方法，做个备忘。

以前经常使用xlwt和xlrd对excel进行读写，但这两个库不支持excel2010以后的版本，也就是xlsx后缀的表格，该工具没办法支持。所以找到一个更新的工具库openpyxl可以同时对excel进行读写操作。

openpyxl的官方文档的地址在：https://openpyxl.readthedocs.io/en/stable/index.html

1. 打开文件和创建文件

读写excel的第一步就是打开文件或者创建新文件，openpyxl库对打开excel文件就是获取到workbook对象，一共有两个常用的方法：load_workbook()和Workbook()，使用方法如下：

workbook = openpyxl.Workbook()
workbook = openpyxl.load_workbook(filename)

打开文件后就要创建或者读取表格中的sheet，有如下方法

sheet = workbook.active # 获取到当前激活的sheet
sheet1 = workbook.create_sheet('title', 0)  # 创建新的sheet

2. 读写表格

读取表格

在获取到sheet对象后，就能够通过sheet获取到rows迭代器，可通过rows遍历表格中存在数据的所有行。在遍历rows时，又可以得到cols迭代器，然后通过cols可以遍历每个单元格中的具体的数据。

openpyxl把每个单元格定义为Cell，一般通过Cell.value属性读取具体的单元格存放的值。

具体的读取代码如下：

workbook = openpyxl.load_workbook(filename)
sheet = workbook.active
for row in list(sheet.rows)[2:]:
      for cell in row:
           print(cell.value)

需要说明的是，如果cell单元格没有数据，那么读取的值就为None，这个值也方便后续对数据的处理。

openpyxl的另一个特殊的地方是，所有的下标均是从1开始的，所以在使用python迭代器遍历的时候，需要在下标加上1。

一般读取的表格会带有title，所以需要从第二行开始读，而sheet.rows返回的是一个迭代器，无法使用切片对其进行操作，所以要将迭代器转换为list，然后使用切片，就有如下代码：list(sheet.rows)[2:]。

openpyxl在读取表格的时候，对于合并单元格的内容，会依次读取，合并后的单元格第一行能读取到值，后续行则为None，也就是合并单元格并不会影响到openpyxl的读取，所以只能自行在程序中对合并的单元格进行处理，这个比较麻烦一点。样例代码可参考如下：

row = 2
for item in self.nomal_assets:
    start_row = row
    sheet_nomal.cell(row=row, column=11, value=item['s1'])
    sheet_nomal.cell(row=row, column=12, value=item['s2'])
    for key, value in item.items():
        if key == 'port':
            for port_item in value:
                for port_key, port_value in port_item.items():
                    sheet_nomal.cell(row=row, column=list(item.keys()).index(key) + 1port_item.keys()).index(port_key)+ 2, value=port_value)
                if port_item is not value[-1]:     # 尾部有空行的情况
                    row = row + 1
            continue
        if key == 's1' or key == 's2':
            continue
        sheet_nomal.cell(row=row, column=list(item.keys()).index(key) + 1, value=value)

写表格

openpyxl在写表格的时候要麻烦一些，需要找到对应的cell进行一格一格的进行写入操作。

具体的代码如下：

sheet.cell(row=1, column=1, value='value')

通过shell对象的cell方法进行写入操作，需要指定具体的row和colum，然后value为具体写入的值。

3. 保存表格

保存表格很简单，在对sheet对象是从workbook对象获取到的，所以只需要调用workbook对象的save()方法即可保存文件。

代码如下：

workbook.save(filename)  

这样会在当前目录保存命名的文件。

4. 合并单元格

还有个较为常用的方法就是合并单元格，用程序可以指定合并单元格的范围。

self.row_alpha = 'ABCDEFGHIJKL'
for aplha in self.row_alpha:
    start = aplha + str(start_row)
    end = aplha + str(row)
    sheet_nomal.merge_cells(f'{start}:{end}')

用该代码可以指定具体列进行合并单元格操作，用到的关键方法就是merge_cells。