526互联

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

发布时间 2023-11-19 23:50:50作者: 打盹的闲鱼

不会编程的看这里：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 == 90:
                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 == 90:
        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 == 90:
                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 == 90:
        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 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 == 90:
                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 == 90:
        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 == 90:
                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 == 90:
        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("登录成功")

# 等待元素加载，并点击（通过 XPATH 获取元素）


# def waitEle(driver, XPATH):
#     WebDriverWait(driver, 30, 0.5).until(EC.presence_of_element_located((By.XPATH, XPATH)))

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
    #点击搜索
    flag = 0
    while True:
        try:
            if flag == 10:
                break
            sendInf0(driver, '//*[@id="aab004"]', id)
            clickEle(driver, '//*[@id="on_query"]/span')
            break
        except:
            # print("bug")
            flag = flag + 1
            time.sleep(0.5)
    if flag == 10:
        raise Exception("出问题了,点不了搜索身份证")

    # mark = 0
    # while True:
    # try:
    #     th = driver.find_element(By.XPATH, '//*[@id="ui-tabpanel-4"]/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')
    #     tds = th.find_elements(By.TAG_NAME, 'td')
    #     print(len(tds))
    #     xinxi = tds[0].text.strip()
    #     print(xinxi)
    # except Exception as e:
    #     print(str(e))
    # print(driver.find_element(
    #         '//*[@id="ui-tabpanel-2"]/div/busi-tab/object-poor-family/p-panel[3]/div/div[2]/div/div/object-poor-family-grid/p-datatable/div/nui-paginator/div/div/div[3]/label/span').text.strip())
        # if driver.find_element(
        #         '//*[@id="ui-tabpanel-2"]/div/busi-tab/object-poor-family/p-panel[3]/div/div[2]/div/div/object-poor-family-grid/p-datatable/div/nui-paginator/div/div/div[3]/label/span').text.strip() != "总户数：0　总人数：0":
        #     break
        # if driver.find_element(
        #         '//*[@id="ui-tabpanel-2"]/div/busi-tab/object-poor-family/p-panel[3]/div/div[2]/div/div/object-poor-family-grid/p-datatable/div/nui-paginator/div/div/div[3]/label/span').text.strip() == "总户数：0　总人数：0":
        #     time.sleep(0.5)
        #     mark == mark + 1
        # if mark == 60:
        #     raise Exception("身份证搜不到")


    # flag1 = 0
    # while True:
    # #     try:
    #         if flag1 == 10:
    #             break
def incomeCalculationPage(id):
    # 点击姓名超链接，进入监测户信息界面
    flag = 0
    while True:
        try:
            # print("尝试")
            if flag == 3:
                # print("用身份证搜不到人")
                break
            clickEle(driver,
                 '//*[@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')
            clickEle(driver,
                     '//*[@id="ui-tabpanel-1"]/div/busi-tab/object-poor-family/p-dialog[1]/div/div[2]/p-tabview/div/ul/li[3]/a')
            break
        except:
            time.sleep(1)
            flag = flag + 1
            # print(flag)
    if flag == 3:
        # print("抛出异常")
        raise Exception("找不到人")
            # waitEle(driver, '//*[@id="aac073_3"]')
            # break
        # except:
        #     # print("bug2")
        #     time.sleep(0.5)
        #     flag1 = flag1 + 1
    # if flag1 == 10:
    #     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 getIncomeInfo(table, row):
    dict = {"户主姓名":"", "公益性岗位收入": "", "其它工资性收入": "", "财产性收入": "最低保证金", "特困人员救助供养金": "",
            "养老或离退休金": "", "计划生育金": "","生态补偿金": "", "产业奖励": "", "就业奖励": "",
            "其它转移性收入": "", "生产经营性收入": "", "专项用于减少生产经营支出的补贴": "","生产经营性支出": ""}
    dict["户主姓名"] = getData(table, row, 5)
    dict["公益性岗位收入"] = getData(table, row, 9)
    dict["其它工资性收入"] = getData(table, row, 10)
    dict["财产性收入"] = getData(table, row, 11)
    dict["最低保证金"] = getData(table, row, 13)
    dict["特困人员救助供养金"] = getData(table, row, 14)
    dict["养老或离退休金"] = getData(table, row, 15)
    dict["计划生育金"] = getData(table, row, 16)
    dict["生态补偿金"] = getData(table, row, 17)
    dict["产业奖励"] = getData(table, row, 18)
    dict["就业奖励"] = getData(table, row, 19)
    dict["其它转移性收入"] = getData(table, row, 20)
    dict["生产经营性收入"] = getData(table, row, 21)
    dict["专项用于减少生产经营支出的补贴"] = getData(table, row, 24)
    dict["生产经营性支出"] = getData(table, row, 23)
    return dict

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




