使用xlwt写excel文件例子--杂七杂八记一下

 

使用xlwt写excel文件例子

#!/usr/bin/python
# this script can create a excel for the given data
# create date: 2011-5-27
# Modify date: 2011-6-14

import xlwt

#file_name is the excel file name which will store the data,
def write_xls(file_name, suite):
    suite_name = suite.getSuiteName()
    case_dict = suite.get_case_dic()
    book = xlwt.Workbook()
    sheet = book.add_sheet("sheet 1")
    #heading style
    style_heading = xlwt.easyxf("""
        font:
            name Arial,
            colour_index white,
            bold on,
            height 0xA0;
        align:
            wrap on,
            vert center,
            horiz left;
        pattern:
            pattern solid,
            fore-colour 0x19;
        borders:
            left THIN,
            right THIN,
            top THIN,
            bottom THIN
        """)
    #title, how many cell will use
    headings = [['Test Suite', 0, 0], \
                ['Test Case', 1, 2], \
                ['Test Case Parameters', 3, 3], \
                ['Results', 4, 4], \
                ['Comment', 5, 5]]
    #write head
    row_start = 0
    row_end = 0
    index = 0
    for index, item in enumerate(headings):
        sheet.write_merge(row_start, row_end, item[1], item[2], item[0], style_heading)
    #style_suite for test suite, size 0xF0 mean 12
    style_suite = xlwt.easyxf("""
        font:
            name Arial,
            colour_index black,
            bold on,
            height 0xF0;
        pattern:
            pattern solid,
            fore-colour 0x32;
        align:
            wrap on,
            vert center,
            horiz center;
        borders:
            left THIN,
            right THIN,
            top THIN,
            bottom THIN ;  
        """)

    #style_case for test case and test parameters
    style_case = xlwt.easyxf("""
        font:
            name Arial,
            colour_index blue,
            height 0xA0;
        align:
            wrap on,
            vert top,
            horiz left;
        borders:
            left THIN,
            right THIN,
            top THIN,
            bottom THIN; 
        """)
   
    #style_result_pass for test pass result
    style_result_pass = xlwt.easyxf("""
        font:
            name Arial,
            colour_index black,
            height 0xA0;
        borders:
            left THIN,
            right THIN,
            top THIN,
            bottom THIN;
        """)
    #style_result_fail for test fail result
    style_result_fail = xlwt.easyxf("""
        font:
            name Arial,
            colour_index red,
            height 0xA0;
        borders:
            left THIN,
            right THIN,
            top THIN,
            bottom THIN;
        """)
    #style_result_na for test not available result, whitch will mark as N/A
    style_result_na = xlwt.easyxf("""
        font:
            name Arial,
            colour_index 0x30,
            height 0xA0;
        borders:
            left THIN,
            right THIN,
            top THIN,
            bottom THIN;
        """)
    #style_result_ready for test with doesn't run
    style_result_ready = xlwt.easyxf("""
        font:
            name Arial,
            colour_index 0x30,
            height 0xA0;
        borders:
            left THIN,
            right THIN,
            top THIN,
            bottom THIN;
        """)
    suite_count = 0
    case_start = 0
    suite_name = suite.getSuiteName()
    case_dic = suite.get_case_dic()
    case_order=suite.get_case_order()
    for case_name in case_order:
