background: in the group to generate test reports for each project, some bugs in the current project are counted. Considering the complexity of manual statistics, and is a long-term work, so write a script to automatic statistics of the bug. (We use the bug platform is bugfree, directly can export Excel, because each time the project requirements ID is not necessarily, so manually export Excel to a directory.) Using scripts for statistics)
first, environmental installationPython3, installs Xlrd and XLWT, xlutils these 3 third-party packages, these three packs all support Python2 and Python3pip Install xlrdpip Install XLWTpip Install xlutils xlrd: Reading an Excel fileXLWT: Write to Excelxlutils: Copy Operations to open Excel(This is my more tangled point, because Excel can not directly to the existing table to edit, you must open the existing table after copy a copy, modified and then saved with the same name, if you have a good way to recommend it.) )
Second, the above three third-party library simple application Method
xlrd: Reading Excel filesImport xlrdWorkbook = Xlrd.open_workbook (filepath) #filepath为excel文件路径, can be a relative addresssheet1=workbook.sheet_by_index (0) #通过sheet的索引打开sheet, sheet starting from 0sheet2=workbook.sheet_by_name ("SheetName") #通过sheet名字打开sheet rowvalues=sheet1.row_values (i) #取出第i行的全部数据colvalues=sheet1.col_values (i) #取出第i列的全部数据 rowsnum=sheet1.nrows# The number of rows to get Sheet1colsnum=sheet1.ncols# number of columns to get Sheet1 Cellvalue=sheet.cell (1,5). value# Remove the contents of the cell based on the row and column number, 1 is the line number, and 5 is the column number
XLWT: Writing to an Excel fileImport XLWTF=XLWT. Workbook ()sheet1=f.add_sheet ("Bug stats", cell_overwrite_ok=true) #添加一个sheet1, sheet's name is "Bug stats"sheet.write (1,5, "data") #在行号为1, the cell with column number 5 then cell insert data. Both the line number and the column number are from 0.f.save ("Xxx.xls") #保存写入的结果Note: If you want to implement the style of editing cells, you can do this as followsstyle = XLWT. Xfstyle ()Style.pattern.pattern = 1Style.pattern.pattern_fore_colour = 3# Sets the background color of the cellstyle.borders.bottom = # set BorderG_headerfont = xlwt. Font () #设置字体g_headerfont.bold = TrueStyle.font = G_headerfont sheet.write (1,5, "data", style)
xlutils: Copying an open Excel objectFrom xlutils.copy Import copyworkbook = Xlrd.open_workbook ("./bug analysis. xls") #xlrd通过通过索引或名字打开的sheet都不能编辑, will prompt for no write methodwb=copy (Workbook) #Excel处理不支持直接更改已存在的文件, you need to open a copy, and then save the same name, and later want to optimizesheet1=wb.get_sheet (0)Sheet1.write (0,0, "number of bugs in ios") #此时写入也是用write方法去写Wb.save ("./bug analysis. xls")
Three, the idea of the bug statistics1. Create a User.txt file containing the development and corresponding group (iOS, Android, server, etc.)2. The Excel to which the bug is to be counted is placed in the directory where the script will read the Excel file3. The script summarizes the bugs to be processed and re-processes them according to interface bugs, requirements changes, and so on. Put in the bug analysis. xls, at the same time according to which side of the bug to statistics, if the User.txt table in the development of the name is not complete, will be counted to the unknown end of the sheet, need to manually complete the User.txt file The directory is as follows:
Other:Python can also do many things with Excel, including pasting pictures, modifying Excel report styles, and so on. If there is time I will optimize the result style, now too ugly = =. There are good suggestions to exchange a bit ~
Python implementation bug statistics for test reports
