# coding=utf-8
import xlrd
# import xlsxwriter as xlsxwriter
import xlwt
import paramiko

client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
know_host = paramiko.AutoAddPolicy()
client.set_missing_host_key_policy(know_host)


def ssh(ip, port, username, passwd, shell):
    # def ssh(hostinfo, shell):
    client.connect(hostname=ip, port=port, username=username, password=passwd)
    stdin, stdout, stderr = client.exec_command(shell, get_pty=True, timeout=1)
    # get_pty=True #可以同时执行多条命令
    out = (stdout.read().decode('utf-8'))
    status = stdout.channel
    code = status.recv_exit_status()
    outinfo = {
        "host": ip,
        "out": out,
        "code": code,
        "error": stderr,
    }
    print("outinfo:%s" % outinfo)
    return outinfo


user = 'root'
pawd = '123456'
cmd = 'userdel test1'
port = 22

data = [
    ['2017-9-1', '2017-9-2', '2017-9-3', '2017-9-4', '2017-9-5', '2017-9-6'],
    [10, 40, 50, 20, 10, 50],
    [30, 60, 70, 50, 40, 30],
]  # 自己造的数据
host = ['192.168.1.1', '10.10.10.1', '20.20.20.1', '30.30.30.30']
workbook = xlsxwriter.Workbook('new_excel1.xlsx')  # 新建excel表
worksheet = workbook.add_worksheet('sheet1')  # 新建sheet（sheet的名称为"sheet1"）
headings = ['Host', 'Code', 'result']  # 设置表头

worksheet.write_row('A1', headings)

for ip in host:
    print(ip)
    worksheet.write_column('A2', ip)
# worksheet.write_column('A2', data[0])
# worksheet.write_column('B2', data[1])
# worksheet.write_column('C2', data[2])  # 将数据插入到表格中

workbook.close()
# workBook = xlrd.open_workbook(r'/Users/dayang/PycharmProjects/Devops/host.xls')
# logs = xlwt.Workbook(encoding='utf-8')  # 新建excel表
# worksheet = logs.add_sheet('logs')  # 新建sheet（sheet的名称为"logs"
# headings = ['Host', 'code', 'result']  # 设置表头

# sheet2 = workBook.sheet_by_index(0)
# for rows in range(int(sheet2.nrows)):
#     row = sheet2.row_values(rows)
#     info = ssh(row[0], port, user, pawd, cmd)
#     worksheet = logs.get_sheet('sheet')
#     worksheet.write(rows, rows, info['code'])
#     logs.save('logs.xls')

# worksheet.write(rows, (info['host'])
# print(info)
# print(info['host'])
# print(info['code'])
# print(info['out'])