农经权二轮延包软件—摸底申请表生成

"农村土地经营权第二轮承包到期后再延长三十年的工作，是在第二轮土地承包基础上开展工作。第一步的工作为摸底调查，需要生成《农村土地承包经营权摸底申请表》（简称摸底表）。小编制作了摸底表生成程序，需要的自取。"

1、摸底表基础数据

在生成表之前得有个空白的摸底表模板文件：摸底申请表.docx。这个word文件包含三个空白表格。需要在表1、表2中填入数据，方便开展摸底工作。

接下来就是mdb表格，建议按村出摸底表，当然有些地方是按照小组发包的。可以参照文章：《农经权属性数据mdb分镇分村提取软件》进行拆分。保证mdb数据库里表格：CBDKXX、CBF、CBF_JTCY、CBHT、FBF，数据完整可用。

还有DK.shp文件里的dbf文件也是必不可少的。数据文件最好是汇交格式的文件，防止字段缺失导致无法生成摸底表。

2、软件使用

下载生成摸底表.exe、摸底申请表.docx两个文件，直接打开exe文件（有杀毒软件最好先退出）。

程序包含五个按钮，界面是我用笔记本做的，所以台式电脑会出现显示不全的情况，不影响使用。由于涉及到access数据库，为确保程序运行，请确定是否安装Microsoft Access database engine 2010（该驱动与office版本不冲突，64位系统即可，测试偶发office2019也可以使用该程序）。再强调下Microsoft Access database engine 2010的下载参考文章《【ArcGIS技巧】都数据入库了你的Access(mdb)还没安装好吗？》。

四个输入框分别选择输入文件，前三个为前面提到的模板文件、MDB数据库、DBF数据。第四个为新建文件夹数据。填写完整后就可以点击生成摸底表了。

生成完后会有提示，我们可以查看生成的文件名称为：承包方编码+承包方姓名.docx。打开其中一个文件查看，表1、表2的数据填写完整，方便修改。

3、简单演示与代码分享

下面为生成摸底调查表的演示，已经在笔记本与台式电脑运行过，系统window7以上且是64位系统。

界面代码

