openpyxl常用用法

openpyxl简介

openpyxl是一个Python的实用第三方库，用户读取/写入后缀为xlsx / xlsm / xltx / xltm的Excel表格文件。

• openpyxl官方文档

openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.0.9 documentation

表格基本操作

• 导入openpyxl库

import openpyxl

对工作簿操作

• 打开工作簿

wb = load_workbook(filename='文件路径', data_only=False)

filename: 文件路径

data_only: 默认为False，以Excel公式的形式读取；当为True的时候以值的形式读取

  

• 创建工作簿

wb = Workbook()

• 保存工作簿

wb.save('文件路径')

对工作表操作

• 打开活动工作表

ws = wb.active

• 打开指定名称工作表

ws = wb['工作表名']

• 创建新工作表

ws = wb.create_sheet('新工作表名')

括号里不传递名字的话，会默认用Sheet、Sheet1、Sheet2……命名

• 修改工作表名称

ws.title = '新工作表名'

• 查看所有工作表的名称

wb.sheetnames

返回值是所有工作表名称组成的列表['Sheet1', 'Sheet2', 'Sheet3']

• 获取工作表的数据范围

获取最大行

max_row = ws.max_row

获取最大列

max_col = ws.max_column

• 删除工作表

wb.remove(ws)

• 复制工作表（创建副本）

source = wb.active
target = wb.copy_worksheet(source)

把source创建一个副本，并用target表示

• 修改工作表标签颜色

# 如：ws.sheet_properties.tabColor = "1072BA"
ws.sheet_properties.tabColor = '颜色十六进制码'

对行/列操作

• 在最后添加一行数据

# 可迭代对象有列表、元组
ws.append(可迭代对象)

• 插入行/列

ws.insert_rows(要添加的行, 插入的行数)
ws.insert_cols(要添加的列, 插入的列数)

• 删除行/列

ws.delete_rows(开始行, 删除的行数)
ws.delete_cols(开始列, 删除的列数)

对单元格操作

• 访问单个单元格

  • 使用索引

# 如取第A1单元格：c = ws['A1']
c = ws['单元格位置']

• • 使用ws.cell()方法

# 如取A1单元格：c = ws.cell(1, 1)
c = ws.cell(行, 列)

• 访问多个单元格

# 如取A1到C2单元格：cell_range = ws['A1':'C2']
cell_range = ws['首单元格':'末单元格']

• 获取单元格的值

  • 使用索引

# 如取第A1单元格的值：cv = ws['A1'].value
cv = ws['单元格位置'].value

• • 使用ws.cell()方法

# 如取A1单元格：cv = ws.cell(1,1).value
cv = ws.cell(行, 列).value

• 向单元格里写入值

# 单元格对象为上方的c
单元格对象.value = 值

• 按行遍历获取单元格

for row in ws.iter_rows(min_row=1, max_row=1, min_col=1, max_col=1, values_only=False):
    print(row)

row为每行单元格组成的元组

min_row: 最小行

max_row: 最大行

min_col: 最小列

max_col: 最大列

values_only: 默认为False，以单元格对象返回；当为True时，以单元格值返回

• 按列遍历获取单元格

for col in ws.iter_cols(min_row=1, max_row=1, min_col=1, max_col=1, values_only=False):
    print(col)

col为每列单元格组成的元组

min_row: 最小行

max_row: 最大行

min_col: 最小列

max_col: 最大列

values_only: 默认为False，以单元格对象返回；当为True时，以单元格值返回

• 合并单元格

# 如合并A1到C2: ws.merge_cells('A1:C2')
ws.merge_cells('首格:末格')

# 另一种方法，合并A1到B2的单元格
ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=2)

start_row: 开始行

srart_column: 开始列

end_row: 结束行

end_column: 结束列

• 拆分合并单元格

# 如拆分A1到C2: ws.unmerge_cells('A1:C2')
ws.unmerge_cells('首格:末格')

# 另一种方法，拆分A1到B2的单元格
ws.unmerge_cells(start_row=1, start_column=1, end_row=2, end_column=2)

start_row: 开始行

srart_column: 开始列

end_row: 结束行

end_column: 结束列

表格进阶操作

表格显示操作

• 移动覆盖数据区域

# 将D4到F10向上移动1行，向右移动2列
ws.move_range("D4:F10", rows=-1, cols=2)

• 冻结行/列

# 如冻结B列和第2行: ws.freeze_panes = 'B2'
ws.freeze_panes = '作为基准的单元格'

• 隐藏行/列

# 隐藏A到D列
ws.column_dimensions.group('A', 'D', hidden=True)
# 隐藏1到10行
ws.row_dimensions.group(1, 10, hidden=True)

Excel公式操作

• 使用Excel公式

# 如求和: ws["A1"] = "=SUM(1, 1)"
ws['单元格位置'] = '=Excel公式'

非文本内容操作

• 插入图片

from openpyxl.drawing.image import Image
img = Image('图片文件')
ws.add_image(img, '单元格位置') 

巧记忆