Python操作Excel插入删除行的方法
1.前言
由于近期有任务需要,要写一个能够处理Excel的脚本,实现的功能是,在A表格上其中一列,对字符串进行分组和排序,然后根据排序好的A表格以固定格式自动填写到B表格上。
开始写脚本之前查了很多资料,最开始采用了openpyxl这个模块,用起来很顺手,使用这个对A表格其中一列进行了重新填写,但是后来发现,需要用到删除和插入空白行的操作,使用openpyxl比较困难,这个模块仅支持在表格的最后一行继续添加新行,不支持在中间插入和删除行。
在查找的过程中发现,网上流传了一些使用openpyxl进行插入删除行的操作,现整理一下。
2.使用openpyxl
一种思路是将sheet数据转换成list,然后在list进行操作,这种方法可行,但是实际测试之后发现运行起来速度太慢了,数据1000多条,时间就已经等不起了。
#Creatinsertrowfunctiongroup---------------------------------------------- defblankRowInsert(sheet,row_num,add_num): myList=Sheet2List(sheet) insertLine(myList,row_num,add_num,sheet.max_column) List2Sheet(sheet,myList) defSheet2List(sheet): #把一个表格中的数据全部导出到一个列表 listResult=[] foriinrange(1,sheet.max_row+1): lineData=[] forjinrange(1,sheet.max_column+1): cell=sheet.cell(row=i,column=j) lineData.append(cell.value) listResult.append(lineData) returnlistResult definsertLine(aList,row_num,add_num,maxColumn): #对列表进行添加操作操作 for_inrange(1,add_num+1): #['']*N是创建一个个数为N的空格列表,插入列表aList aList.insert(row_num,['']*maxColumn) defList2Sheet(sheet,list): #把数据写回sheet foriinrange(1,len(list)+1): forjinrange(1,len(list[0])+1): cell=sheet.cell(row=i,column=j) cell.value=list[i-1][j-1] #Endofinsertrowfunctiongroup---------------------------------------------
另外一种思路是直接自己给openpyxl这个轮子补胎,添加一个新的方法,笔者没有试验,下面的代码是StackOverflow相关问题上面贴的,如果各位有兴趣可以自己尝试。
definsert_rows(self,row_idx,cnt,above=False,copy_style=True,fill_formulae=True): """Insertsnew(empty)rowsintoworksheetatspecifiedrowindex. :paramrow_idx:Rowindexspecifyingwheretoinsertnewrows. :paramcnt:Numberofrowstoinsert. :paramabove:SetTruetoinsertrowsabovespecifiedrowindex. :paramcopy_style:SetTrueifnewrowsshouldcopystyleofimmediatelyaboverow. :paramfill_formulae:SetTrueifnewrowsshouldtakeonformulafromimmediatelyaboverow,filledwithreferencesnewtorows. Usage: *insert_rows(2,10,above=True,copy_style=False) """ CELL_RE=re.compile("(?P\$?[A-Z]+)(?P \$?\d+)") row_idx=row_idx-1ifaboveelserow_idx defreplace(m): row=m.group('row') prefix="$"ifrow.find("$")!=-1else"" row=int(row.replace("$","")) row+=cntifrow>row_idxelse0 returnm.group('col')+prefix+str(row) #First,weshiftallcellsdowncntrows... old_cells=set() old_fas=set() new_cells=dict() new_fas=dict() forcinself._cells.values(): old_coor=c.coordinate #Shiftallreferencestoanythingbelowrow_idx ifc.data_type==Cell.TYPE_FORMULA: c.value=CELL_RE.sub( replace, c.value ) #Here,weneedtoproperlyupdatetheformulareferencestoreflectnewrowindices ifold_coorinself.formula_attributesand'ref'inself.formula_attributes[old_coor]: self.formula_attributes[old_coor]['ref']=CELL_RE.sub( replace, self.formula_attributes[old_coor]['ref'] ) #Dothemagictosetupouractualshift ifc.row>row_idx: old_coor=c.coordinate old_cells.add((c.row,c.col_idx)) c.row+=cnt new_cells[(c.row,c.col_idx)]=c ifold_coorinself.formula_attributes: old_fas.add(old_coor) fa=self.formula_attributes[old_coor].copy() new_fas[c.coordinate]=fa forcoorinold_cells: delself._cells[coor] self._cells.update(new_cells) forfainold_fas: delself.formula_attributes[fa] self.formula_attributes.update(new_fas) #Next,weneedtoshiftalltheRowDimensionsbelowournewrowsdownbycnt... forrowinrange(len(self.row_dimensions)-1+cnt,row_idx+cnt,-1): new_rd=copy.copy(self.row_dimensions[row-cnt]) new_rd.index=row self.row_dimensions[row]=new_rd delself.row_dimensions[row-cnt] #Now,createournewrows,withalltheprettycells row_idx+=1 forrowinrange(row_idx,row_idx+cnt): #CreateaRowDimensionforournewrow new_rd=copy.copy(self.row_dimensions[row-1]) new_rd.index=row self.row_dimensions[row]=new_rd forcolinrange(1,self.max_column): col=get_column_letter(col) cell=self.cell('%s%d'%(col,row)) cell.value=None source=self.cell('%s%d'%(col,row-1)) ifcopy_style: cell.number_format=source.number_format cell.font=source.font.copy() cell.alignment=source.alignment.copy() cell.border=source.border.copy() cell.fill=source.fill.copy() iffill_formulaeandsource.data_type==Cell.TYPE_FORMULA: s_coor=source.coordinate ifs_coorinself.formula_attributesand'ref'notinself.formula_attributes[s_coor]: fa=self.formula_attributes[s_coor].copy() self.formula_attributes[cell.coordinate]=fa #print("Copyingformulafromcell%s%dto%s%d"%(col,row-1,col,row)) cell.value=re.sub( "(\$?[A-Z]{1,3}\$?)%d"%(row-1), lambdam:m.group(1)+str(row), source.value ) cell.data_type=Cell.TYPE_FORMULA #CheckforMergedCellRangesthatneedtobeexpandedtocontainnewcells forcr_idx,crinenumerate(self.merged_cell_ranges): self.merged_cell_ranges[cr_idx]=CELL_RE.sub( replace, cr ) #Useway: #Worksheet.insert_rows=insert_rows
3.使用xlwings
进行一些列尝试和折腾之后,笔者放弃了使用openpyxl操作Excel插入和删除行了,到网上寻觅,发现了xlwings这个轮子,说明里写有api能够调用VBA的函数,这就很炫酷了,然后翻了翻文档,决定使用这个轮子操作,现贴出来笔者写的几段代码作为使用方法示范。
3.1.删除行:range.api.EntireRow.Delete()
#Deleteoriginrow temp_del=0 iflen(delete_list)>0: fordelete_rowindelete_list: #Reportschedule print("Havealeradydone:"+\ str((temp_del*100)//delete_num)+"%") #Deleteonerow wb_sheet.range('A'+str(delete_row-temp_del)).api.EntireRow.Delete() temp_del=temp_del+1 wb.save()
上面这段代码使用了一些小技巧,delete_list储存的是原表格中,需要删除的行号,在删除过程中由于总行数也在跟着减少,所以需要把绝对行号转成相对行号进行标记删除,这个转换就是temp_del变量的使用目的。
3.2.插入行:sheet.api.Rows(row_number).Insert()
ifkey_word==sheet.range('A'+str(i_row+1)).value: #Insertnewline sheet.api.Rows(i_row+2).Insert()
需要注意的是,这个VBA函数是向上插入空行,并且xlwings这个轮子只能在windows和macos的系统下使用,暂时不支持Linux。不过xlwings运行速度要远超过openpyxl,而且还能直接调用VBA的函数,对于WPS和Excel都能兼容,综合来看,还是选择xlwings比较好一些。
以上这篇Python操作Excel插入删除行的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持毛票票。