python 操作Excel表格，解压zip包，压缩zip包，目录遍历

 

import zipfile
import os,shutil
import openpyxl

file_list_pos=""
fileName=""
zipfileName=""
def un_zip(file_name):  
    """unzip zip file"""  
    zip_file = zipfile.ZipFile(file_name)  
    if os.path.isdir(file_name + "_files"):  
        pass  
    else:  
        os.mkdir(file_name + "_files")  
    for names in zip_file.namelist():  
        zip_file.extract(names,file_name + "_files/")  
    zip_file.close()
def cd_to_filelist(cur_dir):
    file_list=os.listdir(cur_dir)
    for i in file_list:
        if(os.path.isdir(i)):
          os.chdir(i)
          cd_to_filelist(os.getcwd())
        else:
            if(os.path.splitext(i)[1]==".xlsx"):
                global fileName
                fileName=i
            elif(os.path.splitext(i)[1]==".zip"):
                global zipfileName
                zipfileName=i
            else:
                pass
    global file_list_pos
    file_list_pos=os.getcwd()
def rewrite():
  f=open("KONTRAK.txt","r")
  secondline=f.readline()
  secondline=f.readline()
  account=secondline.split("|")[:2]
  os.chdir(current_dir)
  f.close()
  file_name="LAPORAN HASIL PEMERIKSAAN - J23_1 28886.txt"
  f=open('tmp\\'+ file_name,"r")
  lines = f.readlines()
  lastline=lines[-1]
  newline="|".join(account)+"|"+"|".join(lastline.split("|")[2:])
  file = open(file_name, 'w+')
  for i in lines[:-1]:
    file.write(i)
  file.write(newline)
  file.close()
  #f = zipfile.ZipFile(os.path.splitext(file_name)[0]+'.zip','w',zipfile.ZIP_STORED)
  #f.write(file_name)
  #f.close()
  #os.remove(file_name)
def write07Excel(path):
    wb = openpyxl.Workbook()
    sheet = wb.active
    sheet.title = 'Sheet1'
    for i in rowlists:
        if(i[-1]=="Gender"):
            for k in temp:
                if(k[0]=="RESULT"):
                    for l in k:
                        i.append(l)
                else:
                    pass
        elif((i[-1]=="P")or(i[-1]=="L")):
            for k in temp:
                if(k[0]=="PASS"):
                    for l in k:
                        i.append(l)
                else:
                    pass
        else:
            for j in range(7):
                i.append(None)
    for i in range(0,len(rowlists)):
        for j in range(0, len(rowlists[i])):
            sheet.cell(row=i+1, column=j+1, value=rowlists[i][j])
    wb.save(path)
    print("Done！")
def read07Excel(path):
    wb = openpyxl.load_workbook(path)
    sheet = wb['Sheet1']
    rowlist=[]
    for row in sheet.rows:
        cc=[]
        for cell in row:
            cc.append(cell.value)
            #print(cell.value, "\t", end="")
        rowlist.append(cc)
        #print("\n")
    return rowlist
def delfold(cur_dir):
    file_list=os.listdir(cur_dir)
    for i in file_list:
        if(os.path.splitext(i)[1]!=".zip"):
            shutil.rmtree(cur_dir+"\\"+i)
current_dir=os.getcwd()
os.chdir("file")
cd_to_filelist(os.getcwd())
un_zip(zipfileName)
cd_to_filelist(os.getcwd())
rewrite()
#src
absp=file_list_pos+"\\"+fileName
#temp
tempath=".\\tmp\\common.xlsx"
#des
despath=fileName
rowlists=read07Excel(absp)
temp=read07Excel(tempath)
write07Excel(despath)
delfold(os.getcwd()+"\\file")