用python+selenium导入excel文件

连接mysql

#encoding=utf-8

import pymysql
import time

class 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() 

修改excel

#!/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 ConnMysql

class OpExcel(object):
    def __init__(self):        
        file = xlrd.open_workbook("E:WorkSpacepython34Demodata\运单导入模板.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:WorkSpacepython34Demodata\运单导入模板.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 OpExcel

class 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:WorkSpacepython34Demodata\运单导入模板.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()