# -*- coding:utf-8 -*-

import json
import urllib2
from urllib2 import URLError
#import sys
#import xlrd

class ZabbixTools:
    def __init__(self):
        self.url = 'http://10.11.0.215/zabbix/api_jsonrpc.php']
        self.header = {"Content-Type": "application/json"}

    # 登陆验证
    def user_login(self):
        data = json.dumps({
            "jsonrpc": "2.0",
            "method": "user.login",
            "params": {
                "user": 'admin',
                "password": 'zabbix'
            },
            "id": 0
        })

        request = urllib2.Request(self.url, data)
        for key in self.header:
            request.add_header(key, self.header[key])

        try:
            result = urllib2.urlopen(request)
        except URLError as e:
            print "Auth Failed, please Check your name and password:", e.code
        else:
            response = json.loads(result.read())
            result.close()
            self.authID = response['result']
            return self.authID

   # 获取主机
    def host_get(self):
        data = json.dumps({
            "jsonrpc": "2.0",
            "method": "host.get",
            "params": {
                "output": ["hostid", "host"],
                "selectInterfaces": ["interfaceid", "ip"]
            },
            "auth": self.user_login(),
            "id": 1,
        })

        request = urllib2.Request(self.url, data)
        for key in self.header:
            request.add_header(key, self.header[key])

        try:
            result = urllib2.urlopen(request)
        except URLError as e:
            if hasattr(e, 'reason'):
                print 'We failed to reach a server.'
                print 'Reason: ', e.reason
            elif hasattr(e, 'code'):
                print 'The server could not fulfill the request.'
                print 'Error code: ', e.code
        else:
            response = json.loads(result.read())
            result.close()

            lens = len(response['result'])
            if lens > 0:
                return response['result']
            else:
                return ""

####################################################################################
# -*- coding: utf-8 -*-

import xlrd
import xlwt

#xlutils结合xlrd可以达到修改excel文件目的
from xlutils.copy import copy

from openpyxl import Workbook

from openpyxl import load_workbook

from openpyxl.writer.excel import ExcelWriter

'''
1.类的内部调用：self.<方法名>(参数列表)。

2.在类的外部调用：<实例名>.<方法名>(参数列表)。

注意：以上两种调用方法中，提供的参数列表中都不用包括self。
'''

class ExcelOperation(object):

    '''
    设置默认参数：rowsize  colsize ,创建对象时可以写、可不写默认参数
    '''
    def __init__(self, filename, rowsize=0, colsize=0):
        self.filename = filename
        self.rowsize = rowsize
        self.colsize = colsize

    def open_excel(self):
        return  xlrd.open_workbook(self.filename)


    '''
    table = data.sheets()[0]       #通过索引顺序获取
    table = data.sheet_by_index(0) #通过索引顺序获取
    table = data.sheet_by_name(u'Sheet1')#通过名称获取
    '''

    def read_excel(self):
        workbook = self.open_excel()
        sheet = workbook.sheet_by_index(0)

        nrows = sheet.nrows
        ncols = sheet.ncols

        data = []

        for loop in range(0,nrows):
            data.append(sheet.row_values(loop))
        return data




    def write_excel(self):
        wbk = xlwt.Workbook()
        sheet = wbk.add_sheet('sheet1')
        # 第0行第一列 写入内容
        sheet.write(0,1,'test ')
        wbk.save('test.xls')

    def read_write_excel(self):
        '''
                4.openpyxl可以对excel文件进行读写操作

        from openpyxl import Workbook

        from openpyxl import load_workbook

        from openpyxl.writer.excel import ExcelWriter
        workbook_ = load_workbook(u"新歌检索失败1477881109469.xlsx")
        sheetnames =workbook_.get_sheet_names() #获得表单名字
        print sheetnames
        sheet = workbook_.get_sheet_by_name(sheetnames[0])
        print sheet.cell(row=3,column=3).value
        sheet['A1'] = '47'
        workbook_.save(u"新歌检索失败1477881109469_new.xlsx")
        wb = Workbook()
        ws = wb.active
        ws['A1'] = 4
        wb.save("新歌检索失败.xlsx")
                :return:
        '''

        workbook_ = load_workbook(u'test.xlsx')
        sheetnames = workbook_.get_sheet_names()
        sheet = workbook_.get_sheet_by_name(sheetnames[0])
        print sheet.cell(row=3, column=3).value               




##################################################################################
# -*- coding: utf-8 -*-

from  testZabbixApi import  ZabbixTools
from pyExcel import  ExcelOperation


# 程序的入口
if __name__ == "__main__":
    #实例化excel对象
    tab = ExcelOperation('主机列表.xlsx')
    data = tab.read_excel()
    dirc = {'item{}'.format(i): x for i, x in enumerate(data)}
    #print  dirc['item45'][6]

    # 实例化ZabbixTools对象
    test = ZabbixTools()
    result = test.host_get()

    for key in dirc:
        flag = 'F'
        for i in range(len(result)):
            if dirc[key][6].strip()  ==  result[i]["interfaces"][0]["ip"]:
                flag = 'T'
        if flag == 'T':
            #print(dirc[key][6] + " zabbix")
            pass

        else:
            print(dirc[key][6] + "not add zabbix")