from openpyxl.workbook import Workbook
from openpyxl.reader.excel import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
import re
# from openpyxl.writer.excel import ExcelWriter
# import xlrd
ft = Font(name='Neo Sans Intel', size=11) # define font style
bd = Border(left=Side(border_style='thin', color='00000000'), \
right=Side(border_style='thin', color='00000000'), \
top=Side(border_style='thin', color='00000000'), \
bottom=Side(border_style='thin', color='00000000')) # define border style
alg_cc = Alignment(horizontal='center', \
vertical='center', \
text_rotation=0, \
wrap_text=True, \
shrink_to_fit=True, \
indent=0) # define alignment styles
alg_cb = Alignment(horizontal='center', \
vertical='bottom', \
text_rotation=0, \
wrap_text=True, \
shrink_to_fit=True, \
indent=0)
alg_lc = Alignment(horizontal='left', \
vertical='center', \
text_rotation=0, \
wrap_text=True, \
shrink_to_fit=True, \
indent=0)
patch_file_name = "C:\\Users\\matebook14\\Desktop\\ABC.txt"
patch_file = open(patch_file_name, 'r',encoding='UTF-8') # get data patch text
wb = load_workbook('C:\\Users\\matebook14\\Desktop\\Android_Patch_Review-Y2015.xlsx') # open excel to write
sheetnames1 = wb.sheetnames()
ws = wb.get_sheet_by_name('data') # get sheet
rows = len(ws.rows)
assert ws.cell(row=rows,
column=1).value != None, 'New Document or empty row at the end of the document? Please input at least one row!'
print
"The original Excel document has %d rows totally." % (rows)
end_tag = 'type: stats'
for line in open(patch_file_name):
line = patch_file.readline()
if re.match(end_tag, line) is not None: # end string
break
if len(line) == 1: # go to next patch
rows = rows + 1
continue
line = line.strip()
# print line
ws.cell(row=rows + 1, column=1).value = ws.cell(row=rows, column=1).value + 1 # Write No.
ws.cell(row=rows + 1, column=1).font = ft
ws.cell(row=rows + 1, column=1).border = bd
ws.cell(row=rows + 1, column=1).alignment = alg_cb
ws.cell(row=rows + 1, column=5).border = bd
ws.cell(row=rows + 1, column=9).border = bd
if re.match('change', line) is not None:
ws.cell(row=rows + 1, column=2).value = re.sub('change', '', line) # Write Gerrit ID
ws.cell(row=rows + 1, column=2).font = ft
ws.cell(row=rows + 1, column=2).border = bd
ws.cell(row=rows + 1, column=2).alignment = alg_cb
if re.match('url:', line) is not None:
ws.cell(row=rows + 1, column=3).value = re.sub('url:', '', line) # Write Gerrit url
ws.cell(row=rows + 1, column=3).font = ft
ws.cell(row=rows + 1, column=3).border = bd
ws.cell(row=rows + 1, column=3).alignment = alg_cb
if re.match('project:', line) is not None:
ws.cell(row=rows + 1, column=6).value = re.sub('project:', '', line) # Write project
ws.cell(row=rows + 1, column=6).font = ft
ws.cell(row=rows + 1, column=6).border = bd
ws.cell(row=rows + 1, column=6).alignment = alg_lc
if re.match('branch:', line) is not None:
ws.cell(row=rows + 1, column=7).value = re.sub('branch:', '', line) # Write branch
ws.cell(row=rows + 1, column=7).font = ft
ws.cell(row=rows + 1, column=7).border = bd
ws.cell(row=rows + 1, column=7).alignment = alg_cc
if re.match('lastUpdated:', line) is not None:
ws.cell(row=rows + 1, column=8).value = re.sub('lastUpdated:|CST', '', line) # Write update time
ws.cell(row=rows + 1, column=8).font = ft
ws.cell(row=rows + 1, column=8).border = bd
ws.cell(row=rows + 1, column=8).alignment = alg_cc
if re.match('commitMessage:', line) is not None:
description_str = re.sub('commitMessage:', '', line)
if re.match('Product:|BugID:|Description:|Unit Test:|Change-Id:', line) is not None:
description_str = description_str + '\n' + line #
if re.match('Signed-off-by:', line) is not None:
description_str = description_str + '\n' + line
ws.cell(row=rows + 1, column=4).value = description_str # Write patch description
ws.cell(row=rows + 1, column=4).font = ft
ws.cell(row=rows + 1, column=4).border = bd
ws.cell(row=rows + 1, column=4).alignment = alg_lc
wb.save('Android_Patch_Review-Y2015.xlsx')
print ('Android_Patch_Review-Y2015.xlsx saved!\nPatch Collection Done!')
# patch_file.close()

