批量将txt转换成Excel格式

想要达到的目的：批量将txt转换成对应的Excel格式（一个txt文件对应一个Excel文件）。

1.利用现有工具（适用于数据量少的情况）
可以在线直接转换：https://anyconv.com/txt-to-xlsx-converter/
亲测是可以使用的，但是要一个个下载。

2.利用VBA

Sub txt2excel()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim txt, fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
     With fd
        .AllowMultiSelect = True
         If .Show = -1 Then
             For Each txt In .SelectedItems
                 Workbooks.OpenText Filename:=txt, Origin:=936, DataType:=xlDelimited, Semicolon:=False, Comma:=True, Space:=False, Other:=False, Tab:=True
                 Range("a:u").NumberFormatLocal = "@"
                 ActiveWorkbook.SaveAs Filename:=Mid(txt, 1, Len(txt) - 4) & ".xls", FileFormat:=xlExcel8
                 'ActiveWorkbook.SaveAs Filename:=Mid(txt, 1, Len(txt) - 4), FileFormat:=xlExcel8
                 ActiveWorkbook.Close True
             Next
         Else
             Exit Sub
         End If
     End With
End Sub

3.python 实现 txt2xlsx

import os

import  openpyxl

import pandas as pd

#%%
txtPath = 'txt path'

# txtPath = txtPath.encode('utf-8')

txtType = 'txt'

txtLists = os.listdir(txtPath)

print(txtLists)

for txt in txtLists:
    
    f = open(txtPath+txt,encoding='utf-8')  
    
    lines = f.readlines()
    
    file = openpyxl.Workbook()
    
    worksheet = file.active
    
    i = 2
    
    # print(txt)
    
    for line in lines:
        
        line = line.strip('\n')
        
        worksheet.cell(i, 1,float(str( line)))
        
        i = i + 1
        
        file.save('save path/'+ txt.split('.')[0] +'.xlsx')
        
print('done!')