python爬虫获取sahi报告

将excel.py文件和GetReport.py存放在同一个目录下，修改GetReport.py中的reportURL变量值后，执行该文件爬取Sahi服务器的测试报告。

Excel操作包：excel.py

#!/usr/bin/env python
# coding=utf-8
# 需要xlrd和xlwt库的支持
import os
import xlwt
import xlrd
from xlutils import copy
# from xlrd import *
# from xlwt import *


class excel:
    filePath = ""
    fileName = ""
    sheetName = ""

    def __init__(self, fPath, fName, sName):
        self.filePath = fPath
        self.fileName = fName
        self.sheetName = sName

    def create_file(self, columnList):
        # 指定file以utf-8的格式打开
        file = xlwt.Workbook(encoding='utf-8')

        # 指定打开的sheet名称
        table = file.add_sheet(self.sheetName, cell_overwrite_ok=True)

        # 生成第一行
        row0 = columnList  # [u'用例模块', u'用例名称', u'用例标签', u'执行结果']
        for i in range(0, len(row0)):
            table.write(0, i, row0[i], self.set_style('Times New Roman', 220, True))
        # 设置表格列宽
        table.col(0).width = 256 * 40
        table.col(1).width = 256 * 32
        table.col(2).width = 256 * 32
        table.col(3).width = 256 * 10

        # 指定文件名称
        file.save(self.fileName + '.xls')
        return file

    def read_excel(self):
        workbook = xlrd.open_workbook(self.filePath)
        # print(workbook.sheet_names())  # 获取所有sheet得到[u'sheet1', u'sheet2'])
        rsheet = workbook.sheet_by_index(0)  # 根据sheet索引或者名称获取sheet内容，sheet索引从0开始
        print(rsheet.name, rsheet.nrows, rsheet.ncols)  # sheet的名称，行数，列数
        return rsheet

        # 获取整行和整列的值（数组）
        # rows = sheet2.row_values(3)  # 获取第四行内容
        # cols = sheet2.col_values(2)  # 获取第三列内容
        # 获取单元格内容
        # print(sheet2.cell(1, 0).value.encode('utf-8'))
        # print(sheet2.cell_value(1, 0).encode('utf-8'))
        # print(sheet2.row(1)[0].value.encode('utf-8'))

        # 获取单元格内容的数据类型
        # print(sheet2.cell(1, 0).ctype)

    def get_row_cout(self):
        workbook = xlrd.open_workbook(self.filePath)
        readsheet = workbook.sheet_by_index(0)
        rowcount = readsheet.nrows
        return rowcount

    @staticmethod
    def set_style(name, height, bold=False):  # 设置首行格式
        style = xlwt.XFStyle()  # 初始化样式
        font = xlwt.Font()  # 为样式创建字体
        font.name = name  # 'Times New Roman'
        font.bold = bold
        font.color_index = 4
        font.height = height
        # borders= xlwt.Borders()
        # borders.left= 6
        # borders.right= 6
        # borders.top= 6
        # borders.bottom= 6
        style.font = font
        # style.borders = borders
        return style

    def write_excel(self, rowval):  # 写excel
        readbook = xlrd.open_workbook(self.filePath)
        workbook = copy.copy(readbook)
        readsheet = readbook.sheet_by_index(0)
        rowcount = readsheet.nrows
        # 根据sheet索引或者名称获取sheet内容
        wsheet = workbook.get_sheet(0)  # sheet索引从0开始
        print("The rowcount in write_excel function is : ", rowcount)
        # 生成新的数据行,数据行索引从0开始
        for i in range(0, len(rowval)):
            wsheet.write(rowcount, i, rowval[i])
            # os.remove(self.filePath)
            workbook.save(self.fileName + ".xls")


"""
wkb = create_file(fileName, sheetName)
wks = wkb.get_sheet(0)
print(type(wks))
read_sheet = read_excel(filePath)
rowcnt = get_row_cout(read_sheet)
print(rowcnt)
rowvalue = [u'/系统管理/用户帐户管理/', u'createUser', u'用例标签', u'SUCCESS']
print(type(rowvalue))
# write_excel(wkb, rowvalue)
"""

获取sahi报告脚本GetReport.py

# -*- coding: UTF-8 -*-
from bs4 import BeautifulSoup
import urllib.request
import re
import excel

# reportURL的前半部分是Sahi服务端的测试报告的url地址，后半部分是Sahi服务器对外实际地址，需要根据环境修改
reportURL = "http://localhost:9999/_s_/dyn/pro/DBReports_suiteReport?id=test_all_exceptuer_chrome__24%E5%85%AB%E6%9C%882018__15_32_02_839&o=list".replace("localhost", "192.168.82.77", 1)
filePath = "D:\My Documents\CODE\python\sahiReport.xls"     # 存放新生成文件的路径，文件名与fileName相同，不必手动创建
fileName = "sahiReport"     # 文件名与filePath中相同，不必手动创建
sheetName = "execution_report"      # 测试报告文件的页名，可以修改
wkb = excel.excel(filePath, fileName, sheetName)
fileObj = wkb.create_file(['用例模块', '用例名称', '用例标签', '执行结果'])

page = urllib.request.urlopen(reportURL)
contents = page.read()
soup1 = BeautifulSoup(contents, "html.parser")
for tag1 in soup1.find_all('table', class_='scriptSummaries'):
    for s1 in tag1.find_all('tr', class_='FAILURE'):
        case_status = "FAILURE"
        case_name = s1.find('a', class_='SCRIPT').get_text().split(".")[0]
        url = "http://192.168.82.77:9999" + s1.find('a', class_='SCRIPT').get('href')
        dt_page = urllib.request.urlopen(url)
        dt_contents = dt_page.read()
        soup2 = BeautifulSoup(dt_contents, "html.parser")
        soupPath = soup2.findAll("a", {"title": re.compile("Current Script Path is.+")})
        case_path = soupPath[0].get_text().split("case")[1].split(case_name)[0]
        case_tag = ""
        for soupDoc in soup2.find_all('table', class_='fwTable'):
            for tr in soupDoc.find_all('td', text=re.compile("^dbaudit--.+")):
                case_tag = case_tag + tr.string + " "
        # case_tag = s1.find('div', class_='scriptargs').get_text().split(";")[1].split("\"")[1]

        rowlist = [case_path, case_name, case_tag, case_status]
        # wkb.write_excel(fileObj, rowlist)
        wkb.write_excel(rowlist)

    for s2 in tag1.find_all('tr', class_='SUCCESS'):
        case_status = "SUCCESS"
        case_name = s2.find('a', class_='SCRIPT').get_text().split(".")[0]
        url = "http://192.168.82.77:9999" + s2.find('a', class_='SCRIPT').get('href')
        dt_page = urllib.request.urlopen(url)
        dt_contents = dt_page.read()
        soup2 = BeautifulSoup(dt_contents, "html.parser")
        soupObj = soup2.findAll("a", {"title": re.compile("Current Script Path is.+")})
        case_path = soupObj[0].get_text().split("case")[1].split(case_name)[0]
        case_tag = ""
        for soupDoc in soup2.find_all('table', class_='fwTable'):
            for tr in soupDoc.find_all('td', text=re.compile("^dbaudit--.+")):
                case_tag = case_tag + tr.string + " "
        # case_tag = s2.find('div', class_='scriptargs').get_text().split(";")[1].split("\"")[1]

        rowlist = [case_path, case_name, case_tag, case_status]
        # wkb.write_excel(fileObj, rowlist)
        wkb.write_excel(rowlist)