txt转excel

-- coding: utf-8 --

“”"
Spyder Editor

This is a temporary script file.
“”"

import re #正则
import os #文件操作
import time #获取系统时间
#import openpyxl as opxl
from openpyxl import workbook #workbook文件的创建
from openpyxl import load_workbook #装载定位表
#Excel：表格的基本组成：workbook -> sheet -> cell -> cell(行列号定位cell)

def creat_excel(get_log_target,file_name_pre,file_name_suffix,file_name_date,file_extension,file_path):

"""
log本身样子
109/11/2020 15:09:22.426log <K_DATA_UNITS_WRITTEN>349505500</K_DATA_UNITS_WRITTEN>

创建文件并且写入从log获取信息且写入Excel内
:param get_log_target:获取log信息的目的
:param file_name_pre: 保存Excel文件的前缀 --> file_name_pre =get_log_target
:param file_name_suffix：保存Excel文件的后缀
:param file_name_date：创建Excel文件的时间
:param file_extension: Excel文件的扩展名
:param file_path:读取文件路径
:return:
"""
file_name_connecter = '_'    #文件名连接符
#保存文件名：文件前缀 + 文件后缀 + 系统时间.文件扩展名
save_file_name = file_name_pre + file_name_connecter + file_name_suffix + \
    file_name_connecter + file_name_date + file_extension

#创建Excel文件
exworkbook = workbook.Workbook()
# 创建一个sheet表，并且以获取log目的为sheet表名
exworkbook.create_sheet(file_name_suffix,index = 0 )
#保存 Excel工作薄,
#save_info = file_path + r'll'+ save_file_name     # r'\\’ 解析目标'\'：（python解析'\'歧异，利用r'\\'替代'\'）
save_file = file_path + save_file_name             # r'\\'解析目标'\'：（python解析'\'产生岐异，利用r'\\'替代'\'）
exworkbook.save(save_file) 
#print(get_Log_target)

# 打开工作薄
open_work = load_workbook(save_file)
# 定位到表单
open_sheet = open_work[file_name_suffix]


#search_feil :要查找的内容
search_low_lba = 'file 1 @ 0x0000:'        #
search_high_lba = 'file 1 @ 0x0021:'      #
search_num_writes = 'file 1 @ 0x0042:'  #
search_total_lbas_writtrn = 'total_lbas_written'             #
search_quality_ya_li_ji_suan = 'FileData from file 1 @ 0x0000:'       #
search_k_data_units_writer = 'K_DATA_UNITS_WRITTEN'          #
search_k_data_units_read = 'K_DATA_UNITS_READ'               #

search_fild_list = [search_low_lba, search_high_lba,search_num_writes,search_total_lbas_writtrn,
                    search_quality_ya_li_ji_suan, search_k_data_units_writer, search_k_data_units_read]
                    
print('search field len:{}'.format(len(search_fild_list)))

#cell基本单元的组成元素的累计
row_count = 1
columns_count = 1
file_log_num = 1
#列表标题

head_lst_columns = ['file 1','file 2','file 3','k_data_units_r']
             
#向sheet写入列表标题
for i,lie_biao_bian_li in enumerate(head_lst_columns): 
    open_sheet.cell(1,columns_count).value = head_lst_columns[i]        #head_1st_columns[i]
    columns_count += 1
    
#open_sheet.cell(1, 1).value = ''
#open_sheet.cell(1, 2).value = '年龄'

#利用正则,进行切割 re : match, search, find
#date spilt

date_patter_01 = r'\d{2}/\d{2}/\d{4}\s' 
date_patter_02 = r'\d{2}:\d{2}:\d{2}\.\d{3}'
make_up = date_patter_01 + date_patter_02 

#主要信息截取
long_paater = r'<(\w+)>(.*)<(/\1)>' 
#Long_paater_01 = r'<(\w+)>.*</(/\1)>' 
#Long_03 = re.search(Long_paater, long_info)

for root, dirs, files in os.walk(file_path): 
    print('root\t:{}, \n'
          'dirs\t:{}, \n'
          'file_name\t:{}.'.format(root, dirs, files))
    #file_log_num += 1
    
for file in files:
    sub_log_file = os.path.join(file_path,file) 
    fp = open(sub_log_file,encoding='utf-8')
    print('\n第 {} 个 测试log文件：\n'
          '文件名：{}\n'.format(file_log_num,file))
    file_log_num += 1
    
    for line_field in fp.readlines():
        #row_count += 1
        if search_k_data_units_writer in line_field:
            date = re.search(make_up,line_field)
            zhuyao = re.search(long_paater,line_field)
            #print('%s' %line_field,end='')
            row_count += 1
            open_sheet.cell(row_count,1).value = date.group()
            open_sheet.cell(row_count,2).value = int(zhuyao.group(2))
        if search_k_data_units_read in line_field:
            date = re.search(make_up, line_field) 
            zhuyao = re.search(long_paater, line_field) 
            # print('%s' %line_field,end='')
            
            open_sheet.cell(row_count, 3).value = date.group() 
            open_sheet.cell(row_count, 4).value = int(zhuyao.group(2))
            #row_count += 1
            
#保存文件
open_work.save(save_file)
#关闭文件
open_work.close()

def get_log_info_and_writer_excel():
pass

def main():
#文件的基本要素
get_log_target = ‘quality’ #抓取Log信息的主要目的
file_name_pre = get_log_target # 把获取Log的目的作为文件名的前缀
file_name_suffix = ‘ProxyFile’ # 把获取的SN盘作为文件名的后缀
file_name_date = time.strftime(“%Y%m%d%H%M%S”, time.localtime()) # 文件名时间部分
file_extension = ‘.xlsx’ #保存Excel文件的扩展名
file_path = r’C:\Project\message\setpoint_Visibility\setpoint-Visibility-NO’ # 获取Log的路径
creat_excel(get_log_target,file_name_pre, file_name_suffix, file_name_date, file_extension, file_path)

if name==“main”:
main()