素材、笔记文档下载

1 安装openpyxl

Windows用户打开命令行输入：pip install openpyxl

Mac用户打开终端/Terminal输入：pip3 install openpyxl

2 创建新的表格

2.1 创建一个工作簿

from openpyxl import Workbook # 引入openpyxl模块，可以创建xls或者xlsx文件

from openpyxl import Workbookworkbook = Workbook()sheet = workbook.activesheet.title = '表格1'workbook.save(filename='写入表格.xlsx')

2.2 对表格对象的一些操作

2.2.1 新建工作表

create_sheet()方法返回一个新的表格对象

index和title参数，指定新工作表的索引及名称

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.active# 使用工作簿对象创建一个新的表格，表格名称为表格2workbook.create_sheet(index=None,title='表格2')print(workbook.sheetnames)

index：整数类型，设置新工作表索引，默认为None即放在最后，如果设置为0，则表格放置在最前。

title：字符串类型，设置新工作表名称，如果新工作表名称已存在，新工作表名称会自动变为title1。

2.2.2 删除工作表

remove()方法中接收一个表格对象而不是表格名称的字符串。需要具象化表名。

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook['表格2']workbook.remove(sheet)# workbook.remove(workbook['表格2'])print(workbook.sheetnames)workbook.save(filename='写入表格.xlsx')

2.2.3 复制工作表

workbook.copy_worksheet(sheet)

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activesheet = workbook['表格1']workbook.copy_worksheet(sheet)print(workbook.sheetnames)workbook.save(filename='写入表格.xlsx')

2.2.4 修改工作表名

sheet.title = ‘工作表名’

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activesheet = workbook['表格1 Copy']sheet.title = '表格3'print(workbook.sheetnames)workbook.save(filename='写入表格.xlsx')

3 读取表格内容

3.1 获取工作簿对象

openpyxl.load_workbook()方法可以接收多个参数进行读取

from openpyxl import load_workbookwb = openpyxl.load_workbook(filename='写入表格.xlsx', read_only=False, keep_vba=False, data_only=False, keep_links=True)print(wb.sheetnames)

filename：字符串类型，读取Excel文件的文件路径，可使用相对路径或是绝对路径。

read_only：布尔类型，选择只读模式或是读写模式，若想写入公式及重新获取结果，需要用将这个模式关闭。默认为False。

keep_vba：布尔类型，保留vba内容(这并不意味着可以使用它)，默认为False。

data_only：布尔类型，如果设置为True则包含公式的单元格，显示最近计算结果或是None，如果设置为False 则单元格显示公式，默认为False。

keep_links：布尔类型，是否保留指向外部工作簿的链接。默认为True。

3.2 获取工作表对象

3.2.1 获取工作薄所有工作表名称

load_workbook(filename=“表格文件路径”) #若是绝对路径直接写文件名。

sheetnames属性可以取得工作簿中所有表名，返回为一个列表。

from openpyxl import load_workbookfileName = "写入表格.xlsx"wb = load_workbook(filename = fileName)print(wb.sheetnames)

3.2.2 选中需要操作的工作表

sheet = wb.active # 获取工作簿中的当前活动表

sheet = wb[‘工作表名称’] # 通过工作表名称选中的工作表

sheet.title # 获取活动表的表名称

# 获取上次关闭表格前激活的表格名称from openpyxl import load_workbookfileName = "写入表格.xlsx"wb = load_workbook(filename = fileName) sheet = wb.activeprint(sheet.title)

# 获取特定表的表名称from openpyxl import load_workbookfileName = "写入表格.xlsx"wb = load_workbook(filename = fileName)sheet = wb['Sheet2'] print(sheet.title)

3.2.3 获取表格范围

获取表格：workbook[sheet名称]

获取表格的尺寸大小：sheet.dimensions(表格存在数据的大小)

单独获取最大行：sheet.max_row

单独获取最大列：sheet.max_column

