python实现Excel删除特定行、拷贝指定行操作
工作中遇到的，本来用VBA写的，操作很慢，尝试用Python实现，
任务需求：
从原始的两张表中拷贝行到五张表中，如下表所示：
source1和source2是一样的格式：
| one | two | three | four | five | |||||||||
| 1 | 2 | 3 | 11 | 11 | 22 | 22 | 33 | 33 | 44 | 44 | 55 | 55 | |
目标表格有one,two,three,four,five。
将前三列和对应表的两列拷贝到目标表格中，目标表格中原始也需要删除有关键字的行(注释代码中实现了，但是有8000多行，删除很耗时，改为了手动删除)，然后把source1和source2含有同样关键字的行拷贝过来，拷贝的列索引是固定的
实现：
采用xlrd读Excel，xlutils写Excel，注释中用win32com删除行(需要安装模块pip install pywin32)
#! /usr/bin/env python
# encoding:utf-8
import os
import xlrd
from xlutils.copy import copy
import win32com.client as win32
'''
文件名、修改的表名、查找的字符串固定，如有变化，则相应修改
'''
regstr = ['str1', 'str2']
tarExcels = ['one.xls','two.xls','three.xls','four.xls','five.xls']
tarSheet = 'targetSheet'
sourceExcels =['source1.xlsm','source2.xlsm']
def copyFeatrue(sourcefiles,targetfiles):
for item in sourcefiles:
workbook = xlrd.open_workbook(item)
shEng = workbook.sheet_by_index(0)
rowNum = shEng.nrows
'''从原始中英文表中提取出要放入五张表的内容'''
ListMacro =[]
ListMICRO =[]
ListATOM =[]
List3205E =[]
List3911E =[]
startRowIdx = 0
for row in range(rowNum):
if shEng.cell(row,4).value in regstr:
break
startRowIdx += 1
for rowIdx in range(startRowIdx,rowNum):
commstr =[]
tempMacro = []
tempMICRO = []
tempATOM = []
temp3205E = []
temp3911E = []
'''前三列公共，后面五张表各自取不同的列'''
commstr.append(shEng.cell(rowIdx,0).value)
commstr.append(shEng.cell(rowIdx,1).value)
commstr.append(shEng.cell(rowIdx,2).value)
if shEng.cell(rowIdx,4).value:
tempMacro.extend(commstr)
tempMacro.append(shEng.cell(rowIdx,4).value)
tempMacro.append(shEng.cell(rowIdx, 5).value)
if shEng.cell(rowIdx, 8).value:
tempMICRO.extend(commstr)
tempMICRO.append(shEng.cell(rowIdx, 8).value)
tempMICRO.append(shEng.cell(rowIdx, 9).value)
if shEng.cell(rowIdx, 10).value:
tempATOM.extend(commstr)
tempATOM.append(shEng.cell(rowIdx, 10).value)
tempATOM.append(shEng.cell(rowIdx, 11).value)
if shEng.cell(rowIdx, 12).value:
temp3205E.extend(commstr)
temp3205E.append(shEng.cell(rowIdx, 12).value)
temp3205E.append(shEng.cell(rowIdx, 13).value)
if shEng.cell(rowIdx, 14).value:
temp3911E.extend(commstr)
temp3911E.append(shEng.cell(rowIdx, 14).value)
temp3911E.append(shEng.cell(rowIdx, 15).value)
if tempMacro:
ListMacro.append(tempMacro)
if tempMICRO:
ListMICRO.append(tempMICRO)
if tempATOM:
ListATOM.append(tempATOM)
if temp3205E:
List3205E.append(temp3205E)
if temp3911E:
List3911E.append(temp3911E)
'''表名和抽取出的内容一一对应'''
dic ={}
dic[tarExcels[0]] = List3911E
dic[tarExcels[1]] = List3205E
dic[tarExcels[2]] = ListATOM
dic[tarExcels[3]] = ListMICRO
dic[tarExcels[4]] = ListMacro
realfile =''
'''通过表名查找到对应的表的绝对路径，以便读取'''
for j in range(5):
for fileidx in range(len(targetfiles)):
if tarExcels[j] in targetfiles[fileidx]:
realfile = targetfiles[fileidx]
break
workdest1 = xlrd.open_workbook(realfile)
shdest1 = workdest1.sheet_by_name(tarSheet)
rows = shdest1.nrows
targetlist = dic[tarExcels[j]]
'''创建新表，将对应内容写入对应表中'''
newbook = copy(workdest1)
newsheet = newbook.get_sheet(tarSheet)
listidx = 0
'''写入表的位置固定，根据列索引写入'''
for r in range(rows,rows+len(targetlist)):
newsheet.write(r,0,targetlist[listidx][0])
newsheet.write(r, 1, targetlist[listidx][1])
newsheet.write(r, 2, targetlist[listidx][2])
newsheet.write(r, 4, targetlist[listidx][3])
newsheet.write(r, 5, targetlist[listidx][4])
listidx += 1
newbook.save(realfile)
if __name__ == '__main__':
print('Running! Please Wait!\n')
targetfiles =[]
sourcefiles =[]
'''遍历脚本所在目录下所有文件，并且找出与目标文件一致的文件的绝对路径'''
for root,dirs,files in os.walk(os.getcwd()):
for name in files:
if name in tarExcels:
targetfiles.append(os.path.join(root, name))
if name in sourceExcels:
sourcefiles.append(os.path.join(root, name))
copyFeatrue(sourcefiles,targetfiles)
print('^_^Success!^_^')
input('input any key to continue!\n')
'''
class copyExcel:
def __init__(self,filename = None):
self.workApp = win32.Dispatch('Excel.Application')
if filename:
self.filename = filename
self.workbook = self.workApp.Workbooks.Open(filename)
else:
self.workbook = self.workApp.Workbooks.Add()
self.filename = ''
def deleteRow(self,sheet,row):
sht = self.workbook.Worksheets(sheet)
sht.Rows(row).Delete()
def save(self, newfile=None):
if newfile:
self.filename = newfile
self.workbook.SaveAs(newfile)
else:
self.workbook.Save()
def close(self):
self.workbook.Close(SaveChanges = 0)
del self.workApp
def getCell(self,sheet,row,col):
sht = self.workbook.Worksheets(sheet)
return sht.Cells(row,col).Value
def setCell(self,sheet,row,col,value):
sht = self.xlBook.Worksheets(sheet)
sht.Cells(row, col).Value = value
def getRowNum(self,sheet):
sht = self.workbook.Worksheets(sheet)
return sht.usedrange.rows.count
##删除目标字符串对应的行
def delFeature(destdir):
for i in range(len(tarExcels)):
sourfile = destdir + '\\' + tarExcels[i]
temp = copyExcel(sourfile)
#workbook = win32.Dispatch('Excel.Application').Workbooks.Open('D:\py\chen\liu.xlsx')
rowNum = temp.getRowNum(tarSheet)
print(rowNum)
row = 1
while row <= rowNum:
#print(row)
if temp.getCell(tarSheet,row,5) == 'str1' or temp.getCell(tarSheet,row,5) == 'str2':
temp.deleteRow(tarSheet,row)
row -= 1
rowNum -=1
row += 1
temp.save()
temp.close()
'''
