1000字范文 > 和度耕地资源质量分类更新数据库快速汇总成果excel表

和度耕地资源质量分类更新数据库快速汇总成果excel表

时间：2020-03-15 10:32:37

步骤一：首先需将数据库要素表全部转为excel表，可使用本人制作的工具箱：数据库要素表批量转excel

注意在10.8版本新建工程文件中打开工具箱

步骤二：制作模板，使用最终成果excel表格制作模板，所有数据留空，实际行政代码自行填写

步骤三：制作行政区代码模板excel，内容放在表单3，“Sheet3”

步骤四：在python3.9环境下运行以下代码，本人使用pycharm运行，缺少的python包请自行安装，步骤一到三的实际路径请在代码中对应提示行修改

（一）针对耕地分类汇总表，新增耕地、减少耕地汇总表（修改195、206、214、215行内容）

# -*- coding:utf-8 -*-# ---------------------------------------------------------------------------# Author: LGZ# Created on: /12# Reference:# ---------------------------------------------------------------------------import os, xlrd, xlwtimport matplotlib.pyplot as pltimport copyimport traceback,sysimport pandas as pdimport openpyxlimport logging# logging.disable(logging.critical)logging.basicConfig(level=logging.DEBUG, format="%(asctime)s-%(levelname)s-%(message)s")logging.debug("start of program")def readtab(filename,sheet_name):"""points_genarator(txt_file) return listtxt_file：文本文件地址将txt转换为可以使用的点集列表"""try:data = xlrd.open_workbook(filename)table = data.sheet_by_name(sheet_name)nrow = table.nrows # 获取最大行数logging.debug(nrow)# name_list = [str(table.cell_value(i, 3)) for i in range(1, nrows)]ZD = table.row_values(0, start_colx=0, end_colx=None) # 获取字段名logging.debug(ZD)res = {}field_names = {}FLfield_namesdict = {}for n0, ZDM in enumerate(ZD, 0):logging.debug(n0)logging.debug(ZDM)for nn0 in JBfield_names:if str(nn0) == str(ZDM):field_names[nn0] = n0for nn1 in FLfield_names:if str(nn1) == str(ZDM):FLfield_namesdict[nn1] = n0print(field_names)print(FLfield_namesdict)for n1 in range(1, nrow):vle = table.row_values(n1, start_colx=0, end_colx=None)# logging.debug(vle)m1 = 0# 可使用字典get()方法进行改良if str(vle[FLfield_namesdict["ZLDWDM"]][:9]) not in res.keys():for jbm in field_names.items():if m1 == 0:res.update({str(vle[FLfield_namesdict["ZLDWDM"]][:9]): {str(jbm[0]) + str(vle[jbm[1]]): float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)}})m1 += 1else:res[str(vle[FLfield_namesdict["ZLDWDM"]][:9])].update({str(jbm[0]) + str(vle[jbm[1]]): float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)})else:for jbm in field_names.items():if str(jbm[0]) + str(vle[jbm[1]]) not in res[str(vle[FLfield_namesdict["ZLDWDM"]][:9])].keys():res[str(vle[FLfield_namesdict["ZLDWDM"]][:9])].update({str(jbm[0]) + str(vle[jbm[1]]): float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)})else:res[str(vle[FLfield_namesdict["ZLDWDM"]][:9])][str(jbm[0]) + str(vle[jbm[1]])] += float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)# logging.debug("res")# logging.debug(res)m2 = 0for n2 in res.keys():if m2 == 0:res2 = copy.deepcopy(res[n2])m2 += 1# logging.debug(res2)else:for n3 in res[n2].keys():if n3 not in res2.keys():res2.update({n3: res[n2][n3]})else:res2[n3] += float(res[n2][n3])res3 = {"10": "1", "2a": "2", "2b": "3", "3a": "4", "3b": "5", "j": "1", "g": "2"}for ph1 in list(res3.keys()):for ph2 in list(res2.keys()):# print(ph1,ph2)if ph1 in ph2:if ph1 == "j" or ph1 == "g":res2[ph2[:-1] + res3[ph1]] = res2.pop(ph2)else:res2[ph2[:-2] + res3[ph1]] = res2.pop(ph2)for ph1 in list(res3.keys()):for ph2 in list(res.keys()):# print(field_names2[ph2].keys())for ph3 in list(res[ph2].keys()):# print(ph1,ph2)if ph1 in ph3:# res[ph2][ph3[:-2] + res3[ph1]] = res[ph2].pop(ph3)if ph1 == "j" or ph1 == "g":res[ph2][ph3[:-1] + res3[ph1]] = res[ph2].pop(ph3)else:res[ph2][ph3[:-2] + res3[ph1]] = res[ph2].pop(ph3)return res, res2except Exception:# exc_type, exc_value, exc_traceback = sys.exc_info()print(traceback.format_exc())def wttab1(shtn,path1, path2, res, res2):wb = openpyxl.load_workbook(path1)ws = wb[shtn]ws["D" + str(5)].value = round(sum(res2.values()) / 9, 6)ws["C" + str(5)].value = round(sum(res2.values()) / 9, 6)field_names2 = {u'PDJB': [6,5], u'TCHDJB': [11,3], u'TRZDJB': [14,3], u'TRYJZHLJB': [17,3],u'TRPHZJB': [20,5], u'SWDYXJB': [25,3], u'TRZJSWRJB': [28,3], u'SZJB': [31,3], u'GDEJDLJB': [34,ZL[fileshtn][1]]}for jb in field_names2.keys():n = field_names2[jb][0]m = field_names2[jb][1]k = n+mk1 = n-1while n <= k-1:if str(jb + str(n - k1)) in res2.keys():ws["D" + str(n)].value = round(res2[jb + str(n - k1)], 6)ws["C" + str(n)].value = round(res2[jb + str(n - k1)], 6)else:ws["D" + str(n)].value = 0ws["C" + str(n)].value = 0n += 1wb.save(path1)wb = openpyxl.load_workbook(path2)ws = wb[shtn]field_names2 = {u'PDJB': [4,5], u'TCHDJB': [9,3], u'TRZDJB': [12,3], u'TRYJZHLJB': [15,3],u'TRPHZJB': [18,5], u'SWDYXJB': [23,3], u'TRZJSWRJB': [26,3], u'SZJB': [29,3], u'GDEJDLJB': [32,3]}# field_names3 = ZL[fileshtn][2]for jb in field_names2.keys():n = field_names2[jb][0]# m = field_names2[jb][1]# k = n+m# k1 = n-1for n4 in range(1,6):if n+n4>ZL[fileshtn][2]:break# 汇总部分开始if str(jb + str(n4)) in res2.keys():ws.cell(row=5, column=n+n4-1).value = round(res2[jb + str(n4)], 6)else:ws.cell(row=5, column=n+n4-1).value = 0# 汇总部分结束m = 6for n5 in sorted(res.keys()):ws.cell(row=m, column=1).value = n5ws.cell(row=m, column=2).value = ZMC[n5]if str(jb + str(n4)) in res[n5].keys():ws.cell(row=m, column=n + n4-1).value = round(res[n5][jb + str(n4)], 6)else:ws.cell(row=m, column=n + n4-1).value = 0m +=1for n6 in range(5, len(list(res.keys()))+5+1):ws["C" + str(n6)].value = ws["D" + str(n6)].value + ws["E" + str(n6)].value + ws["F" + str(n6)].value+ ws["G" + str(n6)].value+ ws["H" + str(n6)].valuewb.save(path2)if __name__ == '__main__':"""脚本单独使用""""""----------------------------------------------""""""---------------------PARA---------------------"""QY = "445103潮安区" # 根据实际行政区修改ZL = {u'FLDY': ["GD",3,35,"\\"+ QY +"耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"耕地资源质量分类面积汇总表-分行政区.xlsx"],u'KCFLDY': ["HF",2,34,"\\"+ QY +"耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"耕地资源质量分类面积汇总表-分行政区.xlsx"],u'JSGD': ["GD", 3, 35,"\\"+ QY +"减少耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"减少耕地资源质量分类面积汇总表-分行政区.xlsx"], u'JSHFDL': ["HF", 2, 34,"\\"+ QY +"减少耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"减少耕地资源质量分类面积汇总表-分行政区.xlsx"],u'XZGD': ["GD", 3, 35,"\\"+ QY +"新增耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"新增耕地资源质量分类面积汇总表-分行政区.xlsx"], u'XZHFDL': ["HF", 2, 34,"\\"+ QY +"新增耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"新增耕地资源质量分类面积汇总表-分行政区.xlsx"]}JBfield_names = [u'PDJB', u'TCHDJB', u'TRZDJB', u'TRYJZHLJB',u'TRPHZJB', u'SWDYXJB', u'TRZJSWRJB', u'SZJB', u'GDEJDLJB',]FLfield_names = [u'ZLDWDM', u'TBDLMJ']ZMC = {}wb = openpyxl.load_workbook(r"E:\汕头市、潮州市行政区划（三调）.xlsx") # 根据实际修改，步骤三生成的excel行政代码表路径ws = wb["Sheet3"]for i in range(2, ws.max_row + 1):ZMC[str(ws["A" + str(i)].value)] = ws["B" + str(i)].valueprint(ZMC)print(len(list(ZMC.keys())))for ii in list(ZL.keys()):filename = "E:\\饶平县\\耕地质量分类\\更新\\潮安\\445103潮安区(度)\\导出数据库表\\"+ ii +".xls" # 根据实际修改，步骤一生成的数据库转excel表的所在路径path = r"E:\饶平县\耕地质量分类\更新\潮安\445103潮安区(度)\数据成果程序表" # 根据实际修改，步骤二生成的成果excel模板表路径，运行代码完成后直接修改模板生成最终成果表fileshtn = ii# fileshtn = str(os.path.splitext(os.path.basename(filename))[0])path1 = path + ZL[fileshtn][3]path2 = path + ZL[fileshtn][4]"""----------------------------------------------""""""----------------------------------------------"""dict1, dict2 = readtab(filename, fileshtn)print(dict1)print(dict2)wttab1(ZL[fileshtn][0], path1, path2, dict1, dict2)print("success")

