python解析excel并按照格式输出 | ntssl.cn

python解析excel并按照格式输出

python解析excel并按照格式输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
# -*-coding:utf-8 -*-
# BY WANGCC

import os,re
from xlrd import open_workbook
import xlsxwriter
import time
from xlutils.copy import copy
import xlrd,xlwt
import time



def walk(path):
file_list = []
if not os.path.exists(path):
return -1
for root,dirs,names in os.walk(path):
for filename in names:
file_list.append(os.path.join(root,filename)) #路径和文件名连接构成完整路径
return (file_list)

def ip_exist_one(one_url):
ip_list=[]
compile_rule = re.compile(r'\d+[\.]\d+[\.]\d+[\.]\d+')
match_list = re.findall(compile_rule, one_url)
for i in match_list:
if i.isdigit:
ip_list.append(i)
return ip_list

def parse_excel(ip,parse_fie_path):
data=[]
file_name_list = parse_fie_path.split()
#print(ip,file_name)
now_time = time.strftime("%Y/%m/%d %H:%M:%S", time.localtime())
str_8 = '第三方检查'
for key,value in enumerate(file_name_list):
try:
workbook = open_workbook(file_name_list[key])
sheet = workbook.sheet_by_name(r'漏洞信息')
except Exception as e:
print('*'*20,e)
pass
for key1,value in enumerate(range(sheet.nrows)):
content=sheet.row_values(key1)
content.insert(0,ip[key])
content.insert(1, '操作系统')
data.append(content)
del data[0]
# for line in (data):
# print(line)
for i in range(len(data)):
list_del = [-1, -3, -4, -5, -3, -4, -4, -4, -4, -4, -4,3,3,4]
for index in list_del:
del data[i][index]
data[i][2],data[i][3],data[i][4],data[i][5],data[i][6],data[i][7]=str(data[i][4])[1],data[i][5],data[i][3],data[i][6],data[i][7],data[i][2]
print( '---<<<',data[i])
data[i]= data[i]
data[i].append(now_time)
data[i].append(str_8)
len_data = len(data)
port = data[i][7]
return (data)




def write_excel_xls(path, sheet_name, value):
index = len(value) # 获取需要写入数据的行数
workbook = xlwt.Workbook(path+'\\'+sheet_name) # 新建一个工作簿
worksheet = workbook.add_sheet('资产漏洞信息') # 在工作簿中新建一个表格
worksheet.write(0, 0, 'IP')
worksheet.write(0, 1, '漏洞影响')
worksheet.write(0, 2, '风险等级')
worksheet.write(0, 3, 'CVE编号')
worksheet.write(0, 4, '漏洞名称')
worksheet.write(0, 5, '漏洞描述')
worksheet.write(0, 6, '整改意见')
worksheet.write(0, 7, '发现时间')
worksheet.write(0, 8, '漏洞检查来源')
workbook.save(path+'\\'+sheet_name) # 保存工作簿
print("xls格式表格写入数据成功!")

def write_excel_xls_append(path, value):
now_time = time.strftime("%Y/%m/%d %H:%M:%S", time.localtime())
index = len(value) # 获取需要写入数据的行数
workbook = xlrd.open_workbook(path,formatting_info=True) # 打开工作簿
sheets = workbook.sheet_names() # 获取工作簿中的所有表格
worksheet = workbook.sheet_by_name(sheets[0]) # 获取工作簿中所有表格中的的第一个表格
rows_old = worksheet.nrows # 获取表格中已存在的数据的行数
new_workbook = copy(workbook) # 将xlrd对象拷贝转化为xlwt对象
new_worksheet = new_workbook.get_sheet(0) # 获取转化后工作簿中的第一个表格
for i in range(0, index):
for j in range(0, len(value[i])):
new_worksheet.write(i+rows_old, j, value[i][j]) # 追加写入数据,注意是从i+rows_old行开始写入
new_workbook.save(path) # 保存工作簿
print(now_time,"---> xls格式表格【追加】写入数据成功!")

def copy_file(src_file_name,dir_file_name):
# 打开文件
source_file = open(src_file_name, "rb")
new_file = open(dir_file_name, "wb")

# 操作文件
content = source_file.read()

new_file.write(content)

# 关闭文件
source_file.close()
new_file.close()

def create_excel(src_file_name,dir_file):
t = time.time()
stamp = str(int(t))
now_time = time.strftime("%Y/%m/%d %H:%M:%S", time.localtime()) #发现时间
excel_name ='资产漏洞模板_'+stamp +".xls"
if not os.path.exists(dir_file+'\\'+excel_name):
copy_file(src_file_name,dir_file+'\\'+excel_name)
print(dir_file+'\\'+excel_name,'创建完成!')
return (dir_file+'\\'+excel_name)

if __name__ == '__main__':
file_list=[]
src_file_path=r'C:\Users\wangchuanchen\Desktop\导入\资产漏洞模板.xls'
dir_path = r'C:\Users\wangchuanchen\Desktop\平台导入\dir_path\1'
new_excel_name = create_excel(src_file_path,dir_path)
file_name_list = walk(dir_path)
for file_name in file_name_list:
Correct_file=ip_exist_one(file_name)
if len(Correct_file) == 1:
wait_processed=(dir_path+'\\'+ ' '.join(Correct_file)+'.xls')
parse_excel(Correct_file, wait_processed)
data = parse_excel(Correct_file,wait_processed)
write_excel_xls_append(new_excel_name, data)