import re

import os

import openpyxl

from openpyxl import Workbook,workbook

from concurrent.futures import ThreadPoolExecutor

import time

class xshell_session:

    def __init__(self,excel_name):

        self.excel_name = excel_name

        self.all_row_dic = []  # 存储列表所有字典

        with open("tmp.xsh", "r", encoding="utf-16-le") as tmp:

            self.tmp_file = tmp.read()

        try:

            os.mkdir("./session")

        except:

            pass

    def get_ip_list(self):

        wb = openpyxl.load_workbook(filename=self.excel_name)   #type:workbook.Workbook

        sheet_info = wb[wb.sheetnames[0]]  # 进入当前工作sheet

        max_colum = sheet_info.max_column

        max_row = sheet_info.max_row

        row_data = list(sheet_info.rows)  #遍历行数据

        header = []    # 创建空列表存储key值

        for data in range(1,max_colum+1):

            get_header = sheet_info.cell(row=1,column=data).value

            header.append(get_header)

        #print(header)

        for row in row_data:

            reslut = []   # 创建列表，存储每行的数据

            for i in row:

                reslut.append(i.value)

            row_dic = dict(zip(header,reslut))

            #print(row_dic)

            self.all_row_dic.append(row_dic)

        self.all_row_dic.pop(0)

        print(self.all_row_dic)

        for i in self.all_row_dic:

            print(i)

    def creat_session_file(self):

        os.chdir("./session")

        for i in self.all_row_dic:

            ip = i["IP"]

            dev_name = i["name"]

            #print(dev_name)

            #print(ip)

            new_file = dev_name+".xsh"

            session = re.sub("Host=.*", "Host=" + ip, self.tmp_file)

            with open(new_file,"w",encoding="utf-8") as file:

                file.write(session)

            print(new_file)

session = xshell_session("iplist.xlsx")

session.get_ip_list()

session.creat_session_file()