Python学习 - openpyxl库

1. 第三方库 openpyxl

openpyxl 官方文档

openpyxl 是一个处理Excel表格的第三方库。openpyxl 库可以处理Excel2010以后的电子表格格式，包括：xlsx/xlsm/xltx/xltm。

2.Pycharm下载openpyxl

1. 菜单栏中的“File”，选择“Settings”。
2. 选择“Project:项目名”下的“Project Interpreter”，右侧的窗口中，点击“+”按钮，搜索并选择“openpyxl”，最后点击安装 “Install Package”

3.openpyxl的基本使用

教程

3.1 新建工作表

from openpyxl import Workbook

1.新建
wb = Workbook()
2.获取活跃的工作薄(sheet)
ws = wb.active
3.保存工作表并命名
wb.save('test.xlsx')
1
2
3
4
5
6
7
8

3.2 工作薄

1. sheet的属性
## 新建工作薄
ws1 = wb.create_sheet("Mysheet") 

## 修改工作薄的名字
ws.title = "New Title"

## 使用 Workbook.sheetname 属性查看工作簿中所有工作表的名称
print(wb.sheetnames) 

## 遍历工作表
for sheet in wb:
    print(sheet.title)
    
## 在**一个工作表**中创建一个工作簿的复制
source = wb.active
target = wb.copy_worksheet(source)

## 读取指定工作表
ws1 = wb.create_sheet("Mysheet1")
ws2 = wb.create_sheet("Mysheet2")
ws3 = wb.create_sheet("Mysheet3")

ws4=wb["Mysheet3"]


2. sheet的样式
## 默认情况下，包含该标题的选项卡的背景颜色为白色。你也可以使用 RRGGBB 颜色来改变 Worksheet.sheet_properties.tabColor 属性
ws.sheet_properties.tabColor = "1072BA" # 渐变蓝

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

3.3 单元格

3.3.1 单个访问单元格

1.访问单元格
c = ws['A4'] = 'test'
print(c) # test

2.单元格赋值
ws['A4'] = 4

3. 行列符号访问单元格
## 行 列 值
d = ws.cell(row=4, column=2, value=10) # B4 赋值 10(value)
print(d) # <Cell 'New Title'.B4>

4. cell(行,列).value
ws.cell(1,2).value="hello,openpyxl"

5. 先获取后赋值
b2 = ws['B2']
b2.value = 'new！'

6. 在最下面新增一行追加一个或多个值
ws.append([1, 2, 3])

举例
##  1.九九乘法表
wb = Workbook()
ws = wb.active
ws.title = "New Title"
for i in range(1, 10):
    for j in range(1, i+1):
        a = str(i)
        b = str(j)
        c = str(i * j)
        d = b + "*" + a + "=" + c
        ws.cell(row=i, column=j, value=d)
wb.save('test.xlsx')

## 2. 使用数字格式
import datetime
from openpyxl import Workbook

ws['A1'] = datetime.datetime(2023, 2, 28, 23, 59, 59) # xlsx A1显示 2023/2/28  23:59:59
time = ws['A1'].number_format
print(time) # yyyy-mm-dd h:mm:ss

## 使用公式
ws["A1"] = "=SUM(1, 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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

3.3.2 大量访问单元格

3.3.2.1 范围取值

1. 可以使用切片来访问一系列单元格
cell_range = ws['A1':'C2']
print(cell_range) # ((<Cell 'New Title'.A1>, <Cell 'New Title'.B1>, <Cell 'New Title'.C1>), (<Cell 'New Title'.A2>, <Cell 'New Title'.B2>, <Cell 'New Title'.C2>))

2. 一系列的行和列也可以通过类似的方法获取:
# 用上面的乘法表作为例子

# 选取第2行（下标从1开始）
row_cells = ws[2]  
print(row_cells) ## A2, B2, C2, D2, E2, F2, G2, H2, I2

#  选取B列
row_cells = ws[2]
print(row_cells)# B1, B2, B3, B4, B5, B6, B7, B8, B9