from openpyxl import load_workbook workbook = load_workbook(filename="46sheet = workbook['Booklist 12-26'] print("表格范围是：",sheet.dimensions)print("最大行数是：", sheet.max_row) print("最大列数是：", sheet.max_column)

输出结果：

表格范围是： A1:E4693

最大行数是： 4693

最大列数是： 5

3.3 获取单元格对象

3.3.1 获取单元格的某些属性

.row 行数

.column 列数

.coordinate 坐标

from openpyxl import load_workbook workbook = load_workbook(filename="4600本书单名.xlsx") sheet = workbook.active cell = sheet['A5'] print("单元格的行号：",cell.row) print("单元格的列号：",cell.column) print("单元格的坐标：",cell.coordinate)

输出结果：

单元格的行号： 5

单元格的列号： A

单元格的坐标： A5

3.3.2 获取表格内的数据

3.3.2.1 获取表格内某一范围的数据

# 取某一单元格cell = sheet['A1']# 取一列单元格cells = sheet['A']# 取一行单元格cells = sheet[1]# 取表格当中的所有列，一列为一组cells = sheet.columns# 取表格当中的所有行，一行为一组cells = sheet.rows# 已知单元格列范围坐标时cells = sheet['A:C']cells = sheet['1:3']# 已知单元格矩形范围坐标时cells = sheet['A1:C5']# 已知单元格起始与终结的行列数时cells = sheet.iter_cols(min_row=1, max_row=5, min_col=1, max_col=3)cells = sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3)

workbook.active 打开活跃的/唯一的表格

cell.value 格子的数据

from openpyxl import load_workbookworkbook = load_workbook(filename="4600本书单名.xlsx")sheet = workbook.activecell = sheet['A1']print(cell.value)

sheet.iter_rows(min_row=最低行数,max_row=最高行数,min_col=最低列数,max_col=最高列数): # 行

sheet.iter_cols(min_row=最低行数,max_row=最高行数,min_col=最低列数,max_col=最高列数): # 列

from openpyxl import load_workbookworkbook = load_workbook(filename="4600本书单名.xlsx")sheet = workbook.activefor row in sheet.iter_rows(min_row=2,max_row=3,min_col=1,max_col=2):print(row)# for row in sheet.iter_cols(min_row=2,max_row=3,min_col=1,max_col=2):#print(row)

3.3.2.2 迭代所有的行/列

.rows

.columns

# 遍历所有的行from openpyxl import load_workbookworkbook = load_workbook(filename="4600本书单名.xlsx")sheet = workbook.active获取所有的行for row in sheet.rows:print(row)

# 遍历所有的列from openpyxl import load_workbookworkbook = load_workbook(filename="4600本书单名.xlsx")sheet = workbook.activefor col in sheet.columns:print(col)

输出结果：

(<Cell ‘Booklist 12-26’.A1>, <Cell ‘Booklist 12-26’.A2>, <Cell ‘Booklist 12-26’.A3>, <Cell ‘Booklist 12-26’.A4>…

结果一样，输出顺序不一样。

4 对单元格对象的一些操作

4.1 插入行/列

.insert_cols(idx=数字编号，amount=要插入的列数)

.insert_rows(idx=数字编号，amount=要插入的行数)

在idx列左边插入一列

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activesheet.insert_cols(idx=2)# sheet.insert_rows(idx=2) # 插入一行workbook.save(filename='写入表格.xlsx')

在idx列左边插入多列

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activesheet.insert_cols(idx=2,amount=3)# sheet.insert_rows(idx=2,amount=3) # 插入多行workbook.save(filename='写入表格.xlsx')

4.2 删除行/列

.delete_cols(idx=数字编号，amount=要删除的列数)

.delete_rows(idx=数字编号，amount=要删除的行数)

在idx列这一行/列开始，包括idx这一行/列

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activesheet.delete_cols(idx=2)# sheet.delete_rows(idx=2) # 删除一行workbook.save(filename='写入表格.xlsx')

4.3 插入一行数据

append()方法接收一个可变参数，包括但不限于列表、范围或生成器或字典

如果传入一个列表：从第一列开始顺序添加所有值，列表元素对应每一行

如果传入一个字典：值被分配给键(数字或字母)指示的列

注意：append一次只能添加一行的数据，如果想要多行添加，需要与循环或其他方法相结合实现。

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activedata = [[‘张三',1],[‘李四',2],[‘王五',3],[‘赵六',4],]for row in data:sheet.append(row)workbook.save(filename='写入表格.xlsx')

4.4 修改单元格数据及定义公式

4.4.1 修改单元格数据

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activesheet['A1'] = 'test'workbook.save(filename='写入表格.xlsx')

输出结果：

表格[A1]：test

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activecell = sheet['A1']cell.value = 'test'workbook.save(filename='写入表格.xlsx')

输出结果：

表格[A1]：test

sheet.cell(row=行数,column=列数)

cell.value = ‘内容’ # 为单元格赋值

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activecell = sheet.cell(row=1,column=1)cell.value = 'test'

输出结果：

表格[A1]：test

4.4.2 插入公式

直接赋值公式字符串

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activesheet['B6'] = '=SUM(B2:B5)'workbook.save(filename='写入表格.xlsx')

查看openpyxl支持的公式

from openpyxl.utils import formulasprint (formulas)

<module ‘openpyxl.utils.formulas’ from ‘D:\Software\Python\lib\site-packages\openpyxl\utils\formulas.py’>

根据这个路径打开相应的formulas.py，显示如下：

FORMULAE = (“CUBEKPIMEMBER”, “CUBEMEMBER”, “CUBEMEMBERPROPERTY”, “CUBERANKEDMEMBER”, “CUBESET”,

…

4.5 移动单元格

.move_range(‘C1:D4’,rows=2,cols=-2)

正整数为向下或者向右，负整数为向左或者向上，类似于剪贴CTRL+X功能。

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")sheet = workbook.activesheet.move_range("C1:D4", rows=2, cols=-2)workbook.save(filename='写入表格.xlsx')

4.6 查看Excel表格内容

4.6.1 设置显示或隐藏状态

设置表格为隐藏状态：sheet.sheet_state = 'hidden'

设置表格为显示状态：sheet.sheet_state = 'visible'

from openpyxl import load_workbookworkbook = load_workbook(filename="写入表格.xlsx")print(workbook.sheetnames)m = input("是否要隐藏工作表Y/N：")if m == "Y" or "y":sheet = workbook.activesheet.sheet_state = 'hidden'else:sheet = workbook.activesheet.sheet_state = 'visible'workbook.save(filename='写入表格.xlsx')

4.6.2 冻结窗格

sheet.freeze_panes = “单元格”

from openpyxl import load_workbookworkbook = load_workbook(filename="4600本书单名.xlsx")sheet = workbook.activesheet.freeze_panes = "B2"workbook.save(filename='4600本书单名.xlsx')

4.6.3 添加筛选

auto_filter.ref

from openpyxl import load_workbookworkbook = load_workbook(filename="4600本书单名.xlsx")sheet = workbook['Booklist 12-26']sheet.auto_filter.ref = sheet.dimensions # 对整张表进行筛选workbook.save(filename='4600本书单名.xlsx')

5 批量调整字体、样式

5.1 获取字体样式

cell.font.属性

from openpyxl.styles import Fontfrom openpyxl import load_workbookworkbook = load_workbook(filename='写入表格.xlsx')sheet = workbook.activecell = sheet['A1']font = cell.fontprint('font.name是:',font.name,'font.size是:',font.size,'是否粗体:',font.bold,'是否斜体:',font.italic)

5.2 修改字体样式

font = Font(name=‘字体名称’,size =字体大小,bold = 是否粗体,italic = 是否斜体,color =‘字体颜色’)

from openpyxl.styles import Fontfrom openpyxl import load_workbookworkbook = load_workbook(filename='写入表格.xlsx')sheet = workbook.activecell = sheet['A1']font = Font(name='宋体',size =12,bold = True,italic = True,color ='FF0000')cell.font =fontworkbook.save(filename='写入表格.xlsx')

5.3 设置单元格格式分类

可以通过设置单元格的风格来设置单元格格式分类

[openpyxl所支持的excel的单元格格式][https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html?highlight=openpyxl.styles.numbers]

from openpyxl.styles import Fontfrom openpyxl import load_workbookworkbook = load_workbook(filename='写入表格.xlsx')sheet = workbook.activecell = sheet['A1']# 设置单元格分类为百分比cell.style = '百分比'cell.style = '常规'# 常规单元格cell.number_format = 'General'# 百分比单元格cell.number_format = '0.00%'# 科学计数法cell.number_format = '0.00E+00'workbook.save(filename='写入表格.xlsx')

5.4 设置对齐样式

Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)

from openpyxl.styles import Alignmentfrom openpyxl import load_workbookworkbook = load_workbook(filename='写入表格.xlsx')sheet = workbook.activecell = sheet['A1']alignment = Alignment(horizontal='center',vertical='center',text_rotation=45)cell.alignment =alignmentworkbook.save(filename='写入表格.xlsx')

horizontal：'distributed', 'justify','center','left';'fill', 'centerContinuous','right",'general'

vertical：'bottom', 'distributed','justify','center';'top'

wrap_text：布尔类型，设置是否自动换行

textRotation：整数类型，设置文本旋转角度，最大值180

5.5 设置边框样式

side = Side(style=边线样式,color=边线颜色)

border = Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)

from openpyxl.styles import Side, Borderfrom openpyxl import load_workbookworkbook = load_workbook(filename='写入表格.xlsx')sheet = workbook.activecell = sheet['A1']side = Side(style='thin',color='FF000000')border = Border(left=side,right=side,top=side,bottom=side)cell.border=borderworkbook.save(filename='写入表格.xlsx')

side.style：'double','mediumDashDotDot','slantDashDot','dashDotDot','dotted', 'hair', 'mediumDashed', 'dashed' 'dashDot' ,'thin', 'Dash Dot', 'medium', 'thick'

5.6 设置填充颜色

PatternFill(fill_type=填充样式,fgColor=填充颜色)

渐变颜色填充

GradientFill(stop=(渐变颜色1,渐变颜色2,渐变颜色3…))

from openpyxl.styles import PatternFill, GradientFillfrom openpyxl import load_workbookworkbook = load_workbook(filename='写入表格.xlsx')sheet = workbook.activecell_a1 = sheet['A1']pattern_fill = PatternFill(fill_type='solid',fgColor='00B0F0')cell_a1.fill = pattern_fillcell_b4 = sheet['B4']gradient_fill = GradientFill(stop=('FFFFFF', '99CCFF', '000000'))cell_b4.fill = gradient_fillworkbook.save(filename='写入表格.xlsx')

5.7 设置行高和列高

.row_dimensions[行编号].height = 行高

.column_dimensions[列编号].width = 列宽

from openpyxl.styles import PatternFill, GradientFillfrom openpyxl import load_workbookworkbook = load_workbook(filename='写入表格.xlsx')sheet = workbook.activesheet.row_dimensions[1].height = 50sheet.column_dimensions['B'].width = 20workbook.save(filename='写入表格.xlsx')

5.8 合并单元格

sheet.merge_cells(‘合并的范围’)

sheet.merge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)