# -*- coding: utf-8 -*-# Form implementation generated from reading ui file 'modi.ui'
#
# Created by: PyQt5 UI code generator 5.15.4
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.import sys,os
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import QFileDialog,QMessageBox
from modibiao import Modibiaoclass Ui_Form(object):def setupUi(self, Form):Form.setObjectName("Form")Form.resize(518, 227)self.pushButton = QtWidgets.QPushButton(Form)self.pushButton.setGeometry(QtCore.QRect(10, 10, 91, 31))self.pushButton.setObjectName("pushButton")self.lineEdit = QtWidgets.QLineEdit(Form)self.lineEdit.setGeometry(QtCore.QRect(110, 10, 401, 31))self.lineEdit.setObjectName("lineEdit")self.pushButton_2 = QtWidgets.QPushButton(Form)self.pushButton_2.setGeometry(QtCore.QRect(10, 50, 91, 31))self.pushButton_2.setObjectName("pushButton_2")self.lineEdit_2 = QtWidgets.QLineEdit(Form)self.lineEdit_2.setGeometry(QtCore.QRect(110, 50, 401, 31))self.lineEdit_2.setObjectName("lineEdit_2")self.pushButton_3 = QtWidgets.QPushButton(Form)self.pushButton_3.setGeometry(QtCore.QRect(10, 90, 91, 31))self.pushButton_3.setObjectName("pushButton_3")self.lineEdit_3 = QtWidgets.QLineEdit(Form)self.lineEdit_3.setGeometry(QtCore.QRect(110, 90, 401, 31))self.lineEdit_3.setObjectName("lineEdit_3")self.pushButton_4 = QtWidgets.QPushButton(Form)self.pushButton_4.setGeometry(QtCore.QRect(10, 130, 91, 31))self.pushButton_4.setObjectName("pushButton_4")self.lineEdit_4 = QtWidgets.QLineEdit(Form)self.lineEdit_4.setGeometry(QtCore.QRect(110, 130, 401, 31))self.lineEdit_4.setObjectName("lineEdit_4")self.pushButton_5 = QtWidgets.QPushButton(Form)self.pushButton_5.setGeometry(QtCore.QRect(410, 180, 101, 31))self.pushButton_5.setObjectName("pushButton_5")self.label = QtWidgets.QLabel(Form)self.label.setGeometry(QtCore.QRect(20, 180, 291, 31))self.label.setObjectName("label")self.retranslateUi(Form)QtCore.QMetaObject.connectSlotsByName(Form)def retranslateUi(self, Form):_translate = QtCore.QCoreApplication.translateForm.setWindowTitle(_translate("Form", "生成调查摸底表"))self.pushButton.setText(_translate("Form", "打开摸底表模板"))self.pushButton_2.setText(_translate("Form", "打开MDB文件"))self.pushButton_3.setText(_translate("Form", "打开DBF文件"))self.pushButton_4.setText(_translate("Form", "WORD存放路径"))self.pushButton_5.setText(_translate("Form", "生成摸底表"))self.label.setText(_translate("Form", "联系方式：关注“玩大数据的规划师”，获取软件。"))class ModiTable(QtWidgets.QWidget):def __init__(self):super().__init__()self.ui = Ui_Form()self.ui.setupUi(self)# 连接按钮点击信号self.ui.pushButton.clicked.connect(self.open_source_docx)self.ui.pushButton_2.clicked.connect(self.open_source_mdb)self.ui.pushButton_3.clicked.connect(self.open_source_dbf)self.ui.pushButton_4.clicked.connect(self.open_directory)# 绑定开始按钮事件self.ui.pushButton_5.clicked.connect(self.start_modi)def open_source_docx(self):"""打开源mdb文件"""file_path, _ = QFileDialog.getOpenFileName(self,"选择源模板文件","","word文件 (*.docx);;所有文件 (*)")if file_path:self.ui.lineEdit.setText(file_path)def open_source_mdb(self):"""打开源mdb文件"""file_path, _ = QFileDialog.getOpenFileName(self,"选择源数据库文件","","Access数据库文件 (*.mdb);;所有文件 (*)")if file_path:self.ui.lineEdit_2.setText(file_path)def open_source_dbf(self):"""打开源mdb文件"""file_path, _ = QFileDialog.getOpenFileName(self,"选择源地块数据","","DBF文件 (*.dbf);;所有文件 (*)")if file_path:self.ui.lineEdit_3.setText(file_path)def open_directory(self):# 打开文件夹选择对话框，并获取选中的目录路径directory = QFileDialog.getExistingDirectory(self, "选择文件夹", "/home")  # 可以修改初始目录为需要的路径if directory:  # 如果用户选择了文件夹self.ui.lineEdit_4.setText(directory)def validate_inputs(self):"""校验四个输入框的非空性"""inputs = [(self.ui.lineEdit, "源模板文件"),(self.ui.lineEdit_2, "源数据库文件"),(self.ui.lineEdit_3, "源地块数据路径"),(self.ui.lineEdit_4, "目标文件夹")]for line_edit, field_name in inputs:text = line_edit.text().strip()  # 去除首尾空格if not text:self.highlight_empty_field(line_edit)QMessageBox.warning(self, "输入错误",f"{field_name}不能为空！")return Falsereturn Truedef highlight_empty_field(self, widget):"""高亮空输入框"""widget.setStyleSheet("background-color: #FFE4E1;")QtCore.QTimer.singleShot(1000, lambda: widget.setStyleSheet(""))def start_modi(self):"""开始提取的主逻辑"""if not self.validate_inputs():return# 获取有效参数source_word = self.ui.lineEdit.text().strip()source_mdb = self.ui.lineEdit_2.text().strip()source_dbf = self.ui.lineEdit_3.text().strip()target_wjj = self.ui.lineEdit_4.text().strip()try:modib=Modibiao(source_word,source_mdb,source_dbf,target_wjj)modib.ModiTable()# TODO 添加实际的数据库操作代码QMessageBox.information(self, "完成", "摸底表生成成功！")except Exception as e:QMessageBox.critical(self, "错误", f"生成失败：{str(e)}")if __name__ == "__main__":app = QtWidgets.QApplication(sys.argv)mdtable =ModiTable()mdtable.show()sys.exit(app.exec_())

