Python-Excel-OpenPyXl

openpyxl，为python中常用的Excel模块之一。本思维导图介绍了该模块的安装，以及从工作簿-工作表-单元格的顺序，详细讲解各个对象相关的操作方法，并再对单元格样式的设置进一步的深入。 希望能对你学习Python，并提升Excel的技能有所帮助！

Python - Excel应用模块 - OpenPyXl

OpenPyXl模块简介：模块安装、Excel相关、模块导入

（1） openyxl安装

pip install openpyxl

（2） openpyxl与Excel应用

1. 支持文件类型

支持文件类型

.xlsx \ .xlsm \ .xlsx \ .xltm

不支持文件类型

.xls

2. 是否依赖Excel

不依赖Excel

计算机没安装Excel，也不影响openpyxl模块的使用

（3） 模块导入方式

from openpyxl import Workbook,load_workbook

Workbook

创建一个新的工作薄

load_workbook

打开已有的工作簿

from openpyxl.styles import *

用于单元格的格式调整

Workbook对象：创建/打开/修改/保存/关闭，呈现了openpyxl的一个完整操作

1. 创建一个新的工作簿

Workbook方法

wb = Workbook()

1. 创建了一个新的工作簿（Workbook对象）

2. 含有一个名为“Sheet”的工作表

2. 打开已存在的工作簿

load_workbook方法

wb = load_workbook(

filename,

文件名（含文件路径），string型

read_only=False,

只读，Bool型

keep_vba=False,

保留vba代码（用于带VBA宏的文件），Bool型

data_only=False,

用于带公式的单元格，只显示最近计算结果（而非公式），Bool型

keep_links=True,

是否保留外部链接，Bool型

)

返回一个Workbook对象（赋值给变量wb）

3. 修改工作簿

通过Workbook对象，获得Worksheet对象

通过Worksheet对象，获得单元格对象

对单元格进行修改（字体，值，颜色...）

4. 保存工作簿

save方法

1. 保存为普通文件（.xlsx）

wb.save(filename)

filename，文件名（可含路径）

2. 保存为模板（.xltx）

wb.template=True #将template属性设置为True wb.save('temp.xltx')

5. 关闭工作簿

close方法

wb.close()

Worksheet对象：创建/删除/引用/复制工作表，及工作表相关的行列操作

1. 创建工作表

workbook对象的create_sheet方法

ws=wb.create_sheet(

title=None，

工作表的名称，string

index=None

新工作表插入的位置，int

从左 → 右，0为基数

从右 → 左，-1为基数

)

返回一个工作表对象，并自动成为当前活动工作表

2. 删除工作表

（1） workbook对象的remove方法

wb.remove(worksheet)

该参数（worksheet）为一个worksheet对象

（2） del命令删除工作表

del wb[ws.title]

该参数（ws.title）为工作表的名称，string型

3. 引用工作表

（1） 获取活动工作表

workbook对象的active属性

ws=wb.active

（2） 获取worksheets集合

通过workbook对象的worksheets属性

sheets=wb.worksheets

返回的是一个worksheet对象的集合

（3） 获取worksheet的表名

1||| 获取单个worksheet对象

通过worksheet对象的title属性

ws.title

获取sheet标题

ws.title=title

修改sheet标题

2||| 获取整个工作簿，所有worksheet的表名

通过workbook对象的sheetnames属性

返回的是以工作表名构成的列表

（4） 引用工作表

1||| 通过worksheets集合的索引号

ws = sheets[0]

sheets=wb.worksheets

2||| 通过工作表名称

ws=wb['Sheet']

4. 复制工作表

workbook对象的copy_worksheet方法

copy_sheet1 = wb.copy_worksheet(

from_worksheet

被复制的工作表对象

)

返回新复制的工作表对象

1||| 工作表名，在源工作表名称后面添加“Copy”

2||| 多次复制同1个工作表，“Copy”后会添加数字

3||| 新复制的工作表默认放到最后

4||| 只能在工作簿内进行复制，不能跨工作簿复制

5. 移动工作表

workbook对象的move_sheet方法

wb.move_sheet(

sheet,

要移动的工作表对象或表名

offset=0

移动的位置

offset>0，表示工作表向右侧移动指定个数的位置

offset<0，表示工作表向左侧移动指定个数的位置

)

移动sheet工作表

6. 工作表的行/列操作

（1） 新增一行

worksheet对象的append方法

ws.append(

iterable

iterable: list, range or generator, or dict containing values to append

1||| 当参数为list

list中的元素，将按序添加到该行的单元格。

2||| 当参数为dict

键

字母/数字，表示Excel之中对应的列

值

根据对应的列，在工作表末行添加对应的值

)

在工作表（ws）中的末尾新增一行

