view plaincopy to clipboardprint?
#****************************************************************  
# TestFrame.py  
# Author     : Vince  
# Version    : 1.1.2  
# Date       : 2011-3-14  
# Description: 自动化测试平台  
#****************************************************************  
 
import os,sys, urllib, httplib, profile, datetime, time  
from xml2dict import XML2Dict  
import win32com.client  
from win32com.client import Dispatch  
import xml.etree.ElementTree as et  
#import MySQLdb  
 
#Excel表格中测试结果底色  
OK_COLOR=0xffffff 
NG_COLOR=0xff 
#NT_COLOR=0xffff  
NT_COLOR=0xC0C0C0 
 
#Excel表格中测试结果汇总显示位置  
TESTTIME=[1, 14]  
TESTRESULT=[2, 14]  
 
#Excel模版设置  
#self.titleindex=3        #Excel中测试用例标题行索引  
#self.casebegin =4        #Excel中测试用例开始行索引  
#self.argbegin   =3       #Excel中参数开始列索引  
#self.argcount  =8        #Excel中支持的参数个数  
class create_excel:  
    def __init__(self, sFile, dtitleindex=3, dcasebegin=4, dargbegin=3, dargcount=8):  
        self.xlApp = win32com.client.Dispatch('et.Application')   #MS:Excel  WPS:et  
        try:  
            self.book = self.xlApp.Workbooks.Open(sFile)  
        except:  
            print_error_info()  
            print "打开文件失败" 
            exit()  
        self.file=sFile  
        self.titleindex=dtitleindex  
        self.casebegin=dcasebegin  
        self.argbegin=dargbegin  
        self.argcount=dargcount  
        self.allresult=[]  
          
        self.retCol=self.argbegin+self.argcount  
        self.xmlCol=self.retCol+1 
        self.resultCol=self.xmlCol+1 
 
    def close(self):  
        #self.book.Close(SaveChanges=0)  
        self.book.Save()  
        self.book.Close()  
        #self.xlApp.Quit()  
        del self.xlApp  
          
    def read_data(self, iSheet, iRow, iCol):  
        try:  
            sht = self.book.Worksheets(iSheet)  
            sValue=str(sht.Cells(iRow, iCol).Value)  
        except:  
            self.close()  
            print('读取数据失败')  
            exit()  
        #去除'.0'  
        if sValue[-2:]=='.0':  
            sValue = sValue[0:-2]  
        return sValue  
 
    def write_data(self, iSheet, iRow, iCol, sData, color=OK_COLOR):  
        try:  
            sht = self.book.Worksheets(iSheet)  
            sht.Cells(iRow, iCol).Value = sData.decode("utf-8")  
            sht.Cells(iRow, iCol).Interior.Color=color  
            self.book.Save()  
        except:  
            self.close()  
            print('写入数据失败')  
            exit()  
      
    #获取用例个数      
    def get_ncase(self, iSheet):  
        try:  
            return self.get_nrows(iSheet)-self.casebegin+1 
        except:  
            self.close()  
            print('获取Case个数失败')  
            exit()  
      
    def get_nrows(self, iSheet):  
        try:  
            sht = self.book.Worksheets(iSheet)  
            return sht.UsedRange.Rows.Count  
        except:  
            self.close()  
            print('获取nrows失败')  
            exit()