逻辑代码

# -*- coding: utf-8 -*-
from docx import Document
import pyodbc
import pandas as pd
from dbfread import DBF
import win32com.client as win32dict_gxdm={'01': '本人', '02': '户主', '10': '配偶', '11': '夫', '12': '妻', '20': '子', '21': '独生子', '22': '长子', '23': '次子', '24': '三子', '25': '四子', '26': '五子', '27': '养子或继子', '28': '女婿', '29': '其他儿子', '30': '女', '31': '独生女', '32': '长女', '33': '二女', '34': '三女', '35': '四女', '36': '五女', '37': '养女', '38': '儿媳', '39': '其他女儿', '40': '孙子、孙女或外孙子、外孙女', '41': '孙子', '42': '孙女', '43': '外孙子', '44': '外孙女', '45': '孙媳妇或外孙媳妇', '46': '孙女婿或外孙女婿', '47': '曾孙子或曾外孙子', '48': '曾孙女或曾外孙女', '49': '其他孙子或外孙子', '50': '父母', '51': '父亲', '52': '母亲', '53': '公公', '54': '婆婆', '55': '岳父', '56': '岳母', '57': '养母或继父', '58': '继父或养母', '59': '其他父母关系', '60': '祖母或外祖母', '61': '祖父', '62': '祖母', '63': '外祖父', '64': '外祖母', '65': '配偶的祖父母或外祖父母', '66': '曾祖父', '67': '曾祖母', '68': '配偶的曾祖父母', '69': '其他祖父母或外祖父母关系', '70': '兄弟姐妹', '71': '兄', '72': '嫂', '73': '弟', '74': '弟媳', '75': '姐姐', '76': '姐夫', '77': '妹妹', '78': '妹夫', '79': '其他兄弟姐妹', '80': '其他', '81': '伯父', '82': '伯母', '83': '叔父', '84': '婶母', '85': '舅父', '86': '舅母', '87': '姨父', '88': '姨母', '89': '姑父', '90': '姑母', '91': '堂兄弟、堂姐妹', '92': '表兄妹、表姐妹', '93': '侄子', '94': '侄女', '95': '外甥', '96': '外甥女', '97': '其他亲属', '99': '非亲属'}
dict_xb={'1':'男','2':'女'}
dict_dklb={'10':'承包地块','21':'自留地','22':'机动地','23':'开荒地','99':'其他集体土地'}
dict_qdfs={'100':'承包','110':'家庭承包','120':'其他方式承包','121':'招标','122':'拍卖','123':'公开协商','129':'其他方式','200':'转让','300':'互换','900':'其他方式'}
dict_tdyt={'1':'种植业','2':'林业','3':'畜牧业','4':'渔业','5':'非农业用途'}
dict_sfjbnt={'1':'是','2':'否'}class Modibiao:def __init__(self,source_word,source_mdb,source_dbf,target_wjj):self.source_word=source_wordself.source_mdb=source_mdbself.source_dbf=source_dbfself.target_wjj=target_wjjdef ModiTable(self):# 读取MDB中FBF、CBF、CBDKXX、CBHT、CBF_JTCY五张表conn_str = (r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+self.source_mdb+r";")conn = pyodbc.connect(conn_str)df1 = pd.read_sql("SELECT * FROM FBF", conn)  # 读取表FBF(表1）t1_fbfdm = df1['FBFBM'].values.tolist()  # 发包方代码t1_fbfmc = df1['FBFMC'].values.tolist()  # 发包方全称t1_fbffzrxm = df1['FBFFZRXM'].values.tolist()  # 发包方负责人# 建立FBFBM与FBFMC映射表dict_fbfbmtofbfmc = dict(zip(t1_fbfdm, t1_fbfmc))# 建立FBFBM与FBFFZRXM映射表dict_fbfbmtofbffzrxm = dict(zip(t1_fbfdm, t1_fbffzrxm))df2 = pd.read_sql("SELECT * FROM CBF", conn)  # 读取表CBF(表2）sortd_df2 = df2.sort_values(by='CBFBM')  # 对表格2以CBFBM进行升序排列t2_cbfbm = sortd_df2['CBFBM'].values.tolist()  # 承包方编码t2_cbfmc = sortd_df2['CBFMC'].values.tolist()  # 承包方名称t2_zjh = sortd_df2['CBFZJHM'].values.tolist()  # 身份证号码t2_cbfdz = sortd_df2['CBFDZ'].values.tolist()  # 承包方地址t2_lxdh = sortd_df2['LXDH'].values.tolist()  # 联系电话# 建立CBFBM与CBFMC映射表dict_cbfbmtocbfmc = dict(zip(t2_cbfbm, t2_cbfmc))# 建立CBFBM与CBFZJHM映射表dict_cbfbmtocbfzjhm = dict(zip(t2_cbfbm, t2_zjh))# 建立CBFBM与CBFDZ映射表dict_cbfbmtocbfdz = dict(zip(t2_cbfbm, t2_cbfdz))# 建立CBFBM与LXDH映射表dict_cbfbmtolxdh = dict(zip(t2_cbfbm, t2_lxdh))df3 = pd.read_sql("SELECT * FROM CBHT", conn)  # 读取表CBHT(表3）sortd_df3 = df3.sort_values(by='CBFBM')  # 对表格以CBFBM进行升序排列t3_cbfbm = sortd_df2['CBFBM'].values.tolist()  # 承包方编码t3_htdm = sortd_df3['CBHTBM'].values.tolist()  # 合同代码t3_cbfs = sortd_df3['CBFS'].values.tolist()  # 承包方式# 转换日期字段# sortd_df3['CBQXQ']=pd.to_datetime(sortd_df3['CBQXQ'], format='%Y-%m-%d')sortd_df3['dateq_str'] = sortd_df3['CBQXQ'].dt.strftime('%Y%m%d')t3_cbqxq = sortd_df3['dateq_str'].values.tolist()  # 承包期限起始# t3_cbqxq=pd.to_datetime(sortd_df3['CBQXQ'], format='%Y%m%d').values.tolist()#承包期限起始sortd_df3['datez_str'] = sortd_df3['CBQXZ'].dt.strftime('%Y%m%d')t3_cbqxz = sortd_df3['datez_str'].values.tolist()  # 承包期限终止t3_htzmj = sortd_df3['HTZMJM'].values.tolist()  # 合同总面积t3_dkzs = sortd_df3['CBDKZS'].values.tolist()  # 地块总数t3_fbfbm = sortd_df3['FBFBM'].values.tolist()  # 发包方编码# 建立CBFBM与FBFBM映射表dict_cbfbmtofbfbm = dict(zip(t3_cbfbm, t3_fbfbm))# 建立CBFBM与CBHTBM映射表dict_cbfbmtohtdm = dict(zip(t3_cbfbm, t3_htdm))# 建立CBFBM与CBFS映射表dict_cbfbmtocbfs = dict(zip(t3_cbfbm, t3_cbfs))# 建立CBFBM与CBQXQ映射表dict_cbfbmtocbqxq = dict(zip(t3_cbfbm, t3_cbqxq))# 建立CBFBM与CBQXZ映射表dict_cbfbmtocbqxz = dict(zip(t3_cbfbm, t3_cbqxz))# 建立CBFBM与HTZMJ映射表dict_cbfbmtohtzmj = dict(zip(t3_cbfbm, t3_htzmj))# 建立CBFBM与CBDKZS映射表dict_cbfbmtodkzs = dict(zip(t3_cbfbm, t3_dkzs))# 以承包方编码为键，家庭成员情况为值建立字典，值为列表dict_jtcy = {}df4 = pd.read_sql("SELECT * FROM CBF_JTCY", conn)  # 先读取表CBF_JTCY(表4）sortd_df4 = df4.sort_values(by=['CBFBM', 'YHZGX'], ascending=True)  # 对表格以CBFBM进行升序排列for i in t2_cbfbm:hq_jtcy = sortd_df4[(sortd_df4.CBFBM == i)]t4_cbfbm = hq_jtcy['CBFBM'].values.tolist()  # 承包方编码t4_name = hq_jtcy['CYXM'].values.tolist()  # 成员姓名t4_xb = hq_jtcy['CYXB'].values.tolist()  # 成员性别t4_yhzgx = hq_jtcy['YHZGX'].values.tolist()  # 与户主关系t4_zjh = hq_jtcy['CYZJHM'].values.tolist()  # 身份证号码t4_cybz = hq_jtcy['CYBZ'].values.tolist()  # 备注jtcy_list = []for j in range(len(t4_cbfbm)):jtcy_list.append([t4_name[j], t4_xb[j], t4_yhzgx[j], t4_zjh[j], ''])dict_jtcy[i] = jtcy_listdf5 = pd.read_sql("SELECT * FROM CBDKXX", conn)  # 读取表CBDKXX(表5）sortd_df5 = df5.sort_values(by=['CBFBM', 'DKBM'], ascending=True)  # 对表格以DKBM进行升序排列t5_htmj = sortd_df5['HTMJM'].values.tolist()  # 确权（合同）面积亩t5_dkbm = sortd_df5['DKBM'].values.tolist()  # 地块编码t5_cbfbm = sortd_df5['CBFBM'].values.tolist()  # 承包方编码# 建立DKBM与CBFBM映射表dict_dkbmtocbfbm = dict(zip(t5_dkbm, t5_cbfbm))# 建立DKBM与HTMJM映射表dict_dkbmtohtmj = dict(zip(t5_dkbm, t5_htmj))conn.close()# 读取DBF中DK.dbf一张表table = DBF(self.source_dbf, encoding='utf-8')table_list = [record for record in table]df6 = pd.DataFrame(table_list)  # 读取表DK(表6）df6_filtered = df6[df6['DKBM'].isin(t5_dkbm)]  # 过滤掉在CBDKXX中没有承包方编码的地块# 先给DataFrame添加一列CBFBMdkbm_fz = df6_filtered['DKBM'].values.tolist()  # 辅助的地块编码add_cbfbm = list(dict_dkbmtocbfbm[i] for i in dkbm_fz)  # 增加的承包方编码列表df6_filtered.insert(len(df6_filtered.columns), 'CBFBM', add_cbfbm)  # 在末尾添加上一列sortd_df6 = df6_filtered.sort_values(by=['CBFBM', 'DKBM'], ascending=True)  # 对表格以DKBM进行升序排列# 给地块添加一个承包方地块列表字典dict_dk = {}for i in t2_cbfbm:hq_dk = sortd_df6[(sortd_df6.CBFBM == i)]t6_dkmc = hq_dk['DKMC'].values.tolist()  # 地块名称t6_dkbm = hq_dk['DKBM'].values.tolist()  # 地块编码t6_dkdz = hq_dk['DKDZ'].values.tolist()  # 东至t6_dkxz = hq_dk['DKXZ'].values.tolist()  # 西至t6_dknz = hq_dk['DKNZ'].values.tolist()  # 南至t6_dkbz = hq_dk['DKBZ'].values.tolist()  # 北至t6_dldj = hq_dk['DLDJ'].values.tolist()  # 质量等级t6_tdyt = hq_dk['TDYT'].values.tolist()  # 土地用途t6_tdlylx = hq_dk['TDLYLX'].values.tolist()  # 地类t6_sfjbnt = hq_dk['SFJBNT'].values.tolist()  # 是否基本农田t6_scmjm = hq_dk['SCMJM'].values.tolist()  # 实测面积t6_cbfbm = hq_dk['CBFBM'].values.tolist()  # 承包方编码dk_list = []for j in range(len(t6_cbfbm)):dk_list.append([t6_dkbm[j], t6_dkmc[j], t6_dkdz[j], t6_dknz[j], t6_dkxz[j], t6_dkbz[j], t6_scmjm[j],dict_dkbmtohtmj[t6_dkbm[j]], t6_tdyt[j], t6_dldj[j], t6_tdlylx[j], t6_sfjbnt[j]])dict_dk[i] = dk_listfor i in t2_cbfbm:try:# 打开Word文档doc = Document(self.source_word)table1 = doc.tables[0]  # 定位第一个表格# 向指定单元格填充数据（行索引、列索引均从0开始）table1.cell(2, 2).text = dict_cbfbmtofbfbm[i]  # 第3行第3列table1.cell(3, 2).text = dict_fbfbmtofbfmc[dict_cbfbmtofbfbm[i]]  # 第4行第3列table1.cell(4, 2).text = dict_fbfbmtofbffzrxm[dict_cbfbmtofbfbm[i]]  # 第5行第3列table1.cell(5, 2).text = i  # 第6行第3列table1.cell(6, 2).text = dict_cbfbmtocbfmc[i]  # 第7行第3列table1.cell(7, 2).text = dict_cbfbmtocbfzjhm[i]  # 第8行第3列table1.cell(8, 2).text = dict_cbfbmtocbfdz[i]  # 第9行第3列try:table1.cell(9, 2).text = dict_cbfbmtolxdh[i]  # 第10行第3列except:table1.cell(9, 2).text = ''table1.cell(10, 2).text = dict_qdfs[dict_cbfbmtocbfs[i]]  # 第11行第3列table1.cell(11, 2).text = dict_cbfbmtohtdm[i]  # 第12行第3列table1.cell(12, 2).text = dict_cbfbmtocbqxq[i] + r'-' + dict_cbfbmtocbqxz[i]  # 第13行第3列table1.cell(13, 2).text = str(dict_cbfbmtohtzmj[i])  # 第14行第3列table1.cell(14, 2).text = str(dict_cbfbmtodkzs[i])  # 第15行第3列outputname = i + dict_cbfbmtocbfmc[i] + r'.docx'# 保存修改后的文档doc.save(self.target_wjj + '\\' + outputname)# 删除文档对象的引用del docdef safe_insert_rows(doc_path, outputfile, table_index, num_rows):word = win32.Dispatch("Word.Application")doc = word.Documents.Open(doc_path)tables = doc.Tablestarget_table = tables[table_index]# 插入多行并复制格式last_row = target_table.Rows.Lastfor _ in range(num_rows):new_row = target_table.Rows.Add(last_row)  # 仅插入 1 行last_row = new_rowdoc.SaveAs(outputfile)doc.Close()word.Quit()jtcy = dict_jtcy[i]len_jtcy = len(jtcy)if len_jtcy > 5:inputfile = self.target_wjj + '\\' + outputnameoutputfile = self.target_wjj + '\\' + outputnamesafe_insert_rows(inputfile, outputfile, 0, len_jtcy - 5)dk = dict_dk[i]len_dk = len(dk)if len_dk > 11:inputfile = self.target_wjj + '\\' + outputnameoutputfile = self.target_wjj + '\\' + outputnamesafe_insert_rows(inputfile, outputfile, 1, len_dk - 11)doc2 = Document(self.target_wjj + '\\' + outputname)table2_1 = doc2.tables[0]  # 定位第一个表格for i in range(len_jtcy):value_cells = jtcy[i]table2_1.cell(17 + i, 0).text = value_cells[0]table2_1.cell(17 + i, 2).text = dict_xb[value_cells[1]]table2_1.cell(17 + i, 3).text = dict_gxdm[value_cells[2]]table2_1.cell(17 + i, 4).text = value_cells[3]# table2_1.cell(17 + i, 4).text = value_cells[4]table2_2 = doc2.tables[1]  # 定位第二个表格for i in range(len_dk):value_cells = dk[i]table2_2.cell(2 + i, 0).text = value_cells[0]table2_2.cell(2 + i, 1).text = value_cells[1]table2_2.cell(2 + i, 2).text = value_cells[2]table2_2.cell(2 + i, 3).text = value_cells[3]table2_2.cell(2 + i, 4).text = value_cells[4]table2_2.cell(2 + i, 5).text = value_cells[5]table2_2.cell(2 + i, 6).text = str(value_cells[6])table2_2.cell(2 + i, 7).text = str(value_cells[7])table2_2.cell(2 + i, 8).text = dict_tdyt[value_cells[8]]table2_2.cell(2 + i, 9).text = value_cells[9]table2_2.cell(2 + i, 10).text = value_cells[10]table2_2.cell(2 + i, 11).text = dict_sfjbnt[value_cells[11]]doc2.save(self.target_wjj + '\\' + outputname)del doc2except:pass