Tools/加密后软件工具/exe/Excel格式转换/源代码/FormatConvert.py

# -*- coding: utf-8 -*-
import openpyxl
from openpyxl.styles import Alignment
from openpyxl.styles import Border, Side
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
import pandas as pd
import tkinter as tk
from tkinter import filedialog as tkFileDialog
from tkinter import messagebox
import encryption
import os
from sympy import *
import uuid
import time
import re

type_gd = ['水田', '水浇地', '旱地']
type_yd = ['果园', '茶园', '橡胶园', '其他园地']
type_ld = ['乔木林地', '竹林地', '红树林地', '森林沼泽', '灌木林地', '灌丛沼泽', '其他林地']
type_cd = ['天然牧草地', '沼泽草地', '人工牧草地', '其他草地']
type_sf = ['零售商业用地', '批发市场用地', '餐饮用地', '旅馆用地', '商务金融用地', '娱乐用地', '其他商服用地']
type_gk = ['工业用地', '采矿用地', '盐田', '仓储用地']
type_zz = ['城镇住宅用地', '农村宅基地']
type_gg = ['机关团体用地','新闻出版用地','教育用地','科研用地','医疗卫生用地','社会福利用地','文化设施用地','体育用地','公用设施用地','公园与绿地']
type_ts = ['军事设施用地','使领馆用地','监教场所用地','宗教用地','殡葬用地','风景名胜设施用地']
type_jt = ['铁路用地','轨道交通用地','公路用地','城镇村道路用地','交通服务站用地','农村道路','机场用地','港口码头用地','管道运输用地']
type_sy = ['河流水面','湖泊水面','水库水面','坑塘水面','沿海滩涂','内陆滩涂','沟渠','沼泽地','水工建筑用地','冰川及永久积雪']
type_qt = ['空闲地','设施农用地','田坎','盐碱地','沙地','裸土地','裸岩石砾地']