（二）针对二级地类汇总表（修改235、249、258、259行内容）

# -*- coding:utf-8 -*-# ---------------------------------------------------------------------------# Author: LGZ# Created on: /09# Reference:# ---------------------------------------------------------------------------import os, xlrd, xlwtimport matplotlib.pyplot as pltimport copyimport traceback,sysimport pandas as pdimport openpyxlimport logging# logging.disable(logging.critical)logging.basicConfig(level=logging.DEBUG, format="%(asctime)s-%(levelname)s-%(message)s")logging.debug("start of program")def readtab(filename,sheet_name):"""points_genarator(txt_file) return listtxt_file：文本文件地址将txt转换为可以使用的点集列表"""try:data = xlrd.open_workbook(filename)table = data.sheet_by_name(sheet_name)nrow = table.nrows # 获取最大行数logging.debug(nrow)# name_list = [str(table.cell_value(i, 3)) for i in range(1, nrows)]ZD = table.row_values(0, start_colx=0, end_colx=None) # 获取字段名logging.debug(ZD)res = {}field_names = {}FLfield_namesdict = {}for n0, ZDM in enumerate(ZD, 0):logging.debug(n0)logging.debug(ZDM)for nn0 in JBfield_names:if str(nn0) == str(ZDM):field_names[nn0] = n0for nn1 in FLfield_names:if str(nn1) == str(ZDM):FLfield_namesdict[nn1] = n0print(field_names)print(FLfield_namesdict)for n1 in range(1, nrow):vle = table.row_values(n1, start_colx=0, end_colx=None)logging.debug(vle)m1 = 0# 可使用字典get()方法进行改良BHQGDJBcoln = FLfield_namesdict["BHQGDEJDLJB"]GDJBcoln = FLfield_namesdict["GDEJDLJB"]if str(vle[BHQGDJBcoln] + "B" + vle[GDJBcoln]) not in res.keys():for jbm in field_names.items():if m1 == 0:res.update({str(vle[BHQGDJBcoln] + "B" + vle[GDJBcoln]): {str(jbm[0]) + str(vle[jbm[1]]): float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)}})m1 += 1else:res[str(vle[BHQGDJBcoln] + "B" + vle[GDJBcoln])].update({str(jbm[0]) + str(vle[jbm[1]]): float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)})else:for jbm in field_names.items():if str(jbm[0]) + str(vle[jbm[1]]) not in res[vle[BHQGDJBcoln] + "B" + vle[GDJBcoln]].keys():res[str(vle[BHQGDJBcoln] + "B" + vle[GDJBcoln])].update({str(jbm[0]) + str(vle[jbm[1]]): float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)})else:res[str(vle[BHQGDJBcoln] + "B" + vle[GDJBcoln])][str(jbm[0]) + str(vle[jbm[1]])] += float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)logging.debug("res")logging.debug(res)m2 = 0for n2 in res.keys():if m2 == 0:res2 = copy.deepcopy(res[n2])m2 += 1# logging.debug(res2)else:for n3 in res[n2].keys():if n3 not in res2.keys():res2.update({n3: res[n2][n3]})else:res2[n3] += float(res[n2][n3])res3 = {"10": "1", "2a": "2", "2b": "3", "3a": "4", "3b": "5", "j": "1", "g": "2"}for ph1 in list(res3.keys()):for ph2 in list(res2.keys()):# print(ph1,ph2)if ph1 in ph2:if ph1 == "j" or ph1 == "g":res2[ph2[:-1] + res3[ph1]] = res2.pop(ph2)else:res2[ph2[:-2] + res3[ph1]] = res2.pop(ph2)for ph1 in list(res3.keys()):for ph2 in list(res.keys()):# print(field_names2[ph2].keys())for ph3 in list(res[ph2].keys()):# print(ph1,ph2)if ph1 in ph3:# res[ph2][ph3[:-2] + res3[ph1]] = res[ph2].pop(ph3)if ph1 == "j" or ph1 == "g":res[ph2][ph3[:-1] + res3[ph1]] = res[ph2].pop(ph3)else:res[ph2][ph3[:-2] + res3[ph1]] = res[ph2].pop(ph3)return res, res2except Exception:# exc_type, exc_value, exc_traceback = sys.exc_info()print(traceback.format_exc())def wttab1(shtn,path1, path2, res, res2):wb = openpyxl.load_workbook(path1)ws = wb[shtn]ws["D" + str(5)].value = round(sum(res2.values()) / 2 / 9, 6)ws["E" + str(5)].value = round(sum(res2.values()) / 2 / 9, 6)field_names2 = {u'PDJB': [6,5], u'TCHDJB': [21,3], u'TRZDJB': [30,3], u'TRYJZHLJB': [39,3],u'TRPHZJB': [48,5], u'SWDYXJB': [63,3], u'TRZJSWRJB': [72,3], u'SZJB': [81,3], u'GDEJDLJB': [90,ZL[fileshtn][1]]}for jb in field_names2.keys():n = field_names2[jb][0]m = field_names2[jb][1]k = n+mk1 = n-1while n <= k-1:if str("BHQ" + jb + str(n -k1)) in res2.keys():ws["D" + str(n)].value = round(res2["BHQ" + jb + str(n -k1)], 6)ws["E" + str(n)].value = round(res2["BHQ" + jb + str(n -k1)], 6)else:ws["D" + str(n)].value = 0ws["E" + str(n)].value = 0n +=1while n <= k-1+ m:if str(jb + str(n - k1 -m)) in res2.keys():ws["D" + str(n)].value = round(res2[jb + str(n - k1 -m)], 6)ws["E" + str(n)].value = round(res2[jb + str(n - k1 -m)], 6)else:ws["D" + str(n)].value = 0ws["E" + str(n)].value = 0n +=1while n <= k-1+ 2*m:ws["D" + str(n)].value = ws["D" + str(n- m)].value -ws["D" + str(n - 2*m)].valuews["E" + str(n)].value = ws["E" + str(n- m)].value -ws["E" + str(n - 2*m)].valuen +=1wb.save(path1)wb = openpyxl.load_workbook(path2)ws = wb[shtn]field_names2 = {u'PDJB': [5,5], u'TCHDJB': [10,3], u'TRZDJB': [13,3], u'TRYJZHLJB': [16,3],u'TRPHZJB': [19,5], u'SWDYXJB': [24,3], u'TRZJSWRJB': [27,3], u'SZJB': [30,3], u'GDEJDLJB': [33,3]}field_names3 = ZL[fileshtn][2]for jb in field_names2.keys():n = field_names2[jb][0]# m = field_names2[jb][1]# k = n+m# k1 = n-1for n4 in range(1,6):if n+n4>ZL[fileshtn][3]:break# 汇总部分开始if str("BHQ" + jb + str(n4)) in res2.keys():ws.cell(row=5, column=n+n4).value = round(res2["BHQ" + jb + str(n4)], 6)else:ws.cell(row=5, column=n+n4).value = 0if str(jb + str(n4)) in res2.keys():ws.cell(row=6, column=n+n4).value = round(res2[jb + str(n4)], 6)else:ws.cell(row=6, column=n+n4).value = 0ws.cell(row=7, column=n+n4).value = ws.cell(row=6, column=n+n4).value -ws.cell(row=5, column=n+n4).value# 汇总部分结束for n5 in list(field_names3.keys()):m = field_names3[n5]if n5 in list(res.keys()):if str("BHQ" + jb + str(n4)) in res[n5]:ws.cell(row=m, column=n + n4).value = round(res[n5]["BHQ" + jb + str(n4)], 6)else:ws.cell(row=m, column=n + n4).value = 0if str(jb + str(n4)) in res[n5]:ws.cell(row=m+1, column=n + n4).value = round(res[n5][jb + str(n4)], 6)else:ws.cell(row=m+1, column=n + n4).value = 0ws.cell(row=m+2, column=n + n4).value = ws.cell(row=m+1, column=n + n4).value - ws.cell(row=m,column=n + n4).valueelse:ws.cell(row=m, column=n + n4).value = 0ws.cell(row=m+1, column=n + n4).value = 0ws.cell(row=m+2, column=n + n4).value = 0for n6 in range(5, ZL[fileshtn][4]):ws["E" + str(n6)].value = ws["F" + str(n6)].value + ws["G" + str(n6)].value + ws["H" + str(n6)].value+ ws["I" + str(n6)].value+ ws["J" + str(n6)].valuewb.save(path2)if __name__ == '__main__':"""脚本单独使用""""""----------------------------------------------""""""---------------------PARA---------------------"""QY = "445103潮安区" # 根据实际行政区修改ZL = {u'EJDLBH': ["GD",3,{u'1B2': 8, u'1B3': 11, u'2B1':14, u'2B3': 17,u'3B1': 20, u'3B2': 23},36,26,"\\"+ QY +"二级地类变化耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"二级地类变化耕地资源质量分类面积汇总表-分行政区.xlsx"],u'HFSXBH': ["HF",2,{u'jBg': 8, u'gBj': 11},35,14,"\\"+ QY +"二级地类变化耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"二级地类变化耕地资源质量分类面积汇总表-分行政区.xlsx"]}JBfield_names = [u'PDJB', u'TCHDJB', u'TRZDJB', u'TRYJZHLJB',u'TRPHZJB', u'SWDYXJB', u'TRZJSWRJB', u'SZJB', u'GDEJDLJB', u'BHQPDJB', u'BHQTCHDJB', u'BHQTRZDJB', u'BHQTRYJZHLJB',u'BHQTRPHZJB', u'BHQSWDYXJB', u'BHQTRZJSWRJB', u'BHQSZJB', u'BHQGDEJDLJB']FLfield_names = [u'BHQGDEJDLJB', u'GDEJDLJB', u'TBDLMJ']ZMC = {}wb = openpyxl.load_workbook(r"E:\汕头市、潮州市行政区划（三调）.xlsx") # 根据实际修改，步骤三生成的excel行政代码表路径ws = wb["Sheet3"]for i in range(2, ws.max_row + 1):ZMC[str(ws["A" + str(i)].value)] = ws["B" + str(i)].valueprint(ZMC)print(len(list(ZMC.keys())))for ii in list(ZL.keys()):filename = "E:\\饶平县\\耕地质量分类\\更新\\潮安\\445103潮安区(度)\\导出数据库表\\"+ ii +".xls" # 根据实际修改，步骤一生成的数据库转excel表的所在路径path = r"E:\饶平县\耕地质量分类\更新\潮安\445103潮安区(度)\数据成果程序表" # 根据实际修改，步骤二生成的成果excel模板表路径，运行代码完成后直接修改模板生成最终成果表"""field_names = {u'PDJB': 25, u'TCHDJB': 28, u'TRZDJB': 31, u'TRYJZHLJB': 34,u'TRPHZJB': 37, u'SWDYXJB': 39, u'TRZJSWRJB': 41, u'SZJB': 43, u'GDEJDLJB': 45,u'BHQPDJB': 50, u'BHQTCHDJB': 52, u'BHQTRZDJB': 54, u'BHQTRYJZHLJB': 56,u'BHQTRPHZJB': 58, u'BHQSWDYXJB': 60, u'BHQTRZJSWRJB': 62, u'BHQSZJB': 64, u'BHQGDEJDLJB': 66}"""fileshtn = ii# fileshtn = str(os.path.splitext(os.path.basename(filename))[0])path1 = path + ZL[fileshtn][5]path2 = path + ZL[fileshtn][6]"""----------------------------------------------""""""----------------------------------------------"""dict1, dict2 = readtab(filename, fileshtn)wttab1(ZL[fileshtn][0], path1, path2, dict1, dict2)print("success")

