从几个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:\Python\Visual Studio Code Proyects\M2M Real\Archivos")

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:\Python\Visual Studio Code Proyects\M2M Real\Archivos")

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循环外部。