from openpyxl.styles import PatternFill, GradientFillfrom openpyxl import load_workbookworkbook = load_workbook(filename='写入表格.xlsx')sheet = workbook.activesheet.merge_cells('C1:C2')sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=4)workbook.save(filename='写入表格.xlsx')

6 生成图表

6.1 插入图片

openpyxl.drawing.image

sheet.add_image

from openpyxl import load_workbookfrom openpyxl.drawing.image import Imageworkbook = load_workbook(filename='写入表格.xlsx')sheet = workbook.activelogo = Image("butterfly.png")logo.height = 100logo.width = 100sheet.add_image(logo, "D1")workbook.save(filename='写入表格.xlsx')

6.2 生成图表

6.2.1 插入柱状图

表数据【A1：C7】

BarChart() Reference()

from openpyxl import load_workbookfrom openpyxl.chart import BarChart, Referenceworkbook = load_workbook(filename='图表.xlsx')sheet = workbook.activechart = BarChart()data = Reference(worksheet=sheet, min_row=1, max_row=7, min_col=2, max_col=3)categories = Reference(sheet,min_col=1,min_row=2,max_row=7)chart.add_data(data, titles_from_data=True)chart.set_categories(categories)sheet.add_chart(chart, "E2")workbook.save(filename='图表.xlsx')

6.2.2 插入条形图

表数据【A19：M21】

LineChart()

from openpyxl import load_workbookfrom openpyxl.chart import LineChart, Referenceworkbook = load_workbook(filename='图表.xlsx')sheet = workbook.activechart = LineChart()# data = Reference(worksheet=sheet, min_row=1, max_row=7, min_col=2, max_col=3)# categories = Reference(sheet,min_col=1,min_row=2,max_row=7)data = Reference(worksheet=sheet, min_row=20, max_row=21, min_col=1, max_col=13)categories = Reference(sheet, min_col=2, min_row=19, max_col=13)chart.add_data(data, from_rows=True, titles_from_data=True)chart.set_categories(categories)sheet.add_chart(chart, "E2")workbook.save(filename='图表.xlsx')