def exists(self):
title = [u"告警时间", u"数据录入时间", u"所属分行", u"IP地址", u"MAC地址",u"感染主机名",
u"病毒名称", u"病毒类型", u"受感染文件", u"感染源", u"感染路径", u"处理结果", u"感染类型",
u"感染机被感染时间", u"扫描方式", u"病毒码组件", u"系统类型"]
self.date = datetime.datetime.now().strftime("%Y%m%d")
if os.path.exists("logFile/%s.xlsx" % self.date):
wb = openpyxl.load_workbook("logFile/%s.xlsx" % self.date)
try:
ws = wb.get_sheet_by_name(u"告警日志")
except:
wb.close()
wb = Data_Operat().create_Newsheet("logFile/%s.xlsx" % self.date, [u"告警日志"])
ws = Data_Operat().get_Sheet(wb, u"告警日志", title)
else:
wb = openpyxl.Workbook()
wb.create_sheet(u"告警日志")
wb.remove_sheet(wb.get_sheet_by_name("Sheet"))
ws = wb.get_sheet_by_name(u"告警日志")
ws.append(title)
return wb, ws
class Log_Virus(object):
def __init__(self, time_, asset_virus, asset_branch, log):
self.time = time_
self.asset_virus = asset_virus
self.asset_branch = asset_branch
self.log = log
def excel_Open(self):
if not os.path.exists("logFile/" + self.time + ".xlsx"):
self.wb_virus_log = DO().create_Newsheet(excel_name="logFile/" +
self.time + ".xlsx",
sheet_name=[u"日志"])
else:
self.wb_virus_log = openpyxl.load_workbook("logFile/" + self.time +
".xlsx")
def log_Operat(self):
if not os.path.exists("logFile/" + self.time + ".xlsx"):
ws_virus_log = DO().get_Sheet(wb=self.wb_virus_log,
sheet_name=u"日志",
title=[
u"结构", u"IP地址", u"主机类型",
u"MAC地址", u"计算机名", u"病毒名称",
u"病毒类型", u"受感染文件", u"感染路径",
u"攻击类型", u"处理措施", u"感染类型", u"时间",
u"扫描类型", u"组件版本", u"操作系统"
])
else:
ws_virus_log = self.wb_virus_log.get_sheet_by_name(u"日志")
for log in self.log:
log_end = list(log)
if log_end[7] == "21":
log_end[7] = u"已清除"
if log_end[7] == "121":
log_end[7] = u"已删除"
if log_end[7] == "25":
log_end[7] = u"已忽略"
if log_end[7] == "22":
log_end[7] = u"无法清除文件"
if log_end[7] == "122":
log_end[7] = u"无法删除文件"
if log_end[7] == "81":
log_end[7] = u"已加密"
host = DO().system_Belong(ip=log[1],
asset=self.asset_branch,
defaulthost=u"未知设备")
log_end.insert(2, host)
virus = DO().virus_Belong(virus=log[4],
asset=self.asset_virus,
defaultvirus=u"未知病毒")
log_end.insert(6, virus)
U = DO().U_Belong(U=log[6])
log_end.insert(9, U)
operat = DO().operat_Belong(operat=log[7])
log_end.insert(11, operat)
ws_virus_log.append(log_end)
def run(self):
self.excel_Open()
self.log_Operat()
self.wb_virus_log.save("logFile/" + self.time + ".xlsx")
self.wb_virus_log.close()
class Daily_Operat(object):
def __init__(self):
self.top = {}
self.charts = {}
self.chartr = {}
self.port = {}
self.regular_type = []
self.title = [
u'告警时间', u'规则名称', u'源IP', u'源端口', u'目的IP', u'目的端口', u'上报引擎',
u'返回消息', u'网口编号', u'网口别名', u'全行'
]
def internet_Event(self):
self.__init__()
filename = "Internet_Event"
self.regular_type.append(re.compile(r'http.status_code=4(.*?);'))
self.regular_type.append(re.compile(r'http.status_code=;'))
self.regular_type.append(re.compile(r'http.url=/;'))
self.regular_type.append(re.compile(r'http.url=;'))
_xlxs_csv.Csv2Xlxs(self.time, filename)
wb_source = openpyxl.load_workbook("inputFile/" + self.time + "/" +
filename + ".xlsx")
ws_source = wb_source.get_sheet_by_name("Sheet")
ws_filter_portall = DO().get_Sheet(self.wb_filter, u"端口(全)",
self.title)
ws_filter_urlall = DO().get_Sheet(self.wb_filter, u"URL(全)",
self.title)
ws_chart_portall = DO().get_Sheet(self.wb_chart, u"端口(全)",
[u"端口", u"次数"])
ws_chart_urlall_sys = DO().get_Sheet(self.wb_chart, u"URL(全)系统",
[u"系统", u"次数"])
ws_chart_urlall_rul = DO().get_Sheet(self.wb_chart, u"URL(全)规则",
[u"规则", u"次数"])
ws_top_all = DO().get_Sheet(self.wb_top, u"URL(全)", [u"IP", u"次数"])
for rows_source in list(ws_source.rows):
rows_source = list(rows_source)
sys_all = DO().system_Belong(rows_source[4].value, self.assetall)
if sys_all != None:
data = DO().get_Data(rows_source)
data.append(sys_all)
self.port = DO().count_Dict(self.port, data[5])
ws_filter_portall.append(data)
if data[7] != None:
if "http" in data[7]:
if DO().data_Regular(rows_source[7].value,
self.regular_type):
ws_filter_urlall.append(data)
self.top = DO().count_Dict(self.top, data[2])
self.charts = DO().count_Dict(
self.charts, data[-1])
self.chartr = DO().count_Dict(self.chartr, data[1])
ws_top_all = DO().get_Dict_data(ws_top_all, self.top)
ws_chart_urlall_sys = DO().get_Dict_data(ws_chart_urlall_sys,
self.charts)
ws_chart_urlall_rul = DO().get_Dict_data(ws_chart_urlall_rul,
self.chartr)
ws_chart_portall = DO().get_Dict_data(ws_chart_portall, self.port)
self.wb_filter.save("outputFile/" + self.time + "/" + u"数据筛选" +
".xlsx")
self.wb_chart.save("outputFile/" + self.time + "/" + u"图表" + ".xlsx")
self.wb_top.save("outputFile/" + self.time + "/" + u"Top5" + ".xlsx")
wb_source.close()
print u"======原始筛选完成======"
def vulnerability_Attack(self):
filename = "Vulnerability_Attack"
self.similar(filename, u"漏洞(全)")
print u"======漏洞筛选完成======"
def cross_Site(self):
filename = "Cross_Site_Injection"
self.similar(filename, u"跨站(全)")
print u"======跨站筛选完成======"
def login_Attempt(self):
self.__init__()
filename = "Login_Attempt"
self.regular_type.append(re.compile(r'http.status_code=4(.*?);'))
self.regular_type.append(re.compile(r'http.status_code=;'))
_xlxs_csv.Csv2Xlxs(self.time, filename)
wb_source = openpyxl.load_workbook("inputFile/" + self.time + "/" +
filename + ".xlsx")
ws_source = wb_source.get_sheet_by_name("Sheet")
ws_filter_all = DO().get_Sheet(self.wb_filter, u"登录(全)", self.title)
ws_chart_loginall = DO().get_Sheet(self.wb_chart, u"登录(全)",
[u"系统", u"次数"])
ws_top_all = DO().get_Sheet(self.wb_top, u"登录(全)", [u"IP", u"次数"])
for rows_source in list(ws_source.rows):
rows_source = list(rows_source)
if DO().data_Regular(rows_source[7].value, self.regular_type):
sys_all = DO().system_Belong(rows_source[4].value,
self.assetall)
if sys_all != None:
data = DO().get_Data(rows_source)
data.append(sys_all)
ws_filter_all.append(data)
self.top = DO().count_Dict(self.top, data[2])
self.charts = DO().count_Dict(self.charts, data[-1])
ws_top_all = DO().get_Dict_data(ws_top_all, self.top)
ws_chart_loginall = DO().get_Dict_data(ws_chart_loginall, self.charts)
self.wb_filter.save("outputFile/" + self.time + "/" + u"数据筛选" +
".xlsx")
self.wb_chart.save("outputFile/" + self.time + "/" + u"图表" + ".xlsx")
self.wb_top.save("outputFile/" + self.time + "/" + u"Top5" + ".xlsx")
wb_source.close()
print u"======登录筛选完成======"
def information_Detetion(self):
filename = "Information_Detection"
self.similar(filename, u"探测(全)")
print u"======探测筛选完成======"
def similar(self, filename, class_):
self.__init__()
self.regular_type.append(re.compile(r'http.status_code=4(.*?);'))
self.regular_type.append(re.compile(r'http.status_code=;'))
_xlxs_csv.Csv2Xlxs(self.time, filename)
wb_source = openpyxl.load_workbook("inputFile/" + self.time + "/" +
filename + ".xlsx")
ws_source = wb_source.get_sheet_by_name("Sheet")
ws_filter_all = DO().get_Sheet(self.wb_filter, class_, self.title)
ws_chart_all_sys = DO().get_Sheet(self.wb_chart, class_ + u"系统",
[u"系统", u"次数"])
ws_chart_all_rul = DO().get_Sheet(self.wb_chart, class_ + u"规则",
[u"规则", u"次数"])
ws_top_all = DO().get_Sheet(self.wb_top, class_, [u"IP", u"次数"])
for rows_source in list(ws_source.rows):
rows_source = list(rows_source)
if DO().data_Regular(rows_source[7].value, self.regular_type):
sys_all = DO().system_Belong(rows_source[4].value,
self.assetall)
if sys_all != None:
data = DO().get_Data(rows_source)
data.append(sys_all)
ws_filter_all.append(data)
self.top = DO().count_Dict(self.top, data[2])
self.charts = DO().count_Dict(self.charts, data[-1])
self.chartr = DO().count_Dict(self.chartr, data[1])
ws_top_all = DO().get_Dict_data(ws_top_all, self.top)
ws_chart_all_sys = DO().get_Dict_data(ws_chart_all_sys, self.charts)
ws_chart_all_rul = DO().get_Dict_data(ws_chart_all_rul, self.chartr)
self.wb_filter.save("outputFile/" + self.time + "/" + u"数据筛选" +
".xlsx")
self.wb_top.save("outputFile/" + self.time + "/" + u"Top5" + ".xlsx")
self.wb_chart.save("outputFile/" + self.time + "/" + u"图表" + ".xlsx")
wb_source.close()
def IP(self):
ws_source = self.wb_count.get_sheet_by_name(u"IP(全)")
ws_char_country = DO().get_Sheet(self.wb_chart, u"IP(全)1", [u"国家"])
ws_char_city = DO().get_Sheet(self.wb_chart, u"IP(全)2", [u"城市"])
ws_top = DO().get_Sheet(self.wb_top, u"IP(全)", [u"IP", u"次数"])
count = 0
data = []
data_sort = []
for row in list(ws_source.rows):
data_ = []
row = list(row)
if count < 1:
count += 1
continue
for x in row:
data_.append(x.value)
data.append(data_)
for x in range(len(data)):
if x == 0:
data_sort.append(data[x])
continue
data_sort.append(data[x])
for i in range(1, x + 1):
if int(data_sort[x + 1 - i][1]) > int(data_sort[x - i][1]):
a = data_sort[x - i]
data_sort[x - i] = data_sort[x + 1 - i]
data_sort[x + 1 - i] = a
for data_ in data_sort:
ws_char_country.append([data_[2]])
if u"澳门" not in data_[4] and u"香港" not in data_[
4] and u"台湾" not in data_[4] and u"NULL" not in data_[
4] and u"中国" in data_[2]:
ws_char_city.append([data_[4]])
ws_top.append([data_[0], data_[1]])
self.wb_top.save("outputFile/" + self.time + "/" + u"Top5" + ".xlsx")
self.wb_chart.save("outputFile/" + self.time + "/" + u"图表" + ".xlsx")
def rate_Count(self):
filename = 'IP_with_area'
_xlxs_csv.Csv2Xlxs(self.time, filename)
DO().rate_Count(self.time)
print u"======频率统计完成======"
def open_Excel(self):
filtername = [
u"端口(全)", u"URL(全)", u"漏洞(全)", u"跨站(全)", u"登录(全)", u"探测(全)"
]
chartname = [
u"IP(全)1", u"IP(全)2", u"端口(全)", u"URL(全)系统", u"URL(全)规则",
u"漏洞(全)系统", u"漏洞(全)规则", u"跨站(全)系统", u"跨站(全)规则", u"登录(全)",
u"探测(全)系统", u"探测(全)规则"
]
topname = [u"IP(全)", u"URL(全)", u"漏洞(全)", u"跨站(全)", u"登录(全)", u"探测(全)"]
countname = [u"统计"]
self.wb_filter = DO().create_Newsheet(
"outputFile/" + self.time + "/" + u"数据筛选" + ".xlsx", filtername)
self.wb_chart = DO().create_Newsheet(
"outputFile/" + self.time + "/" + u"图表" + ".xlsx", chartname)
self.wb_top = DO().create_Newsheet(
"outputFile/" + self.time + "/" + u"Top5" + ".xlsx", topname)
self.wb_count = DO().create_Newsheet(
"outputFile/" + self.time + "/" + u"统计" + ".xlsx", countname)
def get_Asset(self):
wb_asset = openpyxl.load_workbook('inputFile/assets2017-5-26.xlsx')
ws_assetall = wb_asset.get_sheet_by_name(u"全行资产")
self.assetall = []
count = 0
for x in list(ws_assetall.rows):
if count < 1:
count += 1
continue
self.assetall.append([x[1].value, x[2].value])
def data_Count_(self, ws_count, x):
number = len(list(
self.wb_filter.get_sheet_by_name(x + u'(全)').rows)) - 1
number_system = len(
list(self.wb_chart.get_sheet_by_name(x + u'(全)系统').rows)) - 1
if number_system != 0:
system = list(
list(self.wb_chart.get_sheet_by_name(x + u'(全)系统').rows)
[1])[0].value
else:
system = None
if number != 0:
percent_system = float(
list(
list(self.wb_chart.get_sheet_by_name(x + u'(全)系统').rows)
[1])[1].value / float(number))
else:
percent_system = None
if number_system != 0:
rule = list(
list(self.wb_chart.get_sheet_by_name(x + u'(全)规则').rows)
[1])[0].value
else:
rule = None
ws_count.append(
[x, number, number_system, system, percent_system, rule])
return ws_count
def data_Count(self):
ws_count = DO().get_Sheet(
self.wb_count, u"统计",
[u"种类", u"次数", u"系统个数", u"系统", u"百分比", u"规则"])
ws_count.append([
u"告警总数",
len(list(self.wb_filter.get_sheet_by_name(u'端口(全)').rows)) - 1
])
ws_count.append([
u"IP",
len(list(self.wb_top.get_sheet_by_name(u'IP(全)').rows)) - 1
])
ws_count.append([
u"端口",
len(list(self.wb_chart.get_sheet_by_name(u'端口(全)').rows)) - 1
])
ws_count = self.data_Count_(ws_count, u"URL")
ws_count = self.data_Count_(ws_count, u"漏洞")
ws_count = self.data_Count_(ws_count, u"跨站")
ws_count.append([
u"登录(全)",
len(list(self.wb_filter.get_sheet_by_name(u'登录(全)').rows)) - 1
])
ws_count = self.data_Count_(ws_count, u"探测")
self.wb_count.save("outputFile/" + self.time + "/" + u"统计" + ".xlsx")
print u"======数据统计完成======"
def top5_Count(self):
class_ = [
u"IP(全)",
u"URL(全)",
u"漏洞(全)",
u"跨站(全)",
u"登录(全)",
u"探测(全)",
]
for x in class_:
DO().area_Mate(self.time, x)
print u"======Top5统计完成======"
def run(self, time):
self.time = time
self.rate_Count()
self.get_Asset()
self.open_Excel()
self.internet_Event()
self.vulnerability_Attack()
self.cross_Site()
self.login_Attempt()
self.information_Detetion()
self.IP()
self.top5_Count()
self.data_Count()
class Daily_Operat(object):
def __init__(self):
self.topall = {}
self.topzz = {}
self.topwy = {}
self.chartall = {}
self.chartzz = {}
self.charts = {}
self.chartwy = {}
self.regular_type_1 = []
self.regular_type_0 = []
self.title = [
u'告警时间', u'规则名称', u'源IP', u'源端口', u'目的IP', u'目的端口', u'上报引擎',
u'返回消息', u'网口编号', u'网口别名', u'分行'
]
self.title_f = [
u'告警时间', u'规则名称', u'源IP', u'源端口', u'目的IP', u'目的端口', u'上报引擎',
u'Host', u"Url", u"User-Agent", u"Status_Code", u'网口编号', u'网口别名',
u'分行'
]
self.title_lan = [
u'告警时间', u'规则名称', u'源IP', u'源端口', u'目的IP', u'目的端口', u'上报引擎',
u'返回消息', u'网口编号', u'网口别名', u'源所属分行', u"源设备", u"目所属分行", u"目设备"
]
def internet_Event(self):
self.__init__()
filename = "Internet_Event"
self.regular_type_1.append(re.compile(r'http.status_code=4(.*?);'))
self.regular_type_0.append(re.compile(r'http.status_code=(.*?);'))
self.regular_type_0.append(re.compile(r'http.url=/(.*?);'))
self.regular_type_0.append(re.compile(r'http.url=(.*?);'))
try:
_xlxs_csv.Csv2Xlxs(self.time, filename)
except:
pass
wb_source = openpyxl.load_workbook("inputFile/" + self.time + "/" +
filename + ".xlsx")
ws_source = wb_source.get_sheet_by_name("Sheet")
ws_filter_urlall = DO().get_Sheet(self.wb_filter, u"URL(分)",
self.title)
ws_filter_url_f = DO().get_Sheet(self.wb_filter, u"URL(分)筛",
self.title_f)
ws_chart_urlsys = DO().get_Sheet(self.wb_chart, u"URL(分)系统",
[u"系统", u"次数"])
ws_chart_url = DO().get_Sheet(self.wb_chart, u"URL(分)筛URL",
[u"URL", u"次数"])
ws_top_url = DO().get_Sheet(self.wb_top, u"URL(分)",
[u"URL", u"应用系统", u"源IP", u"告警名称", u"频率"])
for rows_source in list(ws_source.rows):
rows_source = list(rows_source)
sys_other = DO().system_Belong(rows_source[4].value,
self.assetother)
if sys_other != None:
data = DO().get_Data(rows_source)
data.append(sys_other)
ws_filter_urlall.append(data)
self.charts = DO().count_Dict(self.charts, data[-1])
if rows_source[7].value != None:
if "http.host" in rows_source[7].value:
if DO().data_Regular(rows_source[7].value,
self.regular_type_1, 1):
if DO().data_Regular(rows_source[7].value,
self.regular_type_0, 0):
host, url, user_agent, status_code = DO(
).http_Split(data[7])
for x in status_code, user_agent, url, host:
data.insert(7, x)
ws_filter_url_f.append(data)
if host != 'NULL':
self.chartall = DO().count_Dict(
self.chartall, host + data[8])
self.topall = DO().count_Dict(
self.topall, host + data[8],
[data[-1], data[2], data[1]])
else:
self.chartall = DO().count_Dict(
self.chartall, data[4] + data[8])
self.topall = DO().count_Dict(
self.topall, data[4] + data[8],
[data[-1], data[2], data[1]])
ws_chart_url = DO().get_Dict_data(ws=ws_chart_url,
dict=self.chartall,
des=1)
ws_chart_urlsys = DO().get_Dict_data(ws=ws_chart_urlsys,
dict=self.charts,
des=1)
ws_top_url = DO().get_Dict_data(ws=ws_top_url,
dict=self.topall,
des=1,
delet=1)
self.wb_filter.save("outputFile/" + self.time + "/" + u"数据筛选" +
".xlsx")
self.wb_chart.save("outputFile/" + self.time + "/" + u"图表" + ".xlsx")
self.wb_top.save("outputFile/" + self.time + "/" + u"Top" + ".xlsx")
wb_source.close()
print u"======分行筛选完成======"
def lan_Event(self):
self.__init__()
filename = "Lan_Event"
self.regular_type_1.append(re.compile(u"r'http.status_code=4(.*?);'"))
self.regular_type_0.append(re.compile(u"r'http.status_code=(.*?);'"))
try:
_xlxs_csv.Csv2Xlxs(self.time, filename)
except:
pass
wb_source = openpyxl.load_workbook("inputFile/" + self.time + "/" +
filename + ".xlsx")
ws_source = wb_source.get_sheet_by_name("Sheet")
ws_filter_lan = DO().get_Sheet(self.wb_filter, u"内网(分)",
self.title_lan)
ws_filter_zz = DO().get_Sheet(self.wb_filter, u"自助设备", self.title_lan)
ws_filter_wy = DO().get_Sheet(self.wb_filter, u"网银体验", self.title_lan)
ws_chart_lan = DO().get_Sheet(self.wb_chart, u"内网(分)", [u"分行", u"次数"])
ws_chart_zz = DO().get_Sheet(self.wb_chart, u"自助设备", [u"分行", u"次数"])
ws_chart_wy = DO().get_Sheet(self.wb_chart, u"网银体验", [u"分行", u"次数"])
ws_top_lan = DO().get_Sheet(
self.wb_top, u"内网(分)",
[u"源IP", u"源所属分行/设备", u"目IP", u"目所属分行/设备", u"规则", u"次数"])
ws_top_zz = DO().get_Sheet(
self.wb_top, u"自助设备",
[u"源IP", u"源所属分行/设备", u"目IP", u"目所属分行/设备", u"规则", u"次数"])
ws_top_wy = DO().get_Sheet(
self.wb_top, u"网银体验",
[u"源IP", u"源所属分行/设备", u"目IP", u"目所属分行/设备", u"规则", u"次数"])
for rows_source in list(ws_source.rows):
rows_source = list(rows_source)
if u"告警时间" not in rows_source[0].value:
if DO().data_Regular(rows_source[7].value, self.regular_type_1,
1):
if DO().data_Regular(rows_source[7].value,
self.regular_type_0, 0):
sbranch, sequ = DO().branch_Belong(
rows_source[2].value, self.assetlan)
obranch, oequ = DO().branch_Belong(
rows_source[4].value, self.assetlan)
data = DO().get_Data(rows_source)
data.append(sbranch)
data.append(sequ)
data.append(obranch)
data.append(oequ)
ws_filter_lan.append(data)
self.chartall = DO().count_Dict(
self.chartall, data[-2])
self.topall = DO().count_Dict(self.topall, data[2], [
data[-4] + '/' + data[-3], data[4],
data[-2] + '/' + data[-1], data[1]
])
if u"自助设备" in sequ or u"自助设备" in oequ:
ws_filter_zz.append(data)
self.chartzz = DO().count_Dict(
self.chartzz, data[-2])
self.topzz = DO().count_Dict(
self.topzz, data[2], [
data[-4] + '/' + data[-3], data[4],
data[-2] + '/' + data[-1], data[1]
])
if u"网银体验" in sequ or u"网银体验" in oequ:
ws_filter_wy.append(data)
self.chartwy = DO().count_Dict(
self.chartwy, data[-2])
self.topwy = DO().count_Dict(
self.topwy, data[2], [
data[-4] + '/' + data[-3], data[4],
data[-2] + '/' + data[-1], data[1]
])
ws_chart_lan = DO().get_Dict_data(ws_chart_lan, self.chartall, 1)
ws_chart_zz = DO().get_Dict_data(ws_chart_zz, self.chartzz, 1)
ws_chart_wy = DO().get_Dict_data(ws_chart_wy, self.chartwy, 1)
ws_top_lan = DO().get_Dict_data(ws_top_lan, self.topall, 1)
ws_top_zz = DO().get_Dict_data(ws_top_zz, self.topzz, 1)
ws_top_wy = DO().get_Dict_data(ws_top_wy, self.topwy, 1)
self.wb_filter.save("outputFile/" + self.time + "/" + u"数据筛选" +
".xlsx")
self.wb_chart.save("outputFile/" + self.time + "/" + u"图表" + ".xlsx")
self.wb_top.save("outputFile/" + self.time + "/" + u"Top" + ".xlsx")
wb_source.close()
print u"======内网筛选完成======"
def open_Excel(self):
filtername = [u"URL(分)", u"URL(分)筛", u"内网(分)", u"自助设备", u"网银体验"]
chartname = [u"URL(分)系统", u"URL(分)筛URL", u"内网(分)", u"自助设备", u"网银体验"]
topname = [u"URL(分)", u"内网(分)", u"自助设备", u"网银体验"]
countname = [u"统计"]
self.wb_filter = DO().create_Newsheet(
"outputFile/" + self.time + "/" + u"数据筛选" + ".xlsx", filtername)
self.wb_chart = DO().create_Newsheet(
"outputFile/" + self.time + "/" + u"图表" + ".xlsx", chartname)
self.wb_top = DO().create_Newsheet(
"outputFile/" + self.time + "/" + u"Top" + ".xlsx", topname)
self.wb_count = DO().create_Newsheet(
"outputFile/" + self.time + "/" + u"统计" + ".xlsx", countname)
self.wb_rate_internet = DO().create_Newsheet("../IP/Internet.xlsx",
[self.time])
self.wb_rate_lan = DO().create_Newsheet("../IP/Lan.xlsx", [self.time])
def data_Count(self):
ws_count = DO().get_Sheet(self.wb_count, u"统计", [u"类别", u"次数"])
ws_count.append([
u"互联网告警数",
len(list(self.wb_filter.get_sheet_by_name(u"URL(分)").rows)) - 1
])
ws_count.append([
u"内网告警数",
len(list(self.wb_filter.get_sheet_by_name(u"内网(分)"))) - 1
])
ws_count.append([
u"自助设备数",
len(list(self.wb_filter.get_sheet_by_name(u"自助设备"))) - 1
])
ws_count.append([
u"网银体验数",
len(list(self.wb_filter.get_sheet_by_name(u"网银体验"))) - 1
])
self.wb_count.save("outputFile/" + self.time + "/" + u"统计" + ".xlsx")
def get_Asset(self):
wb_asset = openpyxl.load_workbook('inputFile/assets2017-5-26.xlsx')
wb_asset_lan = openpyxl.load_workbook('inputFile/all.xlsx')
wb_asset_2to1 = openpyxl.load_workbook('inputFile/2to1.xlsx')
ws_assetother = wb_asset.get_sheet_by_name(u"分行资产")
ws_asset_lan = wb_asset_lan.get_sheet_by_name(u"Sheet")
ws_asset_2to1 = wb_asset_2to1.get_sheet_by_name(u"Sheet")
self.assetother = []
self.assetlan = []
self.asset2to1 = []
count = 0
for x in list(ws_assetother.rows):
if count < 1:
count += 1
continue
self.assetother.append([x[1].value, x[2].value])
for x in list(ws_asset_2to1.rows):
data = DO().get_Data(x)
self.asset2to1.append(data)
for x in list(ws_asset_lan.rows):
if x[6].value != None:
result = re.compile(r'\((.*?)\)').findall(x[1].value)[0]
for y in self.asset2to1:
if result in y:
branch = y[0]
self.assetlan.append(
[branch, x[4].value, x[6].value, x[7].value])
def run(self, time):
self.time = time
self.get_Asset()
self.open_Excel()
self.internet_Event()
self.lan_Event()
self.data_Count()
self.wb_count.close()
self.wb_filter.close()
self.wb_top.close()
self.wb_chart.close()
