python使用openpyxl处理excel

一、写在前面

1、安装openpyxl

pip install openpyxl 

2、认识excel窗口

明确下行（row），列（column）,单元格（cell），坐标（coordinate）与表（sheet）的概念。
这里还要注意一点，在Excel中是从1开始计数的，并不是像程序编码中0开始，这点很重要。比如图中 【后羿】标出来的格子对应的单元格是B4，B4就是它的坐标。

二、基本使用

1、打开excel

from openpyxl import load_workbookfilePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开已经存在的excel文件
print(workbook.sheetnames) # 打印excel中的表格（sheet）名
# 结果：['花名册', '成绩单']

2、获取sheet表格

'''
一般而言我们操作的都是第一个表，而获取表的方法也多种，常用的有三种:
workbook[表格名] ：通过传入相应的表格名来获取对应的表数据；
workbook.worksheets[索引] ：通过索引从workbook.worksheets中获取对应的表数据；
workbook.active ：获取当前活动的工作表
'''
from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
print(workbook.sheetnames) # 打印excel中的表格（sheet）名
sheet = workbook["花名册"] # 根据表名获取表格
sheet2 = workbook.worksheets[0] #根据索引在worksheets中获取表格
sheet3 = workbook.active # 获取当前活跃的表
print(sheet)
print(sheet2)
print(sheet3)
'''
运行结果：
['花名册', '成绩单']
<Worksheet "花名册">
<Worksheet "花名册">
<Worksheet "成绩单">
'''

3、获取sheet表格 尺寸

'''
获取一个表格的尺寸
'''
from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
print(workbook.sheetnames) # 打印excel中的表格（sheet）名
sheet = workbook["花名册"] # 根据表名获取表格
sheet2 = workbook.worksheets[1] #根据索引在worksheets中获取表格
print(sheet, sheet.dimensions)
print(sheet2, sheet2.dimensions)
'''
运行结果：
['花名册', '成绩单']
<Worksheet "花名册"> A1:C5
<Worksheet "成绩单"> A1:D5
'''

4、获取单元格数据

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook["花名册"] # 根据表名获取表格
sheet2 = workbook.worksheets[1] #根据索引在worksheets中获取表格
print(sheet["B2"]) #获取sheet的B2单元格的对象
print(sheet2["C4"]) #获取sheet2的C4单元的对象
print(sheet["B2"].value) #获取sheet的B2单元格中的值
print(sheet2["C4"].value) #获取sheet2的C4单元格中的值
'''
结果：
<Cell '花名册'.B2>
<Cell '成绩单'.C4>
李白
96
'''

单元格数据除了坐标的方式来定位，我们还可以用行列来找到它，比如B2对应的是第2行第2列（row=2,column=2）, C4对应的是第4行第3列（row=4, column=3)。

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook["花名册"] # 根据表名获取表格
sheet2 = workbook.worksheets[1] #根据索引在worksheets中获取表格
cell = sheet.cell(row=2, column=2) #获取B2的数据
cell2 = sheet2.cell(row=4, column=3) #获取C4的数据
print(cell, cell.value) #获取B2单元格中的值
print(cell2, cell2.value) #获取C4单元格中的值

5、获取区域单元格数据

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook["花名册"] # 根据表名获取表格
cells = sheet["A1:C4"] #获取A1:C4区域的数据
for i in cells:print(i)for j in i:print(j.value)'''
结果：
(<Cell '花名册'.A1>, <Cell '花名册'.B1>, <Cell '花名册'.C1>)
学号
姓名
性别
(<Cell '花名册'.A2>, <Cell '花名册'.B2>, <Cell '花名册'.C2>)
1
李白
男
(<Cell '花名册'.A3>, <Cell '花名册'.B3>, <Cell '花名册'.C3>)
2
甄姬
女
(<Cell '花名册'.A4>, <Cell '花名册'.B4>, <Cell '花名册'.C4>)
3
后羿
男
'''

从打印的结果可以看出，cells中的数据是按行读取的。

