# encoding=utf8'''Python将700多个Excel汇总到一个Excel表格中'''# 导入包import xlsxwriterimport os,sys,shutil,time,datetimeimport xlrd # pip install xlrd==1.2.0from xlrd import xldate_as_tuple# 定义方法def reexcellist(excelpath): '''返回一个目录下所有excel文件路径的数组''' patharray = [] for foldName, subfolders, filenames in os.walk(excelpath): de = 0 for filename in filenames: #遍历列表下的子文件夹名 de = de + 1 allpath = os.path.join(excelpath, filename) dictjson = {"ex_id": de, "ex_path": allpath} patharray.append(dictjson) return patharraydef import_excel(excelpa): '''将excel表格内容导入到tables列表中''' data = xlrd.open_workbook(excelpa) table = data.sheets()[0] excel = table tables = [] line = 0 for rown in range(excel.nrows): array = { 'id':'', 'order':'', 'timedate':'', 'proname':'', 'srcprice':'', 'showprice':'' } array['id'] = table.cell_value(rown,0) # 读取第一列 array['order'] = table.cell_value(rown,1) # 转换时间格式方法 if table.cell(rown,2).ctype == 3: date = xldate_as_tuple(table.cell(rown,2).value,0) array['timedate'] = datetime.datetime(*date) array['proname'] = table.cell_value(rown,3) array['srcprice'] = table.cell_value(rown,4) array['showprice'] = table.cell_value(rown,5) if line > 0: tables.append(array) line = line+1 return tablesdef createexcel(exceldata, tabpath): '''根据提供的列表，创建需要的excel表格''' # 创建一个工作簿并添加一个工作表 workbook = xlsxwriter.Workbook(tabpath) worksheet = workbook.add_worksheet('Sheet1') # 表头字段格式 header = { 'bold' : True, #粗体 'font_name' : '微软雅黑', 'font_size' : 11, 'border' : True, #边框线 'align' : 'center', #水平居中 'valign' : 'vcenter', #垂直居中 'color':"#232323", # 文字颜色 'bg_color' : '#D9E1F2' #背景颜色 } headerpm = workbook.add_format(header) # 添加粗体格式 bold = workbook.add_format({'bold': True}) # 正文字段格式 text = { 'font_name' : '微软雅黑', 'font_size' : 10, 'border' : True, 'align' : 'left', #左对齐 "color":"#232323", # 文字颜色 'valign' : 'vcenter' } textpm = workbook.add_format(text) # 编写一些头数据。 worksheet.write('A1', '售卖时间', headerpm) worksheet.write('B1', '产品名称', headerpm) worksheet.write('C1', '原金额', headerpm) worksheet.write('D1', '实际付款', headerpm) worksheet.set_row(0, 30) # 表头高度 worksheet.set_column('A:A',18) #A列宽度 worksheet.set_column('B:B',50) #B列宽度 # 从第一个单元格开始。 行和列的索引为零。 row = 1 col = 0 # 遍历数据并逐行写出。 for jsonc in exceldata: worksheet.write(row, col, str(jsonc['timedate']), textpm) worksheet.write(row, col + 1, jsonc['proname'], textpm) worksheet.write(row, col + 2, jsonc['srcprice'], textpm) worksheet.write(row, col + 3, jsonc['showprice'], textpm) row += 1 # 汇总销售额 worksheet.write(row, 0, '最终数据统计',bold) worksheet.write(row, 1, '共统计到'+str(row)+'个订单商品',bold) worksheet.write(row, 2, '=SUM(C2:C'+str(row)+')') worksheet.write(row, 3, '=SUM(D2:D'+str(row)+')') # 关闭对象 workbook.close() print("\n所有表格汇总完成:"+tabpath)if __name__ == '__main__': # 订单excel文件存放目录 excelpath = r'D:\Test\excel' # 表格创建保存目录 tabpath = r'D:\test\订单数据统计.xlsx' # 开始处理 time_start = time.time() # 记录开始时间 patharray = reexcellist(excelpath) exceldata = [] for paths in patharray: tablec = import_excel(paths['ex_path']) exceldata = exceldata + tablec createexcel(exceldata, tabpath) time_end = time.time() # 记录结束时间 time_sum = time_end - time_start # 计算程序的执行时间 print("处理用时：" + str(round(time_sum,2)) + "秒")# ... end
