pd生成excel

#xlwt
#xlrd
import pandas  as pd
from share.ssh import SSH
from datetime import datetime
from conf.conf import ip_all, excel_dir




def excel():
    disk = """ df -h |awk  '$NF=="/"{print  $4,$5}' """
    cpu = "top -bn 1 -i -c  | grep Cpu |awk -Fid, '{print $1}' |awk -F, '{print $NF}'"
    disk_trs = """df -h |awk  '$NF=="/TRS"{print  $4,$5}'"""
    disk_data = """df -h |awk  '$NF=="/data"{print  $4,$5}'"""
    swap = """ free -h|grep  "Swap"|awk '{print $2,$4}' """
    mem = """ free -m | awk 'NR==2{printf "%.2f%%",$3*100/$2 }'"""
    ip_list = []
    disk_list = []
    cpu_list = []
    disk_trs_list = []
    disk_data_list = []
    swap_list = []
    mem_list=[]

    with  open(ip_all) as  f:
        for i in f:
            ip = i.strip()
            cli = SSH(ip)
            disk_out = cli.exec_command(disk.strip())
            cpu_out = 100 - float(cli.exec_command(cpu))
            disk_trs_out = cli.exec_command(disk_trs)
            disk_data_out = cli.exec_command(disk_data)
            swap_out = cli.exec_command(swap)
            mem_out=cli.exec_command(mem)

            ip_list.append(ip)
            disk_list.append(disk_out)
            cpu_list.append(cpu_out)
            disk_trs_list.append(disk_trs_out)
            disk_data_list.append(disk_data_out)
            swap_list.append(swap_out)
            mem_list.append(mem_out)
    # # 写
    dic1 = {'ip': ip_list,
            '/剩余及使用率': disk_list,
            'cpu使用率': cpu_list,
            '/TRS剩余及使用率': disk_trs_list,
            '/data剩余及使用率': disk_data_list,
            '物理内存剩余': mem_list,
            'swap总内存及剩余': swap_list
            }
    df = pd.DataFrame(dic1)
    file_name = "xunjian" + "_" + datetime.now().strftime("%Y%m%d") + ".xlsx"
    df.to_excel(f"{excel_dir}/{file_name}", index=False)