import xlrd import os from xlwt import * # 遍历文件夹下所有文件 def show_files(path, all_files): # 首先遍历当前目录所有文件及文件夹 file_list = os.listdir(path) # 准备循环判断每个元素是否是文件夹还是文件,是文件的话,把名称传入list,是文件夹的话,递归 for file in file_list: # 利用os.path.join()方法取得路径全名,并存入cur_path变量,否则每次只能遍历一层目录 cur_path = os.path.join(path, file) # 判断是否是文件夹 if os.path.isdir(cur_path): show_files(cur_path, all_files) else: all_files.append(cur_path) return all_files # 读取excel文件内容 def excel(excel_path): try: wb = xlrd.open_workbook(excel_path) # 打开Excel文件 sheet_name = wb.sheet_names()[0] sheet = wb.sheet_by_name(sheet_name) # 通过excel表格名称(rank)获取工作表 dat = [] # 创建空list print("这个sheet一共{}行".format(sheet.nrows)) # 第二行和第三行数据 academy = sheet.row_values(1)[1] name = sheet.row_values(1)[2] phone = sheet.row_values(1)[4] student_num = sheet.row_values(2)[4] # 第六行到第二十行数据 for x in range(6,sheet.nrows-1): date = sheet.row_values(x)[0] addr = sheet.row_values(x)[1] mon_heat = sheet.row_values(x)[2] non_heat = sheet.row_values(x)[3] is_sick = sheet.row_values(x)[4] is_out = sheet.row_values(x)[5] is_party = sheet.row_values(x)[6] cell_list=[academy, name, phone, student_num,date,addr,mon_heat,non_heat,is_sick,is_out,is_party] dat.append(cell_list) print(dat) return dat except Exception as e: print(e) return [excel_path] # 将内容写入excel表 def write_to_excel(data, path): file = Workbook(encoding='utf-8') # 指定file以utf-8的格式打开 table = file.add_sheet('data') ldata = [] num = sorted([a for a in data]) # for循环指定取出key值存入num中 # 字典数据取出后无需,需要先排序 for x in num: # for循环将data字典中的键和值分批的保存在ldata中 t = [int(x)] for a in data[x]: t.append(a) ldata.append(t) for i, p in enumerate(ldata): # 将数据写入文件,i是enumerate()函数返回的序号数 for j, q in enumerate(p): table.write(i, j, q) file.save(path) # 整个流程 def organize_floder_file(floder_path,target_file_path): """ 整理文件夹下全部excel内容,并且写入到一个新的exceL中 :param floder_path: 需要整理的文件夹绝对路径 :param target_file_path: 写入的文件的最终路径 :return: """ # 一,获取文件夹下全部文件列表 contents_list = show_files(path=floder_path, all_files=[]) print("一共有表格:{}份".format(len(contents_list))) # 二,读取每个excel文件内容,写入all dict all_dict = {} num = 0 for excel_path in contents_list: print(excel_path) num += 1 student_info = excel(excel_path=excel_path) for info in student_info: num += 1 all_dict[num] = info # 三,将内容写入新的excel表中 write_to_excel(data=all_dict, path=target_file_path) organize_floder_file(floder_path="D:\2017级",target_file_path="D:\2017\test.xls")