python读取txt文件批量导出excle文件
前言
本文是将txt文件批量导出为excel文件的python脚本;
txt文件保存于一个文件下与txt文件同名的文件夹中;
脚本的作用是将所有的txt文件读取后导出到与该txt文件同名的sheet表中。
一、pandas是什么?
pandas 是基于NumPy 的一种工具,该工具是为了解决数据分析任务而创建的。
二、使用步骤
1.引入库
from openpyxl import Workbook
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
2.将txt转换成excel
def read_txt(Filelist):
lst1 = []
for File in Filelist:
lst1.append(File.name)
workbook = Workbook()
workbook.save('iptables.xlsx')
for i in range(len(lst1)):
workbook.create_sheet(title=lst1[i])
workbook.save('iptables.xlsx')
writer = pd.ExcelWriter('iptables.xlsx')
n = 0
while n < len(lst1):
txt_name = lst1[n]
for sheet in workbook:
if txt_name == sheet.title:
with open(Filelist[n], 'r+') as data:
layerdata = data.readlines()
df = pd.DataFrame((x.split(' '))for x in layerdata)
df.to_excel(writer, sheet_name=sheet.title)
n += 1
else:
continue
writer.save()
3.设置列宽
def reset_col(filename):
wb=load_workbook(filename)
for sheet in wb.sheetnames:
ws=wb[sheet]
df=pd.read_excel(filename,sheet).fillna('-')
df.loc[len(df)]=list(df.columns)
for col in df.columns:
index=list(df.columns).index(col)
letter=get_column_letter(index+1)
collen=df[col].apply(lambda x:len(str(x).encode())).max()
ws.column_dimensions[letter].width=collen*1.2+4
wb.save(filename)
4.main
if __name__ == '__main__':
p = Path("D://pycharm/txt")
Filelist = list(p.glob("**/*.txt"))
read_txt(Filelist)
reset_col('iptables.xlsx')