通过sheet[“坐标:坐标2”]获取区域内单元格的数据，它返回的是一个区域内的数据。其实我们还可以通过sheet[]方法获取某行或某列的数据，格式如下：
获取第A列的数据：sheet[“A”]
获取第4行的数据：sheet[“4”]

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook["花名册"] # 根据表名获取表格
print("第4行的数据：")cells = sheet["4"] #获取第4行的数据
for i in cells:print(i.value)print("第A列的数据：")
cells = sheet["A"] #获取A列的数据
for i in cells:print(i.value)print("2-4行之间的数据：")
cells = sheet[2:4] #获取第2行到第4行之间的数据
for i in cells:print(i)for j in i:print(j.value)print("A-C列之间的数据：")
cells = sheet["A:C"] #获取第A列到第C列之间的数据
for i in cells:print(i)for j in i:print(j.value)

sheet[]获取单行单列的数据没有问题，如果获取多行或是多列呢？答案仍是通过sheet[]函数，格式如：
获取行1到行2之间的数据：sheet[“行1:行2”]
获取列1与列2之间的数据：sheet[“列1:列2”]
注意行是由数字表示，列是由A-Z的字母组合表示。

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook["花名册"] # 根据表名获取表格print("2-4行之间的数据：")
cells = sheet[2:4] #获取第2行到第4行之间的数据
for i in cells:print(i)for j in i:print(j.value)print("A-C列之间的数据：")
cells = sheet["A:C"] #获取第A列到第C列之间的数据
for i in cells:print(i)for j in i:print(j.value)

6、sheet.iter_rows()方法

前面我们学习了用sheet[]方法获取单元格区域内数据，sheet[]很强大，既能获取单行或单列数据，又能获取多行或多列之间的数据，还能根据单元格坐标获取两个坐标内单元格的数据。

但是sheet[]方法有一定的局限性，比如我需要第10行至第15行，第3列至第10列之间的全部单元格数据，我需要先把行列信息转化为字母数字坐标，那有没有一种方法直接就根据行列数据就能获取相应范围内的数据呢，答案当然是有的，那就是sheet.iter_rows()。我们看下它的定义：

'''
min_row为最小行数索引
max_row为最大行数索引
min_col为最小列数索引
max_col为最大列数索引
'''
def iter_rows(self, min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)

这里我们可以通过min_row,max_row,min_col,max_col来控制需要获取的行或列的索引，这四个值都是可以缺省的，如果全部缺省就是按行返回表的所有数据。这里需要注意索引最小值都是从1开始的。

values_only 默认是Flase, 如果设置为True则直接输出值，不必利用单元格的value属性来输出值了。

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook["花名册"] # 根据表名获取表格# 按行获取范围内数据
for i in sheet.iter_rows(min_row=2, max_row=4, min_col=1, max_col=2):print(i)for j in i:print(j.value)# 按行获取所有数据
for i in sheet.iter_rows():print(i)for j in i:print(j.value)

当缺省min_row时是默认取的最小行；
当缺省max_row时是默认取的最大行；
当缺省min_col时是默认取的最小列；
当缺省max_col时是默认取的最大列；

7、修改单元格的值

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook['花名册'] # 根据表名获取表格sheet["B2"] = '有霸夫' # 修改B2单元格的值为'有霸夫'
cell = sheet["B3"] # 获取B3单元格为cell
cell.value = '蔡文姬' # 修改cell的内容为 '蔡文姬'savePath = r'D:\王者小学2.xlsx'
workbook.save(savePath) # 另存为D:\王者小学2.xlsx

8、向表格中插入行数据

append()会在表格已有的数据后面增添你要加入的数据，注意它是按行插入的。

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) # 打开excel文件
sheet = workbook['花名册'] # 根据表名获取表格
data = [['5', '安琪拉', "女"],['6', '荆轲', "女"],['7', '夏侯惇', "男"],
]
for row in data:
sheet.append(row) #按行插入到表格数据最后面
workbook.save(filePath) # 保存到源文件

9、实战：合并多个excel

import time
from openpyxl import Workbook, load_workbook
import os
def 合并表格(file_dir, save_dir):files_name = os.listdir(file_dir)# 创建新表格new_wb = Workbook()new_ws = new_wb.activeheader = ['销售日期', '员工工号', '销售员', '货号', '销售单号', '销量', '销售额']new_ws.append(header)# 向新的表格写入数据for file_name in files_name:wb = load_workbook(file_dir + "\\" + file_name)for sheet in wb.sheetnames:ws = wb[sheet]# 设置values_only=True 则可以直接得到单元格中的值for row in ws.iter_rows(min_row=2, values_only=True):new_ws.append(row)print('全年销售数据为 %s 行' % (new_ws.max_row - 1)) # 去除第一行标题剩下的则为全年销售数据总行数# 数据保存if not os.path.exists(save_dir):os.makedirs(save_dir)new_wb.save(save_dir + "\\" + "全年数据.xlsx")if __name__ == '__main__':开始时间 = time.time()合并表格(r"D:\2024年销售明细", r"D:\2024年销售分析")print('合并完成耗时：', time.time() - 开始时间, "秒")

