python 漂亮的excel_python 读取excel文件数据生成比较好看的html报告

随手写的，后续整理。

1.自行定义好excel数据格式：

2.设置html基本格式(参考HTMLTestRunner模板)

# -*- coding:utf-8 -*-

'''

Created on 2016年10月9日

@author: mengxing

'''

from xml.sax import saxutils

import sys

import datetime

import xlrd

import os

reload(sys)

sys.setdefaultencoding('utf8')

d = datetime.datetime.now()

HTML_TMPL = r"""<?xml version="1.0" encoding="UTF-8"?>

%(title)s

%(stylesheet)s

output_list = Array();

/* level - 0:Summary; 1:Failed; 2:All */

function showCase(level) {

trs = document.getElementsByTagName("tr");

for (var i = 0; i

tr = trs[i];

id = tr.id;

if (id.substr(0,2) == 'ft') {

if (level

tr.className = 'hiddenRow';

}

else {

tr.className = '';

}

}

if (id.substr(0,2) == 'pt') {

if (level > 1) {

tr.className = '';

}

else {

tr.className = 'hiddenRow';

}

}

}

}

function showClassDetail(cid, count) {

var id_list = Array(count);

var toHide = 1;

for (var i = 0; i

tid0 = 't' + cid.substr(1) + '.' + (i+1);

tid = 'f' + tid0;

tr = document.getElementById(tid);

if (!tr) {

tid = 'p' + tid0;

tr = document.getElementById(tid);

}

id_list[i] = tid;

if (tr.className) {

toHide = 0;

}

}

for (var i = 0; i

tid = id_list[i];

if (toHide) {

document.getElementById('div_'+tid).style.display = 'none'

document.getElementById(tid).className = 'hiddenRow';

}

else {

document.getElementById(tid).className = '';

}

}

}

function showTestDetail(div_id){

var details_div = document.getElementById(div_id)

var displayState = details_div.style.display

// alert(displayState)

if (displayState != 'block' ) {

displayState = 'block'

details_div.style.display = 'block'

}

else {

details_div.style.display = 'none'

}

}

function html_escape(s) {

s = s.replace(/&/g,'&');

s = s.replace(/,'<');

s = s.replace(/>/g,'>');

return s;

}

/* obsoleted by detail in

function showOutput(id, name) {

var w = window.open("", //url

name,

"resizable,scrollbars,status,width=800,height=450");

d = w.document;

d.write("

");

d.write(html_escape(output_list[id]));

d.write("\n");

d.write("close\n");

d.write("

\n");

d.close();

}

*/

-->

%(heading)s

%(report)s

%(ending)s

"""

# variables: (title, generator, stylesheet, heading, report, ending)

# ------------------------------------------------------------------------

# Stylesheet

#

# alternatively use a for external style sheet, e.g.

#  

STYLESHEET_TMPL = """

body        { font-family: verdana, arial, helvetica, sans-serif; font-size: 80%; }

table       { font-size: 100%; }

pre         { }

/* -- heading ---------------------------------------------------------------------- */

h1 {

font-size: 16pt;

color: gray;

}

.heading {

margin-top: 0ex;

margin-bottom: 1ex;

}

.heading .attribute {

margin-top: 1ex;

margin-bottom: 0;

}

.heading .description {

margin-top: 4ex;

margin-bottom: 6ex;

}

/* -- css div popup ------------------------------------------------------------------------ */

a.popup_link {

}

a.popup_link:hover {

color: red;

}

.popup_window {

display: none;

position: relative;

left: 0px;

top: 0px;

/*border: solid #627173 1px; */

padding: 10px;

background-color: #E6E6D6;

font-family: "Lucida Console", "Courier New", Courier, monospace;

text-align: left;

font-size: 8pt;

width: 500px;

}

}

/* -- report ------------------------------------------------------------------------ */

#show_detail_line {

margin-top: 3ex;

margin-bottom: 1ex;

}

#result_table {

width: 80%;

border-collapse: collapse;

border: 1px solid #777;

}

#header_row {

font-weight: bold;

color: white;

background-color: #777;

}

#result_table td {

border: 1px solid #777;

padding: 2px;

}

#total_row  { font-weight: bold; }

.passClass  { background-color: #6c6; }

.failClass  { background-color: #c60; }

.errorClass { background-color: #c00; }

.passCase   { color: #6c6; }

.failCase   { color: #c60; font-weight: bold; }

.errorCase  { color: #c00; font-weight: bold; }

.hiddenRow  { display: none; }

.testcase   { margin-left: 2em; }

/* -- ending ---------------------------------------------------------------------- */

#ending {

}

"""

