#功能说明: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!")