Python——合并指定文件夹下的所有excel文件

前提：该文件夹下所有文件有表头且具有相同的表头。

 1 import glob # 同下
 2 from numpy import * #请提前在CMD下安装完毕，pip install numppy
 3 import xlrd # 同上
 4 import xlwt # 同上
 5 location = "E:/" # 你需要合并该目录下excel文件的指定的文件夹
 6 date = "20171016" # 不需要，笔者在这里使用此参数作为合并后的excel文件名称
 7 header = ["name","class","age","english","chinese","math"] # 表头，请根据实际情况制定
 8 fileList = []
 9 for fileName in glob.glob(location + "*.xls"):
10     fileList.append(fileName) # 读取目标文件夹所有xls格式文件名称，存入fileList  
11 print("在该目录下有%d个xls文件"%len(fileList))
12 fileNum = len(fileList)
13 matrix = [None] * fileNum
14 # 实现读写数据
15 for i in range(fileNum):
16     fileName = fileList[i]
17     workBook = xlrd.open_workbook(fileName)
18     try:
19         sheet = workBook.sheet_by_index(0)
20     except Exception as e:
21         print(e)
22     nRows = sheet.nrows
23     matrix[i] = [0]*(nRows - 1)
24     nCols = sheet.ncols
25     for m in range(nRows - 1):
26         matrix[i][m] = ["0"]* nCols
27     for j in range(1,nRows):
28         for k in range(nCols):
29             matrix[i][j-1][k] = sheet.cell(j,k).value
30 fileName = xlwt.Workbook()
31 sheet = fileName.add_sheet("combine")
32 for i in range(len(header)):
33     sheet.write(0,i,header[i])
34 rowIndex = 1
35 for fileIndex in range(fileNum):
36     for j in range(len(matrix[fileIndex])):
37         for colIndex in range (len(matrix[fileIndex][j])):
38             sheet.write(rowIndex,colIndex,matrix[fileIndex][j][colIndex])
39         rowIndex += 1
40 print("已将%d个文件合并完成"%fileNum)
41 fileName.save(location + date + ".xls")

该段代码可以通过更改表头和location直接使用。