# ------------------------------------------------------------------------

# Heading

#

HEADING_TMPL = """

%(title)s

%(parameters)s

%(description)s

""" # variables: (title, parameters, description)

HEADING_ATTRIBUTE_TMPL = """

%(name)s: %(value)s

""" # variables: (name, value)

# ------------------------------------------------------------------------

# Report

#

#Summary

#Failed

REPORT_TMPL = """

Show

收起

失败

展开

%(Project_Name)s

总数

通过

失败

查看

%(test_list)s

合计%(count)s%(Pass)s%(fail)s 

""" # variables: (test_list, count, Pass, fail, error)

REPORT_CLASS_TMPL = r"""

%(desc)s%(count)s%(Pass)s%(fail)s 用例列表

""" # variables: (style, desc, count, Pass, fail, error, cid)

REPORT_TEST_WITH_OUTPUT_TMPL = r"""

%(desc)s

%(status)s

[x]

%(script)s

""" # variables: (tid, Class, style, desc, status)

REPORT_TEST_NO_OUTPUT_TMPL = r"""

%(desc)s

%(status)s

""" # variables: (tid, Class, style, desc, status)

REPORT_TEST_OUTPUT_TMPL = r"""

%(id)s: %(output)s

""" # variables: (id, output)

# ------------------------------------------------------------------------

# ENDING

#

ENDING_TMPL = """

3.读取excel文件数据，组装html,代码如下：

#注意：文件存入的目录\\result\\下

path = unicode(os.getcwd(), 'gb18030')

if True == os.path.isdir(path + '\\result\\'):

path_res = path

else:

path_res = os.path.dirname(path)

def getReportAttributes(startTime,duration,success_count,failure_count):

"""

Return report attributes as a list of (name, value).

Override this to add custom attributes.

