Python自动化之Excel利器openpyxl

前言

openpyxl 是一个 用于读写Excel 2010 xlsx/xlsm/xltx/xltm 文件的Python库，最初是基于PHPExcel。

可以通过pip安装

pip install openpyxl
1

官方文档：https://openpyxl.readthedocs.io/en/stable/

源码地址：https://foss.heptapod.net/openpyxl/openpyxl

一、Workbook

1.1 读取xlsx文件

通过 load_workbook加载文件

def load_workbook(filename, read_only=False, keep_vba=KEEP_VBA,
                  data_only=False, keep_links=True):
1
2

• filename：一般为 xlsx 文件名
• read_only：只读，不能编辑
• keep_vba：保留VBA内容，当设为True时要保存为xlsm格式，xlsx不能保存VBA代码
• data_only：当为True时，公式内容被读取时是公式计算出来的值，而不是公式
• keep_links：保留链接

import openpyxl

wb = openpyxl.load_workbook("示例.xlsx")
valueWb = openpyxl.load_workbook("示例.xlsx", data_only = True)
1
2
3
4

1.2 保存

wb.save('修改后.xlsx')
1

二、Sheet

2.1 创建Sheet

names = ['Sheet', 'Sheet1', 'Sheet2']
for n in names:
	wb.create_sheet(n)
    
# 创建Sheet,插入第一个位置
wb.create_sheet('Sheet3', 0)
1
2
3
4
5
6

2.2 遍历Sheet

# 单个Sheet
ws = wb['Sheet']
ws = wb[0]
# 所有Sheet
wb.worksheets
# 所有Sheet名字
wb.sheetnames
# 遍历所有Sheet
for sheet in wb:
    print(sheet)
# Sheet标题
ws.title
1
2
3
4
5
6
7
8
9
10
11
12

2.3 移动Sheet

ws=wb['Sheet2']
wb.move_sheet(ws, -1)
1
2

2.4 删除Sheet

ws=wb['Sheet1']
wb.remove(ws)

del wb['Sheet']
1
2
3
4

2.5 插入、删除行列

# 在第1行前插入2行
ws.insert_rows(1, 2)
# 在第1列前插入2列
ws.insert_cols(1, 2)

# 增加一行
ws.append([1, 2, 3])

# 从第6行开始，往下删除3行
ws.delete_rows(6, 3)
# 删除第6列
ws.delete_cols(6)
1
2
3
4
5
6
7
8
9
10
11
12

三、单元格

3.1 获取某个单元格

cell = ws[A1]
ws.cell(row=1, column=1)

# 获取单元格值
cell.value

# 获取单元格坐标A1
cell.coordinate
1
2
3
4
5
6
7
8

3.2 遍历单元格

# 按行遍历
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell)

# 按列遍历
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
     for cell in col:
         print(cell)
         
# 遍历范围单元格
cellRange=ws['A1:B3']
for row in cellRange:
    for cell in row:
        print(cell.value)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.3 获取范围单元格

# 范围单元格
cellRange=ws['A1:B3']

# 单元格行
a=ws[1]
b=ws[5:10]

# 单元格列
c=ws['A']
d=ws['C:D']

1
2
3
4
5
6
7
8
9
10
11

3.4 单元格赋值

ws['A1'] = 12

ws.cell(row=1, column=1, value = 12)
1
2
3

3.5 合并/解除合并单元格

ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')

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

3.6 单元格数据格式

cell.data_type
1

• b 布尔值，bool
• e 错误字符串，error_string (‘#NULL!’, ‘#DIV/0!’, ‘#VALUE!’, ‘#REF!’, ‘#NAME?’, ‘#NUM!’, ‘#N/A’)
• d 时间，date
• f 公式，formual
• n 空或数字，null/number
• s 字符串，string

3.7 单元格数字格式

cell.number_format
1

• General 常规
• 0.00_);[Red]0.000.00 数字
• mm-dd-yy 短日期
• [$-F800]dddd,\ mmmm\ dd,\ yyyy 长日期
• [$-F400]h:mm:ss\ AM/PM 时间
• 0.00% 百分数
• # ?/? 分数
• 0.00E+00 科学计数法
• @ 文本

四、样式

具体默认内建样式可以见源码：https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/openpyxl/styles/builtins.py

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
1

4.1 字体

font = Font(name='Calibri',
                 size=11,
                 bold=False,
                 italic=False,
                 vertAlign=None,
                 underline='none',
                 strike=False,
                 color='FF000000')
cell.font = font
1
2
3
4
5
6
7
8
9

4.2 填充

fill = PatternFill(fill_type=None,
                 start_color='FFFFFFFF',
                 end_color='FF000000')
cell.fill = fill
1
2
3
4

4.3 边框

border = Border(left=Side(border_style=None,
                           color='FF000000'),
                 right=Side(border_style=None,
                            color='FF000000'),
                 top=Side(border_style=None,
                          color='FF000000'),
                 bottom=Side(border_style=None,
                             color='FF000000'),
                 diagonal=Side(border_style=None,
                               color='FF000000'),
                 diagonal_direction=0,
                 outline=Side(border_style=None,
                              color='FF000000'),
                 vertical=Side(border_style=None,
                               color='FF000000'),
                 horizontal=Side(border_style=None,
                                color='FF000000')
                )
cell.border = border
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4.4 对齐

alignment=Alignment(horizontal='general',
                     vertical='bottom',
                     text_rotation=0,
                     wrap_text=False,
                     shrink_to_fit=False,
                     indent=0)
cell.alignment = alignment 
1
2
3
4
5
6
7

4.5 内建样式

见文档：https://openpyxl.readthedocs.io/en/stable/styles.html#using-builtin-styles

参考

1. https://openpyxl.readthedocs.io/en/stable/usage.html