参考：https://openpyxl.readthedocs.io/en/stable/

概述

openpyxl模块是一个读写Excel 2010及以上版本(.xlsx)的Python库，如果要处理更早格式的Excel文档(.xls)，需要用到额外的库(xlrd/xlwt)，openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。

安装

1
pip install openpyxl

若需要插入图像，需要安装：

1
pip install pillow

方法

创建

创建文件

1
2
3
4
5
6
7
8
# 创建
from openpyxl import Workbook

wb = Workbook()  # 实例化
ws = wb.active   #  获取当前active的sheet
ws.title = '今天天气真好'  # 设置sheet表名
print(ws.title)
wb.save('测试.xlsx')   # 保存

创建sheet

使用Workbook.create_sheet()可以创建新的sheet：

1
2
3
4
5
>>> ws1 = wb.create_sheet("Mysheet") # 默认新建sheet是插到末尾
# or
>>> ws2 = wb.create_sheet("Mysheet", 0) # 插到第一个
# or
>>> ws3 = wb.create_sheet("Mysheet", -1) # 插到倒数第二个

若不指定sheet的名称，则会安装sheet1、sheet2…的方式创建。

修改sheet的名称：

1
ws.title = "New Title"

默认title的背景色为白色，指定背景色的方法如下：

1
ws.sheet_properties.tabColor = "1072BA"

可以循环所有的sheet的：

1
2
>>> for sheet in wb:
...     print(sheet.title)

打开已有文件

1
2
from openpyxl import load_workbook
wb = load_workbook('测试.xlsx')

插入数据

插入的方式

方式1：数据可以直接分配到单元格中(可以输入公式)

1
2
3
4
5
6
from openpyxl import load_workbook
wb = load_workbook('测试.xlsx')
ws = wb.active
ws['C2'] = '你好啊'
ws['D2'] = '世界！'
wb.save('测试.xlsx')

方式2：可以附加行，从第一列开始附加(从最下方空白处)

1
ws.append([1, 2, 3, 4])

方式3：Python类型会被自动转换

1
2
3
4
5
6
7
8
9
from openpyxl import load_workbook
import datetime
wb = load_workbook('测试.xlsx')
ws = wb.active
# ws['C2'] = '你好啊'
# ws['D2'] = '世界！'
# ws.append([1, 2, 3, 4])
ws['E5'] = datetime.datetime.now().strftime('%Y-%m-%d')
wb.save('测试.xlsx')

获取单元格数据

可以使用切片的方式获取单元格的数据：

1
>>> cell_range = ws['A1':'C2']

行和列范围内的数据也能获取：

1
2
3
4
>>> colC = ws['C']
>>> col_range = ws['C:D']
>>> row10 = ws[10]
>>> row_range = ws[5:10]

如果只需要工作表中的值，则可以使用Worksheet.value属性。这将迭代工作表中的所有行，但只返回单元格值。

1
2
3
for row in ws.values:
    for value in row:
        print(value)

保存文件

1
2
>>> wb = Workbook()
>>> wb.save('balances.xlsx')  # 此操作将在没有警告的情况下覆盖现有文件。

保存为模板：

1
2
3
>>> wb = load_workbook('document.xlsx')
>>> wb.template = True
>>> wb.save('document_template.xltx')

单元格格式

1
2
3
4
5
6
7
8
9
>>> import datetime
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # set date using a Python datetime
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>>
>>> ws['A1'].number_format
'yyyy-mm-dd h:mm:ss'

函数

1
2
3
4
5
6
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # add a simple formula
>>> ws["A1"] = "=SUM(1, 1)"
>>> wb.save("formula.xlsx")

合并和拆分单元格

合并单元格时，除左上角单元格外的所有单元格都将从工作表中删除。

1
2
3
4
5
6
7
8
9
10
11
>>> from openpyxl.workbook import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> ws.merge_cells('A2:D2')
>>> ws.unmerge_cells('A2:D2')
>>>
>>> # or equivalently
>>> ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
>>> ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

插入图片

1
2
3
4
5
6
7
8
9
10
11
12
13
>>> from openpyxl import Workbook
>>> from openpyxl.drawing.image import Image
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>> ws['A1'] = 'You should see three logos below'
 
 # create an image
>>> img = Image('logo.png')

# add to worksheet and anchor next to cells
>>> ws.add_image(img, 'A1')
>>> wb.save('logo.xlsx')