openpyxl详细介绍文档

openpyxl1、创建一个工作薄fromopenpyxlimportWorkbook,load_workbookwb=Workbook()#该方法主要用于创建一个新的wb=load_workbook('./name.xlsx')#该方法主要用于导入一个已经存在的工作薄2、激活表格sheet=wb.activeWorkbook的一些属性方法：sheetcell3、按行读取excel表格的方法按行读取：  1、方法一#按行读取foriinrange(minrow,maxrow+1):forjinrange(mincol,maxcol+1):cell=sheet.cell(i,j).valueprint(cell,end="")print()方法二forrowinsheet.rows:forcellinrow:print(cell.value,end='\t')print()#目的在于不换行按列读取：  1、方法一#按列读取forminrange(mincol,maxcol+1):forninrange(minrow,maxrow+1):cell=sheet.cell(n,m).valueprint(cell,end="")print()4、openpyxl的写入  1、单元格的输入sheet['A1']='name'#按照想应的位置输入sheet.cell(1,2).value='123456'#cell后面接的是行和列  2、一行一行的写  如user=['test’,'123456’]sh1.append(user)  现在有个数组要写入到excel表里  login=[  ['张飞’,'123456’],  ['赵云’,'123456’],  ['许褚’,'123456’],  ['典韦’,'123456’],  ['关羽’,'123456’],  ['黄忠’,'123456’],  ['徐晃’,'123456’],  ['马超’,'123456’]  ]bk=openpyxl.Workbook()sh1=bk.activeforiinrange(len(login)):sh1.append(login[i])bk.save('userinfo.xlsx')2.Python操作Excel2.1Python操作Excel之读取2.1.1打开本地工作簿，获取所有工作表名称#获取所有的工作表名称importopenpyxlwb=openpyxl.load_workbook('excelTest.xlsx')#获取所有的工作表名称print(wb.sheetnames)#获取当前激活的工作表print(wb.active.title)#通过工作簿获取forsinwb:print(s.title)2.1.2创建工作表importopenpyxlwb=openpyxl.load_workbook('excelTest.xlsx')#创建工作表mySheet=wb.create_sheet('mySheet')print(wb.sheetnames)#遍历获取工作表的名称forsheetinwb:print(sheet.title)2.1.3根据工作表名称获取工作表importopenpyxlwb=openpyxl.load_workbook('excelTest.xlsx')#根据工作表名称获取工作表，DeprecationWarning:Calltodeprecatedfunctionget_sheet_by_name(Usewb[sheetname]).#会产生弃用警告，wb['my_sheet']，可以避免#my_sheet=wb.get_sheet_by_name('my_sheet')#根据名称获取表单sheet3=wb.get_sheet_by_name('Sheet2')#或者sheet4=wb['mySheet']2.1.4获取单元格对象及单元格的值importopenpyxlwb=openpyxl.load_workbook('excelTest.xlsx')#获取当前激活的工作表ws=wb.active#返回的是工作表对象print(ws)#<Worksheet"Sheet1">#获取Cell对象及单元格的值print(ws['A1'])#<Cell'Sheet1'.A1>print(ws['A1'].value)2.1.5获取单元格的行、列及值#获取单元格对象的行列和值importopenpyxl#获取工作簿对象wb=openpyxl.load_workbook('excelTest.xlsx')#获取sheetws=wb.active#根据单元格名称获取单元格对象c=ws['C3']print('row:{},column:{},value:{}'.format(c.row,c.column,c.value))print('coordinate:',c.coordinate)print('ws.cell()获取单元格的值')print(ws.cell(row=2,column=2).value)print('循环遍历获取：')forrinws.rows:forcinr:print(c.value,end='\t\t')print()2.1.6读取整行、整列及部分几行#读取整行整列及部分行importopenpyxl#获取工作簿wb=openpyxl.load_workbook('excelTest.xlsx')#获取sheetws=wb.activeprint('获取一整行')rows=ws[2]print(rows)#遍历所有单元格forrinrows:print(r.value,end='\t')print('获取整列')columns=ws['B']print(columns)forcincolumns:print(c.value,end='\t')print('获取部分行对行进行切片')row_range=ws[2:3]print(row_range)forrinrow_range:forcinr:print(c.value,end='\t')print()columns_range=ws['A':'C']print(columns_range)forcolincolumns_range:forcincol:print(c.value,end='\t\t')print()2.1.7使用iter_rows()逐行读取importopenpyxl#获取工作簿wb=openpyxl.load_workbook('excelTest.xlsx')#获取当前激活态的sheetws=wb.activeprint('总共{}，总共{}列'.format(ws.max_row,ws.max_column))print(ws.iter_rows())#generatorobject获取一个生成器对象#获取所有行所有列forrowinws.iter_rows():forcellinrow:print(cell.value,end='\t\t')print()print('读取部分行部分列')forrowinws.iter_rows(min_row=2,max_row=3,min_col=2,max_col=2):forcellinrow:print(cell.value,end='\t\t')print()print()print('iter.cols()')print(ws.iter_cols())forcellinws.iter_cols():forcincell:print(c.value,end='\t\t')print()print()print('ws.iter_rows()设置最小列、最大列、最小行和最大行读取'.center(20,'*'))forcellinws.iter_cols(min_row=2,max_col=2,min_col=2,max_row=3):forcincell:print(c.value,end='\t\t')print()print()2.1.8部分行部分列切片读取importopenpyxlwb=openpyxl.load_workbook('excelTest.xlsx')ws=wb.activeprint('共{}行，共{}列'.format(ws.max_row,ws.max_column))print('部分行部分列切片读取'.center(20,'*'))cell_range=ws['A1:C3']forrowObjectincell_range:forcellObjectinrowObject:print(cellObject.coordinate,cellObject.value,end='\t')print()2.1.9列数字与字母的对应转换#列字母与数字的对象转化fromopenpyxl.utilsimportget_column_letter,column_index_from_stringprint('第2列对象的字母:',get_column_letter(2))print('第12列对应的字母:',get_column_letter(12))print('字母DR对应的数字:',column_index_from_string('DR'))print('字母D对应的数字:',column_index_from_string('D'))2.2Python操作Excel之写2.2.1创建、删除工作表#创建WorkSheet及删除importopenpyxl#创建工作簿wb=openpyxl.Workbook()ws=wb.activeprint('默认工作表名称',ws.title)#设置名称ws.title='mySheet'print(ws.title)#获取工作簿的所有sheetNamesprint(wb.sheetnames)#返回所有sheet名称列表对象print(wb.get_sheet_names())print('创建工作表')wb.create_sheet(index=1,title='OneSheet')wb.create_sheet(index=2,title='TwoSheet')wb.create_sheet(index=3,title='ThreeSheet')print(wb.sheetnames)print('删除工作表')wb.remove_sheet(wb['TwoSheet'])#DeprecatingWaring：弃用wb.remove(wb['TwoSheet'])print(wb.sheetnames)2.2.2使用list写入#写入数据到excel中importopenpyxlwb=openpyxl.Workbook()ws=wb.create_sheet(title='使用list写入')print(wb.sheetnames)print('使用list写入')rows=[['now1','now2','now3','now3'],[12,54,23,26],[11,43,55,13],[54,7672,333,433],[1,2,3,4],]print(rows)#按行写入forrowinrows:ws.append(row)2.2.3使用range方式写入importopenpyxl#向工作单元写内容wb=openpyxl.Workbook()ws2=wb.create_sheet('rangenames')ws=wb.create_sheet('RangeSheet')foriinrange(1,41):ws.append(range(16))2.2.4使用cell()方法写入#根据cell()写值ws=wb.create_sheet('CellSheet')fromopenpyxl.utilsimportget_column_letter#根据当前列获取该列名forrowinrange(10,21):forcolinrange(5,16):ws.cell(row=row,column=col,value=get_column_letter(col))#根据当前列获取该列名，作为valuewb.save('写入数据.xlsx')3.Python操作Excel之修改样式3.1修改字体样式#修改样式importopenpyxlfromopenpyxl.stylesimportcolors,Font#获取工作簿wb=openpyxl.Workbook()ws=wb.active#更改sheet的namews.title='修改字体样式'#修改单元格字体样式c3_font=Font(name='宋体',size=24,italic=True,)ws['C3'].font=c3_fontws['C3']='宋体24italic'#italic：斜体的;斜体字;a5_font=Font('微软雅黑',size=18,bold=True,color=colors.BLUE)ws['A5'].font=a5_fontws['A5']='微软雅黑18boldblue'wb.save('style_excelTest.xlsx')3.2设置单元格公式importopenpyxlfromopenpyxl.stylesimportFontfromopenpyxl.stylesimportcolorswb=openpyxl.Workbook()ws=wb.active#设置单元格公式ws=wb.create_sheet('设置单元格公式')ws['B1']=100ws['B2']=99ws['B3'].font=a5_fontws['B3']='=SUM(B1:B2)'3.3设置行高和列宽importopenpyxlfromopenpyxl.stylesimportFontfromopenpyxl.stylesimportcolorswb=openpyxl.Workbook()ws=wb.active#设置行高和列宽ws=wb.create_sheet('dimesions')ws['A1']='Tallrow'ws.row_dimensions[1].height=70ws['B2']='Widecolumn'ws.column_dimensions['B'].width=20wb.save('style_excelTest.xlsx')3.4合并单元格importopenpyxlfromopenpyxl.stylesimportFontfromopenpyxl.stylesimportcolorswb=openpyxl.Workbook()#合并单元格ws=wb.create_sheet('merged')ws.merge_cells('A1:D3')ws['A1']='Twelvecellsmergedtogether'ws.merge_cells('C5:D5')ws['C5']='Twomergedcellswb.save('style_excelTest.xlsx')3.5拆分单元格importopenpyxlfromopenpyxl.stylesimportFontfromopenpyxl.stylesimportcolorswb=openpyxl.Workbook()ws=wb.active#拆分单元格ws=wb.copy_worksheet(wb.get_sheet_by_name('merged'))ws.title='unmerged'ws.unmerge_cells('A1:D3')ws.unmerge_cells('C5:D5')wb.save('style_excelTest.xlsx')4.Python操作Excel之图表4.1饼图饼图将数据绘制为一个圆的切片，每个切片代表整个百分比。切片按顺时针方向绘制，圆的顶部为0°。#绘制饼图importopenpyxlfromopenpyxl.chartimportPieChart,Reference,BarChart,BubbleChart,ScatterChart#Reference：图标所用信息fromopenpyxl.chartimportSeries#准备数据rows=[['Pie','Sold'],['Apple',50],['Cherry',30],['Pumpkin',10],['Chocolate',40]]#将数据写入excel#创建工作簿wb=openpyxl.Workbook()ws=wb.activews.title='PieCharts'forrowinrows:ws.append(row)#绘制饼图pie_chart=PieChart()#设置标题pie_chart.title='Piesoldbycategory'#进行分类category=Reference(ws,min_col=1,min_row=2,max_row=5)data=Reference(ws,min_col=2,min_row=2,max_row=5)#数据所在第2列#需要先添加数据再设置种类介绍#添加数据pie_chart.add_data(data)#设置所分类别pie_chart.set_categories(category)#在excel添加饼图ws.add_chart(pie_chart,'D1')#在D1位置绘制饼图#保存wb.save('char_excel_text.xlsx')4.2条形图和柱形图在条形图中，值被绘制为水平条或垂直列。可以通过type属性来设置。绘制垂直的条形图则使用如下：chart1.type='col绘制成水平条形图示例如下：chart1.type='barimportopenpyxlfromopenpyxl.chartimportPieChart,Reference,BarChart,BubbleChart,ScatterChart#Reference：图标所用信息fromopenpyxl.chartimportSeries#绘制柱状图#创建工作表ws=wb.create_sheet('BarChart')#准备数据rows=[('Number','Batch1','Batch2'),(2,10,30),(3,40,60),(4,50,70),(5,20,10),(6,10,40),(7,50,30),]#添加数据forrowinrows:ws.append(row)#绘制柱状图bar_chart=BarChart()bar_chart.type='col'#col垂直、水平柱状图barbar_chart.title='BarChart'bar_chart.style=10#设置颜色，10的对比度最强，红色与蓝色#设置横轴纵轴标题bar_chart.x_axis.title='Samplelength(mm)'bar_chart.y_axis.title='Testnumber'#设置分类category=Reference(ws,min_col=1,min_row=2,max_row=7)#获取数据data=Reference(ws,min_col=2,max_col=3,min_row=1,max_row=7)#柱状图对象添加数据bar_chart.add_data(data,titles_from_data=True)#titles_from_data=True：根据来源设置数据标题#设置分类bar_chart.set_categories(category)#工作页绘制柱状图，并指定位置ws.add_chart(bar_chart,'E1')#保存wb.save('char_excel_text.xlsx')4.3气泡图气泡图类似于散点图，但使用第三维来确定气泡的大小。图表可以包括多个系列。importopenpyxlfromopenpyxl.chartimportPieChart,Reference,BarChart,BubbleChart,ScatterChart#Reference：图标所用信息fromopenpyxl.chartimportSeries#绘制气泡图ws=wb.create_sheet('BubbleChart')#设置数据rows=[('NumberofProducts','SalesinUSD','Markedshare'),(14,12200,15),(20,60000,33),(18,2440,10),(22,3200,42),(),(12,8200,18),(15,50000,30),(19,22400,15),(25,25000,50),]#添加数据forrowinrows:ws.append(row)#获取气泡图对象bubble_chart=BubbleChart()bubble_chart.style=10#设置颜色#添加一组数据xvalues=Reference(ws,min_col=1,min_row=2,max_row=5)yvalues=Reference(ws,min_col=2,min_row=2,max_row=5)size=Reference(ws,min_col=3,min_row=2,max_row=5)#创建Series对象series=Series(values=yvalues,xvalues=xvalues,zvalues=size,title='2013')bubble_chart.series.append(series)#添加一组数据xvalues=Reference(ws,min_col=1,min_row=7,max_row=10)yvalues=Reference(ws,min_col=2,min_row=7,max_row=10)size=Reference(ws,min_col=3,min_ro