def convert(input, moban, output):
    wb = openpyxl.load_workbook(moban)
    wb1 = openpyxl.load_workbook(input)
    sheet_names = wb1.sheetnames
    sheet = wb.active
    for j in range(1, len(sheet_names)):
        wb.copy_worksheet(sheet)
    sheet_names1 = wb.sheetnames


    for i in range(0, len(sheet_names)):
        sheet_info = wb1[sheet_names[i]]
        info_all = getinfo(sheet_info)
        info_gr = info_all[0]
        info_c = info_all[1]
        info_x = info_all[2]
        zyd = info_all[3]
        # if i == 1:
        #     sheet.title = info_c[0]
        # else:
        #     sheet = wb[sheet_names1[i - 1]]
        #     sheet.title = info_c[0]
        sheet = wb[sheet_names1[i]]
        sheet.title = info_c[0]
        # 记录行数
        row = 2
        xh = 0
        thin = Side(border_style="thin", color="000000")
        for i in range(0, len(info_gr)):
            # 地块数
            num_dk = 0
            # 耕地数
            num_gd = 0
            # 园地数
            num_yd = 0
            # 耕地和园地总面积
            area_gd = 0
            area_yd = 0
            # 序号
            xh = i
            for j in range(2, len(info_gr[i])):
                if type_gd.__contains__(info_gr[i][j][0]) and info_gr[i][j][4] == '' or \
                        type_gd.__contains__(info_gr[i][j][0]) and info_gr[i][j][4] == ' ':
                    num_gd = num_gd + 1
                    num_dk = num_dk + 1
                    sheet['E' + str(num_gd + row)].value = info_gr[i][j][0]
                    sheet['F' + str(num_gd + row)].value = '地块' + str(num_dk)
                    sheet['G' + str(num_gd + row)].value = info_gr[i][j][1]
                    sheet['G' + str(num_gd + row)].number_format = '0.00'
                    sheet['H' + str(num_gd + row)].value = info_gr[i][j][3]
                    area_gd = info_gr[i][j][1] + area_gd
                if type_yd.__contains__(info_gr[i][j][0]) and info_gr[i][j][4] == '' or \
                        type_yd.__contains__(info_gr[i][j][0]) and info_gr[i][j][4] == ' ':
                    num_dk = num_dk + 1
                    num_yd = num_yd + 1
                    sheet['I' + str(num_yd + row)].value = info_gr[i][j][0]
                    sheet['J' + str(num_yd + row)].value = '地块' + str(num_dk)
                    sheet['K' + str(num_yd + row)].value = info_gr[i][j][1]
                    sheet['K' + str(num_yd + row)].number_format = '0.00'
                    sheet['L' + str(num_yd + row)].value = info_gr[i][j][2]
                    sheet['M' + str(num_yd + row)].value = info_gr[i][j][3]
                    sheet['N' + str(num_yd + row)].value = info_gr[i][j][1]
                    sheet['N' + str(num_yd + row)].number_format = '0.00'
                    sheet['O' + str(num_yd + row)].value = info_gr[i][j][5]
                    area_yd = info_gr[i][j][1] + area_yd

            # 计算园地中小计的单元格融合范围
            flag = []  # 相同类型园地
            for i in range(row + 1, row + num_yd):
                area_xj = float(sheet['k' + str(i)].value)
                start = 0
                end = 0
                cf = 0  # 重复次数
                for j in range(i + 1, row + num_yd + 1):
                    if sheet['M' + str(i)].value == sheet['M' + str(j)].value and \
                            sheet['L' + str(i)].value == sheet['L' + str(j)].value:
                        # 再次重复
                        if (start == i):
                            # 只记录未被融合过的单元格
                            if j not in flag:
                                cf = cf + 1
                                end = j
                                area_xj = area_xj + float(sheet['k' + str(j)].value)
                                flag.append(j)
                                if j == row + num_yd:
                                    sheet.merge_cells(range_string='N' + str(start) + ':' + 'N' + str(end))
                                    sheet['N' + str(start)].value = area_xj
                                    sheet['N' + str(start)].number_format = '0.00'
                            else:
                                continue
                        # 首次重复
                        else:
                            if i not in flag:
                                cf = cf + 1
                                start = i
                                end = j
                                area_xj = area_xj + float(sheet['k' + str(j)].value)
                                flag.append(i)
                                flag.append(j)
                                if j == row + num_yd:
                                    sheet.merge_cells(range_string='N' + str(start) + ':' + 'N' + str(end))
                                    sheet['N' + str(start)].value = area_xj
                                    sheet['N' + str(start)].number_format = '0.00'
                            else:
                                continue
                    else:
                        if cf != 0:
                            sheet.merge_cells(range_string='N' + str(start) + ':' + 'N' + str(end))
                            sheet['N' + str(start)].value = area_xj
                            sheet['N' + str(start)].number_format = '0.00'
                            cf = 0

            # 合计行行数
            hj_row = row + max(num_yd, num_gd) + 1
            sheet.merge_cells(range_string='E' + str(hj_row) + ':' + 'F' + str(hj_row))
            sheet['E' + str(hj_row)].value = '合计'
            sheet['G' + str(hj_row)].value = area_gd
            sheet['G' + str(hj_row)].number_format = '0.00'
            sheet['G' + str(hj_row)].font = Font(bold=True)
            sheet.merge_cells(range_string='I' + str(hj_row) + ':' + 'M' + str(hj_row))
            sheet['I' + str(hj_row)].value = '合计'
            sheet['N' + str(hj_row)].value = area_yd
            sheet['N' + str(hj_row)].font = Font(bold=True)
            sheet['N' + str(hj_row)].number_format = '0.00'
            colorfill(sheet, hj_row, 'gr')
            # 序号、户主、身份证、总面积
            sheet.merge_cells(range_string='A' + str(row + 1) + ':' + 'A' + str(hj_row))
            sheet['A' + str(row + 1)].value = xh + 1
            sheet.merge_cells(range_string='B' + str(row + 1) + ':' + 'B' + str(hj_row))
            sheet['B' + str(row + 1)].value = info_gr[xh][1]
            sheet.merge_cells(range_string='C' + str(row + 1) + ':' + 'C' + str(hj_row))
            sheet['C' + str(row + 1)].value = str(info_gr[xh][0])
            sheet.merge_cells(range_string='D' + str(row + 1) + ':' + 'D' + str(hj_row))
            sheet['D' + str(row + 1)].value = area_yd + area_gd
            sheet['D' + str(row + 1)].number_format = '0.00'
            row = row + max(num_yd, num_gd) + 1
            xh = xh + 1

        # 村
        row_c = row
        if len(info_c) != 0:
            merge_c = mergeduplication(info_c)
            row_c = cxdlfill(merge_c, sheet, row, xh)

        # 县
        row_x = row_c
        if len(info_x) != 0:
            merge_x = mergeduplication(info_x)
            row_x = cxdlfill(merge_x, sheet, row_c[0], row_c[1])

        # 争议地
        if len(zyd) != 0:
            merge_zyd = mergedzyd(zyd)
            cxdlfill(merge_zyd, sheet, row_x[0], row_x[1])

        # 格式
        for i in range(1, sheet.max_row + 1):
            for j in range(1, sheet.max_column + 1):
                sheet.cell(i, j).alignment = Alignment(horizontal='center', vertical='center')
        for i in range(3, sheet.max_row + 1):
            for j in range(1, 16):
                sheet.cell(i, j).border = Border(top=thin, left=thin, right=thin, bottom=thin)
        medium = Side(border_style="medium", color="000000")
        sheet['BD1'].border = Border(top=thin, left=thin, right=medium, bottom=thin)
        wb.save(output + '\\' + 'result_4.xlsx')

