输入:
输出:
代码:
#!/usr/bin/python #-*- coding:utf-8 -*- # @Author : qtwang # @Email : qtwang315518@163.com # @Time : 2022-6-22 18:47 """ 纵向拼接excel表 """ import xlrd import xlsxwriter import glob import os import numpy as np biao_tou = [] wei_zhi = "NULL" def writer_log(txtpath,string): fo = open(txtpath, "a", encoding='UTF-8') fo.write(string+"\n") # 获取要合并的所有exce表格 def get_exce(): global wei_zhi wei_zhi = input("请输入Exce文件所在的目录:") all_exce = glob.glob(wei_zhi + "\*.xls") print("该目录下有" + str(len(all_exce)) + "个exce文件:") if (len(all_exce) == 0): return 0 else: for i in range(len(all_exce)): print(all_exce[i]) return all_exce # 打开Exce文件 def open_exce(name): fh = xlrd.open_workbook(name) return fh # 获取exce文件下的所有sheet def get_sheet(fh): sheets = fh.sheets() return sheets # 获取sheet下有多少行数据 def get_sheetcol_num(sheet): return sheet.ncols # 获取sheet下的数据 def get_sheet_data2(sheet, rows): max_m=max(0,15) min_m = min(0, 15) global biao_tou biao_tou = [] for i in range(min_m,max_m): if (i <= 2): temp = sheet.row_values(i) biao_tou.append(temp) continue values = sheet.row_values(i) # print(values) all_data1.append(values) return all_data1 # 获取sheet下的数据 def get_sheet_data(sheet, col): values = sheet.col_values(col) return values if __name__ == '__main__': all_exce = get_exce() # 得到要合并的所有exce表格数据 if (all_exce == 0): print("该目录下无.xlsx文件!请检查您输入的目录是否有误!") os.system('pause') exit() biaotou = [] # 用于保存合并的所有行的数据 new_log = wei_zhi + "/log_single.txt" #用于保存log信息 # 下面开始文件数据的获取 i= 0 for exce in all_exce: if i == 0: fh = open_exce(exce) writer_log(new_log,exce) print(exce) # 打开文件 sheets = get_sheet(fh) biaotou=get_sheet_data(sheets[0], 1) all_data1 = get_sheet_data(sheets[0], 5) all_data1[0] = os.path.basename(exce) biaotou=np.vstack((biaotou,all_data1)) i=i+1 else: fh = open_exce(exce) writer_log(new_log, exce) print(exce) # 打开文件 sheets = get_sheet(fh) values = get_sheet_data(sheets[0], 5) values[0] = os.path.basename(exce) biaotou=np.vstack((biaotou,values)) i = i + 1 # 下面开始文件数据的写入 new_exce = wei_zhi + "/singleReslut.xlsx" # 新建的exce文件名字 fh1 = xlsxwriter.Workbook(new_exce) # 新建一个exce表 new_sheet = fh1.add_worksheet() # 新建一个sheet表 for i in range(len(biaotou)): for j in range(len(biaotou[i])): c = biaotou[i][j] new_sheet.write(i, j, c) fh1.close() # 关闭该exce表 print("文件合并成功,请查看“" + wei_zhi + "\singleReslut.xlsx文件!") os.system('pause') os.system('pause') |