python 读取txt文件到excelc

需求：一个文件夹下的txt文件，并计算每N（3）行数据为平均值，结果需要转换成excel，输出到不同的列。

原始数据：（结构构成）
1321,214124,214124
32423,324525,235
2141,2154124,2132134
1321,214124,214124
32423,324525,235
2141,2154124,2132134
输出结果：（结构构成）

rolnum=0
colnum=0

#阅读txt到excel
def Txt_to_Excel(inputTxt, sheetName, start_row, start_col, outputExcel):
    ws = excel.get_sheet(sheetName)
    with open(inputTxt, "r") as file_txt:
        lines = file_txt. readlines() #不是很大的文件都可以先读取出来
        # count = len(lines)
        rolnum =len(lines)
        rol=0
        #获取每行数据
        for datasrol in lines:
            # print(datarol)
            # 列数归0
            col=0
            datarol = datasrol. split(",")
            # print(datarol)
            row_excel = rol + start_row-1
            rol += 1
            #把数据按列插入
            for data in datarol:
                col_excel = col + start_col- 1
                # print(data)
                ws.write(row_excel, col_excel, data)
                excel.save (outputExcel)
                # print(col_excel)
                col +=1
    cotnum = col
    print("行数, " + str(rolnum) + "列数 " + str(colnum))
    return excel

#处理exceL,计算每列3组的平均值，newSheet为空时代表输入追加第一个sheet
def excelUnit(excelFile,newSheet,tabName):
    #结果输入到新的sheet文件
    if newSheet != None and newSheet !='':
        #创建表格
        CreatExcel(sheetName=newSheet, tabName=tabName, outputExcel=excelFile)
        ws = excel.get_sheet (newSheet)
        col=0
        Data_0 = []
        i=0
        for data in tabName:
            col_excel = col + start_col -1
            df = pd.DataFrame (pd. read_excel(excelFile))
            print(data)
            while i < len(df):
                Data0 = df[data][i:i + group] # 每间隔3个数据取-次数
                print(Data0)
                Data1 = sum(Data0) / group # 求取每组数据的平均值
                Data_0. append((format(Data1,'.6f')))
                i=i+group
            print(Data_0)
            rol=0
            for data0 in Data_0:
                rol_excel = rol + start_row - 1
                ws. write(rol_excel, col_excel, data0)
                excel.save(excelFile)
                rol += 1
            col += 1
            Data_0 = []
            i=0
            return
        else:#追加到原来的sheet
            Data_0 = []
            i=0
            count=len(tabName)
            icount=0#用于合并增加列数
            for data in tabName:
                worksheet.excel.get_sheet(sheetname)
                worksheet.write(i,icount+count,data+'平均数')
                df = pd.DataFrame (pd. read_excel(excelFile))
                print(data)
                index=0
                while i < len(df):
                    Data0 = df[data][i:i + group] # 每间隔3个数据取-次数
                    print(Data0)
                    Data1 = sum(Data0) / group # 求取每组数据的平均值
                    Data_0.append((format(Data1,'.6f')))
                    #合并单元格
                    worksheet.write_merge(i+1,i+group , count+icount, count+icount, Data_0[int(index)], style=Style.default_style)
                    i=i+group

                    index+=1
                    #while index<group:
                        #index+=1
                        #Data_0.append('')
                print(Data_0)
                excel.save(excelFile)
                #df[data+"3组平均数"]=Data_0

                #df.to_excel(excelFile,sheetName,index=False)
                Data_0 = []
                i=0
                icount+=1
          return


# 创建excel, sheetName
def CreatExcel(sheetName, tabName, outputExcel):
    if sheetName == None and sheetName =='' :
        sheetName =" 默认sheet"
    ws = excel.add_sheet(sheetName)
    col=0
    for data in tabName:
        col_excel =col + start_col - 1
        ws.write(0, col_excel,data)
        excel. save (outputExcel)
        col+= 1
    return excel
#阅读文件夹下的所有txt文件
def readTxtDir(path):
    findtxt = os.listdir(path) # 文件夹下的所有文件
    txtsPath=[]
    for file in findtxt:#遍历文件夹
        if os.path.splitext(file)[1] == '.txt':
            # print(file)
            txtsPath.append(file)
    print(txtsPath)
    return txtsPath

if __name__ =='__main__'  :

    #原始文件参数
    outputExcel = '.xls' #输入excel格式
    inputfileDir = 'D://yuanhua/test/case' # 文件目录
    txts=readTxtDir(path=inputfileDir)
    for txt in txts:
        #创建文档
        excel = xlwt .Workbook(encoding='utf-8')
        sheetName = "导航定位综合误差原始数据" # 需要写入exceL中的Sheet中，可以自己
        tabName=['x','y','z']
        start_row = 2 # 从第2行开始写
        start_col = 1 # 从第1列开始写
        #转换文件为excel
        outputExcel ='.xls'
        print(inputfileDir + "/" + txt)
        print(txt.split('.')[0] + outputExcel)
        outputExcel.txt.split('.')[0] + outputExcel
        inputfile = inputfileDir + "/" + txt
    
        excel = CreatExcel( sheetName=sheetName,tabName=tabName,outputExcel=outputExcel)
        Txt_to_Excel(inputTxt =inputfile, sheetName=sheetName, start_row=start_row,start_col=start_col,outputExcel=outputExcel)
    
        avgName = "平均值 "
        group = 3 # n个数据组合为一组
    
        # tabName = ['x', 'y', 'z']
    
        excelUnit(excelFile=outputExcel, newSheet=avgName, tabName=tabName)