从几个TXT创建一本excel书，其中每个TXT都是不同的表格

Question

我正在尝试读取几个TXT文件，我对它们进行了一些修改，然后用Pandas将它们转换为DataFrame，在那里我还运行了一些修改过程，到目前为止一切都很好，一切都很完美，我通过for循环来完成，但在保存xlsx文件并创建第一张工作表的时候，它不会创建新的工作表，它只是创建了第一个。

代码如下：

from netmiko import ConnectHandler
from datetime import datetime
import re
from pathlib import Path
import os
import pandas as pd
ruta = Path("E:PythonVisual Studio Code ProyectsM2M RealArchivos")
def is_free(valor):
color = 'green' if valor == "free"  else 'white'
return 'background-color: %s' % color
list_txt = [ruta/"Router_a.txt", ruta/"Router_b.txt"]
for txt in list_txt:
host = txt.stem
sheet_name=f'{host}-Gi0-3-4-2'
ruta_host = f'{ruta}\interfaces.xlsx'
df = pd.read_fwf(txt)
df["Description"] = (df.iloc[:, 3:].fillna("").astype(str).apply(" ".join, axis=1).str.strip())
df = df.iloc[:, :4]
df = df.drop(columns = ["Status", "Protocol"])
df.Interface = df.Interface.str.extract('Gi0/3/4/2.(d+)')
df = df[df.Interface.notnull()].reset_index()
df = df.drop(columns = ["index"])
df['Interface'] = df['Interface'].astype(int)
df = df.set_index('Interface').reindex(range(1,50)).fillna('free').reset_index()
df = df.style.applymap(is_free)
with pd.ExcelWriter(ruta_host, mode='a') as writer:
df.to_excel(writer, sheet_name, index=False)

txt的格式如下，值得澄清的是，两个路由器的txt基本上都是相同的：

Interface          Status      Protocol    Description
Gi0/3/4/2          up          up          ENLACE A Router_X
Gi0/3/4/2.5        up          up          Frontera Cliente A
Gi0/3/4/2.6        up          up          Frontera Cliente B
Gi0/3/4/2.7        up          up          Frontera Cliente C
Gi0/3/4/2.8        up          up          Frontera Cliente D
Gi0/3/4/2.9        up          up          Frontera Cliente E

知道我做错了什么吗？

Answer 1

我能够解决我的问题，下面是脚本：

from netmiko import ConnectHandler
from datetime import datetime
import re
from pathlib import Path
import os
import pandas as pd
from openpyxl import load_workbook
ruta = Path("E:PythonVisual Studio Code ProyectsM2M RealArchivos")
def is_free(valor):
color = 'green' if valor == "free"  else 'white'
return 'background-color: %s' % color
list_txt = [ruta/"FRUVI01.txt", ruta/"FRUVE01.txt"]
for txt in list_txt:
with open(txt, "r") as f:
lines = f.readlines()
with open(txt, "w") as fw:
for line in lines:
if not re.match("-{5}|s+|([A-Za-z0-9]+( [A-Za-z0-9]+)+)", line):
fw.write(line)
for txt in list_txt:
host = txt.stem
sheet_name=f'{host}-Gi0-3-4-2'
ruta_host = ruta/'ejemplo.xlsx'
df = pd.read_fwf(txt)
df["Description"] = (df.iloc[:, 3:].fillna("").astype(str).apply(" ".join, axis=1).str.strip())
df = df.iloc[:, :4]
df = df.drop(columns = ["Status", "Protocol"])
df.Interface = df.Interface.str.extract('Gi0/3/4/2.(d+)')
df = df[df.Interface.notnull()].reset_index()
df = df.drop(columns = ["index"])
df['Interface'] = df['Interface'].astype(int)
df = df.set_index('Interface').reindex(range(1,3580)).fillna('free').reset_index()
df = df.style.applymap(is_free)
if not ruta_host.exists():
writer = pd.ExcelWriter(ruta_host)
writer.close()            
else:
pass  
with pd.ExcelWriter(ruta_host, mode="a", engine="openpyxl", if_sheet_exists="overlay") as writer:
df = df.to_excel(writer, sheet_name, index=False)

因为我必须创建一个创建文件，它是用一张名为"；Sheet1"；，添加以下脚本以在循环末尾和外部删除它：

wb = load_workbook(ruta_host)
if 'Sheet1' in wb.sheetnames:
wb.remove(wb['Sheet1'])
wb.save(ruta_host)
else:
pass

Answer 2

移动行

with pd.ExcelWriter(ruta_host, mode='a') as writer

在txt文件的for循环之外。