txt文件批量转换为excel文件

把某个文件夹下的所有txt文件全部转换成excel文件

import os
import openpyxl
import codecs
from openpyxl.utils import get_column_letter


def txt_to_xlsx(filename, outfile,  sheetname):
    fr = codecs.open(filename, 'r')
    wb = openpyxl.Workbook()
    ws = wb.active
    ws = wb.create_sheet()
    ws.title = sheetname
    row = 0
    for line in fr:   #按行读取
        row += 1
        line = line.strip()    #移除字符串头尾的空格
        line = line.split(',')      #split()通过指定分隔符对字符串进行切片
        col = 0    #列
        for j in range(len(line)):     #再按列读取
            col += 1
            # print (line[j])
            ws.cell(column=col, row=row, value=line[j].format(get_column_letter(col)))   #遍历列的时候，用字母编号来索引get_column_letter(1) >>> A
    wb.save(outfile)


# 读取xlsx内容
def read_xlsx(filename):
    # 载入文件
    wb = openpyxl.load_workbook(filename)
    # 获取Sheet1工作表
    ws = wb.get_sheet_by_name('Sheet1')
    # 按行读取
    for row in ws.rows:
        for cell in row:
            print(cell.value)
    # 按列读
    for col in ws.columns:
        for cell in col:
            print(cell.value)

if __name__ == '__main__':
    inputdir_path = r' ' #存放txt文件夹路径
    outputdir_path = r' '    #生成excel文件夹路径
    fileList = os.listdir(inputdir_path)
    print(fileList)
    for name in fileList:
        inputfileTxt = os.path.join(inputdir_path,name)
        outfileExcel = os.path.join(outputdir_path,name).replace('.txt', '.xlsx')
        sheetname = name.replace(".txt",'')
        print(sheetname)
        txt_to_xlsx(inputfileTxt, outfileExcel, sheetname)
print(str(len(fileList)) + '个txt文件已转换完毕')