def drawwd():
    window = tk.Tk()  # 创建窗口对象的背景色
    window.title('表格样式转换')  # 设置窗口的标题
    window.geometry('400x250')  # 设置窗口的大小
    title = tk.Label(window, text='表格样式转换',
                     font=('微软雅黑', 12), width=40, height=3)
    title.place(x=0, y=0, anchor='nw')
    choosepath = tk.Label(window, text='要转换的文件（.xlsx）：',
                          font=('微软雅黑', 12), width=20, height=2)
    choosepath.place(x=0, y=50, anchor='nw')
    path_var1 = tk.StringVar()  # 输入路径
    entry1 = tk.Entry(window, textvariable=path_var1)
    entry1.place(x=170, y=65, anchor='nw')

    mbpath = tk.Label(window, text='表头模板文件（.xlsx）：',
                       font=('微软雅黑', 12), width=20, height=2)
    mbpath.place(x=0, y=100, anchor='nw')
    path_var2 = tk.StringVar()  # 输入路径
    entry2 = tk.Entry(window, textvariable=path_var2)
    entry2.place(x=170, y=115, anchor='nw')

    resultpath = tk.Label(window, text='输出文件夹：',
                          font=('微软雅黑', 12), width=20, height=2)
    resultpath.place(x=0, y=150, anchor='nw')
    path_var6 = tk.StringVar()  # 输出路径
    entry6 = tk.Entry(window, textvariable=path_var6)
    entry6.place(x=170, y=165, anchor='nw')

    def choose1():
        file_dir1 = tkFileDialog.askopenfilename(filetypes=[('Excel', '.xlsx'), ('All Files', '*')])
        path_var1.set(file_dir1)

    def choose2():
        file_dir2 = tkFileDialog.askopenfilename(filetypes=[('Excel', '.xlsx'), ('All Files', '*')])
        path_var2.set(file_dir2)

    def choose3():
        file_dir6 = tkFileDialog.askdirectory()
        path_var6.set(file_dir6)

    def tuichu():
        window.destroy()

    def main():
        inpath = entry1.get()
        mbpath = entry2.get()
        outpath = entry6.get()
        convert(inpath,mbpath,outpath)
        messagebox.showinfo("消息", "运行成功")
        window.destroy()

    tk.Button(window, text='选择', command=choose1).place(x=320, y=60, anchor='nw')
    tk.Button(window, text='选择', command=choose2).place(x=320, y=110, anchor='nw')
    tk.Button(window, text='选择', command=choose3).place(x=320, y=160, anchor='nw')
    tk.Button(window, text='确认', command=main).place(x=120, y=210, anchor='nw')
    tk.Button(window, text='取消', command=tuichu).place(x=220, y=210, anchor='nw')
    window.mainloop()

# 从表中收集所有信息
def getinfo(sheet):
    # 身份证号
    sfz = []
    # 所有信息
    info = []
    # 个人信息
    info_gr = []
    # 村
    info_c = []
    # 县
    info_x = []
    # 争议
    info_zy = []
    #村名
    name_c = ''
    #县名
    name_x = ''

    # 记录这是第几条个人信息
    j = -1
    # 遍历整个表获取所有信息
    for i in range(2, sheet.max_row + 1):
        num = sheet['C'+str(i)].value
        if (num != ' ' and num != None) and sheet['I' + str(i)].value != '是':
            if sfz.__contains__(num):
                if type_gd.__contains__(sheet['D' + str(i)].value):
                    gd = dlinfo(sheet, i)
                    info_gr[j].append(gd)
                elif type_yd.__contains__(sheet['D' + str(i)].value):
                    yd = ydinfo(sheet, i)
                    info_gr[j].append(yd)
                elif type_ld.__contains__(sheet['D' + str(i)].value):
                    qmld = ldinfo(sheet, i)
                    info_c.append(qmld)
                else:
                    qt = dlinfo(sheet,i)
                    info_c.append(qt)
            else:
                j = j + 1
                sfz.append(num)
                numlist = [num]
                info_gr.append(numlist)
                name = sheet['B' + str(i)].value
                if type_gd.__contains__(sheet['D' + str(i)].value):
                    gd = dlinfo(sheet, i)
                    info_gr[j].append(name)
                    info_gr[j].append(gd)
                elif type_yd.__contains__(sheet['D' + str(i)].value):
                    yd = ydinfo(sheet, i)
                    info_gr[j].append(name)
                    info_gr[j].append(yd)
                elif type_ld.__contains__(sheet['D' + str(i)].value):
                    ld = ldinfo(sheet, i)
                    info_c.append(ld)
                    info_gr[j].append(name)
        elif sheet['I' + str(i)].value != '是':
            hz = sheet['B'+str(i)].value
            if hz[len(hz)-1] == '村' or '镇' in hz or '组' in hz:
                name_c = hz
                if type_yd.__contains__(sheet['D' + str(i)].value):
                    yd = ydinfo(sheet, i)
                    info_c.append(yd)
                elif type_ld.__contains__(sheet['D' + str(i)].value):
                    ld = ldinfo(sheet, i)
                    info_c.append(ld)
                else:
                    qtinfo = dlinfo(sheet, i)
                    info_c.append(qtinfo)
            elif hz[len(hz)-1] == '县':
                name_x = hz
                if type_yd.__contains__(sheet['D' + str(i)].value):
                    yd = ydinfo(sheet, i)
                    info_x.append(yd)
                elif type_ld.__contains__(sheet['D' + str(i)].value):
                    ld = ldinfo(sheet, i)
                    info_x.append(ld)
                else:
                    qtinfo = dlinfo(sheet, i)
                    info_x.append(qtinfo)
        else:
            if type_yd.__contains__(sheet['D' + str(i)].value):
                yd = ydinfo(sheet, i)
                info_zy.append(yd)
            elif type_ld.__contains__(sheet['D' + str(i)].value):
                ld = ldinfo(sheet, i)
                info_zy.append(ld)
            else:
                qtinfo = dlinfo(sheet, i)
                info_zy.append(qtinfo)

    if name_c != '':
        info_c.insert(0, name_c)
    if name_x != '':
        info_x.insert(0, name_x)
    info.append(info_gr)
    info.append(info_c)
    info.append(info_x)
    info.append(info_zy)
    return info

# 获取除园地、林地以外的其他地类信息
def dlinfo(sheet,i):
    dl = sheet['D' + str(i)].value
    mj = sheet['F' + str(i)].value
    zy = sheet['I' + str(i)].value
    bz = sheet['j' + str(i)].value
    list = []
    list.append(dl)
    list.append(mj)
    list.append('')
    list.append('')
    list.append(zy)
    list.append(bz)
    return list

