将Excel数据自动录入国家防返贫系统

不会编程的看这里：https://www.cnblogs.com/liangxinhy/p/17842931.html

一、配置运行环境

（1）语言python，编程软件Pychram

（2）下载三个功能库——selenium、xlrd、xlwt

import time
from selenium.webdriver.common.by import By
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.wait import WebDriverWait
from selenium import webdriver
import xlrd
import xlwt

selenium新版本大改，很多接口都改了，下低版本，和我版本一致

（3）下载谷歌浏览器，查看浏览器版本（下一个低版本的Chrome）

打开chrome 在网页地址栏中输入 “chrome://version/”来查看chrome版本

（4）下载合适的chromedriver.exe

访问此网站：http://chromedriver.storage.googleapis.com/index.html
然后选择合适版本的chromedriver.exe，版本尽量一致，不一致就选择相似的版本，一般没有问题

（5）将下载的文件解压到D盘下的pip文件夹下面（也可以改代码——driver = getDriver(driverLocation="D:/pip/chromedriver.exe")）

二、复制代码运行

（1）运行前记得改代码

a、getIncomeInfo是获取监测户得收入情况信息，请根据自己的excel修改对应列

def getIncomeInfo(table, row):
    dict = {"户主姓名":"", "公益性岗位收入": "", "其它工资性收入": "", "财产性收入": "最低保证金", "特困人员救助供养金": "",
            "养老或离退休金": "", "计划生育金": "","生态补偿金": "", "产业奖励": "", "就业奖励": "",
            "其它转移性收入": "", "生产经营性收入": "", "专项用于减少生产经营支出的补贴": "","生产经营性支出": ""}
    dict["户主姓名"] = getData(table, row, 3)
    dict["公益性岗位收入"] = getData(table, row, 5)
    dict["其它工资性收入"] = getData(table, row, 6)
    dict["财产性收入"] = getData(table, row, 18)
    dict["最低保证金"] = getData(table, row, 12)
    dict["特困人员救助供养金"] = getData(table, row, 13)
    dict["养老或离退休金"] = getData(table, row, 11)
    dict["计划生育金"] = getData(table, row, 10)
    dict["生态补偿金"] = getData(table, row, 14)
    dict["产业奖励"] = getData(table, row, 15)
    dict["就业奖励"] = getData(table, row, 16)
    dict["其它转移性收入"] = getData(table, row, 17)
    dict["生产经营性收入"] = getData(table, row, 7)
    dict["专项用于减少生产经营支出的补贴"] = getData(table, row, 9)
    dict["生产经营性支出"] = getData(table, row, 8)
    return dict

b、修改excel表的位置和表名（提一句，记得xlrd、xlwt只有控制.xls文件，记得把.xlsx另存为.xls文件）

table = gettable("D:/pip/脱贫户收入录入.xls", "Sheet1")

c、修改excel对应的身份证所在列

id = getData(table, row, 6)

d、修改运行文件所在位置及文件名

savePath = 'D:/pip/运行结果.xls'

（2）代码源码

import time
from selenium.webdriver.common.by import By
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.wait import WebDriverWait
from selenium import webdriver
import xlrd
import xlwt



# 获取driver
def getDriver(driverLocation):
    chromeDriverLocation = driverLocation
    driver = webdriver.Chrome(executable_path=chromeDriverLocation)
    return driver