（2） 插入和删除行/列

1||| worksheet对象的insert_rows方法

ws.insert_rows(idx, amount=1)

idx

插入的位置，基数为1，int

amount

插入的行数，默认1行，int

2||| worksheet对象的insert_cols方法

ws.insert_cols(idx, amount=1)

idx

插入的位置，基数为1，int

amount

插入的列数，默认1行，int

3||| worksheet对象的delete_rows方删除行

ws.delete_rows(idx, amount=1)

idx

删除的位置，基数为1，int

amount

删除的行数，默认1行，int

4||| worksheet对象的delete_cols方删除行

ws.delete_cols(idx, amount=1)

idx

删除的位置，基数为1，int

amount

删除的行数，默认1行，int

（3） 引用及遍历行/列

1||| 通过[]索引方式引用，通过for循环遍历

a. 行

a-1 引用行（用行号）

单行

row=ws[10]

返回的是元组，由第10行的Cell对象构成

连续多行

rows=ws[5:10]

返回的是(二维)元组，先由行组成元组，行由单元格（Cell对象）组成元组

a-2 遍历行

单行

for cell in ws[10]:

遍历第10行，获取Cell对象

连续多行

for row in ws[5:10]

遍历第5~10行，row返回的是行（由Cell对象组成的元组）

b. 列

b-1 引用列（用字母）

单列

colC=ws['C']

返回的是元组，由第3(C)列的Cell对象构成

连续多列

cols1=ws["C:D"]

返回的是(二维)元组，先由列组成元组，列由单元格（Cell对象）组成元组

b-2 遍历列

单列

for cell in ws["A"]：

遍历第1列，cell返回的为Cell对象

连续多列

for column in ws["A:C"]

遍历第A~C列，column返回的是列（由Cell对象构成的元组）

c. 区域

c-1 引用区域（用左上、右下单元格坐标）

ws["A1:C3"]

返回的是一个(二维)元组，区域由行组成的元组，行由单元格组成的元组

c-2 遍历区域（用左上、右下单元格坐标）

for row in ws["A1:C3"]:

遍历A1:C3区域，row返回的是区域内各行Cell对象构成的元组

2||| 通过worksheet对象的属性，获取工作表(数据区域)的最小(最大)行(列)号

1. ws.min_row

返回该最小区域的最小行号

2. ws.max_row

返回该最小区域的最大行号

3. ws.min_column

返回该最小区域的最小列号

4. ws.max_column

返回该最小区域的最大列号

3||| worksheet对象的iter_rows方法

ws.iter_rows(min_row=None,max_row=None,min_col=None,max_col=None,values_only=False)

返回的是生成器，可以遍历指定区域内的行

4||| worksheet对象的iter_cols方法

ws.iter_cols(min_col=None,max_col=None,min_row=None,max_row=None,values_only=False)

返回的是生成器，可以遍历指定区域内的列

5||| 遍历所有行或列

worksheet对象的rows属性

ws.rows

生成器

for row in ws.rows:

row，返回的是以行上的Cell对象组成的元组

worksheet对象的columns属性

ws.columns

生成器

for col in ws.columns:

col，返回的是以列上的Cell对象组成的元组

worksheet对象的values属性

ws.values

生成器

for row in ws.values

row，返回的是以行上的单元格值组成的元组

（4） 改变行高和列宽

1||| 行维

worksheet对象的row_dimensions属性

ws.row_dimensions[2].hight=20

2||| 列维

worksheet对象的column_dimensions属性

ws.column_dimensions["C"].width=35

Cell对象：单元格/单元格区域的引用/属性查看/单元格样式设置

1. 单元格的引用和属性查看

（1） 单元格的引用

I. 通过[]索引方式引用

ws["A1"]

返回一个Cell对象

II. 通过worksheet对象的cell方法

ws.cell(row, column, value=None)

返回一个Cell对象

（2） 单元格的属性

查看(获取)单元格信息

1||| cl.row

获取单元格的行号

2||| cl.column(cl.col_idx)/cl.column_letter

获取单元格的列号/列名

3||| cl.value

获取单元格的值

4||| cl.coordinate

获取单元格的坐标

5||| cl.data_type

单元格中值的数据类型

默认：n 数值；s 字符串；d 日期时间

cell.data_type

6||| cl.hyperlink.ref="https:\\www.baidu.com"

单元格的链接

7||| h=cl.offset(row=1,column=2)

偏移一定的位置（下1行，右2列）后的单元格

8||| cell.encoding

单元格编码格式，默认： utf-8

9||| cell.has_style

是否有样式，默认样式是 Normal,如果是默认样式，返回False

10||| cell.style_id

单元格id

11||| cell.style

单元格样式

