python openpyxl excel库

openpyxl是一个用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。
常用的基本操作如下：

from openpyxl import load_workbook
filename = r'C:\Users\admin\Desktop\参数表telnet1.xlsx'
# 加载excel表
wb = load_workbook(filename)
# 获取所有标签页
sheetnames = wb.sheetnames
print(sheetnames)  # ['sheet1', 'Sheet2', 'Sheet3']

# 获取指定标签页
sheet = wb['sheet1']
print(sheet) # <Worksheet "sheet1">

# 复制标签页
cp_sheet = wb.copy_worksheet(sheet)
print(cp_sheet) # <Worksheet "sheet1 Copy">

# 创建一个标签页
cr_sheet = wb.create_sheet('create')
print(cr_sheet) # <Worksheet "create">

# 获取指定单元格
cell = sheet["A1"]
print(cell) # <Cell 'sheet1'.A1>

cell = sheet.cell(row=1,column=1)
print(cell) # <Cell 'sheet1'.A1>

# 获取多个单元格
cell_range = sheet["A1":"C1"]
print(cell_range)  # ((<Cell 'sheet1'.A1>, <Cell 'sheet1'.B1>, <Cell 'sheet1'.C1>),)
for cells in cell_range:
    for cell in cells:
        print(cell)
# <Cell 'sheet1'.A1>
# <Cell 'sheet1'.B1>
# <Cell 'sheet1'.C1>
cell_range = sheet.iter_rows(max_row=1,max_col=3)
print(cell_range)   # 生成器<generator object Worksheet._cells_by_row at 0x0FAEAFB0>
for cells in cell_range:
    print(cells)  # (<Cell 'sheet1'.A1>, <Cell 'sheet1'.B1>, <Cell 'sheet1'.C1>)

# sheet.rows获取所有行  sheet.columns获取所有列
for row in sheet.rows:
    print(row)


# cell.value获取属性值
cell = sheet["A1"]
print(cell.value)  # 模板

# 写入文本
cp_sheet['A1'] = '复制的模板'
print(cp_sheet['A1'].value) # 复制的模板

# 写入数字
cp_sheet['A2'] = 123
print(cp_sheet['A2'].value) # 123

# 合并单元格
sheet.merge_cells("A2:D2")
# 取消合并单元格
sheet.unmerge_cells("A2:D2")

# 删除sheet
wb.remove(sheet)
# 保存
wb.save(filename)

#关闭
wb.close()

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71