使用 openpyxl 处理 Excel 电子表格_openpyxl修改excel

openpyxl模块让 Python 程序能读取和修改 Excel 电子表格文件。例如，可能有一个无聊的任务，需要从一个电子表格拷贝一些数据，粘贴到另一个电子表格中。或者可能需要从几千行中挑选几行，根据某种条件稍作修改。或者需要查看几百份部门预算电子表格，寻找其中的赤字。正是这种无聊无脑的电子表格任务，可以通过 Python 来完成。

首先，让我们来看一些基本定义。一个 Excel 电子表格文档称为一个工作簿。一个工作簿保存在扩展名为.xlsx 的文件中。每个工作簿可以包含多个表（也称为工作表）。Python 编程快速上手——让繁琐工作自动化用户当前查看的表（或关闭 Excel 前最后查看的表），称为活动表。每个表都有一些列（地址是从 A 开始的字母）和一些行（地址是从 1 开始的数字）。在特定行和列的方格称为单元格。每个单元格都包含一个数字或文本值。单元格形成的网格和数据构成了表。

与Excel文件的基本定义类似，openpyxl中也包含了与之对应的3个对象：

Workbook: 工作簿，一个Excel文件即一个Workbook。
Worksheet: 工作表，一个Workbook可以包含多个Worksheet，不同的Worksheet有不同的名字。
Cell: 单元格，存储数据的对象。
1
2
3

1 工作簿（Workbook）

新建工作簿：

import openpyxl
wb = openpyxl.Workbook()
1
2

新建工作薄的可选关键字：

write_only:bool # 默认为False，仅支持写入Excel文件，无法读取
1

打开原有工作簿：

workbook_path = "./data.xlsx"
wb = openpyxl.load_workbook(workbook_path)
1
2

打开工作簿的可选关键字：

data_only:bool # 默认为False，将各个单元格的公式转为数据
read_only:bool # 默认为False，只读方式打开Excel文件，读取速度较快
1
2

保存工作簿：

wb.save(workbook_path) # 注意，会覆盖原有文件
1

关闭工作簿，关闭打开的工作薄，只对用read-only和write-only方式打开的工作薄有效：

wb.close()
1

2 工作表（Worksheet）

打开当前工作表：

ws = wb.active # 打开当前工作表，即打开Excel文件时显示的那个工作表
1

打开指定工作表：

ws = wb["Sheet_name"] # 或wb.get_sheet_by_name("Sheet_name")
1

获取当前所有工作表名：

sheet_names = wb.sheetnames
1

修改工作表名：

ws.title = "new_name"
1

新建工作表：

ws = wb.create_sheet("new_sheet_name")
1

删除工作表：

wb.remove("sheet_name")
1

复制工作表，该函数不能在工作簿之间复制工作表，只能在其所属的工作簿中进行复制：

wb.copy_worksheet(from_worksheet)
1

3 单元格（Cell）

3.1 行和列

获取行列数：

max_row = ws.max_row # 获取行数，即有数据的所有行中最大的那一行
max_col = ws.max_column # 获取列数，即有数据的所有列中最大的那一列
min_row = ws.min_row
min_col = ws.min_col
1
2
3
4

获取指定行和列：

row = ws[2] # 获取第二行的所有单元格，从第ws.min_col列到第ws.max_col列
col = ws['C'] # 获取第三列的所有单元格，从第ws.min_row行到第ws.max_col行
1
2

遍历所有行和列：

rows = ws.rows # 返回所有行的迭代器
cols = ws.columns # 返回所有列的迭代器
# 逐行逐列遍历所有单元格
for row in rows:
    for cell in row:
        print(f"{cell.coordinate} {cell.value}", end=' ')
    print('\n')
# 逐列逐行遍历所有单元格
for coll in cols:
    for cell in col:
        print(f"{cell.coordinate} {cell.value}", end=' ')
    print('\n')
1
2
3
4
5
6
7
8
9
10
11
12

列字母和数字之间的转换：

from openpyxl.utils import get_column_letter, column_index_from_string
get_column_letter(27) # "AA"
column_index_from_string("AA") # 27
1
2
3

插入行和列：

ws.insert_rows(idx, amount=1) # 在第idx行前插入amount行
ws.insert_cols(idx, amount=1) # 在第idx列前插入amount列
1
2

