Python自动化:openpyxl读取excel，补充了些内容

打开工作簿，选择工作表

import openpyxl
# 打开已有工作簿
wb = openpyxl.load_workbook('example1.xlsx')

# sheetnames,获取工作簿的工作表名称
sh_name = wb.sheetnames
print(sh_name)

# 选择工作表
sh1 = wb.worksheets[0] #获取第一个工作表
sh2 = wb['Sheet1'] #获取名为"Sheet1"的工作表
sh3 = wb.active #获取活动工作表

获取单元格的值

# 获取单元格的值
cv1 = sh1.cell(3,2).value # openpyxl中cell的row, column参数是从1开始计算
print(cv1)   #B3的值，c
cv2 = sh1.cell(row = 3, column = 4).value
print(cv2)    # D3的值，他
cv3 = sh2['A3'].value
print(cv3)
cv4 = sh3['C4']
print(cv4.value)
cv5 = sh1['E2'].value
print(cv5)   # 2025-01-02 00:00:00

获取区域单元格的值

# 获取区域单元格的值(先循环行)
# for r in sh1['A1':'C3']: 也可以
for r in sh1['A1:C3']:
    for c in r:
        print(c.coordinate,c.value)
    print('---END OF ROW---')
# 获取区域单元格的值（先循环列）
for co in sh1["C:E"]:
    for c in co:
        print(c.coordinate,c.value)
    print("---END OF COLUMN---")

获取最大行数、列数

# 获取最大行数、列数
r1 = sh1.max_row
print(r1)   #7
c1 = sh2.max_column
print(c1)    #5

获取行的值

# 获取行的值，第4行
r2 = [list(sh1.rows)[3]]
print(r2)

r3 = []
for r in list(sh1.rows)[3]:
    r3.append(r.value)
print(r3)   # [4, 'd', '四', '她', None]

r4 = []
for r in sh1[4]:
    r4.append(r.value)
print(r4)   #[4, 'd', '四', '她', None]

# 获取多行的值，第3行、第4行的值
print(list(sh1.values)[2:4])
# [(3, 'c', '三', '他', None), (4, 'd', '四', '她', None)]

获取列的值

# 获取列的值，第4列
c2 = list(sh2.columns)[3]
print(c2)

c3 = []
for c in list(sh2.columns)[3]:
    c3.append(c.value)
print(c3) #['你', '我', '他', '她', None, None, None]

c4 = []
for c in sh1["D"]:
    c4.append(c.value)
print(c4) #['你', '我', '他', '她', None, None, None]

获取所有数据

# 获取所有数据,先循环行
a1 = []
for r in sh1.rows:
    for c in r:
        a1.append(c.value)
print(a1)

# 获取所有数据,先循环列
a2 = []
for co in sh1.columns:
    for c in co:
        a2.append(c.value)
print(a2)