前几天接到产品经理的一个需求，开发一个能够导出svn releasenotes的工具，主要功能是提取出代码的提交人、项目名、bug号、svn版本、问题描述、解决方案、代码审核人等数据，生成一个汇总的Excel表格。但是我开发完成之后发现，其实很多时候各个组的提交格式也不同，公司内部推这个事根本推不动。。。
1. 原始导出的数据
原始svn导出的格式
2. 实现思路
- 分割每个提交，将每个提交放到list中的一个元素中
- 判断每个提交的前面的字符，符合提取要求的提取出与下一个之间行数的数据
- 将提取的数据利用openpyxl库导出成Excel文件
3. 代码
import os
from openpyxl import Workbook
from openpyxl.styles import Border, Side, Font, Alignment, NamedStyle
import tkinter.filedialog as filedialog
from tkinter import *
"""
===================================================
作者：blue
部门：北京基地应用开发部
===================================================
版本：v1.0
功能：对svn的ReleaseNotes进行分析处理，输出成excel文件
===================================================
版本：v1.1
功能：
1.新增统计正确与错误的个数
2.对于格式错误的提交不会影响正确格式提交的excel表数据
3.新增“格式错误列表”，统计格式错误的信息，并附修改建议
===================================================
版本：v1.2
功能：新增对spm merge的log信息提取出原始svn log信息
===================================================
"""
class ReleaseNotes(object):
path = ""
"""初始化方法，创建处理Excel的对象"""
def __init__(self):
self.wb = Workbook()
self.ws = self.wb.active
self.ws.title = "svn_info"
self.ws.append(['Bug ID', '责任人', '问题描述', '原因分析', '修改方案', 'svn 版本'])
self.ws_wrong = self.wb.create_sheet("格式错误的提交")
self.ws_wrong.append(['错误类型', 'svn 版本', '提交人', '详细信息', '修改意见'])
"""对txt文件进行分隔，获取每一条信息"""
def get_each_info(self):
lines = []
info = []
file = open(self.path, 'r', encoding='utf-8')
for data in file:
lines.append(data)
if data.startswith("-------"):
info.append(
"".join(lines).replace("------------------------------------------------------------------------",
""))
lines.clear()
info_total = [x for x in info if x != '\n']
file.close()
print("处理结果：")
self.get_detail_info(info_total)
"""对每一条信息进行内容提取，并填充到ws"""
def get_detail_info(self, info_total):
temp = []
temp_wrong = []
for text in info_total:
text_list = [x for x in text.split('\n') if x != "" and x != "[original log] "]
try:
if text_list[1].split(']')[0] == "[original log":
author = text_list[1].split(']')[1].split(":")[1].lstrip()
bug_id = text_list[1].split(']')[2].split("+")[1].lstrip()
else:
author = text_list[1].split(']')[0].split(":")[1].lstrip()
bug_id = text_list[1].split(']')[1].split("+")[1].lstrip()
svn_id = text_list[0].split('|')[0].replace('r', '').lstrip()
temp.append(bug_id)
temp.append(author)
a = text_list.index("1.问题描述")
b = text_list.index("2.原因分析")
if "问题描述" in text:
temp.append("".join(text_list[a + 1:b]).lstrip())
c = text_list.index("3.修改方案")
if "原因分析" in text:
temp.append("".join(text_list[b + 1:c]).lstrip())
d = text_list.index("4.影响范围")
if "修改方案" in text:
temp.append("".join(text_list[c + 1:d]).lstrip())
temp.append(svn_id)
except ValueError:
svn_id = text_list[0].split('|')[0].replace('r', '').lstrip()
author_name = text_list[0].split('|')[1].lstrip()
temp_wrong.append("小标题格式错误")
temp_wrong.append(svn_id)
temp_wrong.append(author_name)
temp_wrong.append("\n".join(text_list[1:]))
temp_wrong.append("标题文字要正确、删除多余的空格、删除冒号、注意是“修改方案”不是“解决方案”")
self.ws_wrong.append(temp_wrong)
temp_wrong.clear()
except IndexError:
svn_id = text_list[0].split('|')[0].replace('r', '').lstrip()
author_name = text_list[0].split('|')[1].lstrip()
temp_wrong.append("总标题格式错误")
temp_wrong.append(svn_id)
temp_wrong.append(author_name)
temp_wrong.append("\n".join(text_list[1:]))
temp_wrong.append("标准格式：[Author : daiqingchen] [YL50B71_CMCC + ODMAAD-394]、中括号要全、不要省略+号")
self.ws_wrong.append(temp_wrong)
temp_wrong.clear()
finally:
if len(temp) == 6:
self.ws.append(temp)
temp.clear()
"""将数据写入Excel，并设置单元格样式"""
def write_excel(self):
left, right, top, bottom = [Side(style='thin', color='000000')] * 4
title = NamedStyle(name="title")
title.font = Font(name=u'宋体', size=11, bold=True)
title.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
title.border = Border(left=left, right=right, top=top, bottom=bottom)
content = NamedStyle(name="content")
content.font = Font(name=u'宋体', size=11)
content.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
content.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long = NamedStyle(name="content_long")
content_long.font = Font(name=u'宋体', size=11)
content_long.border = Border(left=left, right=right, top=top, bottom=bottom)
content_long.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
self.ws.column_dimensions['A'].width = 15
self.ws.column_dimensions['B'].width = 15
self.ws.column_dimensions['C'].width = 80
self.ws.column_dimensions['D'].width = 55
self.ws.column_dimensions['E'].width = 55
self.ws.column_dimensions['F'].width = 10
self.ws_wrong.column_dimensions['A'].width = 20
self.ws_wrong.column_dimensions['B'].width = 15
self.ws_wrong.column_dimensions['C'].width = 15
self.ws_wrong.column_dimensions['D'].width = 80
self.ws_wrong.column_dimensions['E'].width = 80
for i in range(self.ws.max_row):
self.ws.row_dimensions[i + 1].height = 30
for i in range(self.ws_wrong.max_row):
self.ws_wrong.row_dimensions[i + 1].height = 50
for x in self.ws[1]:
x.style = title
for x in self.ws_wrong[1]:
x.style = title
for x in self.ws['A'][1:]:
x.style = content
for x in self.ws_wrong['A'][1:]:
x.style = content
for x in self.ws['B'][1:]:
x.style = content
for x in self.ws_wrong['B'][1:]:
x.style = content
for x in self.ws['C'][1:]:
x.style = content_long
for x in self.ws_wrong['C'][1:]:
x.style = content
for x in self.ws['D'][1:]:
x.style = content_long
for x in self.ws_wrong['D'][1:]:
x.style = content_long
for x in self.ws['E'][1:]:
x.style = content_long
for x in self.ws_wrong['E'][1:]:
x.style = content_long
for x in self.ws['F'][1:]:
x.style = content
print("格式正确个数 %d" % (self.ws.max_row - 1))
print("格式错误个数 %d" % (self.ws_wrong.max_row - 1))
self.wb.save(os.path.dirname(os.path.abspath(self.path)) + "\\" + os.path.basename(self.path).replace(".txt",
".xlsx"))
"""静态方法，打开win窗口"""
@staticmethod
def open_win():
root = Tk()
root.title("svn release notes 分析")
ws = root.winfo_screenwidth()
hs = root.winfo_screenheight()
x = ws / 2 - 400 / 2
y = hs / 2 - 200 / 2
root.geometry("400x200+%d+%d" % (x, y))
def callback():
ReleaseNotes.path = filedialog.askopenfilename()
entry.insert(0, ReleaseNotes.path)
button = Button(root, text="选择ReleaseNotes文件", command=callback)
quit_btn = Button(root, text="确定", command=root.destroy)
entry = Entry(root)
entry.pack(side=TOP, anchor="nw", fill=X, pady=40)
button.pack(side=TOP)
quit_btn.pack(side=TOP, pady=10)
root.mainloop()
if __name__ == "__main__":
ReleaseNotes.open_win()
notes = ReleaseNotes()
notes.get_each_info()
notes.write_excel()
print("数据已经导出完毕，请到对应目录下查看excel文件！")
