用python+selenium导入excel文件

#encoding=utf-8import pymysql
import timeclass ConnMysql(object):
def __init__(self):
self.conn = pymysql.connect(host='192.168.2.53',user='tester',passwd='tester890__',db='ips_sqrz',charset='utf8')
self.cur = self.conn.cursor() def selectSql(self):
selectSql = ("SELECT secondprojectno FROM ips_smallproject_info") #��ѯ��ݿ����
self.cur.execute(selectSql)
self.results = self.cur.fetchall() def getTime(self):
self.temp = int(time.strftime('%Y%m%d%H%M%S',time.localtime(time.time()))) def closeSql(self):
self.cur.close()
self.conn.close()

#!/usr/bin/env python
#-*- coding:gbk -*-
import sys
reload(sys)
sys.setdefaultencoding('gbk')import xlrd
from xlrd import open_workbook
from xlutils.copy import copy
from ConnMysql import ConnMysqlclass OpExcel(object):
def __init__(self):
file = xlrd.open_workbook("E:\WorkSpace\python34Demo\data\\运单导入模板.xlsx")
sheet = file.sheet_by_index(0)
self.sheets = copy(file)
self.table = self.sheets.get_sheet(0) self.conn = ConnMysql()
self.conn.selectSql() def rExcel(self):
print "生成运单模板中，请稍等。。。"
row = 1
col = 0
for each in self.conn.results:
if row == 10:
pass
else:
self.conn.getTime()
self.table.write(row, col, each)
self.table.write(row, col+1, self.conn.temp+row)
self.sheets.save("E:\WorkSpace\python34Demo\data\\运单导入模板.xlsx")
row += 1
self.conn.closeSql()

#!/usr/bin/env python
#-*- coding:gbk -*-
import sys
reload(sys)
sys.setdefaultencoding('gbk')from selenium import webdriver
import time,sys
from OpExcel import OpExcelclass ImportOrder(object):
def __init__(self):
driver = webdriver.Firefox()
driver.get("http://sqrz55.ips.cn")
driver.maximize_window() time.sleep()
driver.find_element_by_id("username").send_keys("sqrz_admin")
driver.find_element_by_id("password").send_keys("admin123")
driver.find_element_by_class_name("login_btn").click() time.sleep(2)
if driver.title == u'首页 - G7':
print '登陆成功'
driver.get("http://sqrz55.ips.cn/ordercenter/import.html") time.sleep(1) #直接为导入控件赋文件的绝对路径
driver.find_element_by_xpath('//*[@id="importfile"]').\
send_keys(u"E:\WorkSpace\python34Demo\data\\运单导入模板.xlsx")
driver.find_element_by_id("btnsubmit").click()
time.sleep(2)
driver.find_element_by_class_name("ui_state_highlight").click() succeed = driver.find_element_by_xpath("//span[@id='success_count']").text #得到成功导入和失败导入的数目
error = driver.find_element_by_xpath("//span[@id='error_count']").text
if succeed >= 0:
print '成功导入'+succeed+'条数据'
print '失败'+error+'条数据'
else:
print '登陆失败，请重新登陆' driver.close()if __name__ == '__main__':
ex = OpExcel()
ex.rExcel()
io = ImportOrder()