# -*- coding: UTF-8 -*- ''' Created on 2016年5月13日 @author: csxie ''' import datetime from Base import BasePage import ExcelOperation as excel from selenium.webdriver.support.ui import WebDriverWait from selenium.webdriver.support.expected_conditions import text_to_be_present_in_element import unittest class JobLog(BasePage): url=""#目标url def setUp(self): BasePage.setUP(self) def tearDown(self): BasePage.tearDown(self) def test_querysql(self): """ 查询到符合条件的信息并写入excel """ driver=self.driver driver.get(self.url) ''' 设置参数 start:起始日期 end:截止日期 user:查询人员 ''' end=datetime.date.today() start=end+datetime.timedelta(days=-6) user=None #起始日期 txtStart=driver.find_element_by_id("ctl01_txtSelectDateFrom") txtStart.clear() txtStart.send_keys(start) #截止日期 txtEnd=driver.find_element_by_id("ctl01_txtSelectDateTo") txtEnd.clear() txtEnd.send_keys(end) #查询人员 if(user!=None): txtuser=driver.find_element_by_id("ctl01_txtUser") txtuser.clear() txtuser.send_keys(user) #查询按钮 btnQueryInfo=driver.find_element_by_id("ctl01_btnQueryInfo") btnQueryInfo.click() #总页数 totalPages=int(driver.find_element_by_id("ctl01_uc_CommonPager_lb_TotalPages").text) i=1 while(i<=totalPages): #table的xpath listTable=self.getTableData(".//*[@id='ctl01__wrLogList_gvlist']",colNO=9,loop=i) #当前页数 currentPage=int(driver.find_element_by_id("ctl01_uc_CommonPager_lb_PageNum").text) if(currentPage==1): excel.save_to_excel_newsheet(listTable,excelPath='joblog.xls') else: excel.save_to_excel_oldsheet(listTable,excelPath='joblog.xls') if(i!=totalPages): #下一页按钮 driver.find_element_by_id("ctl01_uc_CommonPager_NextPage").click() WebDriverWait(driver,8).until(text_to_be_present_in_element(('id','ctl01_uc_CommonPager_lb_PageNum'),str(i+1))) i=i+1 def getTableData(self,value,colTag='td',colNO=None,loop=None): ''' 获取表格数据 colNO:存在合并行时，强制指定表格列数 ''' driver=self.driver table=driver.find_element(by="xpath",value=value) rows=table.find_elements(by="tag name",value="tr")#行集合 maxrowCount=len(rows)#表格行数 #如果colNO=None不为空，则用传进来的值作为列数 if(colNO==None): cols = table.find_elements(by="tag name",value="td"); if(cols!='td'): cols = table.find_elements(by="tag name",value="th") maxcolCount=len(cols)/len(rows)#表格列数 else: maxcolCount=colNO lists = [[] for i in range(maxrowCount)] #print "列数"+str(maxcolCount) #print "行数"+str(maxrowCount) i=1 while i<=maxrowCount: j=1;#初始化列 while j<=maxcolCount: try: if(i==1): try: innerText=driver.find_element(by="xpath",value=value+"/tbody/tr["+str(i)+"]/"+colTag+"["+str(j)+"]").text except: colTag="th" innerText=driver.find_element(by="xpath",value=value+"/tbody/tr["+str(i)+"]/"+colTag+"["+str(j)+"]").text else: colTag="td" if(j==3 or j==5): innerText=driver.find_element(by="xpath",value=value+"/tbody/tr["+str(i)+"]/"+colTag+"["+str(j)+"]/a").get_attribute("title") elif(j==8): innerText=driver.find_element(by="xpath",value=value+"/tbody/tr["+str(i)+"]/"+colTag+"["+str(j)+"]").text innerText=float(innerText.replace("h", "")) else: innerText=driver.find_element(by="xpath",value=value+"/tbody/tr["+str(i)+"]/"+colTag+"["+str(j)+"]").text except: innerText="" #print "第"+str(i)+"行,第"+str(j)+"列"+str(innerText) lists[i-1].append(innerText) j+=1 i+=1 if(loop!=1):#不是第一次循环，列头不要 return lists[1:] return lists if __name__ == "__main__": unittest.main()
# -*- coding: UTF-8 -*- ''' Created on 2016年5月18日 @author: csxie ''' import xlwt from xlrd import open_workbook from xlutils.copy import copy import os def save_to_excel_newsheet(listTable,excelPath=r'C:demo.xls',sheetName='sheet1'): if(isinstance(listTable,list)): rowNO=len(listTable); if(rowNO==0): raise ValueError,u'传入的是list是空的' colNO=len(listTable[0]); wkb = xlwt.Workbook() sheet = wkb.add_sheet(sheetName) for i in range(rowNO): for j in range(colNO): #print listTable[i][j] #print str(i)+'行'+str(j)+'列' sheet.write(i,j,listTable[i][j]) if(os.path.exists(excelPath)): os.remove(excelPath) wkb.save(excelPath) return True else: raise TypeError,u'传入的类型不是list' def save_to_excel_oldsheet(listTable,excelPath=r'C:demo.xls',sheetName='sheet1'): if(isinstance(listTable,list)): rowNO=len(listTable); if(rowNO==0): raise ValueError,u'传入的是list是空的' colNO=len(listTable[0]); rb = open_workbook(excelPath) sheet_index=rb.sheet_names().index(sheetName) old_rows=rb.sheet_by_name(sheetName).nrows#已存在的excel中数据行数 wkb = copy(rb) sheet = wkb.get_sheet(sheet_index) for i in range(rowNO): for j in range(colNO): sheet.write(i+old_rows,j,listTable[i][j]) #print str(i)+'行'+str(j)+'列' wkb.save(excelPath) return True else: raise TypeError,u'传入的类型不是list'
# -*- coding: UTF-8 -*- from selenium import webdriver import unittest class BasePage(unittest.TestCase): def setUP(self): self.driver= webdriver.PhantomJS(executable_path="phantomjs.exe") #self.driver.set_window_size(1400, 1000) #self.driver.implicitly_wait(1) self.verificationErrors = [] def tearDown(self): self.driver.quit() #self.driver.close() self.assertEqual([], self.verificationErrors)