#    for case_name, case_items in case_dic.items():
        case_items=case_dic[case_name]
        para_dic = case_items.get_para_dic()
        case_count = 0
        case_start = suite_count            #which line to record the case
        for para_name, para_result in para_dic.items():
            suite_count = suite_count + 1
            case_count = case_count + 1  
            current_style = style_result_na
            current_result = ''
            test_result = para_result.getParaStatus()
            if test_result.upper()== 'PASS':
                current_style = style_result_pass
                current_result = 'Pass'
            elif test_result.upper() == 'FAIL':
                current_style = style_result_fail
                current_result = 'Fail'
            elif test_result.upper() == 'READY':
                current_style = style_result_ready
                current_result = 'Ready'
            else:
                current_style = style_result_na
            #the case have how many lines (one line one para
            #print suite_name, case_name, para_name, \
            #para_result.getParaName(), para_result.getParaStatus()
            #write parameter to excel
            sheet.write_merge(suite_count, suite_count, 3, 3, para_name, style_case)
            #write result to excel
            sheet.write_merge(suite_count, suite_count, 4, 4, current_result, current_style)
            #write comments to excel
            sheet.write_merge(suite_count, suite_count, 5, 5, '', style_result_pass)
        #write test case to excel
        #print case_start + 1, case_start + case_count + 1
        #print "%d,%d, %s" % (case_start + 1, case_start + case_count, case_name)
        if case_count == 0:  #if no para in the testcase, set 1 for default count
            case_count = 1
            suite_count = suite_count + 1
            sheet.write_merge(suite_count, suite_count, 3, 3, '', style_case)
            #write result to excel
            sheet.write_merge(suite_count, suite_count, 4, 4, '', style_result_pass)
            #write comments to excel
            sheet.write_merge(suite_count, suite_count, 5, 5, '', style_result_pass)
           
        print "%s, case_count %d,case_start %d, suite_count %d" % (case_name, case_count, case_start, suite_count)
        sheet.write_merge(case_start + 1, case_start + case_count, 1, 2, case_name, style_case)
    #print suite_count
    sheet.write_merge(1, suite_count, 0, 0, suite_name, style_suite)
    book.save(file_name)

=========================================

test suite 类

class CTestsuite:
 """a BTA testsuilt which contains tset cases """

 def __init__(self):
  self.SuiteName = None;
  self.SuiteStatus = None;
  self.case_dic={}
  self.case_order=[]
  self.sDuration=0
  self.sHours=0
  self.sMinutes=0
  self.sSeconds=0
                #print 'testcase init %s %s %s' %(name,command,comment);
 def order_add(self,cname):
  self.case_order.append(cname)
 def get_case_order(self):
  return self.case_order
 def getSDuration(self):
  return self.sDuration

        def setSuiteName(self,name):
                self.SuiteName=name
        def getSuiteName(self):
                return self.SuiteName
        #status = '';
        def setSuiteStatus(self,status):
                self.SuiteStatus=status
        def getSuiteStatus(self):
                return self.SuiteStatus
        def setSuiteDuration(self,duration):
                self.SuiteDuration=duration
 def get_case_byname(self,name):
  return self.case_dic[name]
  
 def add(self,name_key,case):
  """Add one test case into testsuilt
  test case will record in dictionary
  """
  self.case_dic[name_key]=case
                self.sHours+=case.cHours
                self.sMinutes+=case.cMinutes
                self.sSeconds+=case.cSeconds
                if self.sSeconds > 59:
                        self.sMinutes=self.sMinutes+self.sSeconds//59
                        self.sSeconds=self.sSeconds%59
                if self.sMinutes > 59:
                        print 'minute >59,minute:%s' % self.sMinutes
                        self.sHours=self.sHours+self.sMinutes//59
                        self.sMinutes=self.sMinutes%59
                       
                       
                       
                self.sDuration="H:%d M:%d S:%d " % (self.sHours,self.sMinutes,self.sSeconds)
                #print self.sDuration

 def get_case_dic(self):
  return self.case_dic

 def printer(self):
  print "X"*30
  print 'Testsuilt name:',self.SuiteName,'\t','Status:',self.SuiteStatus,'\t','Duration:',self.getSDuration()
  print "X"*30
  #for case in self.case_list:
  # case.print_allpara()
  for key,value in self.case_dic.items():
   print "Case Name:%s\tDuration:%s" % (key,value.getCDuration())
   for p_key,parameter in value.para_dic.items():
    print "Case:",key,"\t Parameter:",parameter.getParaName(),"\t Status:",parameter.getParaStatus(),"\t Duration:",parameter.getParaDuration()
   print "---"*4
  print "*"*30