def refresh(driver):
    driver.refresh()
    print("浏览器刷新")
    WebDriverWait(driver, 90, 2).until(
        EC.presence_of_element_located((By.XPATH, '//*[@id="myApplica"]/div/div[1]/div[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(
        EC.presence_of_element_located((By.XPATH, '//*[@id="dialog"]/div/center/div/div/ul/li[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located(
        (By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/div[2]/input'))).send_keys(
        "户年度基础信息更新")

    flag = 1
    while True:
        try:
            if flag == 4:
                break
            WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/div[2]/i'))).click()
            break
        except:
            flag = flag + 1
            time.sleep(0.5)
    if flag == 4:
        raise Exception("出问题了,waitForLogin1")
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH,
                                                                         '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/app-menu/ul/nui-main-menu/div/div/div[2]/div/nui-main-menu-sub/ul/li/nui-main-menu-sub/ul/li/nui-main-menu-sub/ul/li/a/span[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '//*[@id="aab004"]')))
    print("刷新成功")


def refresh1(driver):
    driver.refresh()
    print("浏览器刷新")
    WebDriverWait(driver, 90, 2).until(
        EC.presence_of_element_located((By.XPATH, '//*[@id="myApplica"]/div/div[1]/div[1]'))).click()
    WebDriverWait(driver, 90, 0.5).until(
        EC.presence_of_element_located((By.XPATH, '//*[@id="dialog"]/div/center/div/div/ul/li[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located(
        (By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/div[2]/input'))).send_keys(
        "户年度基础信息更新")
    flag = 1
    while True:
        try:
            if flag == 4:
                break
            WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located(
                (By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/div[2]/i'))).click()
            break
        except:
            flag = flag + 1
            time.sleep(0.5)
    if flag == 4:
        raise Exception("出问题了,waitForLogin1")
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH,
                                                                         '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/app-menu/ul/nui-main-menu/div/div/div[2]/div/nui-main-menu-sub/ul/li/nui-main-menu-sub/ul/li/nui-main-menu-sub/ul/li/a/span[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '//*[@id="aab004"]'))).click()
    print("重新登录成功")



# 登录页面，进入监测户信息界面
def waitForLogin(driver):
    driver.get("http://106.38.235.201:7080/cas/login")
    driver.maximize_window()
    WebDriverWait(driver, 300, 2).until(EC.presence_of_element_located((By.XPATH, '//*[@id="myApplica"]/div/div[1]/div[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '//*[@id="dialog"]/div/center/div/div/ul/li[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/div[2]/input'))).send_keys("户年度基础信息更新")
    flag = 1
    while True:
        try:
            if flag == 4:
                break
            WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/div[2]/i'))).click()
            break
        except:
            flag = flag + 1
            time.sleep(0.5)
    if flag == 4:
        raise Exception("出问题了,waitForLogin")
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/app-menu/ul/nui-main-menu/div/div/div[2]/div/nui-main-menu-sub/ul/li/nui-main-menu-sub/ul/li/nui-main-menu-sub/ul/li/a/span[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH,'//*[@id="aab004"]')))
    print("登录成功")

# 登录页面，进入脱贫户信息界面
def waitForLogin1(driver):
    driver.get("http://106.38.235.201:7080/cas/login")
    driver.maximize_window()
    WebDriverWait(driver, 300, 2).until(EC.presence_of_element_located((By.XPATH, '//*[@id="myApplica"]/div/div[1]/div[1]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '//*[@id="dialog"]/div/center/div/div/ul/li[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/div[2]/input'))).send_keys("户年度基础信息更新")
    flag = 1
    while True:
        try:
            if flag == 4:
                break
            WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/div[2]/i'))).click()
            break
        except:
            flag = flag + 1
            time.sleep(0.5)
    if flag == 4:
        raise Exception("出问题了,waitForLogin1")
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '/html/body/app-root/div/div/app-main-layout/div/div[1]/div/div/app-menu/ul/nui-main-menu/div/div/div[2]/div/nui-main-menu-sub/ul/li/nui-main-menu-sub/ul/li/nui-main-menu-sub/ul/li/a/span[2]'))).click()
    WebDriverWait(driver, 90, 0.5).until(EC.presence_of_element_located((By.XPATH, '//*[@id="aab004"]')))
    print("登录成功")

def clickEle(driver, XPATH):
    WebDriverWait(driver, 60, 0.5).until(EC.presence_of_element_located((By.XPATH, XPATH))).click()

def sendInf0(driver, XPATH, info):
    WebDriverWait(driver, 60, 0.5).until(EC.presence_of_element_located(
        (By.XPATH, XPATH))).clear()
    WebDriverWait(driver, 60, 0.5).until(EC.presence_of_element_located(
        (By.XPATH, XPATH))).send_keys(info)



#输入身份证，打开监测户收入计算界面
def findPerson(id):
    #输入id
    #点击搜索
    WebDriverWait(driver, 120, 0.5).until(EC.presence_of_element_located((By.XPATH, '//*[@id="aab004"]')))
    sendInf0(driver, '//*[@id="aab004"]', id)
    WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="on_query"]/span'))).click()


def incomeCalculationPage(id):
    # 点击姓名超链接，进入监测户信息界面
    flag = 0
    while True:
        try:
            if flag == 40:
                break
            WebDriverWait(driver, 5, 0.5).until(EC.element_to_be_clickable((By.XPATH,
                                                                              '//*[@id="ui-tabpanel-1"]/div/busi-tab/object-poor-family/p-panel[3]/div/div[2]/div/div/object-poor-family-grid/p-datatable/div/div[1]/div/div[2]/div/table/tbody/tr/td[4]/span/span'))).click()
            WebDriverWait(driver, 5, 0.5).until(EC.element_to_be_clickable((By.XPATH,
                                                                              '//*[@id="ui-tabpanel-1"]/div/busi-tab/object-poor-family/p-dialog[1]/div/div[2]/p-tabview/div/ul/li[3]/a'))).click()
            break
        except:
            time.sleep(0.5)
            flag = flag + 1
    if flag == 40:
        raise Exception("找不到这个人，或者实在太卡了")





# #获取监测户得收入情况信息
def getIncomeInfo(table, row):
    dict = {"户主姓名":"", "公益性岗位收入": "", "其它工资性收入": "", "财产性收入": "最低保证金", "特困人员救助供养金": "",
            "养老或离退休金": "", "计划生育金": "","生态补偿金": "", "产业奖励": "", "就业奖励": "",
            "其它转移性收入": "", "生产经营性收入": "", "专项用于减少生产经营支出的补贴": "","生产经营性支出": ""}
    dict["户主姓名"] = getData(table, row, 3)
    dict["公益性岗位收入"] = getData(table, row, 5)
    dict["其它工资性收入"] = getData(table, row, 6)
    dict["财产性收入"] = getData(table, row, 18)
    dict["最低保证金"] = getData(table, row, 12)
    dict["特困人员救助供养金"] = getData(table, row, 13)
    dict["养老或离退休金"] = getData(table, row, 11)
    dict["计划生育金"] = getData(table, row, 10)
    dict["生态补偿金"] = getData(table, row, 14)
    dict["产业奖励"] = getData(table, row, 15)
    dict["就业奖励"] = getData(table, row, 16)
    dict["其它转移性收入"] = getData(table, row, 17)
    dict["生产经营性收入"] = getData(table, row, 7)
    dict["专项用于减少生产经营支出的补贴"] = getData(table, row, 9)
    dict["生产经营性支出"] = getData(table, row, 8)
    return dict


#填写监测户得收入情况信息
def setIncomeInformation(driver, dict):
    message = []
    #获取需填入收入
    #1、公益性岗位收入
    WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac355_3"]'))).click()
    sendInf0(driver, '//*[@id="aac355_3"]', dict["公益性岗位收入"])
    #2、其它工资性收入
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac356_3"]')))
    sendInf0(driver, '//*[@id="aac356_3"]', dict["其它工资性收入"])
    #3、财产性收入
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac072_3"]')))
    sendInf0(driver, '//*[@id="aac072_3"]', dict["财产性收入"])
    #4、最低保证金
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac077_3"]')))
    sendInf0(driver, '//*[@id="aac077_3"]', dict["最低保证金"])
    #5、特困人员救助供养金
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac086_3"]')))
    sendInf0(driver, '//*[@id="aac086_3"]', dict["特困人员救助供养金"])
    #6、养老或离退休金
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac087_3"]')))
    sendInf0(driver, '//*[@id="aac087_3"]', dict["养老或离退休金"])
    #7、计划生育金
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac076_3"]')))
    sendInf0(driver, '//*[@id="aac076_3"]', dict["计划生育金"])
    #8、生态补偿金
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac078_3"]')))
    sendInf0(driver, '//*[@id="aac078_3"]', dict["生态补偿金"])
    #9、产业奖励
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac357_3"]')))
    sendInf0(driver, '//*[@id="aac357_3"]', dict["产业奖励"])
    #10、就业奖励
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac358_3"]')))
    sendInf0(driver, '//*[@id="aac358_3"]', dict["就业奖励"])
    #11、其它转移性收入
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac083_3"]')))
    sendInf0(driver, '//*[@id="aac083_3"]', dict["其它转移性收入"])
    #12、生产经营性收入
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac071_3"]')))
    sendInf0(driver, '//*[@id="aac071_3"]', dict["生产经营性收入"])
    #13、专项用于减少生产经营支出的补贴
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac360_3"]')))
    sendInf0(driver, '//*[@id="aac360_3"]', dict["专项用于减少生产经营支出的补贴"])
    #14、生产经营性支出
    # WebDriverWait(driver, 120, 0.5).until(EC.element_to_be_clickable((By.XPATH, '//*[@id="aac359_3"]')))
    sendInf0(driver, '//*[@id="aac359_3"]', dict["生产经营性支出"])
    # 1、公益性岗位收入
    sendInf0(driver, '//*[@id="aac355_3"]', dict["公益性岗位收入"])
    sendInf0(driver, '//*[@id="aac356_3"]', dict["其它工资性收入"])
    sendInf0(driver, '//*[@id="aac072_3"]', dict["财产性收入"])

    clickEle(driver, '//*[@id="on_save"]/span')
    clickEle(driver, '//*[@id="swal2-content"]')
    cw = driver.find_element(By.XPATH, '//*[@id="swal2-content"]')
    cwtext = cw.text
    message.append(dict["户主姓名"])
    message.append(cwtext)
    # if len(cwtext) < 4:
    #     print("bug文本信息" + cwtext)
    if cwtext == "保存成功":
        clickEle(driver, '/html/body/div[70]/div/div[10]/button[1]')
        clickEle(driver, '//*[@id="ui-tabpanel-1"]/div/busi-tab/object-poor-family/p-dialog[1]/div/div[1]/a/span')
        print(dict["户主姓名"] + cwtext)
        message.append(1)
    else:
        try:
            clickEle(driver, '/html/body/div[70]/div/div[10]/button[2]')
            clickEle(driver, '//*[@id="ui-tabpanel-1"]/div/busi-tab/object-poor-family/p-dialog[1]/div/div[1]/a/span')
            message.append(2)
            print(dict["户主姓名"] + "未录入收入,有严重提醒：" + cwtext)
        except:
            clickEle(driver, '/html/body/div[70]/div/div[10]/button[1]')
            clickEle(driver, '//*[@id="ui-tabpanel-1"]/div/busi-tab/object-poor-family/p-dialog[1]/div/div[1]/a/span')
            message.append(3)
            print(dict["户主姓名"] + "可能未录入收入,有提醒：" + cwtext)
    return message


def gettable(address, tableName):
    excel = xlrd.open_workbook(address)
    table = excel.sheet_by_name(tableName)
    return table

def getData(table, row, col):
    return table.cell_value(row - 1, col - 1)



if __name__ == '__main__':
    start_time = time.time()
    cgmessage = []
    cwmessage = []
    txmessage = []
    # （一）获取录入信息表
    table = gettable("D:/pip/脱贫户收入录入.xls", "Sheet1")
    # （二）获取driver
    driver = getDriver(driverLocation="D:/pip/chromedriver.exe")
    try:
        #（三）登录页面
        waitForLogin1(driver)
        # （四）进行信息录入
        num = 0
        for row in range(2, table.nrows + 1):
            # row = 2
            id = getData(table, row, 4)
            dict = getIncomeInfo(table, row)
            flag = 1
            while True:
                try:
                    if flag == 10:
                        break
                    findPerson(id)
                    try:
                        incomeCalculationPage(id)
                    except:
                        messa = []
                        messa.append(dict["户主姓名"])
                        messa.append("用身份证不到这个人，可能被删了")
                        messa.append(2)
                        messa.append(id)
                        cwmessage.append(messa)
                        break
                    mes = setIncomeInformation(driver, dict)
                    if mes[2] == 1:
                        mes.append(id)
                        cgmessage.append(mes)
                    elif mes[2] == 2:
                        mes.append(id)
                        cwmessage.append(mes)
                    elif mes[2] == 3:
                        mes.append(id)
                        txmessage.append(mes)
                    break
                except:
                    refresh1(driver)
                    flag += 1
            if flag == 10:
                print("已经刷新10次了，换个时间录入吧")
                break
            num = num + 1
    except:
        print("异常结束")
    finally:
        driver.quit()
        finish_time = time.time()
        during_time = finish_time - start_time
        # 生成运行结果excel
        workbook = xlwt.Workbook(encoding='utf-8')

        # 生成表1，运行结果
        sheet0 = workbook.add_sheet("运行结果")
        sheet0.write(0, 0, "成功共录入" + str(len(cgmessage)) + "条数据")
        sheet0.write(1, 0, "未录入" + str(len(cwmessage)) + "条数据")
        sheet0.write(2, 0, "提示消息" + str(len(txmessage)) + "条数据，需要根据提示自己判断有没有录入")
        sheet0.write(3, 0, "程序共运行" + str((during_time - during_time % 60) / 60) + "分钟" + str(during_time % 60)[2] + "秒")
        sheet0.write(4, 0, "共尝试录入" + str(num) + "条数据")
        sheet0.write(5, 0, "从第2行开始录入，一直到" + str(1 + num) + "行结束运行")

        # 生成表2，成功录入收入人员
        head = ["姓名", "点击保存后显示的信息", "分类", "身份证号"]
        # 录入成功人员
        sheet1 = workbook.add_sheet("成功录入收入人员")
        for i in head:
            sheet1.write(0, head.index(i), i)
        for i in range(len(cgmessage)):
            for j in range(len(cgmessage[i])):
                sheet1.write(i + 1, j, cgmessage[i][j])

        # 生成表3，录入没有成功人员
        sheet2 = workbook.add_sheet("没有录入收入人员，严重提醒")
        for i in head:
            sheet2.write(0, head.index(i), i)
        for i in range(len(cwmessage)):
            for j in range(len(cwmessage[i])):
                sheet2.write(i + 1, j, cwmessage[i][j])

        # 生成表4，录入提示信息
        sheet3 = workbook.add_sheet("可能没有录入收入人员，普通提醒")
        for i in head:
            sheet3.write(0, head.index(i), i)
        for i in range(len(txmessage)):
            for j in range(len(txmessage[i])):
                sheet3.write(i + 1, j, txmessage[i][j])

        # 保存运行结果文件
        savePath = 'D:/pip/哇，鑫哥强呀.xls'
        workbook.save(savePath)