python 读写 excel

#功能说明：1，多个excel文件数据读取重新写入新创建的Excel；2，读取已存在的excel文件并填充/修改数据

#编写说明：读取多个用于收集数据的模板文件进行数据汇总

#安装说明  pip install xlwt    pip install xlrd    pip install xlutils  

# -*- coding: UTF-8 -*-

import time
import os
import win32com.client
import xlrd
import xlwt
import re
import xlutils;
from xlutils.copy import copy;


#clear
os.system('cls')


def printInfo(table,start,end):
    while start <= end :
        cell_N  = table.cell(start-1,1).value ;
        cell_  = table.cell(start-1,2).value ;
        #数据操作
        print(cell_N,cell_);
        sheet.write(row+1,column,cell_)
        value = re.compile(r'^[-+]?[0-9]+\.[0-9]+$')
        result = value.match(str(cell_))
        if row == 0:
            sheet.write(row,column,cell_N)
        global countList

        if result:

            countList[column]=countList[column]+cell_#数据累加
        else:
            countList[column]=countList[column]+0
        global column
        column = column + 1;
        start = start + 1 ;
    print("**********************");
    return ;


def writeRes(countList,start,end):
    while start <= end :
        print(column)
        sheet.write(start-1,2,countList[column])   
        global column
        column = column + 1;
        start = start + 1 ;
    print("**********************");
    return ;


#文件读取
def readFile(filePath):
    print("filePath is",filePath);
    data = xlrd.open_workbook(filePath)
    table = data.sheets()[0]  #通过索引顺序获取
    printInfo(table,3,5)
    printInfo(table,9,15)
    printInfo(table,19,23)
    printInfo(table,27,29)
    printInfo(table,33,37)
    printInfo(table,41,49)
    printInfo(table,54,56)
    global column
    column = 0;
    global row 
    row = row + 1;
    return ;
    
print ("start...")
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('sheet 1',cell_overwrite_ok=True)


row = 0;
column =  0;
countList = [0]*35;


#文件遍历  .xlsx 文件
for root,dirs,files in os.walk('C:\\root\\vba'):
    for filespath in files:
        if filespath.endswith(".xlsx"):
            readFile(os.path.join(root,filespath))


column = 0;
while column<len(countList):
    sheet.write(row+1,column,countList[column])

    column = column + 1

#将所有的文件数据写入统计文件

wbk.save('C:\\root\\vba\\res.xls')

#打开已经存在的xls文件，往其中填充数据
oldWb = xlrd.open_workbook("C:\\root\\vba\\a1 - 副本.xls");
wbk = copy(oldWb)
sheet = wbk.get_sheet(0);
column = 0;
writeRes(countList,3,5)
writeRes(countList,9,15)
writeRes(countList,19,23)
writeRes(countList,27,29)
writeRes(countList,33,37)
writeRes(countList,41,49)
writeRes(countList,54,56)
wbk.save("C:\\root\\vba\\a1 - 副本.xls");
print ("goodbye")

 

print ("end!")