删除行和列：

ws.delete_rows(idx, amount=1) # 从第idx行开始删除amount行
ws.delete_cols(idx, amount=1) # 从第idx列开始删除amount列
1
2

3.2 选中单元格

单个单元格：

cell = ws["C2"] # 或cell = ws.cell(2, 3)
cell.row # 查看单元格所在行
cell.column # 查看单元格所在列，返回int
cell.value # 查看单元格值
1
2
3
4

指定范围的单元格：

# 以下三种方式遍历的单元格范围相同
cells = ws["A1:D3"]
cells = ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=4)
cells = ws.iter_cols(min_col=1, max_col=4, min_row=1, max_row=3)
1
2
3
4

3.3 单元格操作

向单个单元格填充数据：

ws["A1"] = 1 # A1单元格数据设为1
ws.cell(2, 1) = 2 # A2单元格数据设为2
cell = ws["A3"]
cell = "=SUM(A1:A2)" # A3单元格插入公式计算A1、A2单元格的和
cell.value # 返回"=SUM(A1:A2)"

# 以data_only=True的方式打开该Excel文件
cell.value # 返回3
1
2
3
4
5
6
7
8

写入一行或多行数据：

data = ["Tom", 18, "北京"]
ws.append(data) # 会接在已有数据后追加写入

datas = [["Tom", 18, "北京"],
        ["Jack", 20, "上海"]]

for data in datas:
    ws.append(data)
1
2
3
4
5
6
7
8

移动范围数据：

# "B3:D5"区域的单元格上移两行，左移一列
ws.move_range("B3:D5", rows=-2, cols=-1)
"""
Signature: ws.move_range(cell_range, rows=0, cols=0, translate=False)
Docstring:
Move a cell range by the number of rows and/or columns:
down if rows > 0 and up if rows < 0
right if cols > 0 and left if cols < 0
Existing cells will be overwritten.
Formulae and references will not be updated."""
1
2
3
4
5
6
7
8
9
10

合并和拆分单元格：

ws.merge_cells("A1:B2")
ws.unmerge_cells("A1:B2")
1
2

冻结窗格：

ws.freeze_panes = "A2" # 冻结第一行
1

4 Excel样式调整

4.1 字体样式

Font(name:str, size:int, bold:bool, italic:bool, color:str)

name: 字体名
size: 字体大小
bold: 是否加粗
italic: 是否斜体
color: 字体颜色

from openpyxl.styles import Font

font = Font(name='Times New Roman', size=10, 
             bold=False, italic=True, color="000000")
ws["A1"].font = font
1
2
3
4
5

4.2 设置对齐样式

Alignment(horizontal:str, vertical:str, text_ritation:int, wrap_text:bool)

水平对齐:distributed, justify, center, left, fill, centerContinuous, right, general
垂直对齐:bottom, distributed, justify, center, top

from openpyxl.styles import Alignment

alignment = Alignment(horizontal="center", vertical="bottom",
                     text_rotation=30, wrap_text=True)
ws["A1"].alignment = alignment
1
2
3
4
5

4.3 设置边框

边框样式设置：Side(style=None, color=None, border_style=None)

可选边框样式：double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick

边框位置设置：Border(left=None, right=None, top=None, bottom=None, diagonal=None, diagonal_direction=None, vertical=None, horizontal=None, diagonalUp=False, diagonalDown=False, outline=True, start=None, end=None,)

from openpyxl.styles import Side, Border

side = Side(style="thin", color="000000")
border = Border(left=side, right=side, top=side, bottom=side)
ws["A1"].border = border
1
2
3
4
5

4.4 设置单元格填充样式

PatternFill(patternType=None, fgColor='000000', bgColor='000000', fill_type=None, start_color=None, end_color=None)

其中，fgColor或start_color表示前景色，或起始颜色；bgColor或end_color表示背景色，或结束颜色；fill_type为填充样式，一般有如下几种：

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

from openpyxl.styles import PatternFill, GradientFill

pattern_fill = PatternFill(fill_type="solid", fgcolor="000000")
ws["A1"].fill = pattern_fill
1
2
3
4

4.5 设置行高和列宽

ws.row_dimensions[1].height = 50 
ws.column_dimensions['C'].width = 20 
1
2

学习更多Python & GIS的相关知识，请移步公众号GeodataAnalysis：