1.获取需要操作的日志文件名及文件路径。
#读取目录下的所有文件,包括嵌套的文件夹
def GetFileList(dir, fileList):
newDir = dir
if os.path.isfile(dir):
fileList.append(dir)
elif os.path.isdir(dir):
for s in os.listdir(dir):
newDir = os.path.join(dir, s)
GetFileList(newDir, fileList)
return fileList
2.让每一个log文件对应一个Sheet
# 使log文件对应相应的Sheet
def log_sheet(f_name):
sh_name=''
if 'gxxx_' in f_name:
sh_name='xxx主库'
# ... (省略。。。)
elif 'cxxx_' in f_name:
sh_name='xxx平台'
else:
pass
return sh_name
3.分块提取log日志内容
def type_inlist(data,in_list):
test_str='' # 定义需要插入的字符串
dict_key=''
flag=False
for each_line in data: # 提取日志文件
if "###" in each_line: # 判断是否要提取的内容
dict_key=each_line
flag=True # 如果是要拼接的字符,则设置flag为True,开始拼接字符
elif '~~' in each_line: # 判断是否到需要提取块的全部内容
# 添加元素2
in_list[dict_key]=test_str
test_str='' # 字符串清空。等待接收下一个需要插入的块
flag=False # flag设回False,继续查找需要插入的块
elif flag:
test_str=test_str+each_line+'\n' # 拼接log文件的字符串,形成一个块
else:
pass
4.返回需要存储在Sheet的位置
# 获取要存储的位置
def insert_sheet(block):
ro = 0
col = 2 # 以后功能增加需要修改
if "###netstat" in block:
ro = 13
elif "###filesystem" in block:
ro = 14
elif "###vmstat" in block:
ro = 15
elif "###svmon" in block:
ro = 16
elif "###check archive" in block:
ro = 17
elif "###check db2diag error" in block:
ro = 18
elif "###transactions" in block:
ro = 19
elif "###tablespaces status" in block:
ro = 20
elif "###tablespaces used" in block:
ro = 21
elif "###app messages" in block:
ro = 22
elif "###buffer pools" in block:
ro = 23
else:
pass
return ro,col
5.写入对应的Sheet中
# 将log日志写入excel表对应的Sheet中
def re_and_wr(file_name,ws):
try:
with open(file_name) as data: # 打开log文件
# ws = wb[sheet_name] # 获取Sheet
in_dict={}
type_inlist(data,in_dict)
for block in in_dict:
ro,col = insert_sheet(block) # 获取块在excel的存储位置
if ro > 0:
ws.cell(row=ro, column=col, value=in_dict[block].format(col)) # 写入excel
except KeyError as k_err:
print(sheet_name+'(Sheet)不存在')
except IOError as err:
print('IOError: ' + str(err))
全部代码:
import os
import os.path
from openpyxl import load_workbook
#读取目录下的所有文件,包括嵌套的文件夹
def GetFileList(dir, fileList):
newDir = dir
if os.path.isfile(dir):
fileList.append(dir)
elif os.path.isdir(dir):
for s in os.listdir(dir):
newDir = os.path.join(dir, s)
GetFileList(newDir, fileList)
return fileList
# 提取指定的内容,存入字典
def type_inlist(data,in_list):
test_str='' # 定义需要插入的字符串
dict_key=''
flag=False
for each_line in data: # 提取日志文件
if "###" in each_line: # 判断是否要提取的内容
dict_key=each_line
flag=True # 如果是要拼接的字符,则设置flag为True,开始拼接字符
elif '~~' in each_line: # 判断是否到需要提取块的全部内容
# 添加元素方法2
in_list[dict_key]=test_str
test_str='' # 字符串清空。等待接收下一个需要插入的块
flag=False # flag设回False,继续查找需要插入的块
elif flag:
test_str=test_str+each_line+'\n' # 拼接log文件的字符串,形成一个块
else:
pass
# 获取要存储的位置
def insert_sheet(block):
ro = 0
col = 2 # 以后功能增加需要修改
if "###netstat" in block:
ro = 13
elif "###filesystem" in block:
ro = 14
elif "###vmstat" in block:
ro = 15
elif "###svmon" in block:
ro = 16
elif "###check archive" in block:
ro = 17
elif "###check db2diag error" in block:
ro = 18
elif "###transactions" in block:
ro = 19
elif "###tablespaces status" in block:
ro = 20
elif "###tablespaces used" in block:
ro = 21
elif "###app messages" in block:
ro = 22
elif "###buffer pools" in block:
ro = 23
else:
pass
return ro,col
# 将log日志写入excel表对应的Sheet中
def re_and_wr(file_name,ws):
try:
with open(file_name) as data: # 打开log文件
# ws = wb[sheet_name] # 获取Sheet
in_dict={}
type_inlist(data,in_dict)
for block in in_dict: # 遍历字典,取出一个个块
ro,col = insert_sheet(block) # 获取块在excel的存储位置
if ro > 0: # 判断是否是要写入的块
ws.cell(row=ro, column=col, value=in_dict[block].format(col)) # 写入excel
except KeyError as k_err:
print(sheet_name+'(Sheet)不存在')
except IOError as err:
print('IOError: ' + str(err))
# 使log文件对应相应的Sheet
def log_sheet(f_name):
sh_name=''
if 'gxxx_' in f_name:
sh_name='xxx主库'
# ... (省略。。。)
elif 'cxxx_' in f_name:
sh_name='xxx平台'
else:
pass
return sh_name
# 打开日志文件目录,将相应的日志文件插入对应的Sheet中
def exce(fileDir,excel_dir):
wb = load_workbook(excel_dir)
list = GetFileList(fileDir, [])
for i in list:
fi_name=os.path.basename(i)
if not os.path.exists(fi_name): # 判断工作目录是否存在文件
continue
else:
sh_name = log_sheet(fi_name) # 使log文件对应相应的Sheet
ws = wb[sh_name] # 获取Sheet
re_and_wr(i,ws) # 将log日志写入excel表对应的Sheet中
wb.save(excel_dir)
if __name__ == "__main__":
fileDir = "C:\\Users\\guguozhao\\Desktop\\test\\"
excel_dir = "C:\\Users\\guguozhao\\Desktop\\log_cut\\ts_log.xlsx"
exce(fileDir,excel_dir)
效果如图:
总结:在使用python将日志导入excel的过程中遇到过许多问题。其中最主要的问题是,如何保持log日志格式,将log日志写入excel中和怎么保存excel表的格式,使在写入后并不会改变excel格式。在实验过程中曾用过 xlwt 和 xlrd 库,发现两个库并不好用。xlrd对excel只读,xlwt对excel只写。如果需要从已有的excel中写入数据,这两个库还需要借助xlutils的copy函数。而且使用过程容易导致excel格式改变。到后期改excel格式,也是个麻烦事。从 xlwt 和 xlrd==》openpyxl 踩过许多坑。今晚太累了。对踩过的坑和一些处理问题的方法改日再总结。