Python 处理Excel之openpyxl库的使用

官方文档链接: https://openpyxl.readthedocs.io/en/stable/tutorial.html#create-a-workbook

使用范例代码:

from openpyxl import load_workbook

# 加载表数据
wb = load_workbook('sample.xlsx')

# 查看表的all sheet
# print(wb.get_sheet_names())

# 获取指定sheet,通过sheet名
# sh1 = wb.get_sheet_by_name('Sheet')
# ws = wb['Sheet']

# 获取指定sheet,通过索引
ws = wb._sheets[0]

#  copy sheet
# ws_bak = wb.copy_worksheet(ws)

# 单元格取值 method 1
a4 = ws['A4']    # ws.cell(4,1)  行,列
a4.value = 'a4' # 赋值

# 单元格取值 method 2
a2 = ws.cell(2,1)
print(a2.value)

# 单元格赋值
ws['A5'] = 5

# 赋值同时获取
b4 = ws.cell(row=4, column=2, value=10)
print(b4.value)

# Todo 获取范围单元格
# 是个大元组,每一行作为一个元素, 其中每个元素又包含每一列的单元格构成的小元组
cell_range = ws['A1':'C2']
# a2 = cell_range[1][0].value


# 获取sheet 的 行,或列
colC = ws['C']  # 获取 C 列
col_range = ws['C:D']  # 获取 C:D 列
row10 = ws[10]  # 获取第10 行
row_range = ws[5:10] # 获取第 5: 10 行, 双闭合



# 按行取
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
   for cell in row:
       print(cell)
# <Cell Sheet1.A1>
# <Cell Sheet1.B1>
# <Cell Sheet1.C1>
# <Cell Sheet1.A2>
# <Cell Sheet1.B2>
# <Cell Sheet1.C2>

# 按列取
# values_only=True 如果加上这个参数,下列结果就只显示各单元格的值,不再显示单元格信息
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell)
# <Cell Sheet1.A1>
# <Cell Sheet1.A2>
# <Cell Sheet1.B1>
# <Cell Sheet1.B2>
# <Cell Sheet1.C1>
# <Cell Sheet1.C2>

# 另存为,不会改变原excel文件内容,要保存到原文件,就保存相同名称就行覆盖即可.
wb.save('sample2.xlsx')

wb.close()