使用Python提取数据到Excel，并读取Excel进行数据处理，最终输出整理后的数据到新的Excel中

使用Python提取数据到Excel，并读取Excel进行数据处理，最终输出整理后的数据到新的Excel中

一、导入相关库函数
#--coding:utf-8 --
import re
import os
import xlsxwriter
import xlrd
import datetime
from xlrd import xldate_as_tuple

二、创建Excel
workbook = xlsxwriter.Workbook(‘Pyuthon_excel.xlsx’)
worksheet = workbook.add_worksheet(‘sheet1’)
headings = [‘Time’, ‘Process’]
worksheet.write_row(‘A1’, headings)
i=1
path=r"C:\Users\Administrator.SC-201605081625\Desktop\Today_task\Log"
files=os.listdir(path)
print(files)
for file in files:
if not os.path.isdir(file):
print (“I am here”)
with open(path+"/"+file,‘r’,encoding = “ANSI”) as f: //这里一定要注意自己文档的格式，使用Notepad++打开后，关注右下角显示即可
with open(‘new.log’, ‘w+’) as g:
for each in f.readlines():
if re.search(r"AccessPersentMonitor: persent change",each)!=None:
g.write(each)

with open (‘new.log’,‘rb’) as f:
data = f.readlines()
for eachline in data:
Time = re.search("\d{2}-\d{2}(\s*)\d{2}:\d{2}:\d{2}:\d{3}",str(eachline))
worksheet.write(i,0, Time[0])
Process = re.search("->(\s *)\d{1,3}", str(eachline))
worksheet.write(i,1, Process[0])
i+=1
workbook.close()

excel_file=xlrd.open_workbook(‘Pyuthon_excel.xlsx’)
table = excel_file.sheets()[0]
all_content = []
for i in range(table.nrows):
row_content = []
for j in range(table.ncols):
ctype = table.cell(i, j).ctype # 获取单元格返回的数据类型
cell_value = table.cell(i, j).value # 获取单元格内容
if ctype == 2 and cell_value % 1 == 0: # 是否是数字类型
cell_value = int(cell_value)
elif ctype == 3: # 是否是日期
date = datetime(*xldate_as_tuple(cell_value, 0))
cell_value = date.strftime(’%Y/%m/%d %H:%M:%S’)
elif ctype == 4: # 是否是布尔类型
cell_value = True if cell_value == 1 else False
row_content.append(cell_value)
all_content.append(row_content)

def Month(a):
Mon=int(a[0]) * 10 + int(a[1])
return Mon
def Day(a):
Da = int(a[3]) * 10 + int(a[4])
return Da
def Hour(a):
Hou = int(a[6]) * 10 + int(a[7])
return Hou
def Mini(a):
Min = int(a[9]) * 10 + int(a[10])
return Min
def Seco(a):
Sec = int(a[12]) * 10 + int(a[13])
return Sec
def HMM(a):
HM = int(a[15]) * 100 + int(a[16]) * 10 + int(a[17])
return HM
def HMMC(a,b):
if b>=a:
return b-a
else:
return ((1000+b)-a)

workbook2 = xlsxwriter.Workbook(‘Pyuthon_excel2.xlsx’)
worksheet2 = workbook2.add_worksheet(‘sheet1’)
headings2 = [‘Time’, ‘Process’,‘Timeinter’]
worksheet2.write_row(‘A1’, headings2)
rownumber=1
Year1=2019

while rownumber < table.nrows-2:
a=all_content[rownumber][0]
b=all_content[rownumber+1][0]
numA = datetime.datetime(Year1, Month(a), Day(a), Hour(a), Mini(a), Seco(a), HMM(a))
numB = datetime.datetime(Year1, Month(b), Day(b), Hour(b), Mini(b), Seco(b), HMM(b))
DayCha = (numB - numA).days
SecCha = (numB - numA).seconds
HmCha = HMMC(HMM(a),HMM(b))
Total = DayCha * 24 * 60 * 60 * 1000 + SecCha * 1000 + HmCha
worksheet2.write(rownumber, 0, all_content[rownumber][0])
worksheet2.write(rownumber, 1, all_content[rownumber][1])
worksheet2.write(rownumber+1, 2, Total)
rownumber+=1
worksheet2.write(rownumber, 0, all_content[rownumber][0])
worksheet2.write(rownumber, 1, all_content[rownumber][1])
workbook2.close()