简介
openpyxl是一个用于读取和编写Excel xlsx/xlsm/xltx/xltm文件的Python库。openpyxl以Python语言和MIT许可证发布。
openpyxl可以处理Excel文件中的绝大多数内容，包括图表、图像和公式。它可以处理大量数据，支持Pandas和NumPy库导入和导出数据。Openpyxl还支持自定义矩阵和PivotTable，以及通过API自动化Excel应用程序。
目录
1. 创建和打开工作簿
1.1. 创建工作表
1.2. 保存Excel文件
1.3. 关闭Excel工作簿
1.4. 获取正在活动的表
1.5. 获取表名
1.6. 移除工作表
1.7. 复制工作表
1.8. 添加数据到末行
2. 写入和读取Excel
2.1. 设置表名
2.2. 写入数据
2.3. 插入空白行/列
2.4. 迭代读取指定行、列
2.5. 遍历所有数据行
2.6. 遍历所有数据列
2.7. 读取最大行号/列号
2.8. 判断空行、列
3.设置Excel格式
3.1. 56种颜色代码
3.2. 设置字体属性
3.3. 设置填充属性
3.4. 设置对齐属性
3.5.设置边框属性
3.6. 合并单元格
3.7. 设置组合样式
4. 其他方法
4.1. csv文件导入Excel
4.2. 画图
① 模块语法
② 折线图
③ 柱状图
1. 创建和打开工作簿
1.1. 创建工作表
创建一个新的工作表，并添加到工作簿中。可以通过index参数来指定插入工作表的位置，默认为在末尾添加。语法
workbook.create_sheet(title=None #可选参数，指定工作表的名称，默认为Sheet+数字(Sheet1)index=None #可选参数，指定工作表的插入位置，从0开始计数。默认将工作表添加到末尾位置。)
示例
from openpyxl import Workbook# 创建一个新的工作簿workbook = Workbook()# 创建一个 AAA 的工作簿，从第1个位置添加（索引从0开始）wb_AAA = workbook.create_sheet('AAA', 0)# 创建一个 BBB 的工作簿，默认添加到末尾wb_BBB = workbook.create_sheet('BBB')# 创建一个不指定名称的工作簿wb_sheet = workbook.create_sheet()# 查看所有的工作表名称for i in workbook.worksheets:print(i.title)# 将这些表保存到磁盘workbook.save('tmp.xlsx')
结果
AAASheetBBBSheet1
如果表名已存在，则会报错
1.2. 保存Excel文件
保存工作簿为一个Excel文件，需要指定文件的路径和文件名。语法
workbook.save('文件名')
举例
from openpyxl import Workbook# 创建一个新的工作簿workbook = Workbook()# 保存工作簿workbook.save('tmp.xlsx')
会默认创建一个sheet页
1.3. 关闭Excel工作簿
在处理完工作簿之后，应该使用close()方法将其关闭。这可以确保文件在使用后得以正常关闭，避免内存泄漏和其他问题。同时，关闭Excel文件还可以防止其他应用程序无法访问或编辑相同的文件。语法
workbook.close()
如果使用 with，则会自动关闭，无需手动操作
from openpyxl import Workbook# 使用with语句打开Excel文件，并创建一个新的工作簿with Workbook() as workbook:worksheet = workbook.active"""操作Excel文件"""# 自动关闭Excel文件
1.4. 获取正在活动的表
获取或设置当前活动的工作表，可以通过该属性来切换不同的工作表或获取当前活动的工作表。语法
# 设置一张活动的表Workbook().active = [表名]# 查看正在活动的表print(Workbook().active)#仅查看表名print(Workbook().active.title)
举例（如果没有活动的表，返回None。使用Workbook就会有一张默认的sheet表活动）
>>> from openpyxl import Workbook>>> print(Workbook().active)<Worksheet "Sheet">>>> print(Workbook().active.title)Sheet
设置一张活动的表
>>> from openpyxl import Workbook# 创建一个新的工作簿>>> workbook = Workbook()# 设置一张表为正在活动的表>>> wb_AAA = workbook.create_sheet('AAA')>>> workbook.active = wb_AAA# 查看活动的表>>> print(workbook.active.title)AAA
1.5. 获取表名
返回工作簿中所有工作表的名称列表。from openpyxl import load_workbook# 打开工作簿f = load_workbook('./tmp.xlsx')# 查看工作簿中所有工作表名print(f.get_sheet_names())
如果在高版本出现了这样的提示
那么按照提示要求，将get_sheet_names() 替换为sheetnames 即可
from openpyxl import load_workbook# 打开工作簿f = load_workbook('./tmp.xlsx')# 查看工作簿中所有工作表名print(f.sheetnames)
1.6. 移除工作表
从工作簿中移除指定的工作表或命名范围。语法
workbook.remove([表名])
删除 AAA 表
>>> from openpyxl import Workbook# 创建一个新的工作簿>>> workbook = Workbook()# 创建两个工作表>>> AAA = workbook.create_sheet('AAA')>>> BBB = workbook.create_sheet('BBB')# 查看所有表名>>> for i in workbook.worksheets:>>>print(i.title)SheetAAABBB# 删除工作簿中的第二个工作表>>> workbook.remove(AAA)# 再次查看所有表名>>> for i in workbook.worksheets:>>>print(i.title)SheetBBB
1.7. 复制工作表
将指定的工作表复制一份，并添加到工作簿中。可以使用这个方法来快速复制工作表，并进行一些定制化操作。直接拷贝工作表，插入的最后面。名称为：[旧名称] + copy
from openpyxl import load_workbook# 打开工作簿f = load_workbook('./tmp.xlsx')# 选择复制的工作表(AAA)f_copy = f['AAA']# 复制并插入表(新表为：[旧名]+copy)ws = f.copy_worksheet(f_copy)# 保存文件f.save('./tmp.xlsx')# 关闭文件f.close()
拷贝工作表后重命名
from openpyxl import load_workbook# 打开工作簿f = load_workbook('./tmp.xlsx')# 复制插入新的工作表，赋值给变量f_copy = f.copy_worksheet(f['AAA'])# 重命名工作表f_copy.title = 'new_copyAAA'# 保存文件f.save('./tmp.xlsx')# 关闭文件f.close()
1.8. 添加数据到末行
自动追加到有数据的末行，从第1列开始追加
from openpyxl import load_workbook# 打开工作簿f = load_workbook('./tmp.xlsx')# 选择需要追加的工作表f_sheet = f['Sheet1']# 追加数据data = ['A', 'B', 'C', 'D']f_sheet.append(data)# 保存文件f.save('./tmp.xlsx')
多行数据使用循环遍历的方式添加
from openpyxl import load_workbook# 打开工作簿f = load_workbook('./tmp.xlsx')# 选择需要追加的工作表f_sheet = f['Sheet1']# 准备多行数据data_list = [['A', 'B', 'C', 'D'],['E', 'F', 'G', 'H'],[1, 2, 3, 4]]# 循环添加for i in data_list:f_sheet.append(i)# 保存文件f.save('./tmp.xlsx')
2. 写入和读取Excel
2.1. 设置表名
获取或设置工作表的名称。title 只能获取已赋值的对象，查看表名get_sheet_names更合适
title 修改表名（将原 Sheet1 修改为 new_Sheet1）
from openpyxl import load_workbook# 打开工作簿f = load_workbook('./tmp.xlsx')# 获取工作表对象sheet = f['Sheet1']# 修改工作表名称sheet.title = 'new_Sheet1'# 保存文件f.save('./tmp.xlsx')
2.2. 写入数据
获取或设置工作表中指定单元格的数据、样式等信息。该方法需要指定行列索引，例如cell(row=1, column=1)表示获取第1行第1列的单元格数据。语法
[工作表].cell(row： #必选参数，指定单元格的行号column：#必选参数，指定单元格的列号value：#可选参数，单元格的值，默认值为Nonecoordinate： #可选参数，单元格的坐标，默认值为None。如果同时设置了row和column参数，那么coordinate参数会被自动计算。)
指定 某行、某列 写入数据。注意：这种方式会将指定文件的原有内容覆盖，工作表留一个Sheet1
import openpyxl# 创建一个 工作簿wb = openpyxl.Workbook()# 获取工作表对象ws = wb.active# 第1行、1列：插入字符串ws.cell(1, 1, 'Hello')# 第1行、2列：插入数字ws.cell(1, 2, 1)# 第1行、3列：插入数字ws.cell(1, 3, 10)# 第1行、4列：插入公式ws.cell(1, 4, '=B1+C1')# 第1行、5列：设置格式ws.cell(row=1, column=5, value='测试')# 保存文件wb.save('./tmp.xlsx')
向已有数据的文件中，写入指定内容
import openpyxl# 打开一个工作簿wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名sheet = wb['Sheet1']# 向文件中写入内容sheet.cell(2, 1, 'abc') #第2行、1列sheet.cell(2, 2, '无敌') #第2行、2列# 保存文件wb.save('./tmp.xlsx')
向某个工作簿插入多条数据
import openpyxl# 打开一个工作簿wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名sheet = wb['Sheet2']# 准备每行内容data = [['包子', '面条', '稀饭', '油条', '豆浆'],['卖火柴', '卖火机', '卖石油', '卖火石'],['西瓜', '香蕉', '苹果', '蓝莓', '石榴']]# 设置开始行、列start_row = 2start_col = 3# 记录初始列init_col = start_colfor data_list in data: #遍历这个列表for value in data_list: #遍历列表中的列表，这才是要插入的数据sheet.cell(row=start_row, column=start_col, value=value) #指定行、列，插入数据start_col += 1 #每次遍历，列数+1start_row += 1 #每次遍历一个列表，行数+1start_col = init_col #每次遍历一个列表，列数回到初始值# 保存文件wb.save('./tmp.xlsx')# 关闭文件wb.close()
2.3. 插入空白行/列
分别用于在指定的行或列插入新的空白行或空白列。插入空白行（原本行的数据会下移n行，n取决于插入的空白行数）
insert_rows([行号] #必选参数，指定插入的行号[行数] #可选参数，指定插入n行空白，默认1)
# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 第2行插入1行空白行wb_sheet.insert_rows(2)# 第2行插入3行空白行wb_sheet.insert_rows(2, 3)# 保存文件wb.save('./tmp.xlsx')
插入空白列（原本列的数据会右移n列，n取决于插入的空白列数）
insert_cols([列号] #必选参数，指定插入的列号[列数] #可选参数，指定插入n列空白，默认1)
# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 第2列插入1列空白列wb_sheet.insert_cols(2)# 第2列插入3列空白列wb_sheet.insert_cols(2, 3)# 保存文件wb.save('./tmp.xlsx')
2.4. 迭代读取指定行、列
语法
iter_rows(min_row：要迭代的起始行号，默认值为1。max_row：要迭代的最后行号，默认值为最大行号，即ws.max_row的值。min_col：要迭代的起始列号，默认值为1。max_col：要迭代的最后列号，默认值为最大列号，即ws.max_column的值。values_only：默认为False，如果设置为True，则仅返回单元格的值，而不是单元格对象。)
表内容如图
指定开始行、结束行和开始列、结束列
import openpyxl# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 指定读取 2-3 行，返回值(而非对象)rows = wb_sheet.iter_rows(min_row=2, max_row=3, values_only=True)# 遍历2-3行for i in rows:print(i)# 指定读取 2-3 行和 3-4 列rows = wb_sheet.iter_rows(min_row=2, max_row=3, min_col=3, max_col=4, values_only=True)# 遍历2-3行，3-4列for i in rows:print(i)
2.5. 遍历所有数据行
分别返回一个生成器对象，用于遍历工作表中所有的行。表内容如图
遍历所有存在数据的行（从第1行开始，无论第1行是否有数据）
import openpyxl# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 遍历所有有数据的行for row in wb_sheet.rows:# 再遍历这一行的所有值for i in row:# 输出单元格名和值print(i.coordinate, i.value)# 每行做一个间隔print('-------------- 下一行 --------------')
输出结果如下：
A1 NoneB1 NoneC1 NoneD1 NoneE1 NoneF1 NoneG1 None-------------- 下一行 --------------A2 NoneB2 NoneC2 包子D2 面条E2 稀饭F2 油条G2 豆浆-------------- 下一行 --------------A3 NoneB3 NoneC3 卖火柴D3 卖火机E3 卖石油F3 卖火石G3 None-------------- 下一行 --------------A4 NoneB4 NoneC4 西瓜D4 香蕉E4 苹果F4 蓝莓G4 石榴-------------- 下一行 --------------
指定遍历第2行
# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 遍历第2行for i in wb_sheet[2]:# 输出单元格名和值print(i.coordinate, i.value)
2.6. 遍历所有数据列
分别返回一个生成器对象，用于遍历工作表中所有的列。表内容如图
遍历所有存在数据的列（从第1列开始，无论第一列是否有数据）
import openpyxl# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 遍历所有有数据的列for col in wb_sheet.columns:# 再遍历这一列的所有值for i in col:# 输出单元格名和值print(i.coordinate, i.value)# 每列做一个间隔print('-------------- 下一列 --------------')
输出结果
A1 NoneA2 NoneA3 NoneA4 None-------------- 下一列 --------------B1 NoneB2 NoneB3 NoneB4 None-------------- 下一列 --------------C1 NoneC2 包子C3 卖火柴C4 西瓜-------------- 下一列 --------------D1 NoneD2 面条D3 卖火机D4 香蕉-------------- 下一列 --------------E1 NoneE2 稀饭E3 卖石油E4 苹果-------------- 下一列 --------------F1 NoneF2 油条F3 卖火石F4 蓝莓-------------- 下一列 --------------G1 NoneG2 豆浆G3 NoneG4 石榴-------------- 下一列 --------------
2.7. 读取最大行号/列号
分别返回当前工作表中最大的行数和最大的列数，可以用来遍历整个工作表的数据。语法
# 查看最后一行的行号[工作表].max_row# 查看最后一列的列号[工作表].max_column
查看最后一行行号，输出最后一行的值
import openpyxl# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 获取最大行max_rows = wb_sheet.max_rowprint(f'存在数据的最大行为:{max_rows}')# 通过行号遍历最后一行的数据for i in wb_sheet[max_rows]:print(i.coordinate, i.value)
查看最后一列的列号
import openpyxl# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 获取最大行max_col = wb_sheet.max_columnprint(f'存在数据的最大列为:{max_col}')
2.8. 判断空行、列
openpyxl 中没有直接判断的方法，使用循环判断。判断图表如下：
判断空行
# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 获取最大行数、最大列数max_row = wb_sheet.max_rowmax_col = wb_sheet.max_column# 通过行数、列数去遍历for row in wb_sheet.iter_rows(max_row=max_row, max_col=max_col):# 判断空行if all(cell.value is None for cell in row):print(f'第{row[0].row}行是空行')
判断空列的方法与空行相似
# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 获取最大行数、最大列数max_row = wb_sheet.max_rowmax_col = wb_sheet.max_column# 通过行数、列数去遍历for col in wb_sheet.iter_cols(max_row=max_row, max_col=max_col):# 判断空列if all(cell.value is None for cell in col):print(f'第{col[0].column_letter}行是空列')
3.设置Excel格式
3.1. 56种颜色代码
3.2. 设置字体属性
例如：字体名称、字号、颜色和是否加粗等。语法
openpyxl.styles.Font(name="Calibri" #字体名称（Arial、Calibri、Times New Roman、Verdana、Helvetica、Tahoma）size=11 #字体大小bold=False #是否加粗italic=False#是否倾斜underline="none" #下划线类型color="000000" #字体颜色，红（FF0000）、橙（993300）、黄（FFFF00）、绿（00FF00）、青（00FFFF）、蓝（0000FF）、紫（CC99FF）scheme="None"#字体下划线颜色方案strike=False#删除线。)
设置单个单元格属性
import openpyxlfrom openpyxl.styles import Font# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet1']# 指定坐标A1，设置属性(颜色：红色)wb_sheet['A1'].font = Font(color='FF0000')# 指定坐标（1行2列）和（2行2列）为蓝色wb_sheet.cell(row=1, column=2).font = Font(color='0000FF')wb_sheet.cell(row=2, column=2).font = Font(color='0000FF')# 保存文件wb.save('./tmp.xlsx')
设置某个工作表所有存在数据的单元格属性
# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 将工作表所有存在数据的单元格设置属性(获取最大行数、最大列数）max_row = wb_sheet.max_rowmax_col = wb_sheet.max_column# 通过行数、列数去遍历for row in wb_sheet.iter_rows(max_row=max_row, max_col=max_col):# 遍历单个单元格for cell in row:# 设置属性(字体大小：18，字体加粗，字体删除线)cell.font = openpyxl.styles.Font(size=18, bold=True,strike=True)#保存文件wb.save('./tmp.xlsx')
指定表格设置属性
import openpyxl# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet2']# 指定开始行、结束行；开始列，结束列设置属性start_row,end_row = 1,3 #1-3行start_col,end_col = 2,4 #2-4列# 通过行数、列数去遍历for row in wb_sheet.iter_rows(min_row=start_row, max_row=end_row, min_col=start_col, max_col=end_col):# 遍历单个单元格for cell in row:# 设置属性(字体颜色：红色)cell.font = openpyxl.styles.Font(color='FF0000')#保存文件wb.save('./tmp.xlsx')
3.3. 设置填充属性
例如：填充类型、填充颜色、前景色、背景色等。语法
openpyxl.styles.PatternFill(fill_type #填充类型fg_color#前景颜色bg_color#背景颜色start_color #开始颜色end_color #结束颜色，仅在 fill_type 为 “gradient” 时有效pattern_type #填充的图案类型，只有在 fill_type 为 “solid” 或 “light* / dark*” 等条纹填充时有效)填充类型包含：none #不填充solid #实心填充mediumGray, darkGray, lightGray, black: 灰阶填充darkHorizontal, darkVertical, darkDown, darkUp, darkGrid, darkTrellis，lightHorizontal, lightVertical, lightDown, lightUp, lightGrid, lightTrellis: 线条或块条纹填充gray0625 #62.5% 灰阶填充gradient #渐变填充path #自定义填充填充的图案类型包括：none #无图案solid #单色图案gray0625 #62.5% 灰阶图案mediumGray, darkGray, lightGray, black: 灰阶图案darkHorizontal, darkVertical, darkDown, darkUp, darkGrid, darkTrellis, lightHorizontal, lightVertical, lightDown, lightUp, lightGrid, lightTrellis: 线条或块条纹图案
示例（设置背景色为绿色）
from openpyxl import load_workbookfrom openpyxl.styles import PatternFillwb = load_workbook('./tmp.xlsx')wb_sheet = wb['Sheet1']# 设置A1填充类型为实心填充，背景颜色为绿色wb_sheet['A1'].fill = PatternFill(fill_type='solid', start_color='00FF00')# 设置A2填充类型为条纹填充，背景颜色为红色，条纹颜色为黄色wb_sheet['A2'].fill = PatternFill(fill_type='lightVertical', start_color='FF0000', end_color='FFFF00')wb.save('./tmp.xlsx')
3.4. 设置对齐属性
例如：水平对齐方式、垂直对齐方式、自动换行等。语法
openpyxl.styles.Alignment(horizontal #水平对齐方式, 默认general(一般对齐)vertical#垂直对齐方式wrap_text#是否自动换行(True 或 False)shrink_to_fit #是否缩小字体以适应单元格大小(True 或 False)indent #缩进级别(0-255)reading_order #读取顺序(1: 从左到右; 2: 从右到左))水平对齐选项：general #默认值，一般对齐left #左对齐center #居中对齐right #右对齐fill #填充对齐justify #两端对齐centerContinuous #连续居中对齐（仅限于分布居中）distributed #分布对齐垂直对齐选项：top#上对齐center #居中对齐bottom #下对齐justify#两端对齐distributed #分布对齐
示例（设置单个单元格对齐方式）
from openpyxl import load_workbookfrom openpyxl.styles import Alignmentwb = load_workbook('./tmp.xlsx')wb_sheet = wb['Sheet1']# 居中对齐wb_sheet['A1'].alignment = Alignment(horizontal='center', vertical='center')# 左对齐，自动换行，缩小字体以适应单元格大小wb_sheet['A2'].alignment = Alignment(horizontal='left', vertical='top', wrap_text=True, shrink_to_fit=True)# 分布对齐wb_sheet['A3'].alignment = Alignment(horizontal='distributed', vertical='center')wb.save('./tmp.xlsx')
示例（设置多个单元格对齐）
from openpyxl import load_workbookfrom openpyxl.styles import Alignmentwb = load_workbook('./tmp.xlsx')wb_sheet = wb['Sheet1']# 设置单元格的对齐方式为居中alignment = Alignment(horizontal='center', vertical='center')# 设置开始行号和结束行号、开始列号和结束列号start_rows,end_rows = 2,5start_col, end_col = 2,5# 采用遍历的方式分别对各个单元格设置对齐方式for row in wb_sheet.iter_rows(min_row=start_rows, max_row=end_rows, min_col=start_col, max_col=end_col):for cell in row:# 为每个单元格设置对齐方式cell.alignment = alignmentwb.save('./tmp.xlsx')
3.5.设置边框属性
例如：边框样式、边框颜色、边框位置等。语法
openpyxl.styles.Border(left #左边框样式(参考下方选项)right #右边框样式(参考下方选项)top #上边框样式(参考下方选项)bottom#下边框样式(参考下方选项)diagonal #对角线样式(参考下方选项)diagonal_direction #对角线方向(up: 对角线从下到上; down: 对角线从上到下)diagonal_color#对角线颜色，同颜色规范)边框样式选项：none #没有边框thin #细边框medium #中等边框dashed #虚线边框dotted #点线边框thick #粗边框double #双边框hair #细边框，类似于毛发mediumDashDot#中等虚线边框dashDot #虚线点线边框mediumDashDotDot #中等点线点线边框dashDotDot #虚线点线点线边框mediumDashed#中等虚线边框
举例（设置单个单元格边框）
from openpyxl import load_workbookfrom openpyxl.styles import Border,Sidewb = load_workbook('./tmp.xlsx')wb_sheet = wb['Sheet1']# 为 A1 单元格添加双线边框，颜色为红色border = Border(left=Side(style='double', color='FF0000'),right=Side(style='double', color='FF0000'),top=Side(style='double', color='FF0000'),bottom=Side(style='double', color='FF0000'))wb_sheet['A1'].border = border# 为 A2 单元格添加细虚线边框，颜色为蓝色thin_border = Border(left=Side(style='thin', color='0000FF'),right=Side(style='thin', color='0000FF'),top=Side(style='thin', color='0000FF'),bottom=Side(style='thin', color='0000FF'))wb_sheet['A2'].border = thin_borderwb.save('./tmp.xlsx')
举例（设置多个单元格边框），使用遍历逐个设置边框
from openpyxl import load_workbookfrom openpyxl.styles import Border,Sidewb = load_workbook('./tmp.xlsx')wb_sheet = wb['Sheet1']# 准备一个虚线边框模板，颜色为红色thin_border = Border(left=Side(style='thin', color='FF0000'),right=Side(style='thin', color='FF0000'),top=Side(style='thin', color='FF0000'),bottom=Side(style='thin', color='FF0000'))# 设置开始行号和结束行号、开始列号和结束列号start_rows,end_rows = 2,5start_col, end_col = 2,5# 采用遍历的方式分别对各个单元格设置边框for row in wb_sheet.iter_rows(min_row=start_rows, max_row=end_rows, min_col=start_col, max_col=end_col):for cell in row:# 为每个单元格设置边框cell.border = thin_borderwb.save('./tmp.xlsx')
3.6. 合并单元格
分别用于合并指定区域中的单元格和取消已经合并的单元格区域。合并单元格语法
worksheet.merge_cells(range_string=None #指定要合并的单元格范围。例如：A1：A2start_row=None #是合并范围的左上角单元格的行号。start_column=None #是合并范围的左上角单元格的列号。end_row=None #是合并范围的右下角单元格的行号。end_column=None#是合并范围的右下角单元格的列号。)
当指定range_string参数时，将自动计算另外4个参数。如果同时指定了5个参数参数，则以range_string参数为准。
取消合并单元格语法
worksheet.unmerge_cells(range_string=None #指定要取消的合并单元格的范围。start_row=None #是要取消的合并单元格的左上角单元格的行号。start_column=None #是要取消的合并单元格的左上角单元格的列号。end_row=None #是要取消的合并单元格的右下角单元格的行号。end_column=None#是要取消的合并单元格的右下角单元格的列号。)
用法与合并单元格一致，不做举例说明
同一行合并（A1:B1，如果2个单元格都有数据，那么只保留A1；B1:A1 这种语法是错误的，合并行或列，必须从走往右、从上往下选择）
# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet1']# 指定A1和B1合并wb_sheet.merge_cells('A1:B1')# 保存文件wb.save('./tmp.xlsx')
斜对行合并
# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet1']# 指定A1和B2合并(自动计算4个单元格)wb_sheet.merge_cells('A1:B2')# 保存文件wb.save('./tmp.xlsx')
指定开始行、列和结束行列（自定义方法适用于遍历）
# 打开文件wb = openpyxl.load_workbook('./tmp.xlsx')# 指定工作表名wb_sheet = wb['Sheet1']# 指定开始行号、结束行号；开始列号、结束列号wb_sheet.merge_cells(start_row=2, end_row=5, start_column=3, end_column=7)# 保存文件wb.save('./tmp.xlsx')
3.7. 设置组合样式
from openpyxl import load_workbookfrom openpyxl.styles import Alignment,NamedStyle,Font,Border,Side,PatternFill# 打开工作簿和对应的工作表wb = load_workbook('./tmp.xlsx')wb_sheet = wb['Sheet1']# 定义一个名为 my_style 的样式对象my_style = NamedStyle(name="my_style")# 设置字体属性my_style.font = Font(size=14, bold=True)# 设置填充属性my_style.fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')# 设置边框属性my_style.border = Border(left=Side(style='thin', color='000000'),right=Side(style='thin', color='000000'),top=Side(style='thin', color='000000'),bottom=Side(style='thin', color='000000'))my_style.alignment = Alignment(horizontal='center', vertical='center')# 设置开始行号和结束行号、开始列号和结束列号start_rows,end_rows = 1,3start_col, end_col = 1,5# 采用遍历的方式分别对各个单元格设置对齐方式for row in wb_sheet.iter_rows(min_row=start_rows, max_row=end_rows, min_col=start_col, max_col=end_col):for cell in row:# 为每个单元格设置自定义的属性cell.style = my_style# 保存文件wb.save('./tmp.xlsx')# 关闭文件wb.close()
4. 其他方法
4.1. csv文件导入Excel
通过pandas.read_csv 读取csv文件内容，语法如下
pandas.read_csv(filepath_or_buffer #必选参数，指定文件sep#可选参数，指定分隔符(默认逗号)delimiter#可选参数，指定分隔符，与sep一样，可用任何长度的字符串作为分隔符。skipinitialspace #可选参数，忽略分隔符后的空白，默认为False。header #可选参数，指定第n行作为每列的列名(例如指定第2行，那么不会输出第1行)names #可选参数，指定列名(例如：names=['AA','BB','CC','DD']，使用列表给每列取列名)usecols #可选参数，通过索引指定列数( [0,1,2] 和 [0,2,1] 读取的结果一致，不会更改数据的顺序)nrows #指定读取的行数(nrows=1 读取第2行)skiprows #可选参数，指定跳过某行(skiprows=[0,2] 跳过1和3行)skipfooter #可选参数，指定跳过尾部行数(skipfooter=2 跳过最后2行)encoding #指定编码格式，默认为None，使用系统默认编码。)
通过to_excel 保存到Excel文件，语法如下
to_excel(excel_writer #指定工作簿名sheet_name#指定工作表名startrow #写入数据的起始行startcol #写入数据的起始列。float_format #精度设置。columns #要写入的列，默认为None，表示写入所有列。header#是否写入列名，默认为True。index #是否写入索引，默认为True。index_label#索引列的列名。merge_cells#合并单元格。encoding #指定编码格式，默认为None，使用系统默认编码。)
读取csv文件内容保存到Excel文件（如果存在该文件自动覆盖；如果不存在该文件自动创建）
import pandas# 读取csv文件，指定分隔符f = pandas.read_csv('./csv.txt', sep=',')# 将csv文件内容覆盖到Excel，执行工作表名为AAA(默认为Sheet1)f.to_excel('tmp.xlsx', index=False, sheet_name='AAA')
读取csv文件内容，保存到Excel指定一个新的Sheet
import pandas# 读取csv文件f = pandas.read_csv('./csv.txt', sep=',')# 指定追加模式with pandas.ExcelWriter('tmp.xlsx', mode='a') as w:# 将追加一个工作表BBB到Excel文件，如果BBB存在则报错f.to_excel(w, sheet_name='BBB', index=False)
读取csv文件内容，指定写入开始行(列)到Excel文件
import pandas# 读取csv文件f = pandas.read_csv('./csv.txt', sep=',')# 指定追加模式with pandas.ExcelWriter('tmp.xlsx', mode='a') as w:# 指定工作表名(ccc)，从第11行、4列开始写入数据f.to_excel(w, sheet_name='CCC', index=False, startrow=10, startcol=3)
4.2. 画图
① 模块语法
matplotlib.pyplot 内置的方法
matplotlib.pyplot.[方法]方法如下( plt 为 matplotlib.pyplot 的简称)：plt.title() #设置图表标题；plt.xlabel() #设置x轴标签；plt.ylabel() #设置y轴标签；plt.grid()#添加网格线到图表中；plt.legend() #在图表中添加图例；plt.savefig() #将图表保存到指定的文件中。plt.xticks() #设置x轴刻度标签；plt.yticks() #设置y轴刻度标签；plt.plot()#绘制折线图、散点图、条形图等；plt.boxplot() #绘制箱体图；plt.bar()#绘制垂直方向的条形图；plt.hist()#绘制直方图；plt.pie()#绘制饼图；plt.imshow() #绘制图像；plt.contour() #绘制等高线图；plt.scatter() #绘制散点图；plt.stem()#绘制干状图(即将每个点的值以线的形式展示)；plt.step()#绘制阶梯图；plt.polar() #绘制极坐标图；plt.subplots() #创建Figure对象和多个子图；plt.semilogx() #以10为底的对数刻度绘制x轴；plt.semilogy() #以10为底的对数刻度绘制y轴；plt.loglog() #以10为底的对数刻度绘制x、y轴；plt.fill()#填充两条曲线之间的区域；plt.text()#在图表中添加文本注释；
折线图语法（matplotlib.pyplot.plot）
matplotlib.pyplot.plot(x#横坐标数组或一个可迭代对象；y#纵坐标数组或一个可迭代对象；c#散点图中点的颜色；label#该图例标签的字符串；linestyle #折线的样式；linewidth #折线或线框的线宽；marker#散点图的标记样式；markersize #标记大小；alpha#透明度；edgecolors #散点或折线周围的颜色；color#折线或线框的颜色；markeredgecolor #标记边界的颜色；markeredgewidth #标记边界的线宽；markerfacecolor #标记内部的颜色；linestyle#折线或线框的样式；dash_capstyle#折线中间断点的样式；dash_joinstyle#折线中间断点的连接样式；solid_capstyle#线框起点或终点的样式；solid_joinstyle #线框起点或终点在交叉区域的样式；zorder #控制图层顺序，越大的值表示越靠近上层；drawstyle#折线的绘制方式，可以是"steps-pre"、“steps-mid"或"steps-post”；dashes #自定义折线的样式，例如[10, 5, 20, 5]表示线段长10，空隙长5，线段长20，空隙长5；antialiased #抗锯齿效果，默认为True；fillstyle#散点的填充样式，可以是"full"、“left”、“right"或"bottom”；solid_joinstyle #线框起点或终点在交叉区域的样式；visible #对象是否可见；clip_on #是否将对象剪裁为轴范围内；clip_path#剪裁路径；markerfacecoloralt #用于面向未填充的标记的交替面颜色。)
柱状图语法（matplotlib.pyplot.bar）
matplotlib.pyplot.bar(x#柱状图中每个柱的横坐标；height#每个柱的高度；width#每个柱的宽度，为浮点数或数组；align#柱的对齐方式，可以是’center’、‘edge’、‘none’，分别表示居中对齐、边缘对齐和不对齐；bottom#每个柱底部的高度；log #是否使用对数坐标轴；color#柱状的颜色，可以是单个颜色字符串，也可以是RGB或RGBA序列、灰度值或html字符串；edgecolor #每个柱的边框颜色；linewidth #每个边框的线宽；hatch#每个柱填充的图案样式(纹理)；capsize #误差线末端箭头的长度；error_kw #误差线相关参数：elinewidth #误差线线宽；capsize #误差线末端箭头的长度；capthick #误差线末端箭头的厚度；ecolor#误差线颜色。align#对齐方式，可以是“edge”，“center”。label#柱状图的标签；alpha#透明度；visible #是否可见；zorder#控制图层顺序，越大的值表示越靠近上层；tick_label #柱状图的刻度标签；xerr #绘制误差线；yerr #绘制误差线。)
颜色写法
red：红色g：绿色b：蓝色c：青色m：品红y：黄色k：黑色w：白色。
② 折线图
Excel数据如下：
读取3列数据，绘制折线图
import pandasimport matplotlib.pyplot as plt# 防止乱码plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']# 读取Excel文件内容，如果读取csv文件，则使用pandas.read_csvdf = pandas.read_excel('./tmp.xlsx', sheet_name='AAA')# 提取横坐标的数据(常见的：日期,时长)x = df['data'].to_numpy()# 通过列名(c1/c2/c3)提取画图的数据y1 = df['c1'].to_numpy()y2 = df['c2'].to_numpy()y3 = df['c3'].to_numpy()# 绘制折线图plt.plot(x, y1, label='c1') # 指定纵坐标y1，并自定义图例标题为c1plt.plot(x, y2, label='c2') # 指定纵坐标y2，并自定义图例标题为c2plt.plot(x, y3, label='c3') # 指定纵坐标y3，并自定义图例标题为c3plt.legend() # 显示图例标题plt.xlabel('横坐标名称')plt.ylabel('纵坐标名称')plt.title('折线图标题')plt.savefig('./aa.jpg')# 保存图片plt.show() #查看图片
如果列名没有在第一行，那么在读取的时候直接跳过n行
# 读取Excel文件内容，header跳过前10行df = pandas.read_excel('./tmp.xlsx', sheet_name='AAA', header=10)
增加属性（标记点、网格、跳过n行）
import pandasimport matplotlib.pyplot as plt# 防止乱码plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']# 读取Excel文件内容，如果读取csv文件，则使用pandas.read_csvdf = pandas.read_excel('./tmp.xlsx', sheet_name='AAA', header=40)# 提取横坐标的数据(常见的：日期,时长)x = df['data'].to_numpy()# 通过列名(c1/c2/c3)提取画图的数据y1 = df['c1'].to_numpy()y2 = df['c2'].to_numpy()y3 = df['c3'].to_numpy()# 定义图表大小(长15，高8)plt.figure(figsize=(15,8))# 绘制折线图。label自定义图例名称，marker自定义标记样式，markersize指定标记大小plt.plot(x, y1, label='c1', marker='o', markersize=3)plt.plot(x, y2, label='c2', marker='o', markersize=3)plt.plot(x, y3, label='c3', marker='o', markersize=3)plt.legend() #显示图例标题plt.grid()#显示网格线plt.xlabel('横坐标名称')plt.ylabel('纵坐标名称')plt.title('折线图标题')plt.savefig('./aa.jpg') # 保存图片plt.show() #查看图片
③ 柱状图
多条柱状图重叠
import pandasimport matplotlib.pyplot as plt# 防止乱码plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']# 读取Excel文件内容df = pandas.read_excel('./tmp.xlsx', sheet_name='AAA', header=40)# 提取横坐标的数据(常见的：日期,时长)x = df['data'].to_numpy()# 通过列名(c1/c2/c3)提取画图的数据y1 = df['c1'].to_numpy()y2 = df['c2'].to_numpy()y3 = df['c3'].to_numpy()# 定义图表大小(长15，高8)plt.figure(figsize=(15,8))# 绘制折线图（label为自定义图例名称；width为柱状宽0-1；alpha为透明度0-1；edgecolor为边框颜色）plt.bar(x, y1, label='c1', width=0.5, alpha=0.5, edgecolor='black')plt.bar(x, y2, label='c2', width=0.5, alpha=0.5, edgecolor='black')plt.bar(x, y3, label='c3', width=0.5, alpha=0.5, edgecolor='black')plt.legend() # 显示图例标题plt.xlabel('横坐标名称')plt.ylabel('纵坐标名称')plt.title('折线图标题')# plt.savefig('./aa.jpg') # 保存图片plt.show() #查看图片
多条图形重叠颜色难以区分，让x轴+n（右移动n）
import pandasimport matplotlib.pyplot as plt# 防止乱码plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']# 读取Excel文件内容df = pandas.read_excel('./tmp.xlsx', sheet_name='AAA', header=40)# 提取横坐标的数据(常见的：日期,时长)x = df['data'].to_numpy()# 通过列名(c1/c2/c3)提取画图的数据y1 = df['c1'].to_numpy()y2 = df['c2'].to_numpy()y3 = df['c3'].to_numpy()# 定义图表大小(长15，高8)plt.figure(figsize=(15,8))# 绘制折线图（label为自定义图例名称；width为柱状宽0-1；alpha为透明度0-1；edgecolor为边框颜色；color为柱状颜色）plt.bar(x, y1, label='c1', width=0.2, alpha=0.5, edgecolor='black',color='g')plt.bar(x+0.2, y2, label='c2', width=0.2, alpha=0.5, edgecolor='black',color='c')plt.bar(x+0.4, y3, label='c3', width=0.2, alpha=0.5, edgecolor='black',color='m')plt.legend() # 显示图例标题plt.xlabel('横坐标名称')plt.ylabel('纵坐标名称')plt.title('折线图标题')plt.savefig('./aa.jpg') # 保存图片plt.show() #查看图片