（三）针对耕地分类组合类型汇总表（修改107、118、119行内容）

（四）针对耕地分类质量建设汇总表（修改233、242、250、251行内容）

# -*- coding:utf-8 -*-# ---------------------------------------------------------------------------# Author: LGZ# Created on: /09# Reference:# ---------------------------------------------------------------------------import os, xlrd, xlwtimport matplotlib.pyplot as pltimport copyimport traceback,sysimport pandas as pdimport openpyxlimport logging# logging.disable(logging.critical)logging.basicConfig(level=logging.DEBUG, format="%(asctime)s-%(levelname)s-%(message)s")logging.debug("start of program")def readtab(filename,sheet_name):"""points_genarator(txt_file) return listtxt_file：文本文件地址将txt转换为可以使用的点集列表"""try:data = xlrd.open_workbook(filename)table = data.sheet_by_name(sheet_name)nrow = table.nrows # 获取最大行数logging.debug(nrow)# name_list = [str(table.cell_value(i, 3)) for i in range(1, nrows)]ZD = table.row_values(0, start_colx=0, end_colx=None) # 获取字段名logging.debug(ZD)res = {}field_names = {}FLfield_namesdict = {}for n0, ZDM in enumerate(ZD, 0):logging.debug(n0)logging.debug(ZDM)for nn0 in JBfield_names:if str(nn0) == str(ZDM):field_names[nn0] = n0for nn1 in FLfield_names:if str(nn1) == str(ZDM):FLfield_namesdict[nn1] = n0print(field_names)print(FLfield_namesdict)for n1 in range(1, nrow):vle = table.row_values(n1, start_colx=0, end_colx=None)# logging.debug(vle)m1 = 0# 可使用字典get()方法进行改良if str(vle[FLfield_namesdict["ZLDWDM"]][:9]) not in res.keys():for jbm in field_names.items():if m1 == 0:res.update({str(vle[FLfield_namesdict["ZLDWDM"]][:9]): {str(jbm[0]) + str(vle[jbm[1]]): float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)}})m1 += 1else:res[str(vle[FLfield_namesdict["ZLDWDM"]][:9])].update({str(jbm[0]) + str(vle[jbm[1]]): float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)})else:for jbm in field_names.items():if str(jbm[0]) + str(vle[jbm[1]]) not in res[str(vle[FLfield_namesdict["ZLDWDM"]][:9])].keys():res[str(vle[FLfield_namesdict["ZLDWDM"]][:9])].update({str(jbm[0]) + str(vle[jbm[1]]): float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)})else:res[str(vle[FLfield_namesdict["ZLDWDM"]][:9])][str(jbm[0]) + str(vle[jbm[1]])] += float(vle[FLfield_namesdict["TBDLMJ"]] * 0.0001)# logging.debug("res")# logging.debug(res)m2 = 0for n2 in res.keys():if m2 == 0:res2 = copy.deepcopy(res[n2])m2 += 1# logging.debug(res2)else:for n3 in res[n2].keys():if n3 not in res2.keys():res2.update({n3: res[n2][n3]})else:res2[n3] += float(res[n2][n3])res3 = {"10": "1", "2a": "2", "2b": "3", "3a": "4", "3b": "5", "j": "1", "g": "2"}for ph1 in list(res3.keys()):for ph2 in list(res2.keys()):# print(ph1,ph2)if ph1 in ph2:if ph1 == "j" or ph1 == "g":res2[ph2[:-1] + res3[ph1]] = res2.pop(ph2)else:res2[ph2[:-2] + res3[ph1]] = res2.pop(ph2)for ph1 in list(res3.keys()):for ph2 in list(res.keys()):# print(field_names2[ph2].keys())for ph3 in list(res[ph2].keys()):# print(ph1,ph2)if ph1 in ph3:# res[ph2][ph3[:-2] + res3[ph1]] = res[ph2].pop(ph3)if ph1 == "j" or ph1 == "g":res[ph2][ph3[:-1] + res3[ph1]] = res[ph2].pop(ph3)else:res[ph2][ph3[:-2] + res3[ph1]] = res[ph2].pop(ph3)return res, res2except Exception:# exc_type, exc_value, exc_traceback = sys.exc_info()print(traceback.format_exc())def wttab1(shtn,path1, path2, res, res2):wb = openpyxl.load_workbook(path1)ws = wb[shtn]ws["D" + str(5)].value = round(sum(res2.values()) / 2 / 9, 6)ws["E" + str(5)].value = round(sum(res2.values()) / 2 / 9, 6)field_names2 = {u'PDJB': [6, 5], u'TCHDJB': [21, 3], u'TRZDJB': [30, 3], u'TRYJZHLJB': [39, 3],u'TRPHZJB': [48, 5], u'SWDYXJB': [63, 3], u'TRZJSWRJB': [72, 3], u'SZJB': [81, 3],u'GDEJDLJB': [90, ZL[fileshtn][1]]}for jb in field_names2.keys():n = field_names2[jb][0]m = field_names2[jb][1]k = n + mk1 = n - 1while n <= k - 1:if str("JSQ" + jb + str(n - k1)) in res2.keys():ws["D" + str(n)].value = round(res2["JSQ" + jb + str(n - k1)], 6)ws["E" + str(n)].value = round(res2["JSQ" + jb + str(n - k1)], 6)else:ws["D" + str(n)].value = 0ws["E" + str(n)].value = 0n += 1while n <= k - 1 + m:if str(jb + str(n - k1 - m)) in res2.keys():ws["D" + str(n)].value = round(res2[jb + str(n - k1 - m)], 6)ws["E" + str(n)].value = round(res2[jb + str(n - k1 - m)], 6)else:ws["D" + str(n)].value = 0ws["E" + str(n)].value = 0n += 1while n <= k - 1 + 2 * m:ws["D" + str(n)].value = ws["D" + str(n - m)].value - ws["D" + str(n - 2 * m)].valuews["E" + str(n)].value = ws["E" + str(n - m)].value - ws["E" + str(n - 2 * m)].valuen += 1wb.save(path1)wb = openpyxl.load_workbook(path2)ws = wb[shtn]field_names2 = {u'PDJB': [5, 5], u'TCHDJB': [10, 3], u'TRZDJB': [13, 3], u'TRYJZHLJB': [16, 3],u'TRPHZJB': [19, 5], u'SWDYXJB': [24, 3], u'TRZJSWRJB': [27, 3], u'SZJB': [30, 3],u'GDEJDLJB': [33, 3]}field_names3 = ZL[fileshtn][2]for jb in field_names2.keys():n = field_names2[jb][0]-1# m = field_names2[jb][1]# k = n+m# k1 = n-1for n4 in range(1, 6):if n + n4 > ZL[fileshtn][2]:break# 汇总部分开始if str("JSQ" + jb + str(n4)) in res2.keys():ws.cell(row=5, column=n + n4).value = round(res2["JSQ" + jb + str(n4)], 6)else:ws.cell(row=5, column=n + n4).value = 0if str(jb + str(n4)) in res2.keys():ws.cell(row=6, column=n + n4).value = round(res2[jb + str(n4)], 6)else:ws.cell(row=6, column=n + n4).value = 0ws.cell(row=7, column=n + n4).value = ws.cell(row=6, column=n + n4).value - ws.cell(row=5,column=n + n4).value# 汇总部分结束m = 8for n5 in sorted(res.keys()):ws.cell(row=m, column=1).value = n5ws.cell(row=m, column=2).value = ZMC[n5]ws.merge_cells(start_row=m, start_column=1, end_row=m+2, end_column=1)ws.merge_cells(start_row=m, start_column=2, end_row=m+2, end_column=2)ws.cell(row=m, column=3).value = "建设前"ws.cell(row=m+1, column=3).value = "建设后"ws.cell(row=m+2, column=3).value = "变化量"if str("JSQ" + jb + str(n4)) in res[n5]:ws.cell(row=m, column=n + n4).value = round(res[n5]["JSQ" + jb + str(n4)], 6)else:ws.cell(row=m, column=n + n4).value = 0if str(jb + str(n4)) in res[n5]:ws.cell(row=m + 1, column=n + n4).value = round(res[n5][jb + str(n4)], 6)else:ws.cell(row=m + 1, column=n + n4).value = 0ws.cell(row=m + 2, column=n + n4).value = ws.cell(row=m + 1, column=n + n4).value - ws.cell(row=m,column=n + n4).valuem += 3for n6 in range(5, len(list(res.keys()))*3+5+3):ws["D" + str(n6)].value = ws["E" + str(n6)].value + ws["F" + str(n6)].value + ws["G" + str(n6)].value + ws["H" + str(n6)].value + ws["I" + str(n6)].valuewb.save(path2)if __name__ == '__main__':"""脚本单独使用""""""----------------------------------------------""""""---------------------PARA---------------------"""QY = "445103潮安区" # 根据实际行政区修改ZL = {u'ZLJS_GD': ["GD",3,35,"\\"+ QY +"质量建设耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"质量建设耕地资源质量分类面积汇总表-分行政区.xlsx"],u'ZLJS_HFDL': ["HF",2,34,"\\"+ QY +"质量建设耕地资源质量分类面积汇总表.xlsx","\\"+ QY +"质量建设耕地资源质量分类面积汇总表-分行政区.xlsx"]}JBfield_names = ['PDJB', 'TCHDJB', 'TRZDJB', 'TRYJZHLJB', 'TRPHZJB', 'SWDYXJB', 'TRZJSWRJB', 'SZJB', 'GDEJDLJB', 'JSQPDJB', 'JSQTCHDJB', 'JSQTRZDJB','JSQTRYJZHLJB', 'JSQTRPHZJB', 'JSQSWDYXJB', 'JSQTRZJSWRJB', 'JSQSZJB', 'JSQGDEJDLJB']FLfield_names = [u'ZLDWDM', u'TBDLMJ']ZMC = {}wb = openpyxl.load_workbook(r"E:\汕头市、潮州市行政区划（三调）.xlsx") # 根据实际修改，步骤三生成的excel行政代码表路径ws = wb["Sheet3"]for i in range(2, ws.max_row + 1):ZMC[str(ws["A" + str(i)].value)] = ws["B" + str(i)].valueprint(ZMC)print(len(list(ZMC.keys())))for ii in list(ZL.keys()):filename = "E:\\饶平县\\耕地质量分类\\更新\\潮安\\445103潮安区(度)\\导出数据库表\\" + ii + ".xls" # 根据实际修改，步骤一生成的数据库转excel表的所在路径path = r"E:\饶平县\耕地质量分类\更新\潮安\445103潮安区(度)\数据成果程序表" # 根据实际修改，步骤二生成的成果excel模板表路径，运行代码完成后直接修改模板生成最终成果表fileshtn = ii# fileshtn = str(os.path.splitext(os.path.basename(filename))[0])path1 = path + ZL[fileshtn][3]path2 = path + ZL[fileshtn][4]"""----------------------------------------------""""""----------------------------------------------"""dict1, dict2 = readtab(filename, fileshtn)print(dict1)print(dict2)wttab1(ZL[fileshtn][0], path1, path2, dict1, dict2)print("success")

最后：本人还是初学者，有任何问题欢迎私信批评指正和交流

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。