import xlrd
import os
import xlwt

FolderPath = r"C:\Users\Administrator.WIN10-803072322\Desktop\贺虎"
pathsDir = os.listdir(FolderPath)
#获取文件夹中表格的路径 并且放入到列表中
PathList = []
for LogsPath in pathsDir:
ExcelPath = os.path.join(FolderPath, LogsPath)
PathList.append(ExcelPath)

#构建函数读取表格数据
def get_sheet_value(i):
Path = PathList[i]
book = xlrd.open_workbook(Path)
sheet1 = book.sheets()[0]
sheet_rows = sheet1.nrows
#sheet_cols = sheet1.ncols
SheetData = []
for numRows in range(0, sheet_rows):
sheet_value = sheet1.row_values(numRows)
SheetData.append(sheet_value)
return SheetData

i = 0
file_value = get_sheet_value(i)
while i < len(PathList):
#print(“第” + str(i + 1) + “张数据表”, end="")
file_value += get_sheet_value(i)
i += 1
#print(file_value)
print(len(file_value))
print(len(file_value[0]))

#将数据写入新表格中
workbook = xlwt.Workbook(FolderPath)
worksheet = workbook.add_sheet(‘test’)
num = 0
numRows = len(file_value)
numCols = len(file_value[0])

print(file_value[3274][0])

#把表格数据合并成列表然后 写入新表格
for numCols in range(len(file_value[0])):
for numRows in range(len(file_value)):
value = file_value[numRows][numCols]
worksheet.write(numRows, numCols, value)

file_name = str(FolderPath).split("\")
file_name1=str(file_name[-1]) +“合并文件” + “.xls”
print(file_name1)
workbook.save(file_name1)