碧海苍梧

V1

2023/01/22阅读:17主题:默认主题

使用 openpyxl 处理 Excel 电子表格

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

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

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

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

1 工作簿(Workbook)

新建工作簿:

import openpyxl
wb = openpyxl.Workbook()

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

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

打开原有工作簿:

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

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

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

保存工作簿:

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

关闭工作簿,关闭打开的工作薄,只对用read-onlywrite-only方式打开的工作薄有效:

wb.close()

2 工作表(Worksheet)

打开当前工作表:

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

打开指定工作表:

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

获取当前所有工作表名:

sheet_names = wb.sheetnames

修改工作表名:

ws.title = "new_name"

新建工作表:

ws = wb.create_sheet("new_sheet_name")

删除工作表:

wb.remove("sheet_name")

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

wb.copy_worksheet(from_worksheet)

3 单元格(Cell)

3.1 行和列

获取行列数:

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

获取指定行和列:

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

遍历所有行和列:

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')

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

from openpyxl.utils import get_column_letter, column_index_from_string
get_column_letter(27# "AA"
column_index_from_string("AA"# 27

插入行和列:

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

删除行和列:

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

3.2 选中单元格

单个单元格:

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

指定范围的单元格:

# 以下三种方式遍历的单元格范围相同
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)

3.3 单元格操作

向单个单元格填充数据:

ws["A1"] = 1 # A1单元格数据设为1
ws.cell(21) = 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

写入一行或多行数据:

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

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

for data in datas:
    ws.append(data)

移动范围数据:

# "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."""

合并和拆分单元格:

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

冻结窗格:

ws.freeze_panes = "A2" # 冻结第一行
freeze_panes的设置 冻结的行和列
ws.freeze_panes = 'A2' 行1
ws.freeze_panes = 'B1' 列A
ws.freeze_panes = 'C1' 列 A 和列 B
ws.freeze_panes = 'C2' 行 1 和列 A 和列 B
ws.freeze_panes = 'A1'ws.freeze_panes = None 没有冻结窗格

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

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

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

4.4 设置单元格填充样式

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

其中,fgColorstart_color表示前景色,或起始颜色;bgColorend_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

4.5 设置行高和列宽

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

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

分类:

后端

标签:

Python

作者介绍

碧海苍梧
V1