# 选取2 3 4 5 6 行
row_range_cells = ws[2:6]
col_range = ws[B:F']

# 选取B C D 散列
col_range_cells = ws["B:D"] 

# 选取C列
colC = ws['C'] 
print(row10) # C1 ~ C9

# 单读第10行
row10 = ws[10]
print(row10) # A10 ~ I10
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

3.3.2.2 迭代取值

1. iter_rows 指定最大最小的行列，下标从1开始
cells = ws.iter_rows(min_row=1, max_row=2, min_col=2, max_col=6)
for cell in cells:
    print(cell) # B1~F1 && B2~F2

2. iter_cols 同理
cells = ws.iter_cols(min_row=1, max_row=3, min_col=2, max_col=5)
for cell in cells:
    print(cell)
3. 其他迭代
for cell in ws.rows:
    print(cell) # A1 ~ I1 …… A9 ~ I9

for cell in ws.columns:
    print(cell)

for row in ws.values:
    for value in row:
        print(value)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3.3.3 合并拆分单元格

# 合并单元格
ws.merge_cells('A1:D4')
# 拆分单元格
ws.unmerge_cells('A1:D4')

# 其他写法
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

3.3.4 插入/删除/移动/隐藏

3.3.4.1 插入和删除

# 插入第一行
ws.insert_rows(1)
# 删除 F:H 列
ws.delete_cols(6, 3)
1
2
3
4

3.3.4.2 移动

在一个工作表内移动范围单元格,已存在的单元格将会被覆盖

# D4:F9 单元格向上移动一行向右移动两列
ws.move_range("D4:F9", rows=-1, cols=2)

#如果单元格包含公式，你可以让 openpyxl 帮你进行translate，但也并非总是你想要的结果，因此默认是禁用的。 同时，只有单元格本身的公式将会被translate。其他单元格对该单元格的引用或defined name将不会被更新
# ws.move_range("G4:H10", rows=1, cols=1, translate=True)
1
2
3
4
5

3.3.4.3 隐藏

# 隐藏A ~ D列
ws.column_dimensions.group('A','D', hidden=True)

# 隐藏1 ～ 10行
ws.row_dimensions.group(1,10, hidden=True)
1
2
3
4
5

3.4 保存到文件

#这个操作将会无警告直接覆盖已有文件
# filename为要保存成为的文件名，如果文件名带有路径，则文件会保存在对应的路径下
语法：wb.save(filename) 
wb = Workbook()
wb.save('balances.xlsx')
1
2
3
4
5

4.样式

4.1 理解和认识

样式可以应用于以下方面:

默认值

from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
font = Font(name='Calibri',
                size=11,
                bold=False,
                italic=False,
                vertAlign=None,
                underline='none',
                strike=False,
                color='FF000000')
fill = PatternFill(fill_type=None,
                start_color='FFFFFFFF',
                end_color='FF000000')
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')
               )
alignment=Alignment(horizontal='general',
                    vertical='bottom',
                    text_rotation=0,
                    wrap_text=False,
                    shrink_to_fit=False,
                    indent=0)
number_format = 'General'
protection = Protection(locked=True,
                        hidden=False)
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

4.2 单元格

4.2.1 单个单元格

from openpyxl.styles import Font

ws['A1'] = "( •̀ ω •́ )✧"
ws['D4'] = "<(￣︶￣)↗[GO!]"
# 添加样式
a1 = ws['A1']
d4 = ws['D4']
ft = Font(size = 14 , color = "FF0000")
a1.font = ft
d4.font = ft
1
2
3
4
5
6
7
8
9
10

4.2.2 合并单元格

合并单元格和其他单元格对象的行为相似，通过左上单元格来定义值和样式。可以改变左上单元格的边框来改变整个合并单元格的边框。 这种格式是出于编辑目的才被生成

from  openpyxl import  Workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment

wb = Workbook()
ws = wb.active

# 添加样式
ws.merge_cells('B2:F4')
top_left_cell = ws['B2']
top_left_cell.value = "My Cell"

thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")

top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)
top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD")
top_left_cell.fill = fill = GradientFill(stop=("000000", "FFFFFF"))
top_left_cell.font  = Font(b=True, color="FF0000")
top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
wb.save('test.xlsx')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20