"""

status = []

status.append('通过数 %s' % success_count)

status.append('失败数 %s' % failure_count)

if status:

status = ' '.join(status)

else:

status = 'none'

return [

('开始时间', startTime),

('持续时间', duration),

('运行状态', status),

]

def generateReport(excel,sheet_name,project_Name,title,duration):

report, pass_count, fail_count = generate_report(excel, sheet_name,project_Name)

report_attrs = getReportAttributes(str(d),duration,pass_count,fail_count)

generator = 'Ports_1.0'

stylesheet = generate_stylesheet()

heading = generate_heading(title,report_attrs)

ending = generate_ending()

output = HTML_TMPL % dict(

title=saxutils.escape(title),

generator=generator,

stylesheet=stylesheet,

heading=heading,

report=report,

ending=ending,

)

#self.stream.write(output.encode('utf8'))

return output

def generate_stylesheet():

return STYLESHEET_TMPL

def generate_heading(title,report_attrs):

a_lines = []

for name, value in report_attrs:

line = HEADING_ATTRIBUTE_TMPL % dict(

name=saxutils.escape(name),

value=saxutils.escape(value),

)

a_lines.append(line)

heading = HEADING_TMPL % dict(

title=saxutils.escape(title),

parameters=''.join(a_lines),

description=saxutils.escape(u"执行情况"),

)

return heading

def exceldata(excel_path, sheet_name):

try:

wb = xlrd.open_workbook(path_res + '\\result\\Excel_report\\' + excel_path)

except:

wb = xlrd.open_workbook(excel_path)

sheet = wb.sheet_by_name(sheet_name)

return sheet

‘’‘

def Duplicate_removal(info_list):

if len(info_list) != 0:

Info = []

[Info.append(i) for i in info_list if not i in Info]

else:

Info = []

return Info

’‘’

def getexceldata(excel,sheet_name):

sheet = exceldata(excel, sheet_name)

a = [];

b = [];

c = []

for x in range(sheet.nrows - 1):

cells = sheet.row_values(x + 1)

a.append(cells[0])

if cells[19] == 'PASS':#统计成功

b.append("PASS")

elif cells[19] == 'FAIL':#统计失败

c.append("FAIL")

#Info = Duplicate_removal(a)

return b,c,sheet

def generate_ending():

return ENDING_TMPL

def generate_report(excel,sheet_name,project_Name):

b, c,sheet=getexceldata(excel,sheet_name)

if "FAIL" in c:

style="failClass"

else:style="passClass"

rows = REPORT_CLASS_TMPL % dict(

style=style,

desc=u"用例名称",

count=len(b) + len(c),

Pass=len(b),

fail=len(c),

cid='c1',

)

rows_list = []

for x in range(sheet.nrows - 1):

cells = sheet.row_values(x + 1)

print

tmp1=REPORT_TEST_WITH_OUTPUT_TMPL

if cells[19]=="FAIL":

class_name='none'

class_style='failCase'

status_res='fail'

f_t="ft1."

else:

class_name='hiddenRow'

class_style='none'

status_res = 'pass'

f_t = "pt1."

# x+1表示序号，script运行过程及结果

xx_res=f_t+str(x+1)+u":\n接口名称："+\

str(cells[0])+u"\n用例名称："+str(cells[1])+\

u"\n请求URL:\n"+str(cells[3])+u"\n请求方式："+str(cells[2])+\

u"\n请求参数和数据：\n"+str(cells[4])+u"\n请求信息头：\n"+str(cells[5])+\

u"\n响应状态："+str(cells[15])+u"\n响应时间："+str(cells[16])+ \

u"\n返回报文：\n" + str(cells[17])+ u"\n" + str(cells[18])+\

u"\n比对结果：" + str(cells[19])+ u"\n执行时间：" + str(cells[20])

row = tmp1 % dict(

tid=f_t+str(x+1),

class="class_name",

style=class_style,

desc=cells[1],

script=saxutils.escape(xx_res),

status=status_res )

rows_list.append(row)

#excel.split(".xls")[0]

report = REPORT_TMPL % dict(

Project_Name=project_Name,

test_list=rows+''.join(rows_list),

count=str(len(b) + len(c)),

Pass=str(len(b)),

fail=str(len(c)),

)

pass_count = len(b)

fail_count = len(c)

return report,pass_count,fail_count

def c_htmlfile(file_name, result_name):

import io

f = io.open(path_res + '\\result\\' + file_name + '\\' + 'html.html', "a",encoding='utf-8')

f.close()

import time

new = time.strftime('%Y%m%d%H%M%S', time.localtime())

os.rename(path_res + '\\result\\' + file_name + '\\' + 'html.html',

path_res + '\\result\\' + file_name + '\\' + result_name + '_' + new + '.html')

html_result = result_name + '_' + new + '.html'

return html_result

def write_html(excel,sheet_name,project_Name,title,duration):#excel名，表名，测试项目名，标题，持续时间

if True== os.path.exists(path_res + '\\result\\Html_report\\html.html'):

os.remove(path_res + '\\result\\Html_report\\html.html')

output = generateReport(excel,sheet_name,project_Name,title,duration)

html_path=c_htmlfile("Html_report", "ports_test")

import io

f = io.open(path_res + '\\result\\Html_report\\'+html_path, "a",encoding='utf-8')

f.write(output)

f.close()

return html_path

write_html('test_1234_20170612121212.xls', u"测试详情","测试标题","0:00:46.800000")

哈哈哈，完美生成html报告