三、获取Excel数据扩展

1、获取单元格其他属性

'''
获取属性的方法其实跟获取值的方法是一样的，
cell.row：获取行数
cell.column：获取列数
cell.coordinate：获取坐标
'''
from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook["花名册"] # 根据表名获取表格
sheet2 = workbook.worksheets[1] #根据索引在worksheets中获取表格
cell = sheet["B2"] #获取B2的数据
cell2 = sheet2["C4"] #获取C4的数据
print("单元格B2 行数：%s 列数：%s 值：%s 坐标：%s" % (cell.row, cell.column,
cell.value, cell.coordinate)) #获取B2单元格属性
print("单元格C4 行数：%s 列数：%s 值：%s 坐标：%s" % (cell2.row, cell2.column,
cell2.value, cell2.coordinate)) #获取C4单元格属性
'''
结果：
单元格B2 行数：2 列数：2 值：李白 坐标：B2
单元格C4 行数：4 列数：3 值：96 坐标：C4
'''

2、sheet.iter_cols()方法

sheet.iter_cols的使用可以说几乎跟sheet.iter_rows()是一样的：

1. 当不传入参数时是按列获取表中的所有数据；
2. 当设置参数时则返回参数限定范围内的表数据；
3. 当设置的参数有缺省时，如果缺省的参数是min_row或min_col则取最小行或是最小列，如果是max_row或是max_col则取最大行或最大列。

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook["花名册"] # 根据表名获取表格
print("不指定min_row:")# 按行获取值，缺省min_row
for i in sheet.iter_cols(max_row=4, min_col=1, max_col=2):print(i)for j in i:print(j.value)print("不指定max_row:")
# 按行获取值,缺省max_row
for i in sheet.iter_cols(min_row=2, min_col=1, max_col=2):print(i)for j in i:print(j.value)print("不指定min_col:")
# 按行获取值，缺省min_row
for i in sheet.iter_cols(min_row=2, max_row=4, max_col=2):print(i)for j in i:print(j.value)print("不指定max_col:")
# 按行获取值,缺省max_row
for i in sheet.iter_cols(min_row=2, max_row=4, min_col=1):print(i)for j in i:print(j.value)

3、sheet.rows和sheet.columns

sheet.rows 获取表格中所有行数据；
sheet.columns获取表格中所有列数据。

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook["花名册"] # 根据表名获取表格print("sheet.rows获取所有行数据:")
# 按行获取值
for i in sheet.rows:print(i)for j in i:print(j.value)print("sheet.columns获取所有列数据:")
# 按列获取值
for i in sheet.columns:print(i)for j in i:print(j.value)

4、插入空行和空列

插入空行和空列的格式如下：
sheet.insert_rows(idx=数字编号, amount=要插入的行数)，插入的行数是在idx行数的位置插入，如果amount不设置则默认插入一行；
sheet.insert_cols(idx=数字编号, amount=要插入的列数)，插入的位置是在idx列数的位置插入，如果amount不设置则默认插入一列。

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook['花名册'] # 根据表名获取表格sheet.insert_rows(idx=3, amount=2) #第三行位置插入两行
sheet.insert_cols(idx=2, amount=1) #第二列位置插入一列savePath = r'D:\王者小学2.xlsx'
workbook.save(savePath) # 另存为D:\王者小学2.xlsx

5、删除行和列

删除行和列的格式如下：
sheet.delete_rows(idx=数字编号, amount=要删除的行数)，删除的行数是在idx行数的位置
sheet.delete_cols(idx=数字编号, amount=要删除的列数)，删除的列数是在idx行数的位置

from openpyxl import load_workbook
filePath = r'D:\王者小学2.xlsx' # 文件路径
workbook = load_workbook(filePath) #打开excel文件
sheet = workbook['花名册'] # 根据表名获取表格sheet.delete_rows(idx=3, amount=2)
sheet.delete_cols(idx=2, amount=1)savePath = r'D:\王者小学2.xlsx'
workbook.save(savePath) # 另存为D:\王者小学2.xlsx

