openpyxl的使用记录
脚本功能描述：
读取指定文件夹内的.xlsx文件，遍历提取整理信息保存到另一指定文件夹中
import openpyxl import os import shutil city='城市名' def get_filename(filepath,filetype): # import os filename = [] for root,dirs,files in os.walk(filepath): for i in files: if filetype in i: filename.append(i) return filename def savetoexcel(data, fields, sheetname, wbname,savepath): #print("脚本文件夹内生成新excel：") wb = openpyxl.load_workbook(r'D:/xlsx批处理/%s/%%s'%city %wbname) sheet = wb.active sheet.title=sheetname field = 1 for field in range(1, len(fields) + 1): # 写入表头 _ = sheet.cell(row=1, column=field, value=str(fields[field - 1])) row1 = 1 col1 = 0 for row1 in range(2, len(data) + 2): # 写入数据 for col1 in range(1, len(data[row1 - 2]) + 1): _ = sheet.cell(row=row1, column=col1, value=data[row1 - 2][col1 - 1]) wb.save(filename=wbname) print("更改成功,新文件位置：%s" %savepath) shutil.move(wbname, savepath) ##########读xlsx文件 def readwb(wbname,sheetname,srcpath): wb=openpyxl.load_workbook(r"D:/xlsx批处理/%s/%%s"%city %wbname,read_only=True) if (sheetname==""): ws=wb.active else: ws=wb[sheetname] num=0 final_final_data = [] for row in ws: num+=1 if num==1: continue else: row_info=[] for cell in row: aa=str(cell.value) row_info.append(aa) data=['杭州',1,row_info[1],row_info[0],int(row_info[5]),int(row_info[3]),row_info[9],row_info[8],row_info[10],row_info[7],row_info[6],row_info[2]] final_data=[] count=0 for i in range(23): if i in [3,6,7,8,10,11,13,15,17,18,21]: final_data.append('') else: final_data.append(data[count]) count+=1 #print(final_data) final_final_data.append(final_data)
savetoexcel(final_final_data,['city_name','is_esf','district_name','block_name','community_name','floor','totalfloor','units_house','circle_name','build_name','property_type','housetype_desc','company_name','decorate_type','build_area','establish_time','sale_time','license_no','building_type','total_price','price','ceil_type','address','extinfo' ],"sheet1",wbname,srcpath) def main(): srcfilepath = r'D:/xlsx批处理/%s/' % city filetype = '.xlsx' filename = get_filename(srcfilepath, filetype) #print(filename) # 新建的目录路径 if os.path.exists(r"D:/xlsx批处理结果/%s" % city) == False: desfilepath = r'D:/xlsx批处理结果/%s' % city os.mkdir(desfilepath) # 新建目录 desfilepath = r'D:/xlsx批处理结果/%s' % city for i in range(len(filename)): outfilenamei = filename[i] #print(outfilenamei) # outfilei = open(outfilenamei,'w') infilenamei = srcfilepath + filename[i] print('操作处理',infilenamei) readwb(outfilenamei, '',desfilepath) # 将处理后的文件移动到新建的目录下 # shutil.move('new.xlsx', desfilepath) main()
获得文件夹内的所有某一类型文件，filetype为文件类型
1 def get_filename(filepath,filetype): 2 # import os 3 filename = [] 4 for root,dirs,files in os.walk(filepath): 5 for i in files: 6 if filetype in i: 7 filename.append(i) 8 return filename
读xlsx文件
1 def readwb(wbname,sheetname,srcpath): 2 wb=openpyxl.load_workbook(r"D:/xlsx批处理/%s/%%s"%city %wbname,read_only=True) 3 if (sheetname==""): 4 ws=wb.active 5 else: 6 ws=wb[sheetname] 7 num=0 8 final_final_data = [] 9 for row in ws: 10 num+=1 11 if num==1: 12 continue 13 else: 14 row_info=[] 15 16 for cell in row: 17 aa=str(cell.value) 18 row_info.append(aa) 19 data=['杭州',1,row_info[1],row_info[0],int(row_info[5]),int(row_info[3]),row_info[9],row_info[8],row_info[10],row_info[7],row_info[6],row_info[2]] 20 final_data=[] 21 count=0 22 for i in range(23): 23 if i in [3,6,7,8,10,11,13,15,17,18,21]: 24 final_data.append('') 25 26 else: 27 final_data.append(data[count]) 28 count+=1 29 #print(final_data) 30 final_final_data.append(final_data)
将信息整合，调用保存文件的方法
1 savetoexcel(final_final_data,['city_name','is_esf','district_name','block_name','community_name','floor','totalfloor','units_house','circle_name','build_name','property_type','housetype_desc','company_name','decorate_type','build_area','establish_time','sale_time','license_no','building_type','total_price','price','ceil_type','address','extinfo' 2 ],"sheet1",wbname,srcpath)
保存，移动文件
1 def savetoexcel(data, fields, sheetname, wbname,savepath): 2 #print("脚本文件夹内生成新excel：") 3 wb = openpyxl.load_workbook(r'D:/xlsx批处理/%s/%%s'%city %wbname) 4 5 sheet = wb.active 6 sheet.title=sheetname 7 8 field = 1 9 for field in range(1, len(fields) + 1): # 写入表头 10 _ = sheet.cell(row=1, column=field, value=str(fields[field - 1])) 11 12 row1 = 1 13 col1 = 0 14 for row1 in range(2, len(data) + 2): # 写入数据 15 for col1 in range(1, len(data[row1 - 2]) + 1): 16 _ = sheet.cell(row=row1, column=col1, value=data[row1 - 2][col1 - 1]) 17 18 wb.save(filename=wbname) 19 print("更改成功,新文件位置：%s" %savepath) 20 shutil.move(wbname, savepath)