#填写监测户得收入情况信息
def setIncomeInformation(driver, dict):
    message = []
    #获取需填入收入
    #1、公益性岗位收入
    sendInf0(driver, '//*[@id="aac355_3"]', dict["公益性岗位收入"])
    #2、其它工资性收入
    sendInf0(driver, '//*[@id="aac356_3"]', dict["其它工资性收入"])
    #3、财产性收入
    sendInf0(driver, '//*[@id="aac072_3"]', dict["财产性收入"])
    #4、最低保证金
    sendInf0(driver, '//*[@id="aac077_3"]', dict["最低保证金"])
    #5、特困人员救助供养金
    sendInf0(driver, '//*[@id="aac086_3"]', dict["特困人员救助供养金"])
    #6、养老或离退休金
    sendInf0(driver, '//*[@id="aac087_3"]', dict["养老或离退休金"])
    #7、计划生育金
    sendInf0(driver, '//*[@id="aac076_3"]', dict["计划生育金"])
    #8、生态补偿金
    sendInf0(driver, '//*[@id="aac078_3"]', dict["生态补偿金"])
    #9、产业奖励
    sendInf0(driver, '//*[@id="aac357_3"]', dict["产业奖励"])
    #10、就业奖励
    sendInf0(driver, '//*[@id="aac358_3"]', dict["就业奖励"])
    #11、其它转移性收入
    sendInf0(driver, '//*[@id="aac083_3"]', dict["其它转移性收入"])
    #12、生产经营性收入
    sendInf0(driver, '//*[@id="aac071_3"]', dict["生产经营性收入"])
    #13、专项用于减少生产经营支出的补贴
    sendInf0(driver, '//*[@id="aac360_3"]', dict["专项用于减少生产经营支出的补贴"])
    #14、生产经营性支出
    sendInf0(driver, '//*[@id="aac359_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')
            if "大于等于0且小数在两位之内" in cwtext:
                message.append(2)
                print(dict["户主姓名"] + "未录入收入,有提醒：" + cwtext)
            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__':
    try:
        start_time = time.time()
        cgmessage = []
        cwmessage = []
        txmessage = []
        #（一）获取录入信息表
        table = gettable("C:/Users/liangxin/Desktop/fx.xls", "总表")
        #（二）获取driver
        driver = getDriver(driverLocation="D:/pip/chromedriver.exe")
        #（三）登录页面
        waitForLogin1(driver)
        # （四）进行信息录入
        # print(table.nrows + 1)
        num = 0
        for row in range(2, table.nrows + 1):
            # row = 2
            id = getData(table, row, 6)
            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
        # print("共录入" + str(num - len(cwmessage)) + "条数据")
        # print("程序共运行" + str((during_time - during_time % 60) / 60) + "分钟" + str(during_time % 60)[2] + "秒")
        # print("=========================成功录入的人员信息============================")
        # for cg in cgmessage:
        #     print(cg[0] + "(" + cg[3] + ")" + ":" + cg[1])
        # print("=========================错误信息====================================")
        # for cw in cwmessage:
        #     print(cw[0] + "(" + cw[3] + ")" + ":" + cw[1])
        # print("==========================提醒信息====================================")
        # for tx in txmessage:
        #     print(tx[0] + "(" + tx[3] + ")" + ":" + tx[1])


        # 生成运行结果excel
        workbook = xlwt.Workbook(encoding='utf-8')
        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(4, 0, "从第2行开始录入，一直到" + str(2 + num) + "行结束运行")



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

        # 录入没有成功人员
        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])

        # 录入提示信息
        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)