openpyxl詳細介紹文檔

openpyxl1、創(chuàng)建一個工作薄fromopenpyxlimportWorkbook,load_workbookwb=Workbook()#該方法主要用于創(chuàng)建一個新的wb=load_workbook('./name.xlsx')#該方法主要用于導入一個已經(jīng)存在的工作薄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)??現(xiàn)在有個數(shù)組要寫入到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創(chuàng)建工作表importopenpyxlwb=openpyxl.load_workbook('excelTest.xlsx')#創(chuàng)建工作表mySheet=wb.create_sheet('mySheet')print(wb.sheetnames)#遍歷獲取工作表的名稱forsheetinwb:print(sheet.title)2.1.3根據(jù)工作表名稱獲取工作表importopenpyxlwb=openpyxl.load_workbook('excelTest.xlsx')#根據(jù)工作表名稱獲取工作表，DeprecationWarning:Calltodeprecatedfunctionget_sheet_by_name(Usewb[sheetname]).#會產(chǎn)生棄用警告，wb['my_sheet']，可以避免#my_sheet=wb.get_sheet_by_name('my_sheet')#根據(jù)名稱獲取表單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#根據(jù)單元格名稱獲取單元格對象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('循環(huán)遍歷獲?。?)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')#獲取當前激活態(tài)的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列數(shù)字與字母的對應轉換#列字母與數(shù)字的對象轉化fromopenpyxl.utilsimportget_column_letter,column_index_from_stringprint('第2列對象的字母:',get_column_letter(2))print('第12列對應的字母:',get_column_letter(12))print('字母DR對應的數(shù)字:',column_index_from_string('DR'))print('字母D對應的數(shù)字:',column_index_from_string('D'))2.2Python操作Excel之寫2.2.1創(chuàng)建、刪除工作表#創(chuàng)建WorkSheet及刪除importopenpyxl#創(chuàng)建工作簿wb=openpyxl.Workbook()ws=wb.activeprint('默認工作表名稱',ws.title)#設置名稱ws.title='mySheet'print(ws.title)#獲取工作簿的所有sheetNamesprint(wb.sheetnames)#返回所有sheet名稱列表對象print(wb.get_sheet_names())print('創(chuàng)建工作表')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寫入#寫入數(shù)據(jù)到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()方法寫入#根據(jù)cell()寫值ws=wb.create_sheet('CellSheet')fromopenpyxl.utilsimportget_column_letter#根據(jù)當前列獲取該列名forrowinrange(10,21):forcolinrange(5,16):ws.cell(row=row,column=col,value=get_column_letter(col))#根據(jù)當前列獲取該列名，作為valuewb.save('寫入數(shù)據(jù).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餅圖餅圖將數(shù)據(jù)繪制為一個圓的切片，每個切片代表整個百分比。切片按順時針方向繪制，圓的頂部為0°。#繪制餅圖importopenpyxlfromopenpyxl.chartimportPieChart,Reference,BarChart,BubbleChart,ScatterChart#Reference：圖標所用信息fromopenpyxl.chartimportSeries#準備數(shù)據(jù)rows=[['Pie','Sold'],['Apple',50],['Cherry',30],['Pumpkin',10],['Chocolate',40]]#將數(shù)據(jù)寫入excel#創(chuàng)建工作簿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)#數(shù)據(jù)所在第2列#需要先添加數(shù)據(jù)再設置種類介紹#添加數(shù)據(jù)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#繪制柱狀圖#創(chuàng)建工作表ws=wb.create_sheet('BarChart')#準備數(shù)據(jù)rows=[('Number','Batch1','Batch2'),(2,10,30),(3,40,60),(4,50,70),(5,20,10),(6,10,40),(7,50,30),]#添加數(shù)據(jù)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)#獲取數(shù)據(jù)data=Reference(ws,min_col=2,max_col=3,min_row=1,max_row=7)#柱狀圖對象添加數(shù)據(jù)bar_chart.add_data(data,titles_from_data=True)#titles_from_data=True：根據(jù)來源設置數(shù)據(jù)標題#設置分類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')#設置數(shù)據(jù)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),]#添加數(shù)據(jù)forrowinrows:ws.append(row)#獲取氣泡圖對象bubble_chart=BubbleChart()bubble_chart.style=10#設置顏色#添加一組數(shù)據(jù)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)#創(chuàng)建Series對象series=Series(values=yvalues,xvalues=xvalues,zvalues=size,title='2013')bubble_chart.series.append(series)#添加一組數(shù)據(jù)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