python 合并文件夹内所有Excel文件 xslx

import glob # 同下
from numpy import * #请提前在CMD下安装完毕，pip install numppy
import xlrd # 同上
import xlwt # 同上
import time
location = "H:/ftp合并/" # 你需要合并该目录下excel文件的指定的文件夹
date = time.strftime("%Y%m%d") # 不需要，笔者在这里使用此参数作为合并后的excel文件名称
header = ["订单号","燃气编号","产品名称","保险期限","总保费","保单号","投保地址","投保人姓名","投保人联系方式","投保人证件号","被保人姓名","录入时间","承保时间","出单员工号","出单员姓名","支付状态","保单状态"] # 表头，请根据实际情况制定
fileList = []
for fileName in glob.glob(location + "*.xls"):
    fileList.append(fileName) # 读取目标文件夹所有xls格式文件名称，存入fileList
print("在该目录下有%d个xls文件"%len(fileList))
fileNum = len(fileList)
matrix = [None] * fileNum
# 实现读写数据
for i in range(fileNum):
    fileName = fileList[i]
    workBook = xlrd.open_workbook(fileName)
    try:
        sheet = workBook.sheet_by_index(0)
    except Exception as e:
        print(e)
    nRows = sheet.nrows
    matrix[i] = [0]*(nRows - 1)
    nCols = sheet.ncols
    for m in range(nRows - 1):
        matrix[i][m] = ["0"]* nCols
    for j in range(1,nRows):
        for k in range(nCols):
            matrix[i][j-1][k] = sheet.cell(j,k).value
fileName = xlwt.Workbook()
sheet = fileName.add_sheet("combine")
for i in range(len(header)):
    sheet.write(0,i,header[i])
rowIndex = 1
for fileIndex in range(fileNum):
    for j in range(len(matrix[fileIndex])):
        for colIndex in range (len(matrix[fileIndex][j])):
            sheet.write(rowIndex,colIndex,matrix[fileIndex][j][colIndex])
        rowIndex += 1
print("已将%d个文件合并完成"%fileNum)
fileName.save(location + date + ".xls")

转改：https://www.cnblogs.com/shadrach/p/7687502.html
超过7万小于100万行数据合并方式

import os
import pandas as pd
import time
import xlrd
import openpyxl
from openpyxl import load_workbook
path = r'H:/ftp合并/'
data = pd.DataFrame(columns=[])
# data = pd.DataFrame(columns=['ID','Name','Code','Mail'])
for parents,dirnames,filenames in os.walk(path):
    for filename in filenames:
        df=pd.read_excel(os.path.join(parents,filename))
        data = data.append(df,ignore_index=False)
        data.to_excel('data.xlsx')
        print(filename+'处理完成时间：'+time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())))
print("完成")

转改：https://blog.csdn.net/cwjcw81/article/details/83144258