from openpyxl.reader.excel import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
import re
# from openpyxl.writer.excel import ExcelWriter
# import xlrd
ft = Font(name='Neo Sans Intel', size=11) # define font style
bd = Border(left=Side(border_style='thin', color='00000000'), \
right=Side(border_style='thin', color='00000000'), \
top=Side(border_style='thin', color='00000000'), \
bottom=Side(border_style='thin', color='00000000')) # define border style
alg_cc = Alignment(horizontal='center', \
vertical='center', \
text_rotation=0, \
wrap_text=True, \
shrink_to_fit=True, \
indent=0) # define alignment styles
alg_cb = Alignment(horizontal='center', \
vertical='bottom', \
text_rotation=0, \
wrap_text=True, \
shrink_to_fit=True, \
indent=0)
alg_lc = Alignment(horizontal='left', \
vertical='center', \
text_rotation=0, \
wrap_text=True, \
shrink_to_fit=True, \
indent=0)
patch_file_name = "C:\\Users\\matebook14\\Desktop\\ABC.txt"
patch_file = open(patch_file_name, 'r',encoding='UTF-8') # get data patch text
wb = load_workbook('C:\\Users\\matebook14\\Desktop\\Android_Patch_Review-Y2015.xlsx') # open excel to write
sheetnames1 = wb.sheetnames()
ws = wb.get_sheet_by_name('data') # get sheet
rows = len(ws.rows)
assert ws.cell(row=rows,
column=1).value != None, 'New Document or empty row at the end of the document? Please input at least one row!'
"The original Excel document has %d rows totally." % (rows)
end_tag = 'type: stats'
for line in open(patch_file_name):
line = patch_file.readline()
if re.match(end_tag, line) is not None: # end string
break
if len(line) == 1: # go to next patch
rows = rows + 1
continue
line = line.strip()
# print line
ws.cell(row=rows + 1, column=1).value = ws.cell(row=rows, column=1).value + 1 # Write No.
ws.cell(row=rows + 1, column=1).font = ft
ws.cell(row=rows + 1, column=1).border = bd
ws.cell(row=rows + 1, column=1).alignment = alg_cb
ws.cell(row=rows + 1, column=5).border = bd
ws.cell(row=rows + 1, column=9).border = bd
if re.match('change', line) is not None:
ws.cell(row=rows + 1, column=2).value = re.sub('change', '', line) # Write Gerrit ID
ws.cell(row=rows + 1, column=2).font = ft
ws.cell(row=rows + 1, column=2).border = bd
ws.cell(row=rows + 1, column=2).alignment = alg_cb
if re.match('url:', line) is not None:
ws.cell(row=rows + 1, column=3).value = re.sub('url:', '', line) # Write Gerrit url
ws.cell(row=rows + 1, column=3).font = ft
ws.cell(row=rows + 1, column=3).border = bd
ws.cell(row=rows + 1, column=3).alignment = alg_cb
if re.match('project:', line) is not None:
ws.cell(row=rows + 1, column=6).value = re.sub('project:', '', line) # Write project
ws.cell(row=rows + 1, column=6).font = ft
ws.cell(row=rows + 1, column=6).border = bd
ws.cell(row=rows + 1, column=6).alignment = alg_lc
if re.match('branch:', line) is not None:
ws.cell(row=rows + 1, column=7).value = re.sub('branch:', '', line) # Write branch
ws.cell(row=rows + 1, column=7).font = ft
ws.cell(row=rows + 1, column=7).border = bd
ws.cell(row=rows + 1, column=7).alignment = alg_cc
if re.match('lastUpdated:', line) is not None:
ws.cell(row=rows + 1, column=8).value = re.sub('lastUpdated:|CST', '', line) # Write update time
ws.cell(row=rows + 1, column=8).font = ft
ws.cell(row=rows + 1, column=8).border = bd
ws.cell(row=rows + 1, column=8).alignment = alg_cc
if re.match('commitMessage:', line) is not None:
description_str = re.sub('commitMessage:', '', line)
if re.match('Product:|BugID:|Description:|Unit Test:|Change-Id:', line) is not None:
description_str = description_str + '\n' + line #
if re.match('Signed-off-by:', line) is not None:
description_str = description_str + '\n' + line
ws.cell(row=rows + 1, column=4).value = description_str # Write patch description
ws.cell(row=rows + 1, column=4).font = ft
ws.cell(row=rows + 1, column=4).border = bd
ws.cell(row=rows + 1, column=4).alignment = alg_lc
wb.save('Android_Patch_Review-Y2015.xlsx')
print ('Android_Patch_Review-Y2015.xlsx saved!\nPatch Collection Done!')
# patch_file.close()






