批量合并excel表内容，保存在新的excel表内

主要是把指定文件夹下不同时间生成具有相同规律的excel文件汇总到一个新的excel内，可以用作后续分析使用

获取指定路径下的具有相同后缀名的文件集合，这里是当前目录下的所有的保存格式为xlsx的文件的集合

import os
from glob import glob

def get_all_excel():
	paths = glob(r'./*.xlsx')
	files = []
	for path in paths:
		file = os.path.basename(path)
		print(file)
		files.append(file)
	return files

从excel中读取数据以及想新表格写入数据，这里新表单句柄为worksheet由上层调用传入

def read_excel(file, worksheet,style):
	global cellNum
	workBook = xlrd.open_workbook(file)
	sheet = workBook.sheet_by_index(0)
	
	num = sheet.nrows
	tem = 1
	while tem < num:
		row = sheet.row_values(tem)
		for i, content in enumerate(row):
			worksheet.write(cellNum,i,content,style)
		cellNum = cellNum + 1
		tem = tem + 1

新表格顶端标题行

def make_excel_header(worksheet,style):
	global cellNum
	worksheet.write(cellNum,0,'A',style)
	worksheet.write(cellNum,1,'B',style)
	worksheet.write(cellNum,2,'C',style)
	worksheet.write(cellNum,3,'C',style)
	worksheet.write(cellNum,4,'D',style)
	worksheet.write(cellNum,5,'E',style)
	worksheet.col(0).width = 256*14
	worksheet.col(1).width = 256*14
	worksheet.col(2).width = 256*22
	worksheet.col(3).width = 256*14
	worksheet.col(4).width = 256*14
	worksheet.col(5).width = 256*14
	
	cellNum = cellNum + 1
	

全部代码汇总情况如下

import xlrd
import xlwt
import os
from glob import glob

file_path = os.getcwd()
fileName = 'worksheet.xls'
cellNum = 0

def get_all_excel():
	paths = glob(r'./*.xlsx')
	files = []
	for path in paths:
		file = os.path.basename(path)
		print(file)
		files.append(file)
	return files
	
def make_excel_header(worksheet,style):
	global cellNum
	worksheet.write(cellNum,0,'A',style)
	worksheet.write(cellNum,1,'B',style)
	worksheet.write(cellNum,2,'C',style)
	worksheet.write(cellNum,3,'D',style)
	worksheet.write(cellNum,4,'E',style)
	worksheet.write(cellNum,5,'F',style)
	worksheet.col(0).width = 256*14
	worksheet.col(1).width = 256*14
	worksheet.col(2).width = 256*22
	worksheet.col(3).width = 256*14
	worksheet.col(4).width = 256*14
	worksheet.col(5).width = 256*14
	
	cellNum = cellNum + 1
	
	
def read_excel(file, worksheet,style):
	global cellNum
	workBook = xlrd.open_workbook(file)
	sheet = workBook.sheet_by_index(0)
	
	num = sheet.nrows
	tem = 1
	while tem < num:
		row = sheet.row_values(tem)
		for i, content in enumerate(row):
			worksheet.write(cellNum,i,content,style)
		cellNum = cellNum + 1
		tem = tem + 1

	
if __name__ == '__main__':
		
	files = []
	files = get_all_excel()
	
	excel_copy = xlwt.Workbook()
	worksheet = excel_copy.add_sheet('sheet1', cell_overwrite_ok=True)
	
	style = xlwt.XFStyle()
	a1 = xlwt.Alignment()
	a1.horz = 0x02
	a1.vert = 0x01
	style.alignment = a1
	
	make_excel_header(worksheet,style)
	
	for file in files:
		read_excel(file,worksheet,style)
		excel_copy.save(fileName)