Python怎么使用openpyxl处理Excel文件
导读:本文共11319字符,通常情况下阅读需要38分钟。同时您也可以点击右侧朗读,来听本文内容。按键盘←(左) →(右) 方向键可以翻页。
摘要:前言安装openpyxl模块:pipinstallopenpyxl导入模块:importopenpyxl官方文档:1. Excel窗口工作簿(workbook):Excel的文件工作表(worksheet):一个工作簿由多个工作表组成列(column):工作表的列名为A、B、C等的大写字母行(row):工作表的行名称为1、2、3等的数字单元格(cell):工作表中的每个格子称为单元格,用(列名,行... ...
目录
(为您整理了一些要点),点击可以直达。前言
安装openpyxl模块:
pipinstallopenpyxl
导入模块:
importopenpyxl
官方文档:
1. Excel窗口
工作簿(workbook):Excel的文件
工作表(worksheet):一个工作簿由多个工作表组成
列(column):工作表的列名为A、B、C等的大写字母
行(row):工作表的行名称为1、2、3等的数字
单元格(cell):工作表中的每个格子称为单元格,用(列名,行名)表示
2. 读取Excel文件
#author:mlntcreatedate:2022/8/16
importopenpyxl
fromopenpyxl.utilsimportget_column_letter,column_index_from_string1.打开文件
使用openpyxl.load_workbook()方法打开Excel文件
filename='data.xlsx'
work_book=openpyxl.load_workbook(filename=filename)#加载Excel文件2.获取工作表名称
"""
-Excel文件对象.sheetnames:获取工作簿文件的所有工作表,以列表数据类型返回
-Excel文件对象.active:获取当前工作表的名称
"""获取所有工作表的名称
work_sheets=work_book.sheetnames
print(f'工作表列表:{work_sheets}')工作表列表:['Sheet1','Sheet2','Sheet3']
获取当前工作表的名称
current_sheet=work_book.active
print(f'当前工作表:{current_sheet}')当前工作表:<Worksheet"Sheet1">
获取当前工作表的内容
title=current_sheet.title
print(f'当前工作表标题:{title}')当前工作表标题:Sheet1
3.切换工作表
work_sheet=work_book['Sheet2']#返回名称相应的工作表
print(f'当前工作表:{work_sheet.title}')当前工作表:Sheet2
work_sheet=work_book['Sheet1']#返回名称相应的工作表
print(f'当前工作表:{work_sheet.title}')当前工作表:Sheet1
4.获取工作表的内容
print(f'单元格A1:{work_sheet["A1"].value}')
print(f'单元格B1:{work_sheet["B1"].value}')
print(f'单元格C1:{work_sheet["C1"].value}')
print(f'单元格D1:{work_sheet["D1"].value}')
print(f'单元格E1:{work_sheet["E1"].value}')
print(f'单元格F1:{work_sheet["F1"].value}')单元格A1:姓名
单元格B1:字
单元格C1:号
单元格D1:所处时代
单元格E1:别称
单元格F1:代表作
获取单元格相对位置信息
column:列,row:行,coordinate:坐标
print(f'单元格A1:{work_sheet["A1"].column},{work_sheet["A1"].row},{work_sheet["A1"].coordinate}')
print(f'单元格B1:{work_sheet["B1"].column},{work_sheet["B1"].row},{work_sheet["B1"].coordinate}')
print(f'单元格C1:{work_sheet["C1"].column},{work_sheet["C1"].row},{work_sheet["C1"].coordinate}')
print(f'单元格D1:{work_sheet["D1"].column},{work_sheet["D1"].row},{work_sheet["D1"].coordinate}')
print(f'单元格E1:{work_sheet["E1"].column},{work_sheet["E1"].row},{work_sheet["E1"].coordinate}')
print(f'单元格F1:{work_sheet["F1"].column},{work_sheet["F1"].row},{work_sheet["F1"].coordinate}')单元格A1:1,1,A1
单元格B1:2,1,B1
单元格C1:3,1,C1
单元格D1:4,1,D1
单元格E1:5,1,E1
单元格F1:6,1,F1
5.获取工作表内容的列数和行数
print(f'工作表列数:{work_sheet.max_column}')
print(f'工作表行数:{work_sheet.max_row}')工作表列数:6
工作表行数:20
6.获取单元格内容
cell(column=n,row=m)
forjinrange(1,work_sheet.max_row+1):
foriinrange(1,work_sheet.max_column+1):
print(work_sheet.cell(column=i,row=j).value,end='')
print()7.工作表对象的rows和columns
"""
创建工作表对象成功后,会自动产生数据产生器(generators):
rows:工作表数据产生器以行方式包裹,每一行用一个Tuple包裹;
columns:工作表数据产生器以列方式包裹,每一列用一个Tuple包裹。
"""
print(type(work_sheet.rows))#<class'generator'>
print(type(work_sheet.columns))#<class'generator'>forcellinlist(work_sheet.columns)[0]:
print(cell.value)forcellinlist(work_sheet.rows)[1]:
print(cell.value,end='')逐行遍历
print('逐行遍历开始...')
forrowinwork_sheet.rows:
forcellinrow:
print(cell.value,end='')
print()
print('逐行遍历结束...')逐列遍历
print('逐列遍历开始...')
forcolumninwork_sheet.columns:
forcellincolumn:
print(cell.value,end='')
print()
print('逐列遍历结束...')8.用整数取代域名
"""
get_column_letter(数值):将数值转成字母
column_index_from_string(字母):将字母转成数值
"""print(f'列数:{get_column_letter(work_sheet.max_column)}')
print(f"3-->{get_column_letter(3)}")
print(f"26-->{get_column_letter(26)}")
print(f"39-->{get_column_letter(39)}")
print(f"46-->{get_column_letter(46)}")
print(f"120-->{get_column_letter(120)}")列数:F
3-->C
26-->Z
39-->AM
46-->AT
120-->DP
print(f"A-->{column_index_from_string('A')}")
print(f"F-->{column_index_from_string('F')}")
print(f"AB-->{column_index_from_string('AB')}")
print(f"BBC-->{column_index_from_string('BBC')}")
print(f"CNN-->{column_index_from_string('CNN')}")A-->1
F-->6
AB-->28
BBC-->1407
CNN-->2406
9.切片
使用切片的概念读取某区间数据
逐行读取
forrowinwork_sheet['A3':'F4']:
forcellinrow:
print(cell.value,end='')
print()白居易乐天香山居士唐朝诗魔、诗王《长恨歌》、《卖炭翁》、《琵琶行》
杜甫子美少陵野老唐朝诗圣《春望》、《茅屋为秋风所破歌》、《登高》、《望岳》
data.xlsx:
3. 写入Excel文件
importopenpyxl1.创建空白工作簿
work_book=openpyxl.Workbook()
2.保存Excel文件
work_book.save('new_workbook.xlsx')
4. 复制Excel文件
importopenpyxlfilename='data.xlsx'
work_book=openpyxl.load_workbook(filename=filename)#开启工作簿
backup_name=filename[:filename.find('.xlsx')]+'-backup.xlsx'
work_book.save(backup_name)
效果:
5. 创建工作表
#author:mlntcreatedate:2022/8/16
importopenpyxl
1.创建空白工作簿
work_book=openpyxl.Workbook()
print(f'工作表列表:{work_book.sheetnames}')#工作表列表:['Sheet']2.创建新的工作表
work_book.create_sheet()
print(f'工作表列表:{work_book.sheetnames}')#工作表列表:['Sheet','Sheet1']
work_sheet=work_book.active#获取当前工作表
print(f'当前工作表:{work_sheet.title}')#当前工作表:Sheet"""
在创建工作表时,预设的工作表名称为”SheetN“,N为数字编号,以递增方式显示;
新建的工作表放在工作表列的最右边。
可以通过在create_sheet()中添加参数title和index设置新工作表的名称及位置(工作表位置从0开始)
"""
work_book.create_sheet(index=0,title='工作表1')
work_book.create_sheet(index=2,title='工作表3')
print(f'工作表列表:{work_book.sheetnames}')工作表列表:['工作表1','Sheet','工作表3','Sheet1']
3.删除工作表
删除”工作表3“
work_book.remove(work_book['工作表3'])
print(f'工作表列表:{work_book.sheetnames}')#工作表列表:['工作表1','Sheet','Sheet1']删除”Sheet“
delwork_book['Sheet']
print(f'工作表列表:{work_book.sheetnames}')#工作表列表:['工作表1','Sheet1']4.写入单元格
work_sheet=work_book.active#获取当前工作表
print(f'当前工作表:{work_sheet.title}')#当前工作表:Sheet
rows=[
['姓名','年龄','联系方式','学历'],
['张三','18','18888886666','大专'],
['王二狗','28','18888888888','研究生'],
['苟恭芝','38','18888889999','博士'],
['李华','20','18888887777','本科'],
['曹亠强','18','18888883333','大专']
]
forrowinrows:
work_sheet.append(row)保存Excel文件
work_book.save('my_workbook.xlsx')
6. 设置单元格字体及颜色
#author:mlntcreatedate:2022/8/16
importopenpyxl
fromopenpyxl.stylesimportFontwb=openpyxl.Workbook()#创建空白工作簿
ws=wb.active#获得当前工作表
"""
bold:加粗,值为True时表示粗体
italic:斜体,值为True时设置斜体
strike:删除线,值为True时设置删除线
name:字体名称,如:Arial
size:字号
color:字体颜色,color='FFFFFF'
"""
fontTitle1=Font(name='微软雅黑',size=24)
ws['A1'].font=fontTitle1
ws['A1']='勿谓言之不预'
fontTitle2=Font(name='楷体',size=18,bold=True)
ws['A2'].font=fontTitle2
ws['A2']='山不在高,有仙则名'设置字体及颜色
RGB颜色对照表:https://www.917118.com/tool/color_3.html
fontTitle3=Font(name='Arial',size=20,italic=True,color='00FF7F')
ws['A3'].font=fontTitle3
ws['A3']='Theearlybirdcatchestheworm.'保存Excel文件
wb.save('设置单元格字体.xlsx')
效果:
7. 数学公式的使用
常用的数学公式:
SUM():总和,如:SUM(A1:A3)
AVERAGE():平均值,如:AVERAGE(A1:A3)
MAX():最大值,如:MAX(A1:A3)
MIN():最小值,如:MIN(A1:A3)
importopenpyxlwb=openpyxl.Workbook()#创建空白工作簿
ws=wb.active#获得当前工作表
rows=[
['学号','姓名','语文','数学','英语','物理','化学','生物','总分'],
['1001','张三',90,98,106,80,85,78,'=SUM(C2:H2)'],
['1002','Tom',93,100,96,84,75,68,'=SUM(C3:H3)'],
['1003','Jack',89,80,108,70,65,88,'=SUM(C4:H4)'],
['1004','Mary',110,88,88,68,68,64,'=SUM(C5:H5)'],
['1005','Jane',98,78,86,56,95,72,'=SUM(C6:H6)']
]
forrowinrows:将数据添加到工作表
ws.append(row)
ws['B7']='总分'
ws['C7']='=SUM(C2:C6)'
ws['D7']='=SUM(D2:D6)'
ws['E7']='=SUM(E2:E6)'
ws['F7']='=SUM(F2:F6)'
ws['G7']='=SUM(G2:G6)'
ws['H7']='=SUM(H2:H6)'ws['B8']='平均分'
ws['C8']='=AVERAGE(C2:C6)'
ws['D8']='=AVERAGE(D2:D6)'
ws['E8']='=AVERAGE(E2:E6)'
ws['F8']='=AVERAGE(F2:F6)'
ws['G8']='=AVERAGE(G2:G6)'
ws['H8']='=AVERAGE(H2:H6)'ws['B9']='最高分'
ws['C9']='=MAX(C2:C6)'
ws['D9']='=MAX(D2:D6)'
ws['E9']='=MAX(E2:E6)'
ws['F9']='=MAX(F2:F6)'
ws['G9']='=MAX(G2:G6)'
ws['H9']='=MAX(H2:H6)'ws['B10']='最低分'
ws['C10']='=MIN(C2:C6)'
ws['D10']='=MIN(D2:D6)'
ws['E10']='=MIN(E2:E6)'
ws['F10']='=MIN(F2:F6)'
ws['G10']='=MIN(G2:G6)'
ws['H10']='=MIN(H2:H6)'wb.save('数学公式的使用.xlsx')
效果:
8. 设置单元格宽高
单元格预设的高度为12.75pt,72pt等于1英寸,使用column_dimensions属性可以设置行高;单元格默认宽度为8.43个英文字符宽度,可使用row_dimensions设置单元格的宽度如果将宽度或高度设置为0,则具有隐藏单元格的效果。
importopenpyxlwb=openpyxl.Workbook()#创建空白工作簿
ws=wb.active#获得当前工作表ws['A1']='海内存知己'
ws['A2']='天涯若比邻'
ws['B2']='Helloworld'
ws.row_dimensions[1].height=30#设置高度为30pt
ws.column_dimensions['B'].width=30#设置宽度为30个英文字符宽
wb.save('设置单元格宽高.xlsx')
效果:
9. 设置单元格对齐方式
使用Alignment()方法,需设置2个参数:
horizontal(水平方向):
left:靠左
right: 靠右
center: 居中
vertical(垂直方向):
top:靠上
center:居中
bottom:靠下
importopenpyxl
fromopenpyxl.stylesimportAlignmentwb=openpyxl.Workbook()#创建空白工作簿
ws=wb.active#获得当前工作表
ws['A1']='测试1'
ws['B1']='测试2'
ws['C1']='测试3'
ws.row_dimensions[1].height=30#设置高度为40pt
ws.column_dimensions['B'].width=20#设置宽度为20个字符宽
ws['A1'].alignment=Alignment(horizontal='left',vertical='top')#居左靠上
ws['B1'].alignment=Alignment(horizontal='center',vertical='center')#水平居中,垂直居中
ws['C1'].alignment=Alignment(horizontal='right',vertical='bottom')#靠右居下保存excel文件
wb.save('设置单元格对齐方式.xlsx')
效果:
10. 合并与取消单元格合并
合并单元格:
使用merge_cells()合并单元格,可以合并同一行(row)、同一列(column)或一个区域的单元格
取消合并单元格:
unmerge_cells()
#author:mlntcreatedate:2022/8/16
importopenpyxl
fromopenpyxl.stylesimportAlignmentwb=openpyxl.Workbook()#创建空白工作簿
ws=wb.active#获得当前工作表"""
1.合并单元格
使用merge_cells()合并单元格,可以合并同一行(row)、同一列(column)或一个区域的单元格
"""
ws['A1']='早起的鸟儿有虫吃'
ws['A2']='Theearlybirdcatchestheworm.'
ws['A3']='人生如戏'
ws['C4']='Wherethereisawillthereisaway.'
ws.merge_cells('A1:D1')#合并A1:D1单元格
ws.merge_cells('A3:A8')#合并A3:A8单元格
ws.merge_cells('C4:G6')#合并C4:G6单元格
ws['A1'].alignment=Alignment(horizontal='center')
ws['A3'].alignment=Alignment(vertical='center')
ws['C3'].alignment=Alignment(horizontal='center',vertical='center')2.取消合并单元格
unmerge_cells()
ws.unmerge_cells('A3:A8')#取消合并A3:A8单元格
wb.save('合并与取消单元格合并.xlsx')
效果:
11. 创建图表
11.1 柱状图
#author:mlntcreatedate:2022/8/16
"""
BarChart:柱状图
BarChart3D:3D柱状图
PieChart:饼图
PieChart:3D饼图
BubleChart:泡泡图
AreaChart:分区图
AreaChart3D:3D分区图
LineChart:折线图
LineChart3D:3D折线图
RedarChart:雷达图
StockChart:股票图
"""
importopenpyxl
fromopenpyxl.chartimportBarChart,Referencewb=openpyxl.Workbook()#创建空白工作簿
ws=wb.active#获得当前工作表
rows=[
['学号','姓名','语文','数学','英语','物理','化学','生物'],
['1001','张三',90,98,106,80,85,78],
['1002','Tom',93,100,96,84,75,68],
['1003','Jack',89,80,108,70,65,88],
['1004','Mary',110,88,88,68,68,64],
['1005','Jane',98,78,86,56,95,72]
]
forrowinrows:将数据添加到工作表
ws.append(row)
chart=BarChart()#直方图
chart.title='2022某班某小组学生成绩表'#图表标题
chart.y_axis.title='分数'#y轴标题
chart.x_axis.title='学员'#x轴标题
data=Reference(ws,min_col=3,max_col=8,min_row=1,max_row=6)#图表数据
chart.add_data(data,titles_from_data=True)#建立图表
x_title=Reference(ws,min_col=2,min_row=2,max_row=6)#x轴标记名称
chart.set_categories(x_title)#设置x轴标记名称
ws.add_chart(chart,'J1')#放置图标位置
wb.save('柱状图.xlsx')
效果:
11.2 饼图
#author:mlntcreatedate:2022/8/16
importopenpyxl
fromopenpyxl.chartimportPieChart,Referencewb=openpyxl.Workbook()#创建空白工作簿
ws=wb.active#获得当前工作表
rows=[
['科目','分数'],
['语文',90],
['数学',98],
['英语',106],
['物理',80],
['化学',85],
['生物',78]
]
forrowinrows:
ws.append(row)chart=PieChart()#饼图
chart.title='某学员成绩分析表'data=Reference(ws,min_col=2,min_row=1,max_row=7)#图表数据
chart.add_data(data,titles_from_data=True)#建立图表
labels=Reference(ws,min_col=1,min_row=2,max_row=7)#标签名称
chart.set_categories(labels)#设置标签名称
ws.add_chart(chart,'D1')
wb.save('饼图.xlsx')
效果:
Python怎么使用openpyxl处理Excel文件的详细内容,希望对您有所帮助,信息来源于网络。