openpyxl 用法总结

发表于 | 分类于 | 阅读次数
openpyxl 是一个开源的、用于读写 Excel 2010 文档的 Python 库，是处理 Excel 复杂问题的首选工具。本文从工作簿、工作表、行/列、单元格、数据处理、表格样式等方面对 openpyxl 的常见用法做了一些总结，希望能够对你有所帮助。

安装

1
pip install openpyxl

工作簿（Workbook）

简单地理解，一个工作簿就是一个 Excel 文档。

新建空白工作簿

1
2
3
from openpyxl import Workbook

wb = Workbook()

读取本地工作簿

1
2
3
4
from openpyxl import load_workbook

file_path = '/Users/dandy/Desktop/test.xlsx'
wb = load_workbook(file_path)

保存工作簿

1
2
output_path = '/Users/dandy/Desktop/output.xlsx'
wb.save(output_path)

工作表（Worksheet）

新建工作表

1
wb.create_sheet(title=None, index=None)

查看工作簿中的所有工作表

1
wb.sheetnames    # 返回由工作表名称字符串所组成的列表

获取工作表对象

在使用 openpyxl 新建一个空白的工作簿时，其中已默认建立了一个空白工作表：

1
ws = wb.active

获取指定名称的工作表：

1
ws = wb['工作表名称']

拷贝工作表

1
wb.copy_worksheet(from_worksheet)    # 不可跨工作簿

移动工作表

1
wb.move_sheet(sheet, offset=0)

重命名工作表

1
ws.title = '新名称'

删除工作表

1
wb.remove(ws)

工作表的遍历

1
2
for sheet in wb:
    print(sheet.title)

行/列（Row/Column）

访问行/列

1
colC = ws['C']

1
col_range = ws['C:D']

1
row10 = ws[10]

1
row_range = ws[5:10]

插入新的行/列

1
ws.insert_rows(idx, amount=1)

1
ws.insert_cols(idx, amount=1)

删除行/列

1
ws.delete_rows(idx, amount=1)

1
ws.delete_cols(idx, amount=1)

获取最大的行/列数

1
ws.max_row

1
ws.max_col

行/列的遍历

1
2
3
4
5
6
7
8
# 行
for row in ws.rows:
    print(row)
# 列
for col in ws.cols:
    print(col)
# ws.rows 与 ws.cols 返回的是一个 generator 对象
# row 与 col 是类似 (<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>) 结构的元胞

列字母与数字的相互转换

1
2
3
4
from openpyxl.utils import get_column_letter, column_index_from_string

print(get_column_letter(1))
print(column_index_from_string('D'))

组合行/列

1
2
ws.column_dimensions.group('A','D', hidden=True)
ws.row_dimensions.group(1,10, hidden=True)

单元格（Cell）

访问单元格

1
c = ws['A1']

1
cell_range = ws['A1':'C2']

获取单元格数据

1
2
c = ws['A1']
print(c.value)

单元格赋值

1
ws['A4'] = 4

1
d = ws.cell(row=4, column=2, value=10)

1
2
3
4
5
6
# 1
data = [1, 2, 3, 4, 5, 6]
ws.append(data)
# 2
for i in range(10):
    ws.append([i, i**2])

单元格遍历

1
2
3
4
5
6
7
for row in ws.rows:
    for cell in row:
        print(cell.value)

for col in ws.cols:
    for cell in col:
        print(cell.value)

1
2
3
4
5
6
7
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell)
        
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell)

1
2
3
4
cell_range = ws['A1':'C2']
for row in cell_range:
    for cell in row:
        print(cell.value)

1
2
3
4
5
6
7
for x in tuple(ws.rows):
    for y in x:
        print(y.value)
        
for x in tuple(ws.cols):
    for y in x:
        print(y.value)

