from concurrent.futures import ThreadPoolExecutor
import netmiko
import os
from threading import Lock
import pandas as pd
from textfsm import TextFSM
import time

class net_dev():

def \_\_init\_\_(self,excel\_name):  
    try :  
        os.mkdir("./log")  
    except:  
        pass  
    self.excel\_name = excel\_name  
    self.list = \[\] # 空列表存储设备信息数据  
    self.pool = ThreadPoolExecutor(10) # 初始化线程数量  
    self.lock = Lock()  # 添加线程锁，避免写入数据丢失  
    self.path = ("./log")  # 创建保存log路径  
def get\_dev\_info(self):  
    # 获取设备信息dataframe  
    dataframe = pd.read\_excel(self.excel\_name,sheet\_name="设备信息")  
    #print(dataframe)  
    self.list = dataframe.to\_dict(orient="records")  # 将数据变为列表  
    print(self.list)

def textfsm\_1(self):  
    path = r'C:\\Users\\17160\\PycharmProjects\\yc\_project\\高级textfsm\_study\\\\'  
    ntc\_templates\_path = r'C:\\Users\\17160\\PycharmProjects\\yc\_project\\高级textfsm\_study\\ntc-templates\\\\'  
    result\_path = r'C:\\Users\\17160\\PycharmProjects\\yc\_project\\高级textfsm\_study\\输出结果\\\\'

    ntc\_file = "cisco\_show\_interface"  # 使用模板名字

    result\_excel\_name = ntc\_file + ".xlsx"  
    os.chdir(path)  
    # 新建一个test.xlsx文件  
    df\_tmp = pd.DataFrame(data=None)  
    with pd.ExcelWriter(str(result\_path) + result\_excel\_name, mode="w") as writer:  
        df\_tmp.to\_excel(writer, sheet\_name="sheet1", index=False)  
    success\_ip = \["YCMBOSS.YUX.6509.SW1","YCMBOSS.YUX.6509.SW2"\]  
    for ip in success\_ip:  
        with open(r'C:\\Users\\17160\\PycharmProjects\\yc\_project\\高级textfsm\_study\\log\\\\' + str(ip) + '.txt', "r",  
                  encoding="utf-8") as file:  
            str1 = str(file.read())  
        info = TextFSM(open(str(ntc\_templates\_path) + ntc\_file )).ParseText(  
            str1)  # 使用textfsm模板  
        header = (TextFSM(open(str(ntc\_templates\_path) + ntc\_file )).header) # testfsm获取头部  
        data = info  
        df = pd.DataFrame(data, columns=header)  
        print(df)

        with pd.ExcelWriter(str(result\_path) +  result\_excel\_name, mode="a") as writer:  
            df.to\_excel(writer, sheet\_name=str(ip), index=False)

yc_use = net_dev("设备信息.xlsx")
yc_use.get_dev_info()
yc_use.textfsm_1()