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

#!/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:\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 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:\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()