# 获取园地信息
def ydinfo(sheet,i):
    dl = sheet['D' + str(i)].value
    mj = sheet['F' + str(i)].value
    sz = sheet['G' + str(i)].value
    csd = sheet['H' + str(i)].value
    zy = sheet['I' + str(i)].value
    bz = sheet['j' + str(i)].value
    list = []
    list.append(dl)
    list.append(mj)
    list.append(sz)
    list.append(csd)
    list.append(zy)
    list.append(bz)
    return list

# 获取林地信息
def ldinfo(sheet,i):
    dl = sheet['D' + str(i)].value
    mj = sheet['F' + str(i)].value
    sz = sheet['G' + str(i)].value
    zy = sheet['I' + str(i)].value
    bz = sheet['j' + str(i)].value
    list = []
    list.append(dl)
    list.append(mj)
    list.append(sz)
    list.append('')
    list.append(zy)
    list.append(bz)
    return list

# 获取争议地信息
def getzyd(infolist):
    zydlist = []
    for i in range(0, len(infolist[0])):
        for j in range(2, len(infolist[0][i])):
            if infolist[0][i][j][len(infolist[0][i][j]) - 2] == '是':
                zydlist.append(infolist[0][i][j])
    for i in range(1, len(infolist[1])):
        if infolist[1][i][len(infolist[1][i]) - 2] == '是':
            zydlist.append(infolist[1][i])
    for i in range(1, len(infolist[2])):
        if infolist[2][i][len(infolist[2][i]) - 2] == '是':
            zydlist.append(infolist[2][i])
    for i in range(1, len(infolist[3])):
        zydlist.append(infolist[3][i])
    return zydlist

# 单元格颜色填充
def colorfill(sheet, row,flag):
    fille = PatternFill("solid", fgColor="E2EFDA")
    if flag == 'gr':
        for i in range(4, 15):
            sheet.cell(row, i).fill = fille
    else:
        for i in range(5, 57):
            sheet.cell(row, i).fill = fille

# 整理集体所有的土地信息
def mergeduplication(info):
    name = info[0]
    info.remove(info[0])
    mergeinfo = []
    ld = []
    yd = []
    qt = []
    for i in range(0, len(info)):
        if type_yd.__contains__(info[i][0]) and info[i][4] != '是':
            yd.append(info[i])
            info[i][4] = 1
            if info[i][5] == None:
                info[i][5] = '无'
        elif type_ld.__contains__(info[i][0]) and info[i][4] != '是':
            ld.append(info[i])
            info[i][4] = 1
            if info[i][5] == None:
                info[i][5] = '无'
            if info[i][2] == None:
                info[i][2] = '无'
        elif info[i][4] != '是':
            qt.append(info[i])
            info[i][4] = 1
            if info[i][5] == None:
                info[i][5] = '无'
    lddf = pd.DataFrame(ld, columns=['dl', 'mj', 'sz', 'csd', 'zy', 'bz'])
    yddf = pd.DataFrame(yd, columns=['dl', 'mj', 'sz', 'csd', 'zy', 'bz'])
    qtdf = pd.DataFrame(qt, columns=['dl', 'mj', 'sz', 'csd', 'zy', 'bz'])

    ldinfo = lddf.groupby(['dl', 'sz', 'bz']).sum()
    #ld_num = lddf.groupby(['dl', 'sz', 'bz']).count()
    ydinfo = yddf.groupby(['dl', 'sz', 'csd', 'bz']).sum()
    #yd_num = lddf.groupby(['dl', 'sz', 'bz']).count()
    qtinfo = qtdf.groupby(['dl', 'bz']).sum()
    #qt_num = lddf.groupby(['dl', 'sz', 'bz']).count()
    # print(ldinfo)
    # print(ydinfo)
    # print(qtinfo)
    ld_list = []
    flag = 0
    for group, values in ldinfo.iteritems():
        group_dict = values.to_dict()  # 每个组转字典
        gplist = list(group_dict)
        vllist = list(group_dict.values())
        for i in range(0, len(gplist)):
            if flag < len(group_dict):
                zh = list(gplist[i])
                zh.append(vllist[i])
                ld_list.append(zh)
                flag = flag + 1
            else:
                ld_list[i].append(vllist[i])
    yd_list = []
    flag = 0
    for group, values in ydinfo.iteritems():
        group_dict = values.to_dict()  # 每个组转字典
        gplist = list(group_dict)
        vllist = list(group_dict.values())
        for i in range(0, len(gplist)):
            if flag < len(group_dict):
                zh = list(gplist[i])
                zh.append(vllist[i])
                yd_list.append(zh)
                flag = flag + 1
            else:
                yd_list[i].append(vllist[i])
    flag = 0
    qt_list = []
    for group, values in qtinfo.iteritems():
        group_dict = values.to_dict()  # 每个组转字典
        gplist = list(group_dict)
        vllist = list(group_dict.values())
        for i in range(0, len(gplist)):
            if flag < len(group_dict):
                zh = list(gplist[i])
                zh.append(vllist[i])
                qt_list.append(zh)
                flag = flag + 1
            else:
                qt_list[i].append(vllist[i])
    mergeinfo.append(ld_list)
    mergeinfo.append(yd_list)
    mergeinfo.append(qt_list)
    mergeinfo.insert(0, name)
    return mergeinfo