6、创建新的sheet表

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) # 打开excel文件workbook.create_sheet("身高") # 创建新的身高表print(workbook.sheetnames)
sheet = workbook.worksheets[2]
data = [['学号', '身高'],['1', '168'],['2', '181'],['3', '160'],['4', '160'],['5', '160'],['6', '170'],['7', '190'],
]
for row in data:sheet.append(row)workbook.save(filePath) # 保存源文件

7、删除sheet表

del workbook[‘表名’]：根据表名删除
workbook.remove(sheet表)：删除某个sheet表对象；

from openpyxl import load_workbook
filePath = r'D:\王者小学.xlsx' # 文件路径
workbook = load_workbook(filePath) # 打开excel文件
print(workbook.sheetnames)
# 方法一
del workbook['身高']
# 方法二
# sheet = workbook['身高']
# workbook.remove(sheet) #删除身高表
print(workbook.sheetnames)
workbook.save(filePath) # 保存为源文件

8、新建Excel与修改sheet表名

from openpyxl import Workbook
workbook = Workbook()
# 保存
workbook.save(filename=r"D:\新建Excel.xlsx")

from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = "我是表格" #设置表格的标题为我是表格
workbook.save(filename=r"D:\新建Excel.xlsx")

9、设置表格样式与单元格属性

'''
我们总结下上面用到的知识点：修改字体样式：Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)单元格颜色填充：PatternFill(fill_type=填充样式，start_color=开始颜色, end_color=结束颜色）单元格对齐方式：Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
水平对齐：‘distributed’，‘justify’，‘center’，‘leftfill’， ‘centerContinuous’，‘right，‘general’；
垂直对齐：‘bottom’，‘distributed’，‘justify’，‘center’，‘top’；设置边框样式：
Side(style=边线样式，color=边线颜色)
Border(left=左边线样式，right=右边线样式，top=上边线样式，bottom=下边线样式)
style参数的种类： 'double, ‘mediumDashDotDot’, ‘slantDashDot’,‘dashDotDot’,‘dotted’,‘hair’, 'mediumDashed, ‘dashed’, ‘dashDot’, ‘thin’,‘mediumDashDot’,‘medium’,
'''from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, fills, colors, Side# 导入表格数据
filePath = r"D:\2021年销售分析\全年数据.xlsx"
wb = load_workbook(filePath)# 操作单元格
ws = wb.active# 调整列宽
ws.column_dimensions["A"].width = 25
ws.column_dimensions["B"].width = 10
ws.column_dimensions["C"].width = 10
ws.column_dimensions["D"].width = 13
ws.column_dimensions["E"].width = 35
ws.column_dimensions["F"].width = 8
ws.column_dimensions["G"].width = 10# 设置单元格格式
# 设置字体格式
font = Font("微软雅黑", size=12, color=colors.BLACK, bold=False)# 单元格颜色填充
fill = PatternFill(fill_type="solid", start_color="CDCDCD", end_color="CDCDCD") #CDCDCD浅灰色# 单元格对齐方式
alignment = Alignment(horizontal="center", vertical="center", indent=0) #wrap_text=True文字换行，shrink_to_fit=True自适应宽度# 单元格边框
bd = Border(left=Side(border_style="thin", color=colors.BLACK),right=Side(border_style="thin", color=colors.BLACK),top=Side(border_style="thin", color=colors.BLACK),bottom=Side(border_style="thin", color=colors.BLACK),outline=Side(border_style="thin", color=colors.BLACK),vertical=Side(border_style="thin", color=colors.BLACK),horizontal=Side(border_style="thin", color=colors.BLACK))# 遍历数据
for row in ws.rows:for cell in row:cell.font = fontcell.fill = fillcell.alignment = alignmentcell.border = bd# 设置表头字体格式
ft = Font("宋体", size=12, color=colors.BLUE, bold=True) # italic=True斜体
ws["A1"].font = ft
ws["B1"].font = ft
ws["C1"].font = ft
ws["D1"].font = ft
ws["E1"].font = ft
ws["F1"].font = ft
ws["G1"].font = ftsavePath = r"D:\2021年销售分析\全年数据-格式调整.xlsx"
# 保存数据
wb.save(savePath)