# -*- 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)
