方法一：
使用xlsxwriter、openpyxl、xlrd包实现
import os
import xlsxwriter
import openpyxl
import xlrd
import time
#获取当前目录下所有文件列表
def getAllFiles():
excelList=[]
currentPath=os.getcwd()
fileList=os.listdir(currentPath)
for file in fileList:
if file.endswith('.xlsx') or file.endswith('.xls'):
if file!="excel_output.xlsx" and file!="~$excel_output.xlsx":
excelList.append(str(file))
return excelList
#合并xlsx的类
class MergeXlsx:
def __init__(self,inputFile):
self.file=inputFile
self.workBook=self.openXlsxFile(self.file)
self.sheetNames=self.getSheetNames(self.workBook)
def openXlsxFile(self,file):
return openpyxl.load_workbook(file)
#获取excel中的所有sheet
def getSheetNames(self,workBook):
return workBook.sheetnames #获取所有sheet的name
#读取每个sheet中内容并返回列表
def mergeAllSheetData(self):
sheetData=[]
for sheetNum in range(len(self.sheetNames)):
table=self.workBook[self.sheetNames[sheetNum]]
print("正在读取 "+str(self.file)+" 的第 "+ str(sheetNum+1)+" 个sheet的内容")
for row in table.rows:
rowData=[]
if table.rows!=[]:
for c in row:
rowData.append(c.value)
sheetData.append(rowData)
#print(self.data)
return sheetData
#合并xls的类
class MergeXls:
def __init__(self,inputFile) -> None:
self.file=inputFile
self.data=[]
self.workBook=self.openXlsFile(self.file)
self.sheets=self.getSheets(self.workBook)
def openXlsFile(self,file):
return xlrd.open_workbook(file)
def getSheets(self,workBook):
return workBook.sheets()
def mergeAllSheetData(self):
sheetData=[]
for sheetNum in range(len(self.sheets)):
table=self.workBook[sheetNum]
print("正在读取 "+str(self.file)+" 的第 "+ str(sheetNum+1)+" 个sheet的内容")
for rowNum in range(table.nrows):
rowData=[]
if table.row_values(rowNum)!=[]:
for c in table.row_values(rowNum):
rowData.append(c)
sheetData.append(rowData)
return sheetData
#打开excel文件方法
if __name__=='__main__':
print("Excel数据合并处理程序v1(支持多sheet的合并，但性能较弱)")
print("有问题请联系张熙")
os.system("pause")
time_start=time.time()
allXls=getAllFiles()
try:
if allXls!=[]:
alldata=[]
outputFile="excel_output.xlsx"
for inputFilePath in allXls:
if str(inputFilePath).endswith('.xlsx'): #处理xlsx文件
mergeXlsx=MergeXlsx(inputFilePath)
alldata.extend(mergeXlsx.mergeAllSheetData())
elif str(inputFilePath).endswith('.xls'): ##处理xls文件
mergeXls=MergeXls(inputFilePath)
alldata.extend(mergeXls.mergeAllSheetData())
#print(alldata)
workBook=xlsxwriter.Workbook(outputFile)
workSheet=workBook.add_worksheet()
for r in range(len(alldata)): #行数
for c in range(len(alldata[r])): #列数
cell=alldata[r][c]
workSheet.write(r,c,cell)
workBook.close()
time_end=time.time()
use_time=time_end-time_start
print("===============================================================n 合并完成，共耗时"+str(round(use_time,2))+"秒，数据存放在excel_output.xlsx")
os.system("pause")
else:
print("未找到xls或者xlsx文件! 你检查一下呢！")
os.system("pause")
except Exception as e:
print(str(e)+'n'+"这个文件"excel_output.xlsx"被打开，请关闭它！")
os.system("pause")
方法二：
基于pandas实现
import time
import pandas as pd
import os
#查找当前目录下的excel文件
def getAllFiles():
excelList=[]
currentPath=os.getcwd()
fileList=os.listdir(currentPath)
for file in fileList:
if file.endswith('.xlsx') or file.endswith('.xls'):
if file!="excel_output.xlsx" and file!="~$excel_output.xlsx":
excelList.append(str(file))
return excelList #返回文件路径的列表
if __name__=="__main__":
excelList=getAllFiles()
new_df=pd.Dataframe()
print("Excel数据合并程序v1_pro(在v1基础上提高了性能，去掉了多sheet的合并，仅支持多文档sheet1的合并)")
print("有问题请联系张熙")
os.system("pause")
start_time=time.time()
try:
for filePath in excelList:
df=pd.read_excel(filePath,header=None)
print("正在读取"+filePath+"文件")
new_df=pd.concat([new_df,df],axis=0,ignore_index=True)
outputPath="excel_output.xlsx"
print("文件正在拼接，请等待.....")
writer=pd.ExcelWriter(outputPath)
new_df.to_excel(writer,'Sheet1',index=False,header=None)
print("合并文件正在保存，请等待....")
writer.save()
end_time=time.time()
use_time=end_time-start_time
print("文件合并成功，共耗时"+str(round(use_time,2))+"秒，输出文件为“excel_output.xlsx”")
os.system("pause")
except Exception as e:
print(str(e)+"n"+"合并失败，“excel_output.xlsx”文件正在使用，请关闭它！再试。")
