openpyxl关于区域的操作

单元格的访问

• worksheet[“A1”]
• worksheet.cell(row, column)

from openpyxl import *
file = r"D:\xlsx\lianxi_1.xlsx"
wb1 = load_workbook(file)
ws1 = wb1["student"]
print(ws1["A1"].value)
print(ws1.cell(1, 2).value)
1
2
3
4
5
6

name
Math
1
2

---

行或者列的访问

• worksheet[column_name],返回一个cell元组
• worksheet[row_num],返回一个cell元组
• worksheet.rows, 获取所有行，返回一个迭代器
• worksheet.columns, 返回所有列，返回一个迭代器

# 列访问
ws1 = wb1["student"]
print(ws1["A"])
print(ws1.rows)
print(ws1.columns)
1
2
3
4
5

(, , , )


1
2
3

# 行访问
ws1 = wb1["student"]
print(ws1[1])
1
2
3

(, , , , )
1

---

访问某个区域

• worksheet[区域表达]
• ws1[“A1:C3”]，ws1[“A:C”]，ws1[1:3]等

print("1--------", ws1["A1:C3"])
print("2--------", ws1["A:C"])
print("3--------", ws1[1:2])
1
2
3

1-------- ((, , ), (, , ), (, , ))
2-------- ((, , , ), (, , , ), (, , , ))
3-------- ((, , , , ), (, , , , ))
1
2
3

---

遍历某个区域的内容

• worksheet[区域表达]
• ws1[“A1:C3”]，ws1[“A:C”]，ws1[1:3]等
• 当遍历某个区域时，是先拿出来一行，然后你需要在这一行遍历每一个单元格
• 当按列时，是先拿出来一列

ws1 = wb1["student"]
for i_row in ws1["A1:C3"]:
    for i_cell in i_row:
        print(i_cell.value)

for i_row in ws1["A1:C3"]:
    print([i_cell.value for i_cell in i_row])
1
2
3
4
5
6
7

ws1 = wb1["student"]
for i_col in ws1["A:C"]:
    for i_cell in i_col:
        print(i_cell.value)

for i_col in ws1["A:C"]:
    print([i_cell.value for i_cell in i_col])
1
2
3
4
5
6
7

---

列号和字母的互转

• openpyxl.utils.get_column_letter(数字)
• openpyxl.utils.column_index_from_string(字母)

print(utils.get_column_letter(5))
print(utils.column_index_from_string("C"))
1
2

E
3
1
2

---

---
## 按行或者列对整张表操作
- 如果空白区域设置过格式的话，也会被认为有数据
1
2
3

for i_row in ws1.rows:
    for i_cell in i_row:
        print(i_cell.value)
1
2
3

name
Math
Chemistry
Chinese
Physics
Lucy
85
92
88
98
Jim
90
87
56
75
HanMei
98
98
100
43
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

---
# 单元格中写数据或者改变数据
- worksheet[]= value
- worksheet.cell(row, column, value)或者worksheet.cell(row, column)= value
1
2
3
4

---

工作表的末尾添加数据

• 添加的类型和设置的一致

list1 = ["张三", 90, 80, 70, 99]
ws1.append(list1)
wb1.save(file)
1
2
3

---

插入列和行

• worksheet.insert_cols(idx, amount)
• worksheet.insert_rows(idx, amount)
• idx:插入的位置，amount:插入的数量

ws1.insert_cols(idx=2, amount=2)
wb1.save(file)
1
2

---

删除行

• delete_rows(idx, amount): 从指定行向下指定所需删的行数
• delete_cols(idx, amount): 从指定列向右指定所需删的列数

---

移动单元格

• worksheet.move_range(range, rows, cols)
• rows：正数时向下移动，负数相反
• cols：正数时向右移动，负数相反

---

属性方法总结

# 最大最小行号：worksheet.max_row, min_row
# 最大最小列号：worksheet.max_column, min_column
# 获取行列：worksheet.rows，columns
# 冻结窗格: worksheet.freeze_panes=单元格
# values：按行获取表格的内容-生成器
# 确定单元格行列号：worksheet["A1"].column, worksheet["A1"].row
# cell.value: 单元格的数值
# cell.coordinate: 单元格的坐标
1
2
3
4
5
6
7
8

# iter_rows(self, min_row=None, max_row=None, min_col=None, max_col=None, values_only=False): 按行指定范围生成表格范围对象
# iter_cols(self, min_row=None, max_row=None, min_col=None, max_col=None, values_only=False): 按列指定范围生成表格范围对象
# append: 在表格末尾添加
# ws1.merge_cells("A1:B3"): 对指定区域进行合并
# ws1.unmerge_cells("A1:B3"): 对指定区域进行合并
# ws1.unmerge_cells("A1:B3")： 取消合并
1
2
3
4
5
6

---

应用举例

每张工作表固定单元格求和

from openpyxl import *
file = r"D:\xlsx\lianxi_3.xlsx"
wb1 = load_workbook(file)
# 遍历
list1 = []
for i_ws in wb1.worksheets:
   list1.append(i_ws["A2"].value)
print(sum(list1))


#列表推导式
print(sum(i_ws["A2"].value for i_ws in wb1.worksheets))
1
2
3
4
5
6
7
8
9
10
11
12

24
24
1
2

---

按行或列求和

from openpyxl import *
file = r"D:\xlsx\lianxi_4.xlsx"
wb1 = load_workbook(file)
ws1 = wb1.worksheets[0]
# for i_col in ws1.columns:
#     print(i_col)
rg1 = list(ws1.columns)[1:]
print(rg1, len(rg1))
list1 = []
for i_col in rg1:
    for i_cell in i_col:
        list1.append(i_cell.value)
print(sum(list1[1:]))

print([i_cell.value for i_cell in i_col for i_col in list(ws1.columns)[1:]])
print(sum(([i_cell.value for i_cell in i_col for i_col in list(ws1.columns)[1:]])[1:]))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

[(, , , )] 1
214
['数学', 95, 84, 35]
214
1
2
3
4

list对整张表操作

ws1 = wb1["student"]
print(list(ws1.values))
1
2

[('name', 'Math', 'Chemistry', 'Chinese', 'Physics'), ('Lucy', 85, 92, 88, 98), ('Jim', 90, 87, 56, 75), ('HanMei', 98, 98, 100, 43)]
1

ws1 = wb1["student"]
rg1 = ws1.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3)
for i_row in rg1:
    for i_cell in i_row:
        print(i_cell.value)
1
2
3
4
5

name
Math
Chemistry
Lucy
85
92
Jim
90
87
1
2
3
4
5
6
7
8
9

条件下的处理

# 把空白格改为缺考
from openpyxl import *
file = r"D:\xlsx\lianxi_5.xlsx"
wb1 = load_workbook(file)
ws1 = wb1["score"]
for i_row in ws1.rows:
    for i_cell in i_row:
        if i_cell.value is None:
            i_cell.value = "缺考"
wb1.save(file)
1
2
3
4
5
6
7
8
9
10

# 删除带有缺考的行
for i_row in ws1.rows:
    for i_cell in i_row:
        if i_cell.value == "缺考":
            ws1.delete_rows(i_cell.row)
            break
wb1.save(file)
1
2
3
4
5
6
7