将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)