1
2
3
4
5
# 该条仅助于理解，不推荐使用
tuple_rows = ws.rows
for i in range(len(tuple_rows):
    for j in range(len(tuple_rows[i])):
        print(tuple_rows[i][j].value)

移动单元格

1
ws.move_range('D4', rows=-1, cols=2)

1
ws.move_range('D4:F10', rows=-1, cols=2)

如果被移动的单元格内包含公式，那么可以使用 translate 自动更新公式 ：

1
ws.move_range("G4:H10", rows=1, cols=1, translate=True)

复制单元格

1
ws['A2'] = ws['A1'].value

清空单元格

1
ws['A1'] = None

合并和拆分单元格

1
2
# 合并单元格后，往左上角写入数据即可
ws.merge_cells('A1:C3')

1
2
# 合并单元格拆分后，值回到A1位置
ws.unmerge_cells('A1:C3')

数据的处理

Excel 公式

1
ws['K1'] = '=SUM(A1:J1)'

1
2
3
from openpyxl.formula.translate import Translator

ws['K2'] = Translator('=SUM(A1:J1)', origin='K1').translate_formula('K2')

数据可视化

openpyxl 支持绘制各类 Excel 图表，具体用法请查阅官方文档（https://openpyxl.readthedocs.io/en/stable/charts/introduction.html）。

样式

1
from openpyxl.styles import Font, colors, Alignment, PatternFill, Border, Side

字体

1
2
3
4
5
6
7
8
9
10
11
12
13
font = Font(name='Calibri',
                size=11,
                bold=False,
                italic=False,
                vertAlign=None,
                underline='none',
                strike=False,
                color='FF000000')

# 示例：设定字体为等线24号，加粗斜体，字体颜色红色。
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
# 将字体赋值给A1
ws['A1'].font = bold_itatic_24_font

对齐方式

1
2
3
4
5
6
7
8
9
alignment = Alignment(horizontal='general',
                      vertical='bottom',
                      text_rotation=0,
                      wrap_text=False,
                      shrink_to_fit=False,
                      indent=0)

# 示例：设置B1中的数据垂直居中和水平居中
ws['B1'].alignment = Alignment(horizontal='center', vertical='center')

horizontal 的可用样式为：

1
{'left':'左对齐', 'center':'居中对齐', 'right':'右对齐', 'distributed':'分散对齐', 'centerContinuous':'跨列居中', 'justify':'两端对齐', 'fill':'填充', 'general':'常规'}

vertical 的可用样式为：

1
{'top':'顶端对齐', 'center':'居中对齐', 'bottom':'底端对齐', 'distributed':'分散对齐', 'justify':'两端对齐'}

wrap_text 为自动换行。

填充单元格颜色

1
2
3
4
5
fill = PatternFill(fill_type=None,
                start_color='FFFFFFFF',
                end_color='FF000000')

ws['A1'].fill = fill

可选择的填充样式为：

1
['none', 'solid', 'darkDown', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkTrellis', 'darkUp', 'darkVertical', 'gray0625', 'gray125', 'lightDown', 'lightGray', 'lightGrid', 'lightHorizontal', 'lightTrellis', 'lightUp', 'lightVertical', 'mediumGray']

设置行高和列宽

1
ws.row_dimensions[2].height = 40

1
ws.column_dimensions['C'].width = 30

设置边框

1
2
3
4
5
6
7
8
9
10
border = Border(left=Side(border_style=None, color='FF000000'),
                right=Side(border_style=None, color='FF000000'),
                top=Side(border_style=None, color='FF000000'),
                bottom=Side(border_style=None, color='FF000000'),
                diagonal=Side(border_style=None, color='FF000000'),
                diagonal_direction=0,
                outline=Side(border_style=None, color='FF000000'),
                vertical=Side(border_style=None, color='FF000000'),
                horizontal=Side(border_style=None, color='FF000000')
               )

可选择的边框样式为：

1
['dashDot', 'dashDotDot', 'dashed', 'dotted', 'double', 'hair', 'medium', 'mediumDashDot', 'mediumDashDotDot', 'mediumDashed', 'slantDashDot', 'thick', 'thin']

设置工作表标签底色

1
ws.sheet_properties.tabColor = "1072BA"

创建一个样式预设

1
2
3
4
5
6
7
8
9
10
11
12
from openpyxl.styles import NamedStyle, Font, Border, Side

highlight = NamedStyle(name='highlight')
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color='000000')
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
# Once a named style has been created, it can be registered with the workbook:
wb.add_named_style(highlight)
# Named styles will also be registered automatically the first time they are assigned to a cell:
ws['A1'].style = highlight
# Once registered, assign the style using just the name:
ws['D5'].style = 'highlight'

其他

插入图片

1
2
3
4
5
from openpyxl.drawing.image import Image

img = Image('logo.png')
img.width, img.height = (180, 80)    # 指定图片尺寸，可省略
ws.add_image(img, 'A1')

插入批注

1
2
3
4
from openpyxl.comments import Comment

comment = Comment('This is the comment text', 'Comment Author')
ws["A1"].comment = comment
坚持原创技术分享，您的支持将鼓励我继续创作！