Python自动化:openpyxl读取excel（openpyxl读写excel）

# pip install openpyxl

工作簿、工作表相关操作：

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

# sheetnames,获取工作簿的工作表名称
sh_name = wb.sheetnames
print(sh_name)
# ['Sheet1', 'Sheet2', 'Sheet3']

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

获取单元格的值：

# 获取单元格的值
v1 = sh1.cell(3,2).value     # openpyxl中cell的row, column参数是从1开始计算
print(v1)    #  B3的值：c
v2 = sh1.cell(row = 3, column = 4).value
print(v2)   # D3的值：他
v3 = sh2['A3'].value
print(v3)   # 3
v4 = sh3['C4']
print(v4.value)   # 四
v5 = sh1['E2'].value
print(v5)   # 2025-01-02 00:00:00

获取区域单元格的值：

# 获取区域单元格的值
for r in sh1['A1':'C3']:
    for c in r:
        print(c.coordinate,c.value)
    print('---END OF ROW---')

行、列相关操作

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

# 获取一行的值
r2 = list(sh1.rows)[1]
print(r2)  
# (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.E2>)

for r in list(sh1.rows)[5]:
    print(r.value,end=',')     # 第6行的值：6,f,None,None,None,
print('\n')

# 获取一列的值
c2 = list(sh2.columns)[3]
print(c2)
# (<Cell 'Sheet1'.D1>, <Cell 'Sheet1'.D2>, <Cell 'Sheet1'.D3>, <Cell 'Sheet1'.D4>, <Cell 'Sheet1'.D5>, <Cell 'Sheet1'.D6>, <Cell 'Sheet1'.D7>)

for c in list(sh2.columns)[3]:
    print(c.value,end=',')    #  第4列的值：你,我,他,她,None,None,None,
print('\n')

参考资料：

《Python办公自动化》，廖茂文

https://automatetheboringstuff.com/2e/chapter13/