2. 引用的单元格区域

I. 通过worksheet对象索引给定区域左上角和右下角单元格的坐标（如上介绍）

II. CellRange对象表示单元格区域

from openpyxl.worksheet import cell_range as cr

cr.CellRange(

range_string=None,

通过区域左上/右下单元格的坐标选择单元格区域

min_col=None,

min_row=None,

max_col=None,

max_row=None,

通过最大最小行列来选择单元格区域

title=None,

)

返回的是一个由单元格坐标组成的列表

CellRange对象（cr0）的主要属性和方法

1||| cr0.bottom/top/left/right

区域底部/顶部/左侧/右侧，一行/一列单元格的做标

2||| cr0.min_row/min_col/max_row/max_col

区域最小行号/列号，最大行号/列号

3||| cr0.size

区域大小

4||| 区域左上角和右下角单元格的坐标

cr0.bounds

(3,2,6,5)

cr0.coord

'C2:F5'

5||| for cell in cr0.rows/cols/cells:

遍历单元格区域的行/列/单元格的坐标

3. 单元格区域移动/合并

（1） 使用worksheet对象的move_range方法，移动指定区域

ws.move_range(

cell_range,

给定区域左上角和右下角单元格的坐标

rows=0,

>0，向下移动；<0，向上移动

cols=0,

>0，向右移动；<0，向左移动

translate=False

）

将单元格区域上下左右进行移动

（2） 使用worksheet对象的merge_cells方法，合并单元格

ws.merge_cells(

range_string=None,

通过区域左上/右下单元格的坐标选择单元格区域

start_row=None,

start_column=None,

end_row=None,

end_column=None,

通过给定行列来选择单元格区域

)

将给定的单元格区域进行合并

（3） 使用unmerge_cells方法解除合并

ws.unmerge_cells(

range_string=None,

通过区域左上/右下单元格的坐标选择单元格区域

start_row=None,

start_column=None,

end_row=None,

end_column=None,

通过给定行列来选择单元格区域

)

将给定的合并单元格区域，解除合并

4. 设置单元格样式

numbers，数字 Font，字体 Alignment，对齐 PatternFill，填充 Border，边框 Protection，保护 使用之前，需从openpyxl.styles中导入 from openpyxl.styles import numbers,Font,Alignment from openpyxl.styles import PatterFill,Border,Side,Protection

（1） 设置字体

font = Font(name="Arial",size=12,bold=True,italic=True,underline="single",strike=False,color="FF0000") name，字体名称 size，字体大小 color，字体颜色 bold，字体是否加粗 italic，字体是否倾斜 underline，下划线设置，值为"none"、"single"、"double" strike，字体是否添加删除线 vertalign，上标、下标设置，值为"superscript"、"subscript"或"baseline"

i. 导入模块

from openpyxl.styles import Font

ii. 创建Font对象

Font( name=None, sz=None, b=None, i=None, charset=None, u=None, strike=None, color=None, scheme=None, family=None, size=None, bold=None, italic=None, strikethrough=None, underline=None, vertAlign=None, outline=None, shadow=None, condense=None, extend=None, )

font = Font(

name=None,

字体名称

strike=None,

字体是否添加删除线

color=None,

字体颜色

size=None,

字体大小

bold=None,

字体是否加粗

italic=None,

是否斜体

strikethrough=None,

字体是否添加删除线

underline=None,

下划线设置

none,single,double

vertAlign=None,

上标、下标设置

superscript, subscript, baseline

)

返回一个Font对象，赋值给变量font

iii. 将Font对象赋给Cell对象cl的font属性

cl=ws['C3'] cl.font=font

（2） 设置颜色

i. 导入模块

from openpyxl.styles.colors import Color

ii. 创建Color对象（颜色设置）的3种方式

1||| RGB颜色

使用rgb参数，设置RGB颜色

c=Color(rgb="00FF00")

RGB颜色值与十六进制颜色码转换工具

常用十六进制颜色对照表代码查询

2||| 索引着色

使用indexed参数，指定索引号，设置对应的颜色

c=Color(indexed=32)

3||| 主题颜色

使用theme参数

c=Color(theme=6,tint=0.5)

iii. 将Color对象赋值给Font对象的color参数

font = Font(color=c)

赋值color对象

font = Font(color="FF0000")

也可直接赋值十六进制的RGB值

（3） 样式——设置背景填充

I. 渐变色填充

i. 导入模块

from openpyxl.styles.fills import GradientFill

ii. 创建GradientFill对象

gfill = GradientFill(

type='linear',

type="linear"

线性渐变

线性渐变：颜色从一侧向另一侧渐变，默认：从左向右 degree，改变角度 stop，设置颜色列表，各颜色等间隔排列，颜色间的间隔通过线性插值得到。

type="path"

路径渐变

路径渐变：颜色从单元格的四条边向内线性渐变。 4个方向填充的宽度，left,right,bottom,top，取值于0~1之间，表示宽度或高度的百分比。

degree=0,

改变角度

left=0,

right=0,

top=0,

bottom=0,

路径渐变，参数在0~1之间取值

stop=(),

设置一个颜色列表

)

返回一个GradientFill对象，赋值给变量gfill

iii. 将GradientFill对象赋给Cell对象(cl)l的fill属性

cl=ws['C3'] cl.fill=gfill

II. 图案填充

i. 导入模块

from openpyxl.styles.fills import PatternFill

ii. 创建PatternFill对象

pfill = PatternFill(

patternType=None,

fill_type=None,

图案填充类型

其值为限定中的一个

darkDown,gray0625,mediumGray,darkHorizontal,lightVertical,darkGrid,lightGray,darkTrellis,darkVertical,ligthGrid,solid,ligthDown,lightUp,darkUp,darkGray,lightTrellis,lightHorizontal,gray125,None

fgColor=<openpyxl.styles.colors.Color object>

Parameters:rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb',

start_color=None,

fgColor、start_color

前景色

fgColor的值必须为Color对象

bgColor=<openpyxl.styles.colors.Color object>

Parameters:rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb',

bgColor、end_color

后景色

bgColor的值必须为Color对象

)

返回一个PatternFill对象，赋值给变量pfill

iii. 将PatternFill对象赋给Cell对象(cl)l的fill属性

cl=ws['C3'] cl.fill=pfill

（4） 设置边框

创建Border对象

i. 导入模块

from openpyxl.styles.borders import Border,Side

ii. 创建Border对象

border = Border(

left=None,

right=None,

top=None,

bottom=None,

diagonal=None,

定义左、右、上、下和对角边框，为Side对象

Side(

style=None,

边线的风格

hair,dashed,mediumDashDot,mediumDashDotDot,slantDashDot,double,thick,mediumDashed,thin,medium,dashDotDot,dashDot,dotted

color=None,

颜色

border_style=None

styled的别名

)

返回一个side对象

diagonalUp=False,

diagonalDown=False,

定义对角线的方向，Bool型

)

返回一个Border对象，将其赋值给变量border

iii. 将Border对象赋给Cell对象(cl)l的fill属性

cl=ws['C3'] cl.border=border

（5） 设置数字格式

i. 先从模块中导入类

from openpyxl.styles import numbers

ii. 使用numbers类和cell对象的number_format属性设置数字格式

1||| 使用OpenPyXl内置的格式常数

ws["D6"].number_format =numbers.FORMAT_GENERAL

2||| 使用表示数字格式的字符串

ws["D6"].number_format='yy-mm-dd'

（6） 设置对齐方式

i. 先从模块中导入类

from openpyxl.styles.alignment import Alignment

ii. 创建Alignmnet对象

align = Alignment(

horizontal=None,

水平对齐

vertical=None,

垂直对齐

textRotation=0,

文字旋转角度，以度为单位

wrapText=None,

是否允许换行

布尔型

shrinkToFit=None,

收缩使单元格装得下

indent=0,

缩格

浮点型

relativeIndent=0,

相对缩格

浮点型

justifyLastLine=None,

调整最后一行

布尔型

readingOrder=0,

阅读顺序

浮点型

text_rotation=None,

textRotation的别名

用于属性名称不合法、与Python保留字混淆或使名称更具描述性时

wrap_text=None,

wrapText的别名

shrink_to_fit=None,

shrinkToFit的别名

mergeCell=None,

合并单元格

)

返回一个Alignment对象，并赋值给变量align

iii. 将Alignment对象赋给Cell对象(cl)l的alignment属性

cl=ws['C3'] cr.alignment=align

（7） 设置保护

i. 先从模块中导入类

from openpyxl.styles import Protection

ii. 创建Protection对象

ptc = Protection(

locked=True,

锁定

hidden=False

隐藏

)

返回一个Protection对象，并赋值给变量ptc

iii. 将Protection对象赋给Cell对象(cl)l的protection属性

cl=ws['C3'] cr.protection=ptc

（8） 插入图片

i. 先从模块中导入类

from openpyxl.drawing.image import Image

ii. 创建Image对象

img = Image(img)

参数img，为图片的路径

使用Image对象的width、hight属性

修改图片的宽、高

iii. 使用worksheet对象的add_image方法

sht.add_image(img,"A1") # 将Image对象(img)添加到A1单元格中

（9） 插入公式

在单元格中输入公式的字符串，如"=SUM(C1:C2)"

-----------------------------------------------End------------------------------------------------