# 将整理后的集体土体信息填到表格中
def cxdlfill(info,sheet,row,xh):
    num_gd = 0
    area_gd = 0
    num_yd = 0
    area_yd = 0
    num_ld = 0
    area_ld = 0
    num_cd = 0
    area_cd = 0
    num_sf = 0
    area_sf = 0
    num_gk = 0
    area_gk = 0
    num_zz = 0
    area_zz = 0
    num_gg = 0
    area_gg = 0
    num_ts = 0
    area_ts = 0
    num_jt = 0
    area_jt = 0
    num_sy = 0
    area_sy = 0
    num_qt = 0
    area_qt = 0
    for i in range(1, len(info)):
        for j in range(0, len(info[i])):
            if type_gd.__contains__(info[i][j][0]):
                num_gd = num_gd + 1
                sheet['E' + str(num_gd + row)].value = info[i][j][0]
                sheet['F' + str(num_gd + row)].value = info[i][j][3]
                sheet['G' + str(num_gd + row)].value = info[i][j][2]
                sheet['G' + str(num_gd + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['H' + str(num_gd + row)].value = ''
                else:
                    sheet['H' + str(num_gd + row)].value = info[i][j][1]
                area_gd = info[i][j][2] + area_gd
            elif type_yd.__contains__(info[i][j][0]):
                num_yd = num_yd + 1
                sheet['I' + str(num_yd + row)].value = info[i][j][0]
                sheet['J' + str(num_yd + row)].value = info[i][j][5]
                sheet['K' + str(num_yd + row)].value = info[i][j][4]
                sheet['K' + str(num_yd + row)].number_format = '0.00'
                sheet['L' + str(num_yd + row)].value = info[i][j][1]
                sheet['M' + str(num_yd + row)].value = info[i][j][2]
                sheet['N' + str(num_yd + row)].value = info[i][j][4]
                sheet['N' + str(num_yd + row)].number_format = '0.00'
                if info[i][j][3] == '无':
                    sheet['O' + str(num_yd + row)].value = ''
                else:
                    sheet['O' + str(num_yd + row)].value = info[i][j][3]
                area_yd = info[i][j][4] + area_yd
            elif type_ld.__contains__(info[i][j][0]):
                num_ld = num_ld + 1
                sheet['P' + str(num_ld + row)].value = info[i][j][0]
                sheet['Q' + str(num_ld + row)].value = info[i][j][4]
                sheet['R' + str(num_ld + row)].value = info[i][j][3]
                sheet['R' + str(num_ld + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['S' + str(num_ld + row)].value = ''
                else:
                    sheet['S' + str(num_ld + row)].value = info[i][j][1]
                if info[i][j][2] == '无':
                    sheet['T' + str(num_ld + row)].value = ''
                else:
                    sheet['T' + str(num_ld + row)].value = info[i][j][2]
                area_ld = info[i][j][3] + area_ld
            elif type_cd.__contains__(info[i][j][0]):
                num_cd = num_cd + 1
                sheet['U' + str(num_cd + row)].value = info[i][j][0]
                sheet['V' + str(num_cd + row)].value = info[i][j][3]
                sheet['W' + str(num_cd + row)].value = info[i][j][2]
                sheet['W' + str(num_cd + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['X' + str(num_cd + row)].value = ''
                else:
                    sheet['X' + str(num_cd + row)].value = info[i][j][1]
                area_cd = info[i][j][2] + area_cd
            elif type_sf.__contains__(info[i][j][0]):
                num_sf = num_sf + 1
                sheet['Y' + str(num_sf + row)].value = info[i][j][0]
                sheet['Z' + str(num_sf + row)].value = info[i][j][3]
                sheet['AA' + str(num_sf + row)].value = info[i][j][2]
                sheet['AA' + str(num_sf+ row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['AB' + str(num_sf + row)].value = ''
                else:
                    sheet['AB' + str(num_sf + row)].value = info[i][j][1]
                area_sf = info[i][j][2] + area_sf
            elif type_gk.__contains__(info[i][j][0]):
                num_gk = num_gk + 1
                sheet['AC' + str(num_gk + row)].value = info[i][j][0]
                sheet['AD' + str(num_gk + row)].value = info[i][j][3]
                sheet['AE' + str(num_gk + row)].value = info[i][j][2]
                sheet['AE' + str(num_gk + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['AF' + str(num_gk + row)].value = ''
                else:
                    sheet['AF' + str(num_gk + row)].value = info[i][j][1]
                area_gk = info[i][j][2] + area_gk
            elif type_zz.__contains__(info[i][j][0]):
                num_zz = num_zz + 1
                sheet['AG' + str(num_zz + row)].value = info[i][j][0]
                sheet['AH' + str(num_zz + row)].value = info[i][j][3]
                sheet['AI' + str(num_zz + row)].value = info[i][j][2]
                sheet['AI' + str(num_zz + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['AJ' + str(num_zz + row)].value = ''
                else:
                    sheet['AJ' + str(num_zz + row)].value = info[i][j][1]
                area_zz = info[i][j][2] + area_zz
            elif type_gg.__contains__(info[i][j][0]):
                num_gg = num_gg + 1
                sheet['AK' + str(num_gg + row)].value = info[i][j][0]
                sheet['AL' + str(num_gg + row)].value = info[i][j][3]
                sheet['AM' + str(num_gg + row)].value = info[i][j][2]
                sheet['AM' + str(num_gg + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['AN' + str(num_gg + row)].value = ''
                else:
                    sheet['AN' + str(num_gg + row)].value = info[i][j][1]
                area_gg = info[i][j][2] + area_gg
            elif type_ts.__contains__(info[i][j][0]):
                num_ts = num_ts + 1
                sheet['AO' + str(num_ts + row)].value = info[i][j][0]
                sheet['AP' + str(num_ts + row)].value = info[i][j][3]
                sheet['AQ' + str(num_ts + row)].value = info[i][j][2]
                sheet['AQ' + str(num_ts + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['AR' + str(num_ts + row)].value = ''
                else:
                    sheet['AR' + str(num_ts + row)].value = info[i][j][1]
                area_ts = info[i][j][2] + area_ts
            elif type_jt.__contains__(info[i][j][0]):
                num_jt = num_jt + 1
                sheet['AS' + str(num_jt + row)].value = info[i][j][0]
                sheet['AT' + str(num_jt + row)].value = info[i][j][3]
                sheet['AU' + str(num_jt + row)].value = info[i][j][2]
                sheet['AU' + str(num_jt + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['AV' + str(num_jt + row)].value = ''
                else:
                    sheet['AV' + str(num_jt + row)].value = info[i][j][1]
                area_jt = info[i][j][2] + area_jt
            elif type_sy.__contains__(info[i][j][0]):
                num_sy = num_sy + 1
                sheet['AW' + str(num_sy + row)].value = info[i][j][0]
                sheet['AX' + str(num_sy + row)].value = info[i][j][3]
                sheet['AY' + str(num_sy + row)].value = info[i][j][2]
                sheet['AY' + str(num_sy + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['AZ' + str(num_sy + row)].value = ''
                else:
                    sheet['AZ' + str(num_sy + row)].value = info[i][j][1]
                area_sy = info[i][j][2] + area_sy
            elif type_qt.__contains__(info[i][j][0]):
                num_qt = num_qt + 1
                sheet['BA' + str(num_qt + row)].value = info[i][j][0]
                sheet['BB' + str(num_qt + row)].value = info[i][j][3]
                sheet['BC' + str(num_qt + row)].value = info[i][j][2]
                sheet['BC' + str(num_qt + row)].number_format = '0.00'
                if info[i][j][1] == '无':
                    sheet['BD' + str(num_qt + row)].value = ''
                else:
                    sheet['BD' + str(num_qt + row)].value = info[i][j][1]
                area_qt = info[i][j][2] + area_qt
    # 合计行行数
    hj_row = row + max(num_yd, num_gd,num_cd,num_ld,num_sf,num_gk,num_zz,num_gg,num_ts,num_jt,num_sy,num_qt) + 1
    sheet.merge_cells(range_string='E' + str(hj_row) + ':' + 'F' + str(hj_row))
    sheet['E' + str(hj_row)].value = '合计'
    sheet['G' + str(hj_row)].value = area_gd
    sheet['G' + str(hj_row)].number_format = '0.00'
    sheet['G' + str(hj_row)].font = Font(bold=True)
    sheet.merge_cells(range_string='I' + str(hj_row) + ':' + 'M' + str(hj_row))
    sheet['I' + str(hj_row)].value = '合计'
    sheet['N' + str(hj_row)].value = area_yd
    sheet['N' + str(hj_row)].font = Font(bold=True)
    sheet['N' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='P' + str(hj_row) + ':' + 'Q' + str(hj_row))
    sheet['P' + str(hj_row)].value = '合计'
    sheet['R' + str(hj_row)].value = area_ld
    sheet['R' + str(hj_row)].font = Font(bold=True)
    sheet['R' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='U' + str(hj_row) + ':' + 'V' + str(hj_row))
    sheet['U' + str(hj_row)].value = '合计'
    sheet['W' + str(hj_row)].value = area_cd
    sheet['W' + str(hj_row)].font = Font(bold=True)
    sheet['W' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='Y' + str(hj_row) + ':' + 'Z' + str(hj_row))
    sheet['Y' + str(hj_row)].value = '合计'
    sheet['AA' + str(hj_row)].value = area_sf
    sheet['AA' + str(hj_row)].font = Font(bold=True)
    sheet['AA' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='AC' + str(hj_row) + ':' + 'AD' + str(hj_row))
    sheet['AC' + str(hj_row)].value = '合计'
    sheet['AE' + str(hj_row)].value = area_gk
    sheet['AE' + str(hj_row)].font = Font(bold=True)
    sheet['AE' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='AG' + str(hj_row) + ':' + 'AH' + str(hj_row))
    sheet['AG' + str(hj_row)].value = '合计'
    sheet['AI' + str(hj_row)].value = area_zz
    sheet['AI' + str(hj_row)].font = Font(bold=True)
    sheet['AI' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='AK' + str(hj_row) + ':' + 'AL' + str(hj_row))
    sheet['AK' + str(hj_row)].value = '合计'
    sheet['AM' + str(hj_row)].value = area_gg
    sheet['AM' + str(hj_row)].font = Font(bold=True)
    sheet['AM' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='AO' + str(hj_row) + ':' + 'AP' + str(hj_row))
    sheet['AO' + str(hj_row)].value = '合计'
    sheet['AQ' + str(hj_row)].value = area_ts
    sheet['AQ' + str(hj_row)].font = Font(bold=True)
    sheet['AQ' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='AS' + str(hj_row) + ':' + 'AT' + str(hj_row))
    sheet['AS' + str(hj_row)].value = '合计'
    sheet['AU' + str(hj_row)].value = area_jt
    sheet['AU' + str(hj_row)].font = Font(bold=True)
    sheet['AU' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='AW' + str(hj_row) + ':' + 'AX' + str(hj_row))
    sheet['AW' + str(hj_row)].value = '合计'
    sheet['AY' + str(hj_row)].value = area_sy
    sheet['AY' + str(hj_row)].font = Font(bold=True)
    sheet['AY' + str(hj_row)].number_format = '0.00'
    sheet.merge_cells(range_string='BA' + str(hj_row) + ':' + 'BB' + str(hj_row))
    sheet['BA' + str(hj_row)].value = '合计'
    sheet['BC' + str(hj_row)].value = area_qt
    sheet['BC' + str(hj_row)].font = Font(bold=True)
    sheet['BC' + str(hj_row)].number_format = '0.00'
    colorfill(sheet, hj_row, 'cx')
    # 序号、户主、身份证、总面积
    sheet.merge_cells(range_string='A' + str(row + 1) + ':' + 'A' + str(hj_row))
    # if xh == 0:
    #     sheet['A' + str(row + 1)].value = xh + 1
    # else:
    #     sheet['A' + str(row + 1)].value = xh + 2
    sheet['A' + str(row + 1)].value = xh + 1
    sheet.merge_cells(range_string='B' + str(row + 1) + ':' + 'B' + str(hj_row))
    sheet['B' + str(row + 1)].value = info[0]
    sheet.merge_cells(range_string='C' + str(row + 1) + ':' + 'C' + str(hj_row))
    sheet.merge_cells(range_string='D' + str(row + 1) + ':' + 'D' + str(hj_row))
    sheet['D' + str(row + 1)].value = area_yd + area_gd +area_cd+area_ld+area_ts+area_gg+area_jt+area_sf+area_gk+area_zz+area_sy+area_qt
    sheet['D' + str(row + 1)].number_format = '0.00'
    thin = Side(border_style="thin", color="000000")
    for i in range(row + 1, sheet.max_row + 1):
        for j in range(1, 57):
            sheet.cell(i, j).border = Border(top=thin, left=thin, right=thin, bottom=thin)
    row = hj_row
    xh = xh + 1
    return row, xh

# 整理争议地信息
def mergedzyd(info):
    mergeinfo = []
    ld = []
    yd = []
    qt = []
    for i in range(0, len(info)):
        if type_yd.__contains__(info[i][0]):
            yd.append(info[i])
            info[i][4] = 1
            if info[i][5] == None:
                info[i][5] = '无'
        elif type_ld.__contains__(info[i][0]):
            ld.append(info[i])
            info[i][4] = 1
            if info[i][5] == None:
                info[i][5] = '无'
            if info[i][2] == None:
                info[i][2] = '无'
        else:
            qt.append(info[i])
            info[i][4] = 1
            if info[i][5] == None:
                info[i][5] = '无'
    lddf = pd.DataFrame(ld, columns=['dl', 'mj', 'sz', 'csd', 'zy', 'bz'])
    yddf = pd.DataFrame(yd, columns=['dl', 'mj', 'sz', 'csd', 'zy', 'bz'])
    qtdf = pd.DataFrame(qt, columns=['dl', 'mj', 'sz', 'csd', 'zy', 'bz'])

    ldinfo = lddf.groupby(['dl', 'sz', 'bz']).sum()
    ydinfo = yddf.groupby(['dl', 'sz', 'csd', 'bz']).sum()
    qtinfo = qtdf.groupby(['dl', 'bz']).sum()
    ld_list = []
    flag = 0
    for group, values in ldinfo.iteritems():
        group_dict = values.to_dict()  # 每个组转字典
        gplist = list(group_dict)
        vllist = list(group_dict.values())
        for i in range(0, len(gplist)):
            if flag < len(group_dict):
                zh = list(gplist[i])
                zh.append(vllist[i])
                ld_list.append(zh)
                flag = flag + 1
            else:
                ld_list[i].append(vllist[i])
    yd_list = []
    flag = 0
    for group, values in ydinfo.iteritems():
        group_dict = values.to_dict()  # 每个组转字典
        gplist = list(group_dict)
        vllist = list(group_dict.values())
        for i in range(0, len(gplist)):
            if flag < len(group_dict):
                zh = list(gplist[i])
                zh.append(vllist[i])
                yd_list.append(zh)
                flag = flag + 1
            else:
                yd_list[i].append(vllist[i])
    flag = 0
    qt_list = []
    for group, values in qtinfo.iteritems():
        group_dict = values.to_dict()  # 每个组转字典
        gplist = list(group_dict)
        vllist = list(group_dict.values())
        for i in range(0, len(gplist)):
            if flag < len(group_dict):
                zh = list(gplist[i])
                zh.append(vllist[i])
                qt_list.append(zh)
                flag = flag + 1
            else:
                qt_list[i].append(vllist[i])
    mergeinfo.append(ld_list)
    mergeinfo.append(yd_list)
    mergeinfo.append(qt_list)
    mergeinfo.insert(0, '争议地')
    return mergeinfo

def dataGet(s0):
    aa = 0
    datekey = {
        'ze':'0','im':'1','tw':'2','ee':'3','fr':'4','ve':'5',
        'ix':'6','se':'7','ei':'8','ni':'9'
    }
    ii = 0
    dastr = []
    while ii < 8:
        x1 = 2 * ii
        y1 = x1 + 2
        das = datekey[s0[x1:y1]]
        dastr.append(das)
        ii = ii + 1
    datestr = ''.join(dastr)
    mm = int(datestr[:2])
    dd = int(datestr[2:4])
    yy = int(datestr[4:])
    tm = time.localtime()
    dy = int(tm.tm_year)
    dm = int(tm.tm_mon)
    dday=int(tm.tm_mday)
    if dy < yy:
        aa = 1.7
    elif dy == yy:
        if dm < mm:
            aa = 1.7
        elif dm == mm:
            if dday <= dd:
                aa = 1.7
            else:
                aa = -3
        else:
            aa = -3
    else:
        aa = -3
    return aa
def macget(sy0,ma0):
    mackey = {
        'a':'10','b':'11','c':'12','d':'13','e':'14','f':'15'
    }
    aa = 0
    sy = list(sy0)
    ma = list(ma0)
    ii = 0
    node = uuid.getnode()
    MAC = uuid.UUID(int = node).hex[-12:]
    mac = MAC.lower()
    while ii < 4:
        try:
            syi = int(sy[ii])
            mai = ma[ii]
            maci = mac[syi]
            if maci == mai:
                aa = 0
            else:
                aa = -2
                break
        except:
            syi = int(mackey[sy[ii]])
            mai = ma[ii]
            maci = mac[syi]
            if maci == mai:
                aa = 0
            else:
                aa = -2
                break
        ii = ii + 1
    if aa == 0:
        aa = 1.7
    else:
        aa = -2
    return aa
def pdd(kk):
    n = Symbol('n')
    if kk > limit(((3*n*n - 5)/(2*n*n + n)),n,oo) and kk < (integrate(n**2,[n,0,1]) * 6):
    # -----------------这句才是程序运行代码----------------------------------
        drawwd()
    # ----------------这句才是程序运行代码----------------------------------
    elif kk == -1:
        messagebox.showerror('错误', '许可文件不存在！')
        encryption.wdshow()
    elif kk == -2:
        messagebox.showerror('错误', '许可文件密钥错误！')
        encryption.wdshow()
    elif kk == -3:
        messagebox.showerror('错误', '超过许可时间！')
        encryption.wdshow()
    else:
        messagebox.showerror('错误', '许可文件损坏！')
        encryption.wdshow()
def bsfGet(s0):
    aa = 0
    bsfkey = {
        'bd':'a','cd':'c','7a':'d','ae':'e','97':'g','6k':'h',
        '57':'i','22':'D','2c':'m','8m':'o','3w':'p','11':'I',
        '5d':'r','4u':'s','9t':'t','au':'u','23':'x','yk':'y',
        'za':'z','k4':'0','v6':'1','tw':'2','h0':'5','op':'7',
        'po':'8','wt':'9','8x':':','yy':',','lo':' ',
    }
    ii = 0
    lens0 = len(s0)
    bsfstr = []
    while ii < (lens0 / 2):
        x1 = 2 * ii
        y1 = x1 + 2
        bsf = bsfkey[s0[x1:y1]]
        bsfstr.append(bsf)
        ii = ii + 1
    bsstr = ''.join(bsfstr)
    return bsstr
def licen():
    aa = 0
    linsy = ''
    linma = ''
    s1 = 'aucd8x'
    s2 = '3w5d8m7aaucd9tlo11228x'
    s3 = 'cd573w6kae5d8x'
    # path = 'D:\\CMCtbxLisence\\tbxLisence.txt'
    # isExists=os.path.exists(path)
    # if not isExists:
    #     path1 = 'E:\\CMCtbxLisence\\tbxLisence.txt'
    #     isExists1=os.path.exists(path1)
    #     if not isExists1:
    #         path2 = 'F:\\CMCtbxLisence\\tbxLisence.txt'
    #         isExists2=os.path.exists(path2)
    #         if not isExists2:
    #             aa = -1
    #         else:
    #             outpath = 'F:\\CMCtbxLisence\\tbxLisence.txt'
    #     else:
    #         outpath = 'E:\\CMCtbxLisence\\tbxLisence.txt'
    # else:
    #     outpath = 'D:\\CMCtbxLisence\\tbxLisence.txt'
    outpath = os.path.expandvars("%APPDATA%") + '\\CMCexeLisence\\formcLisence.txt'
    isExists = os.path.exists(outpath)
    if not isExists:
        aa = -1
    if aa != -1:
        with open(outpath,'r') as lines:
            for line in lines:
                if re.search(bsfGet(s1),line):
                    try:
                        linrq1 = line.split(':',-1)[-1]
                        linrq = linrq1.replace('\n','')
                        aa = dataGet(linrq)
                        if aa == -3:
                            break
                    except:
                        aa = -9999
                if re.search(bsfGet(s2),line):
                    try:
                        linSY1 = line.split('-',-1)[-1]
                        linSY = linSY1.replace('\n','')
                        linsy = linSY.lower()
                    except:
                        aa = -9999
                if re.search(bsfGet(s3),line):
                    try:
                        linma1 = line.split('-',-1)[0]
                        linMA1 = linma1.split(':',-1)[-1]
                        linMA = linMA1.replace('\n','')
                        linma = linMA.lower()
                        aa = macget(linsy,linma)
                    except:
                        aa = -9999
    else:
        aa = -1
    pdd(aa)


if __name__ == '__main__':
    # 输入文件，输出文件
    # convert(r'F:\2024\05\20240515_特定Excel格式转换\ML库区表1 - 副本.xlsx', r'F:\2024\05\20240515_特定Excel格式转换\moban.xlsx',
    #         r'F:\2024\05\20240515_特定Excel格式转换\result')
    licen()