读取excel
#!/usr/bin/env python2
#-*- coding: UTF-8 -*-
import sys
import os
import xlwt
from xlwt import *
import xlrd
from xlrd import *
import time
#from pyExcelerator import *
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER #可取值: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # 可取值: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # 创建样式
style.alignment = alignment # 给样式添加文字居中属性
style_wrap = xlwt.easyxf('align: wrap on') #自动换行属性
w = Workbook()
#ws = w.add_sheet(time.strftime("%Y-%m-%d"))
ws = w.add_sheet('Develop Commit Log')
for j in range(0,11):
if(j == 0):
ws.write(0,j,'Author',style)
if(j == 1):
ws.write(0,j,'Date',style)
if(j == 2):
ws.write(0,j,'Type',style)
if(j == 3):
ws.write(0,j,'Summary',style)
if(j == 4):
ws.write(0,j,'Module',style)
if(j == 5):
ws.write(0,j,'Description',style)
if(j == 6):
ws.write(0,j,'Reference',style)
if(j == 7):
ws.write(0,j,'Company',style)
if(j == 8):
ws.write(0,j,'Team',style)
if(j == 9):
ws.write(0,j,'CommitId',style)
if(j == 10):
ws.write(0,j,'Comment',style)
#Four_col=ws.col(3)
#Four_col.width=256*50
ws.col(1).width=256*10
ws.col(3).width=256*60
ws.col(4).width=256*20
ws.col(5).width=256*80
ws.col(6).width=256*40
ws.col(8).width=256*11
ws.col(10).width=256*135
ws.panes_frozen= True
ws.horz_split_pos= 1
ws.horz_split_first_visible= 1
w.save('CommitLog.xls')
log=open('./data','r')
LineCount=0
for line in log:
LineCount=LineCount+1
print(LineCount)
tempComment=""
Init_line=1
for i in range(0,LineCount):
log=open('./data','r')
if not log.readlines()[i].startswith('Revert'):
log=open('./data','r')
Line_Content=log.readlines()[i].replace("\n","").decode('utf-8').split('*')
if (len(Line_Content)==16):
for x in range(0,11):
log=open('./data','r')
Line_Content=log.readlines()[i].replace("\n","").decode('utf-8').split('*')
if(x==0):
ws.write(Init_line,x,Line_Content[-3])
elif(x==1):
ws.write(Init_line,x,Line_Content[-2])
elif(x==2):
ws.write(Init_line,x,Line_Content[1])
elif(x==3):
ws.write(Init_line,x,Line_Content[2],style_wrap)
elif(x==4):
ws.write(Init_line,x,Line_Content[4],style_wrap)
elif(x==5):
ws.write(Init_line,x,Line_Content[6],style_wrap)
elif(x==6):
ws.write(Init_line,x,Line_Content[8],style_wrap)
elif(x==7):
ws.write(Init_line,x,Line_Content[10])
elif(x==8):
ws.write(Init_line,x,Line_Content[12])
elif(x==9):
ws.write(Init_line,x,Line_Content[-1])
else:
cmd='ssh -p 29418 ci.xiaopeng.local gerrit query --format=text --current-patch-set %s --comments --all-approvals | grep -E "username: [[:graph:]]| message: | comments:| file: |line:| "| grep -vE "\[|Change-Id| username:|Signed-off-by" > log.txt && sed \'s/ //g\' log.txt' % Line_Content[-1]
r=os.popen(cmd)
info = r.readlines()
for line in info[4:-4]:
if line != ' \n':
line = line.strip().replace(' \n','')
if line.startswith('username'):
tempComment=tempComment+line.replace('username','Reviewer')+'\n'
else:
tempComment=tempComment+line.replace('message','Comment').replace('username','Reviewer')+'\n'
ws.write(Init_line,x,tempComment.decode('utf-8'),style_wrap)
tempComment=""
w.save('CommitLog.xls')
Init_line=Init_line+1
else:
ws.write(Init_line,3,Line_Content[:-3],style_wrap)
w.save('CommitLog.xls')
ws.write(Init_line,0,Line_Content[-3])
w.save('CommitLog.xls')
ws.write(Init_line,1,Line_Content[-2])
w.save('CommitLog.xls')
ws.write(Init_line,2,Line_Content[1])
w.save('CommitLog.xls')
ws.write(Init_line,9,Line_Content[-1])
w.save('CommitLog.xls')
cmd='ssh -p 29418 ci.xiaopeng.local gerrit query --format=text --current-patch-set %s --comments --all-approvals | grep -E "username: [[:graph:]]| message: | comments:| file: |line:| "| grep -vE "\[|Change-Id| username:|Signed-off-by" > log.txt && sed \'s/ //g\' log.txt' % Line_Content[-1]
r=os.popen(cmd)
info = r.readlines()
for line in info[4:-4]:
if line != ' \n':
line = line.strip().replace(' \n','')
if line.startswith('username'):
tempComment=tempComment+line.replace('username','Reviewer')+'\n'
else:
tempComment=tempComment+line.replace('message','Comment').replace('username','Reviewer')+'\n'
ws.write(Init_line,10,tempComment.decode('utf-8'),style_wrap)
tempComment=""
w.save('CommitLog.xls')
Init_line=Init_line+1
else:
log=open('./data','r')
Line_Content=log.readlines()[i].replace("\n","").decode('utf-8').split('*')
if (len(Line_Content)==16):
log=open('./data','r')
Line_Content=log.readlines()[i].replace("\n","").decode('utf-8').split('*')
for x in range(0,11):
log=open('./data','r')
Line_Content=log.readlines()[i].replace("\n","").decode('utf-8').split('*')
if(x==0):
ws.write(Init_line,x,Line_Content[-3])
elif(x==1):
ws.write(Init_line,x,Line_Content[-2])
elif(x==2):
ws.write(Init_line,x,Line_Content[1])
elif(x==3):
ws.write(Init_line,x,Line_Content[0]+Line_Content[2],style_wrap)
elif(x==4):
ws.write(Init_line,x,Line_Content[4],style_wrap)
elif(x==5):
ws.write(Init_line,x,Line_Content[6],style_wrap)
elif(x==6):
ws.write(Init_line,x,Line_Content[8],style_wrap)
elif(x==7):
ws.write(Init_line,x,Line_Content[10])
elif(x==8):
ws.write(Init_line,x,Line_Content[12])
elif(x==9):
ws.write(Init_line,x,Line_Content[-1])
else:
cmd='ssh -p 29418 ci.xiaopeng.local gerrit query --format=text --current-patch-set %s --comments --all-approvals | grep -E "username: [[:graph:]]| message: | comments:| file: |line:| "| grep -vE "\[|Change-Id| username:|Signed-off-by" > log.txt && sed \'s/ //g\' log.txt' % Line_Content[-1]
r=os.popen(cmd)
info = r.readlines()
for line in info[4:-4]:
if line != ' \n':
line = line.strip().replace(' \n','')
if line.startswith('username'):
tempComment=tempComment+line.replace('username','Reviewer')+'\n'
else:
tempComment=tempComment+line.replace('message','Comment').replace('username','Reviewer')+'\n'
ws.write(Init_line,x,tempComment.decode('utf-8'),style_wrap)
tempComment=""
w.save('CommitLog.xls')
Init_line=Init_line+1
else:
ws.write(Init_line,3,Line_Content[:-3],style_wrap)
w.save('CommitLog.xls')
ws.write(Init_line,0,Line_Content[-3])
w.save('CommitLog.xls')
ws.write(Init_line,1,Line_Content[-2])
w.save('CommitLog.xls')
ws.write(Init_line,2,Line_Content[1])
w.save('CommitLog.xls')
ws.write(Init_line,9,Line_Content[-1])
w.save('CommitLog.xls')
cmd='ssh -p 29418 ci.xiaopeng.local gerrit query --format=text --current-patch-set %s --comments --all-approvals | grep -E "username: [[:graph:]]| message: | comments:| file: |line:| "| grep -vE "\[|Change-Id| username:|Signed-off-by" > log.txt && sed \'s/ //g\' log.txt' % Line_Content[-1]
r=os.popen(cmd)
info = r.readlines()
for line in info[4:-4]:
if line != ' \n':
line = line.strip().replace(' \n','')
if line.startswith('username'):
tempComment=tempComment+line.replace('username','Reviewer')+'\n'
else:
tempComment=tempComment+line.replace('message','Comment').replace('username','Reviewer')+'\n'
ws.write(Init_line,10,tempComment.decode('utf-8'),style_wrap)
tempComment=""
w.save('CommitLog.xls')
Init_line=Init_line+1