Sanpu_Surface/rongzhong.py

import pandas as pd
import numpy as np
import time
import public as pb
import openpyxl
import matplotlib.pyplot as plt
from scipy.stats import spearmanr
# import tkinter as tk
# from tkinter import ttk
from tkinter import filedialog
from tkinter import messagebox
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as OImage
import os
import ttkbootstrap as ttk
from ttkbootstrap.constants import *
from PIL import Image, ImageTk
from ttkbootstrap.dialogs import Messagebox
import plotly.graph_objects as go
import plotly.io as pio
from sklearn.linear_model import LinearRegression
import report
import partReport
import copy
from docx import Document
from openpyxl.worksheet.hyperlink import Hyperlink
import docx
from docx import Document
from docx.shared import Inches
from docx.oxml import OxmlElement, ns
from docx.shared import Pt, RGBColor
from docx.oxml.ns import nsdecls, nsmap
from docx.oxml import parse_xml
from docx.enum.dml import MSO_THEME_COLOR_INDEX
from docx import Document
from docx.opc.constants import RELATIONSHIP_TYPE as RT
from docx.enum.table import WD_TABLE_ALIGNMENT, WD_CELL_VERTICAL_ALIGNMENT
from docx.oxml.ns import qn
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.enum.section import WD_ORIENTATION
import uuid
import hashlib
from ttkbootstrap.dialogs import Querybox
# 显示所有数据
pd.set_option('display.width', 10000) # 设置字符显示宽度
pd.set_option('display.max_rows', None) # 设置显示最大行
pd.set_option('display.max_columns', None) # 设置显示最大列，None为显示所有列
# 一些全局变量
changeFileUrl = '' # 选择的文件路径
saveFileUrl = '' #保存文件路径
resData_1_Style = None
resData_2 = None
resData_3_Style = pd.DataFrame({})
resData_4 = pd.DataFrame({})
resData_5_Style = None
resData_6 = None
resData_8_Style = None
resData_7 = None
resData_10_Style = None
resData_9 = None
resData_12_Style = None
resData_11 = None
resData_14_Style = None
resData_13 = None
htmlContent = []
htmlStatisticsContent = []
# 报告需要用到的数据
table_1_data = pd.DataFrame({})
table_3_data = pd.DataFrame({})
table_5_data = pd.DataFrame({})
table_8_data = pd.DataFrame({}) # 样品编号替换为编号
table_10_data = pd.DataFrame({})
table_12_data = pd.DataFrame({})
table_14_data = pd.DataFrame({})
checkType = '' # 保存可用类型
# 保存需要用到的异常指标数据
table_1_index = pd.DataFrame({})
table_3_index = pd.DataFrame({})
table_5_index = pd.DataFrame({})
table_8_index = pd.DataFrame({}) # 样品编号替换为编号
table_10_index = pd.DataFrame({})
table_12_index = pd.DataFrame({})
table_14_index = pd.DataFrame({})
# 保存一份原始数据 数据修约时使用
originData = pd.DataFrame({})
# 合并数据 检查审核结果中有阈值以外字段的 提取出来 todo 提取序号 编号 土地类型
# 设置字体
# 设置字体 微软雅黑 新罗马 加粗
plt.rcParams['font.family'] = ['Times New Roman','Microsoft YaHei']
# 设置字体加粗
font = {'weight': 'bold'}
plt.rc('font', **font)  # 应用字体设置
# 公共函数
def calculate_row_range(row):

    return row.max() - row.min()  # 对每一行计算最大值与最小值之差

# 转数字
def filter_number(arr):
    """
    :param arr:
    :return:
    """
    return pd.to_numeric(arr, errors='coerce')
# 公共函数处理重复样品：根据样品编号筛选出所有重复的样品数据，求均值后再和总数据合并
def getRepeat(arr):
    df1 = arr[arr.duplicated(subset='原样品编号',keep=False)].drop_duplicates('原样品编号')['原样品编号']
    dpData = pd.DataFrame({})
    # 循环 筛选对应重复数据
    for i in df1:
        dpArr = arr[arr['原样品编号'] == i]
        numeric_cols = dpArr.select_dtypes(include=['int', 'float']).columns
        dpArr[numeric_cols] = dpArr[numeric_cols].apply(lambda x: round(x.mean(),2), axis=0)
        newData = dpArr.drop_duplicates(subset=['原样品编号'], keep='last')
        dpData = dpData._append(newData)
    return dpData
# 存疑行标红
def highlight_condition(s):
    if s['审核结果'] != '' and not pd.isna(s['审核结果']):
        return ['background-color: #99CC99']*len(s)
    else:
        return ['']*len(s)

# 自适应列宽
def autoColumns(url):
    wb = load_workbook(url)
    ws = wb.active
    ws_pd = wb['频度分析']
    # 自适应调整列宽
    for column_cells in ws.columns:
        length = max(len(str(cell.value)) for cell in column_cells if cell.value is not None)
        ws.column_dimensions[column_cells[0].column_letter].width = length + 15  # 可以根据需要调整额外的宽度
    for column_cells in ws_pd.columns:
        length = max(len(str(cell.value)) for cell in column_cells if cell.value is not None)
        ws_pd.column_dimensions[column_cells[0].column_letter].width = length + 5  # 可以根据需要调整额外的宽度
    # 保存调整后的Excel文件
    wb.save(url)


# 频度分析函数 公用
def frequency_analysis(arr):
    qua_2 = arr.quantile(0.02)

    qua_5 = arr.quantile(0.05)
    qua_10 = arr.quantile(0.1)
    qua_20 = arr.quantile(0.2)
    qua_50 = arr.quantile(0.5)
    qua_80 = arr.quantile(0.8)
    qua_90 = arr.quantile(0.9)
    qua_95 = arr.quantile(0.95)
    qua_98 = arr.quantile(0.98)
    min_value = arr.min()
    max_value = arr.max()

    median_value = arr.median()  # 中位数
    jc_value = arr.max() - arr.min()  # 极差

    std_value = arr.std()  # 标准差
    mean_value = arr.mean()  # 平均数
    variation_value = std_value / mean_value  # 变异系数 = 标准差/均值
    data = pd.DataFrame(
        [qua_2, qua_5, qua_10, qua_20, qua_50, qua_80, qua_90, qua_95, qua_98, min_value, max_value, median_value,
         jc_value, std_value, mean_value, variation_value])
    index_value = ['2%', '5%', '10%', '20%', '50%', '80%', '90%', '95%', '98%', '最小值', '最大值', '中位数', '极差',
                   '标准差', '平均数', '变异系数']
    # 汇总数据
    data.index = index_value
    data_res = round(data, 2)

    return data_res

# 绘图函数
def getImg(x,y,url,name,sheetName,xLabel,YLabel,numArr,fileUrl,loc):
    coef, p_value = spearmanr(x, y)
    fig = go.Figure(data=go.Scatter(
        x=x,
        y=y,
        text=numArr.to_numpy(),
        mode='markers',name='散点数据'))
    # 设置图表布局
    fig.update_layout(title=f"{xLabel}和{YLabel}Spearman相关性系数: {coef:.2f}",
                      xaxis_title=xLabel,
                      yaxis_title=YLabel)

    model = LinearRegression()
    model.fit(x.to_numpy().reshape(-1, 1), y)  # 用x的平方作为特征值
    y_pred = model.predict(x.to_numpy().reshape(-1, 1))
    fig.add_trace(go.Scatter(x=x, y=y_pred, mode='lines', name='拟合直线'))

    html_file_path = f"{url}/{name}频度统计图.html"
    pio.write_html(fig, file=html_file_path, auto_open=False)
    # 在表格中插入html
    workbook = load_workbook(filename=fileUrl)
    # 选择一个工作表
    ws = workbook[sheetName]
    # 将 HTML 内容作为富文本写入单元格
    ws[loc] = '=HYPERLINK("file:///{0}","点击查看统计图")'.format(html_file_path)
    workbook.save(fileUrl)


# 频度统计直方图
def getStatisticsImg(data,xLabel,name,fileUrl,url,loc):
    fig = go.Figure(data=[go.Histogram(x=data)])
    # 设置标题和其他格式
    fig.update_layout(
        title_text= f"{name}统计图",
        xaxis_title=xLabel,
        yaxis_title='频次',
        bargap=0.2,  # 相邻位置坐标的钢筋之间的间隙
        bargroupgap=0.1  #
    )
    html_file_path = f"{url}/{name}频度统计图.html"
    pio.write_html(fig, file=html_file_path, auto_open=False)
    # 在表格中插入html
    workbook = load_workbook(filename=fileUrl)
    # 选择一个工作表
    ws = workbook['频度分析']
    # 将 HTML 内容作为富文本写入单元格
    ws[loc] = '=HYPERLINK("file:///{0}","点击查看统计图")'.format(html_file_path)
    workbook.save(fileUrl)

# ---------------数据读取计算-----------------
def is_trial_file():
    try:
        with open('./html/config.txt', 'r') as file:
            start_date_str = file.read()
        return True
    except FileNotFoundError:
        # 如果文件不存在，这是用户第一次打开，开始试用
        with open('./html/config.txt', 'w') as file:
            file.write('376d8bf8f8855ad8de997fa5dac1bd24956aef0cbfa0cf8ac04053a7043e3d90248051f6f03f02b20430949504a5556fb112131fc81205768229ffa023831b04')
        return False

def is_code_file():
    try:
        with open('./html/code.txt', 'r') as file:
            start_date_str = file.read()
        return True
    except FileNotFoundError:
        # 如果文件不存在，这是用户第一次打开，开始试用

        return False


def getOption():
    # 检查标记文件和注册码文件
    type = 'HUNDRED_DATA' # 试用100条
    configFile = './html/config.docx'
    codeFile = './html/code.docx'
    # resginNum = getNum()
    # 注册码正确 可用
    if not is_trial_file() and not is_code_file():
        type = 'HUNDRED_DATA'
    elif is_trial_file() and not is_code_file():
        type = 'OVER_LINE'
    elif is_code_file():
        type = 'ALL'
    return type


def getNum():
    device_id = uuid.getnode()  # 获取设备的唯一ID，通常是MAC地址
    str = f'{device_id}-window-pc-user'
    sha256 = hashlib.sha256()
    sha256.update(str.encode('utf-8'))  # 将字符串编码为UTF-8格式
    newStr = sha256.hexdigest()
    front_8 = newStr[:8]
    middle_40_36 = newStr[36:40]
    end_4 = newStr[-4:]
    return f"{front_8}{middle_40_36}{end_4}"
# 总体审核函数
def checkData(fileUrl):
    try:
        # 这里先弹窗提示输入注册码，获取注册码请点击下方获取申请码按钮了解详情。
        # 无注册码点击确定按钮弹框消失，但此时只能使用一百条数据
        # 默认读取第一个sheet

        type = getOption()
        global checkType
        checkType = type
        data = pd.read_excel(fileUrl,converters={'原样品编号': str})

        if type == 'OVER_LINE':
            show_error('试用已结束，使用更多请点击下方获取申请码按钮联系管理员！')
        elif type == 'HUNDRED_DATA' or type == 'ALL':
            if type == 'HUNDRED_DATA':
                data = data.head(100)
            global htmlContent
            htmlContent = []
            if not data.empty:
                # 开始审核
                # 计算均值--已有
                # 极差 极差的具体计算公式为：‌R=xmax−xmin
                # 删除质控样品 编号里含zk的样品可以删除
                simpleData = data.dropna(subset=['原样品编号'])
                global originData
                originData = pd.read_excel(fileUrl, dtype='str')

                simpleData = simpleData[~simpleData['原样品编号'].str.contains('ZK')]
                simpleData = simpleData.replace(r'[^.\w]+', '', regex=True)
                # print('simpleData',simpleData)
                simpleData = simpleData.replace('未检测', np.nan)
                # simpleData.iloc[:, 3:] = simpleData.iloc[:, 3:].apply(pd.to_numeric,errors='ignore')
                strList = ['原样品编号','样品编号','地理位置','土壤类型','母质','土地利用类型','土壤质地']
                for i in simpleData.columns:
                    if i not in strList:
                        simpleData[i] = pd.to_numeric(simpleData[i], errors='coerce')
                # 处理重复样品

                res = getRepeat(simpleData)
                simpleData = simpleData._append(res).drop_duplicates(subset=['原样品编号'], keep='last')
                jCData = simpleData[['土壤容重1（g/cm³）', '土壤容重2（g/cm³）', '土壤容重3（g/cm³）', '土壤容重4（g/cm³）']]
                # 计算土壤容重均值
                rZMean = round(simpleData[['土壤容重1（g/cm³）', '土壤容重2（g/cm³）', '土壤容重3（g/cm³）', '土壤容重4（g/cm³）']].mean(
                    axis=1),2)
                # 极差值
                jCResData = jCData.apply(calculate_row_range, axis=1)  # 在每一行上应用函数，‌axis=1表示按行操作
                # 相对极差 相对极差（‌%）‌= 极差（‌绝对极差）‌/ 数值平均值 × 100%。‌
                relativeJCData = jCResData / simpleData['土壤容重平均值（g/cm³）'] * 100
                # 加和
                plusData = simpleData['2~0.2mm颗粒含量'] + simpleData['0.2~0.02mm颗粒含量'] + simpleData[
                    '0.02~0.002mm颗粒含量'] + simpleData['0.002mm以下颗粒含量']
                # ---------------表1----------数据汇总
                resData = pd.DataFrame({
                    '编号': simpleData['原样品编号'],
                    '样品编号': simpleData['样品编号'],
                    '地理位置': simpleData['地理位置'],
                    '土壤类型': simpleData['土壤类型'],
                    '土地利用类型': simpleData['土地利用类型'],
                    '母质': simpleData['母质'],
                    '土壤质地': simpleData['土壤质地'],
                    '土壤容重1(g/cm3)': simpleData['土壤容重1（g/cm³）'],
                    '土壤容重2(g/cm3)': simpleData['土壤容重2（g/cm³）'],
                    '土壤容重3(g/cm3)': simpleData['土壤容重3（g/cm³）'],
                    '土壤容重4(g/cm3)': simpleData['土壤容重4（g/cm³）'],
                    '土壤容重平均值(g/cm3)': simpleData['土壤容重平均值（g/cm³）'],
                    '土壤容重平均值(g/cm3)(计算)': rZMean,
                    '极差': jCResData,
                    '相对极差(%)': relativeJCData,
                    '洗失量(吸管法需填)%': simpleData['洗失量（吸管法需填）'],
                    '2-0.2mm颗粒含量%': simpleData['2~0.2mm颗粒含量'],
                    '0.2-0.02mm颗粒含量%': simpleData['0.2~0.02mm颗粒含量'],
                    '0.02-0.002mm颗粒含量%': simpleData['0.02~0.002mm颗粒含量'],
                    '0.002mm以下颗粒含量%': simpleData['0.002mm以下颗粒含量'],
                    '加和%': plusData,
                    'pH': simpleData['pH']
                })

                # 调用审核函数 得到审核结果
                table_1_res = pb.soil_bulk_density(resData)
                resData = resData.reset_index(drop=True)
                resData['审核结果'] = table_1_res['审核结果']
                global table_1_data
                table_1_data_res = resData[resData['土壤质地'] != resData['土壤质地(判断)']]
                table_1_data = resData
                # 提取异常指标数据
                global table_1_index
                # table_1_index = pd.DataFrame({
                #     '原样品编号': simpleData['原样品编号'],
                #     '样品编号': simpleData['样品编号'],
                #     '土地利用类型': resData['土地利用类型'],
                #     '指标': table_1_res['异常指标'],
                #     '原因': table_1_res['审核结果']
                # })
                table_1_index['原样品编号'] = resData['编号']
                table_1_index['样品编号'] = resData['样品编号']
                table_1_index['土地利用类型'] = resData['土地利用类型']
                table_1_index['指标'] = table_1_res['异常指标']
                table_1_index['原因'] = table_1_res['审核结果']
                # 这里数据直接使用即可（土壤质地填报）todo
                del resData['土壤质地(判断)']
                resData.insert(loc=6, column='土壤质地(判断)', value=table_1_res['土壤质地(判断)'])
                global resData_1_Style
                resData_1_Style = resData.style.apply(highlight_condition,axis=1)
                # 表2--------------------------表2 土壤容重与机械组成总体数据频度分析----------------------------------------
                # 计算6个指标的百分位 及其他值 2% 5% 10% 20% 50% 80% 90% 95% 98% 最小值 最大值 中位数 极差 标准差 平均数 变异系数
                # 土壤容重均值 rZMean
                data_2 = pd.DataFrame({
                    '土壤容重(g/cm3)': resData['土壤容重平均值(g/cm3)(计算)'],
                    '洗失量(吸管法需填)%': simpleData['洗失量（吸管法需填）'],
                    '2-0.2mm颗粒含量%': simpleData['2~0.2mm颗粒含量'],
                    '0.2-0.02mm颗粒含量%': simpleData['0.2~0.02mm颗粒含量'],
                    '0.002-0.002mm颗粒含量%': simpleData['0.02~0.002mm颗粒含量'],
                    '0.002mm以下颗粒含量%': simpleData['0.002mm以下颗粒含量']
                })
                global resData_2
                resData_2 = frequency_analysis(data_2)
                # 表3--------------------------表3水稳性大团聚体数据汇总----------------------------------------
                # 数据计算 这里数据暂时还没有 数据获取到以后再进行测试
                resData_3 = pd.DataFrame({
                    '编号': simpleData['原样品编号'],
                    '总和(%)': simpleData['水稳性大团聚体总和（%）'],
                    '＞5mm%': simpleData['水稳>5mm（%）'],
                    '3-5mm%': simpleData['水稳3mm~5mm（%）'],
                    '2-3mm%': simpleData['水稳2mm~3mm（%）'],
                    '1-2mm%': simpleData['水稳1mm~2mm（%）'],
                    '0.5-1mm%': simpleData['水稳0.5mm~1mm（%）'],
                    '0.25-0.5mm%': simpleData['水稳0.25mm~0.5mm（%）'],
                    'pH值': simpleData['pH'],
                    '有机质g/kg': simpleData['有机质'],
                    '土地利用类型': simpleData['土地利用类型'],
                    '母质': simpleData['母质']
                })
                # 数据审核
                resData_3 = resData_3.reset_index(drop=True)
                res_3_v = pb.water_stable(resData_3)
                resData_3['审核结果'] = res_3_v['审核结果']
                global resData_3_Style
                global table_3_data
                table_3_data = resData_3
                # 提取异常数据
                global table_3_index
                # table_3_index = pd.DataFrame({
                #     '样品编号': simpleData['样品编号'],
                #     '指标': res_3_v['异常指标'],
                #     '原因': res_3_v['审核结果']
                # })

                # table_3_index['样品编号'] = simpleData['样品编号']
                table_3_index['指标'] = res_3_v['异常指标']
                table_3_index['原因'] = res_3_v['审核结果']
                resData_3_Style = resData_3.style.apply(highlight_condition, axis=1)
                # 表4--------------------------表4 水稳性大团聚体频度分析-----------------------

                resData_4_need = resData_3[['总和(%)','＞5mm%','3-5mm%','2-3mm%','1-2mm%','0.5-1mm%','0.25-0.5mm%']]
                global resData_4
                resData_4 = frequency_analysis(resData_4_need)

                # 表5--------------------------表5pH、阳离子交换量、交换性盐基基础数据收集----------------------------------------
                forPlus = simpleData['交换性钙'] + simpleData['交换性镁'] + simpleData['交换性钾'] + simpleData['交换性钠']
                resData_5 = pd.DataFrame({
                    '编号': simpleData['原样品编号'],
                    'pH': simpleData['pH'],
                    '含水量': simpleData['风干试样含水量（分析基）'],
                    '土壤类型': simpleData['土壤类型'],
                    '阳离子交换量Cmol(+)/kg': simpleData['阳离子交换量'],
                    '交换性盐总量Cmol(+)/kg': simpleData['交换性盐基总量'],
                    '交换性钙Cmol(1/2Ca2+)/kg': simpleData['交换性钙'],
                    '交换性镁cmol(1/2Mg2+)/kg': simpleData['交换性镁'],
                    '交换性钾Cmol(+)/kg': simpleData['交换性钾'],
                    '交换性钠cmol(+)/kg': simpleData['交换性钠'],
                    '四大离子之和': forPlus,
                    '阳交量与交盐量差': simpleData['阳离子交换量'] - simpleData['交换性盐基总量'],
                    '盐基饱和度%': simpleData['交换性盐基总量'] / simpleData['阳离子交换量']  # 交换性盐基/阳离子交换量
                })

                resData_5 = resData_5.reset_index(drop=True)
                res_5_v = pb.cation_value(resData_5)


                resData_5['审核结果'] = res_5_v['审核结果']
                global resData_5_Style
                global table_5_data
                table_5_data = resData_5
                # 提取异常数据

                global table_5_index
                # table_5_index = pd.DataFrame({
                #     '样品编号': simpleData['样品编号'],
                #     '指标': res_5_v['异常指标'],
                #     '原因': res_5_v['审核结果']
                # })
                # table_5_index['样品编号'] = simpleData['样品编号']
                table_5_index['指标'] = res_5_v['异常指标']
                table_5_index['原因'] = res_5_v['审核结果']

                resData_5_Style = resData_5.style.apply(highlight_condition, axis=1)

                # 表6--------------------------表6----------------------------------------
                global resData_6
                resData_6 = frequency_analysis(resData_5[['pH']])

                # 表8--------------------------表8----------------------------------------
                # 离子可能存在未检测情况 对离子指标进行转数字操作 防止后续计算出错
                naArr = filter_number(simpleData['水溶性Na⁺含量'])
                kArr = filter_number(simpleData['水溶性K⁺含量'])
                caArr = filter_number(simpleData['水溶性Ca²⁺含量'])
                mgArr = filter_number(simpleData['水溶性Mg²⁺含量'])
                clArr = filter_number(simpleData['水溶性Cl⁻含量'])
                coArr = filter_number(simpleData['水溶性CO₃²⁻含量'])
                hcoArr = filter_number(simpleData['水溶性HCO₃⁻含量'])
                soArr = filter_number(simpleData['水溶性SO₄²⁻含量'])

                # 数据转换
                changeNa = naArr * 22.9898 / 100
                changK = kArr * 39.0983 / 100
                changeCa = caArr * 40 / 100
                changeMg = mgArr * 24.305 / 100
                changeCl = clArr * 35.453 / 100
                changeCo = coArr * 60 / 100
                changeCOH = hcoArr * 61.0168 / 100
                changeSo = soArr * 96.06 / 100
                eightPlusArr = changeNa + changK + changeCa + changeMg + changeCl + changeCo + changeCOH + changeSo
                totalCations = changeNa + changK + changeCa + changeMg
                totalAnions = changeCl + changeCo + changeCOH + changeSo
                allArr = filter_number(simpleData['全盐量'])  # 单位g/kg
                resData_8 = pd.DataFrame({
                    '样品编号': simpleData['原样品编号'],
                    'pH': simpleData['pH'],
                    '水溶性全盐量g/kg': allArr,
                    '电导率ms/cm': simpleData['电导率'],
                    '水溶性钠离子含量Cmol(Na+)/kg': naArr,
                    '水溶性钾离子含量Cmol(K+)/kg': kArr,
                    '水溶性钙离子含量cmol(1/2Ca2+)/kg': caArr,
                    '水溶性镁离子Cmol(1/2Mg2+)/kg': mgArr,
                    '水溶性氯离子含量cmol(Cl-)/kg': clArr,
                    '水溶性碳酸根离子含量cmol(1/2CO32+)/kg': coArr,
                    '水溶性碳酸氢离子含量cmol(1/2HCO3-)/kg': hcoArr,
                    '水溶性硫酸根离子含量cmol(1/2SO42-)/kg': soArr,
                    '八大离子加和g/kg': eightPlusArr,
                    '(水溶性全盐量-八大离子加和)/水溶性全盐量×100': (allArr - eightPlusArr) / allArr * 100,
                    '离子总量g/kg': filter_number(simpleData['离子总量']),
                    '阳离子总量-阴离子总量': totalCations - totalAnions,
                    '土地利用类型': simpleData['土地利用类型']
                })
                # 调用判断函数
                res_value_8 = pb.eight_ion_coun(resData_8, simpleData)
                resData_8 = resData_8.reset_index(drop=True)
                resData_8['审核结果'] = res_value_8['审核结果']
                global resData_8_Style
                global table_8_data
                table_8_data = resData_8
                # 提取异常数据
                global table_8_index
                # table_8_index = pd.DataFrame({
                #     '样品编号': simpleData['样品编号'],
                #     '指标': res_value_8['异常指标'],
                #     '原因': res_value_8['审核结果']
                # })
                # table_8_index['样品编号'] = simpleData['样品编号']
                table_8_index['指标'] = res_value_8['异常指标']
                table_8_index['原因'] = res_value_8['审核结果']
                resData_8_Style = resData_8.style.apply(highlight_condition, axis=1)

                # 表7--------------------------表7 数据频度分析----------------------------------------
                global resData_7
                resData_7 = frequency_analysis(resData_8[['水溶性全盐量g/kg', '电导率ms/cm']])

                # 表10--------------------------表10 有机质、全氮、全磷、全钾数据------------------------
                resData_10 = pd.DataFrame({
                    '编号': simpleData['原样品编号'],
                    '有机质g/kg': simpleData['有机质'],
                    '全氮g/kg': simpleData['全氮'],
                    '全磷g/kg': simpleData['全磷'],
                    '有效磷g/kg': simpleData['有效磷'],
                    '全钾g/kg': simpleData['全钾'],
                    '缓效钾mg/kg': simpleData['缓效钾'],
                    '速效钾mg/kg': simpleData['速效钾'],
                    'pH': simpleData['pH'],
                    '母质': simpleData['母质'],
                    '土地利用类型': simpleData['土地利用类型'],
                    '交换性钾': simpleData['交换性钾'],
                    '阳离子交换量': simpleData['阳离子交换量']
                })
                # 调用判断函数
                res_value_10 = pb.nutrient_data(resData_10)
                resData_10 = resData_10.reset_index(drop=True)
                resData_10['审核结果'] = res_value_10['审核结果']

                # 写入表格
                global resData_10_Style
                global table_10_data
                table_10_data = resData_10
                # 提取异常数据
                global table_10_index
                # table_10_index = pd.DataFrame({
                #     '样品编号': simpleData['样品编号'],
                #     '指标': res_value_10['异常指标'],
                #     '原因': res_value_10['审核结果']
                # })
                # table_10_index['样品编号'] = simpleData['样品编号']
                table_10_index['指标'] = res_value_10['异常指标']
                table_10_index['原因'] = res_value_10['审核结果']
                resData_10_Style = resData_10.style.apply(highlight_condition, axis=1)
                # 表9--------------------------表9----------------------------------------
                # 计算频度数据
                global resData_9
                resData_9 = frequency_analysis(resData_10[
                                                      ['有机质g/kg', '全氮g/kg', '全磷g/kg', '有效磷g/kg', '全钾g/kg',
                                                       '缓效钾mg/kg', '速效钾mg/kg']])
                # 表12--------------------------表12 土壤指标含量----------------------------------------
                resData_12 = pd.DataFrame({
                    '编号': simpleData['原样品编号'],
                    'pH': simpleData['pH'],
                    '母质': simpleData['母质'],
                    '有机质': simpleData['有机质'],
                    '全氮': simpleData['全氮'],
                    '全磷': simpleData['全磷'],
                    '全钾': simpleData['全钾'],
                    '有效磷': simpleData['有效磷'],
                    '速效钾': simpleData['速效钾'],
                    '缓效钾': simpleData['缓效钾'],
                    '有效硫mg/kg': simpleData['有效硫'],
                    '有效硅mg/kg': simpleData['有效硅'],
                    '有效铁mg/kg': simpleData['有效铁'],
                    '有效锰mg/kg': simpleData['有效锰'],
                    '有效铜mg/kg': simpleData['有效铜'],
                    '有效锌mg/kg': simpleData['有效锌'],
                    '有效硼mg/kg': simpleData['有效硼'],
                    '有效钼mg/kg': simpleData['有效钼']
                })

                # 调用判断函数
                res_value_12 = pb.soil_metal(resData_12)
                resData_12 = resData_12.reset_index(drop=True)
                resData_12['审核结果'] = res_value_12['审核结果']
                global resData_12_Style
                global table_12_data
                table_12_data = resData_12
                # 提取异常数据
                global table_12_index
                # table_12_index = pd.DataFrame({
                #     '样品编号': simpleData['样品编号'],
                #     '指标': res_value_12['异常指标'],
                #     '原因': res_value_12['审核结果']
                # })
                # table_12_index['样品编号'] = simpleData['样品编号']
                table_12_index['指标'] = res_value_12['异常指标']
                table_12_index['原因'] = res_value_12['审核结果']
                resData_12_Style = resData_12.style.apply(highlight_condition, axis=1)

                # 写入表格
                # 表11--------------------------表11 土壤指标频度分析----------------------------------------
                global resData_11
                resData_11 = frequency_analysis(resData_12[['有效硅mg/kg', '有效铁mg/kg', '有效锰mg/kg', '有效铜mg/kg',
                                                          '有效锌mg/kg', '有效硼mg/kg', '有效钼mg/kg']])

                # 表14--------------------------表14 土壤重金属指标----------------------------------------
                resData_14 = pd.DataFrame({
                    '编号': simpleData['原样品编号'],
                    '母质': simpleData['母质'],
                    '土地利用类型': simpleData['土地利用类型'],
                    'pH': simpleData['pH'],
                    '镉mg/kg': simpleData['总镉'],
                    '汞mg/kg': simpleData['总汞'],
                    '砷mg/kg': simpleData['总砷'],
                    '铅mg/kg': simpleData['总铅'],
                    '铬mg/kg': simpleData['总铬'],
                    '镍mg/kg': simpleData['总镍']
                })
                # 调用判断函数
                res_value_14 = pb.last_metal(resData_14)
                resData_14 = resData_14.reset_index(drop=True)
                resData_14['审核结果'] = res_value_14['审核结果']
                global resData_14_Style
                global table_14_data
                table_14_data = resData_14
                # 提取异常数据
                global table_14_index
                # table_14_index = pd.DataFrame({
                #     '样品编号': simpleData['样品编号'],
                #     '指标': res_value_14['异常指标'],
                #     '原因': res_value_14['审核结果']
                # })
                # table_14_index['样品编号'] = simpleData['样品编号']
                table_14_index['指标'] = res_value_14['异常指标']
                table_14_index['原因'] = res_value_14['审核结果']
                resData_14_Style = resData_14.style.apply(highlight_condition, axis=1)
                # 写入表格
                # 表13--------------------------表13 土壤重金属频度分析----------------------------------------
                global resData_13

                resData_13 = frequency_analysis(
                    resData_14[['镉mg/kg', '汞mg/kg', '砷mg/kg', '铅mg/kg', '铬mg/kg', '镍mg/kg']])

                show_info('文件审核完成，请点击保存按钮保存文件！')
        else:
            #提示文件为空 重新选择
            print("Excel 文件为空。")
    except Exception as err:
        print('审核过程中出错！', err)
        show_error(f'审核过程中出错！错误原因：{err}')


def makeNormalWord(url):
    # 根据提取数据 合并数据 生成报告
    length = len(table_1_index)
    emptyArr = [np.nan for i in range(length)]
    indexArr = pd.RangeIndex(start=1, stop=length+1)

    newData = pd.DataFrame({
        '序号': indexArr,
        '原样品编号': table_1_index['原样品编号'],
        '样品编号': table_1_index['样品编号'],
        '土地利用类型': table_1_index['土地利用类型'],
        '指标': table_1_index['指标'] + table_3_index['指标'] + table_5_index['指标'] + table_8_index['指标'] + table_10_index['指标'] + table_12_index['指标'] + table_14_index['指标'],
        '原因': table_1_index['原因'] + table_3_index['原因'] + table_5_index['原因'] + table_8_index['原因'] + table_10_index['原因'] + table_12_index['原因'] + table_14_index['原因'],
        '结合外业调查及相关信息评价': emptyArr,
        '数据判定': emptyArr
    })
    newData = newData.replace(np.nan, '')
    print('newData----', newData)
    name = os.path.basename(changeFileUrl)
    n = name.split('.')
    areaName = n[0].replace('数据', '')
    # 新建文档
    doc = Document()

    # 获取文档的第一个节（默认新建文档只有一个节）
    section = doc.sections[0]
    # 设置页面方向为横向
    new_width, new_height = section.page_height, section.page_width
    section.orientation = WD_ORIENTATION.LANDSCAPE
    section.page_width = new_width
    section.page_height = new_height

    # 添加标题
    doc.add_heading(f"{areaName}数据审核过程存疑数据一览表", level=0).bold = True
    table_1 = doc.add_table(rows=length + 1, cols=7, style='Light Shading Accent 1')
    table_1.alignment = WD_TABLE_ALIGNMENT.CENTER
    # 遍历表格 插入数据
    # 遍历表格的所有单元格，并填充内容
    for i, row in enumerate(table_1.rows):
        for j, cell in enumerate(row.cells):
            # 获取单元格中的段落对象
            paragraph = cell.paragraphs[0]
            if i == 0:
                r = paragraph.add_run(str(newData.columns[j]))
                r.font.bold = True
            else:
                r = paragraph.add_run(str(newData.iloc[i - 1, j]))
            r.font.size = Pt(10.5)
            r.font.name = 'Times New Roman'
            r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
            paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
            paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
            paragraph.paragraph_format.line_spacing = 1  # 段落行间距
    # 保存文件
    doc.save(f'{url}/{areaName}存疑数据一览表.docx')

################一下是GUI部分-----------------------------------------------------------------------------

# 选择文件
def open_file():
    # 这里限制只能取 excel文件
    filetypes = (
        ('excel files', '*.xlsx'),
        ('All files', '*.xlsx*')
    )
    # 指定文件路径
    file_path = 'config/fileurl.txt'  # 修改为你的文件路径
    # 读取整个文件内容
    with open(file_path, 'r', encoding='utf-8') as file:
        content = file.read()
    filename = filedialog.askopenfilename(
        title='选择文件',
        initialdir=content,  # D:/实验室/16、三普
        filetypes=filetypes)
    # 提取目录部分
    directory_path = os.path.dirname(filename)
    # 打开文件并写入内容
    with open(file_path, 'w', encoding='utf-8') as file:
        file.write(directory_path)
    # 这里增加判断 若文件为空提示错误
    simpleData = pd.read_excel(filename)
    if not simpleData.empty:
        global changeFileUrl
        changeFileUrl = filename
        titleList = ['序号',	'原样品编号',	'样品编号','地理位置','土壤类型','母岩','母质','土地利用类型','洗失量（吸管法需填）',
                     '2~0.2mm颗粒含量','0.2~0.02mm颗粒含量','0.02~0.002mm颗粒含量','0.002mm以下颗粒含量','土壤质地',
                     '风干试样含水量（分析基）','pH','阳离子交换量','交换性盐基总量','交换性钙','交换性镁','交换性钠',
                     '交换性钾','全盐量','电导率','水溶性Na⁺含量','水溶性K⁺含量','水溶性Ca²⁺含量','水溶性Mg²⁺含量',
                     '水溶性Cl⁻含量','水溶性CO₃²⁻含量','水溶性HCO₃⁻含量','水溶性SO₄²⁻含量', '离子总量','有机质','全氮',
                     '全磷','全钾','全硒','有效磷','速效钾','缓效钾',	'有效硫','有效硅','有效铁',	'有效锰','有效铜','有效锌',
                     '有效硼','有效钼','碳酸钙','总汞','总砷','总铅','总镉','总铬','总镍','土壤容重1（g/cm³）','土壤容重2（g/cm³）',
                     '土壤容重3（g/cm³）','土壤容重4（g/cm³）','土壤容重平均值（g/cm³）','水稳>5mm（%）','水稳3mm~5mm（%）',
                     '水稳2mm~3mm（%）','水稳1mm~2mm（%）','水稳0.5mm~1mm（%）','水稳0.25mm~0.5mm（%）','水稳性大团聚体总和（%）']
        # 也可以增加文件内容判断 格式不正确 提示错误 这里验证表头
        errList = []
        for item in simpleData.columns:
            if item not in titleList:
                errList.append(item)
        if len(errList) > 0:
            show_info(f'{errList}以上指标格式错误，请按照以下格式重新填写表头：{titleList}以保证审核流程正确执行！')
        else:
            # 验证通过 提示框展示文件名称
            show_info('文件选择完成，点击审核按钮开始审核！')
    else:
        show_error('文件为空，请检查文件！')


# 设置字体的函数
def set_font(cell):
    cell.paragraphs[0].runs[0].font.name = "Times New Roman"  # 设置英文字体
    cell.paragraphs[0].runs[0].font.size = Pt(9)  # 字体大小
    cell.paragraphs[0].runs[0]._element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')  # 设置中文字体

# 生成报告
def getReport(originData,data,changeFileUrl, saveFileUrl, check_1_data,
    check_3_data,
    check_5_data ,
    check_8_data,  # 样品编号替换为编号
    check_10_data,
    check_12_data,
    check_14_data ):
    # 根据选择的路径读取数据
    data['原样品编号'] = data['原样品编号'].astype(str)
    # checkData = pd.read_excel(changeFileUrl, sheet_name='检测方法')
    # 生成报告
    name = os.path.basename(changeFileUrl)
    n = name.split('.')
    areaName = n[0].replace('数据', '')
    # 生成一个新的文件夹用于存放审核报告相关的数据
    nowTime = time.strftime("%Y-%m-%d %H时%M分%S秒", time.localtime())
    dir_name = f'{areaName}数据审核报告'
    mkdir_path = saveFileUrl + '/' + dir_name + nowTime
    if not os.path.exists(mkdir_path):
        os.mkdir(mkdir_path)
    # 上面这个地址，可以纯递给函数中，用于保存表格和图片
    # 调用函数 开始生成报告相关内容
    # 表1相关数据
    typeData = report.getSimpleNum(data)
    lenNum_1 = len(typeData['sData'])
    lenNum_1_f = len(typeData['allData'])
    table_1_data = pd.DataFrame({
        '类型': typeData['sData'].index,
        '数量': typeData['sData'],
        '合计': [typeData['sData'].sum() for _ in range(lenNum_1)]
    })
    # 表2数据
    table_2_data = report.getDataComplete(data)
    table_2_data = table_2_data.reset_index()
    table_2_data.columns = ['指标名称', '实测数量', '应测数量']
    # 表3数据
    # table_3_data = report.checkMethod(checkData, mkdir_path)
    # 数据修约 表4  这里需要使用未处理过格式的原始数据 这里传源数据进来
    report.getNum(originData, mkdir_path)
    # 数据填报项审核 表5
    report.dataReportResult(data, mkdir_path)
    # 表6数据 土壤质地类型不一致
    middData = data[['原样品编号', '样品编号']].astype(str)
    middData['编号'] = middData['原样品编号']
    del middData['原样品编号']
    check_1_data = pd.merge(check_1_data,middData, how='left', on='编号')
    check_1_data = check_1_data.replace(np.nan,'')
    typeNotSame = check_1_data[check_1_data['土壤质地'] != check_1_data['土壤质地(判断)']]
    table_6_data = typeNotSame[['编号','样品编号', '土壤质地', '土壤质地(判断)']]

    allNeedData = pd.DataFrame({})
    allNeedData['原样品编号'] = check_1_data['编号']
    getSimpleDataNumber = pd.merge(allNeedData, data[['原样品编号', '样品编号']], how='left', on="原样品编号")
    allNeedData['样品编号'] = getSimpleDataNumber['样品编号']
    allNeedData['土地利用类型'] = check_1_data['土地利用类型']
    allNeedData['审核结果'] = check_1_data['审核结果'] + check_3_data['审核结果'] + check_5_data['审核结果'] + check_8_data['审核结果'] + check_10_data['审核结果'] + check_12_data['审核结果'] + check_14_data['审核结果']
    allNeedData['外业'] = ['' for _ in range(len(check_1_data))]
    table_7_data = allNeedData[allNeedData['审核结果'] != '']
    del table_7_data['审核结果']
    # 写进表格
    with pd.ExcelWriter(f'{mkdir_path}/超阈值样品统计表.xlsx', engine='openpyxl') as writer:
        table_7_data.to_excel(writer, index=False, sheet_name='超阈值数据')
    # 表8数据
    table_8_data = report.getPHData(data, mkdir_path)
    # 表10 数据
    table_10_data = report.getNAndC(data, mkdir_path)
    # 表11 数据：全磷和有效磷异常数据统计
    table_11_data = report.getPData(data, mkdir_path)
    # 表12数据 重金属超标
    caOverData = pd.merge(check_1_data[['编号','土地利用类型']],check_14_data[['编号','pH','镉mg/kg','汞mg/kg',	'砷mg/kg','铅mg/kg',	'铬mg/kg','镍mg/kg',	'审核结果']] , how='outer', on=['编号'])
    caOverData['原样品编号'] = caOverData['编号']
    caOverData = pd.merge(caOverData, data[['原样品编号', '样品编号']], how='left', on='原样品编号')
    first_column = caOverData.pop('样品编号')
    caOverData.insert(0, '样品编号', first_column)
    caOverData_need = caOverData[caOverData['审核结果'] != '']
    report.getKData(data, mkdir_path)
    report.cationExchangeCapacity(data,mkdir_path)
    report.changeCation(data, mkdir_path)
    report.manyTypes(data, mkdir_path)
    # 写进表格
    with pd.ExcelWriter(f'{mkdir_path}/重金属超筛选值情况统计.xlsx', engine='openpyxl') as writer:
        caOverData_need.to_excel(writer, index=False, sheet_name='重金属超筛选值情况统计')
    # 表13 所有存疑数据
    with pd.ExcelWriter(f'{mkdir_path}/数据审核过程存疑数据一览表.xlsx', engine='openpyxl') as writer:
        allNeedData[allNeedData['审核结果'] != ''].to_excel(writer, index=False, sheet_name='存疑数据')
    # 附表： 频度分析图
    report.getFrequencyImage(data, mkdir_path)
    table_f_2_data = report.getFrequencyInformation(data, mkdir_path)

    # 新建一个文档
    doc = Document()
    # 添加标题
    doc.add_heading(f"{areaName}第三次全国土壤普查数据审核报告", level=0)
    # 添加一级标题
    doc.add_heading('一、数据完整性审核', level=1)
    doc.add_heading('1、土地利用类型与检测指标符合性审核', level=2)
    # 插入表格1
    paragraph_1 = doc.add_paragraph()
    paragraph_1.add_run(f"表1：{areaName}三普样品数量统计表（表层）").bold = True
    # 设置居中
    paragraph_1.alignment = WD_ALIGN_PARAGRAPH.CENTER
    table_1 = doc.add_table(rows=lenNum_1 +1, cols=3, style='Light Shading Accent 1')
    table_1.alignment = WD_TABLE_ALIGNMENT.CENTER
    # 遍历表格 插入数据
    # 遍历表格的所有单元格，并填充内容
    for i, row in enumerate(table_1.rows):
        for j, cell in enumerate(row.cells):
            # 获取单元格中的段落对象
            paragraph = cell.paragraphs[0]
            if i == 0:
                r = paragraph.add_run(str(table_1_data.columns[j]))
                r.font.bold = True
            else:
                r = paragraph.add_run(str(table_1_data.iloc[i-1, j]))
            r.font.size = Pt(10.5)
            r.font.name = 'Times New Roman'
            r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
            paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
            paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
            paragraph.paragraph_format.line_spacing = 1  # 段落行间距
    #合并单元格 合并第3列的第二行和第三行
    if lenNum_1 > 1:
        table_1.cell(2, 2).text= ''
        table_1.cell(1, 2).merge(table_1.cell(2, 2))
    # table_1.cell(1, 2).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
    # table_1.cell(2, 2).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
    ############test##############
    doc.add_heading('2、指标名称与实际检测样品数量完整性审核', level=2)
    # 插入表格2
    paragraph_2 = doc.add_paragraph()
    paragraph_2.add_run(f'表2：{areaName}指标名称与实际检测样品数量统计表').bold = True
    table_2 = doc.add_table(rows=len(table_2_data) + 1, cols=3, style='Light Shading Accent 1')
    paragraph_2.alignment = WD_ALIGN_PARAGRAPH.CENTER
    table_2.alignment = WD_TABLE_ALIGNMENT.CENTER
    for i, row in enumerate(table_2.rows):
        for j, cell in enumerate(row.cells):
            # 获取单元格中的段落对象
            paragraph = cell.paragraphs[0]
            if i == 0:
                r = paragraph.add_run(str(table_2_data.columns[j]))
                r.font.bold = True
            else:
                r = paragraph.add_run(str(table_2_data.iloc[i-1, j]))
            paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
            paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
            paragraph.paragraph_format.line_spacing = 1  # 段落行间距
            r.font.size = Pt(10.5)
            r.font.name = 'Times New Roman'
            r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')


    doc.add_heading('二、数据规范性审核', level=1)
    doc.add_heading('1、数据填报规范性审核', level=2)
    # 插入表3
    paragraph_3 = doc.add_paragraph()
    paragraph_3.add_run(f'表3：{areaName}土壤检测数据检测方法填报审核结果表').bold = True
    # table_3 = doc.add_table(rows=2, cols=2)
    paragraph_3.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # table_3.alignment = WD_TABLE_ALIGNMENT.CENTER
    # 写入数据 这里数据写不下 嵌入链接
    doc.add_heading('为避免数据量过多无法显示，请至数据保存文件夹中查看数据表:检测方法审核结果.xlsx', level=4)
    doc.add_heading('2、数值修约规范性审核', level=2)
    # 插入表4
    paragraph_4 = doc.add_paragraph()
    paragraph_4.add_run(f'表4：{areaName}土壤检测数据数值修约结果表').bold = True
    # table_4 = doc.add_table(rows=2, cols=2)
    paragraph_4.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # table_4.alignment = WD_TABLE_ALIGNMENT.CENTER
    doc.add_heading('为避免数据量过多无法显示，请至数据保存文件夹中查看数据表:数值修约审核.xlsx', level=4)
    # 填入数据 这里数据也放不下 嵌入链接
    doc.add_heading('3、数据未检出的填报规范性审核', level=2)
    # 插入表5
    paragraph_5 = doc.add_paragraph()
    paragraph_5.add_run(f'表5：{areaName}土壤检测数据未检出项填报审核结果表').bold = True
    # table_5 = doc.add_table(rows=2, cols=2)
    paragraph_5.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # table_5.alignment = WD_TABLE_ALIGNMENT.CENTER
    # 写入数据 这里数据也放不下 嵌入链接
    doc.add_heading('为避免数据量过多无法显示，请至数据保存文件夹中查看数据表:数据填报项审核结果.xlsx', level=4)
    doc.add_heading('4、土壤质地填报规范性审核', level=2)
    # 插入表6
    paragraph_6 = doc.add_paragraph()
    paragraph_6.add_run(f'表6：{areaName}土壤质地填报审核结果表').bold = True
    table_6 = doc.add_table(rows=len(table_6_data)+1, cols=4, style='Light Shading Accent 1')
    paragraph_6.alignment = WD_ALIGN_PARAGRAPH.CENTER
    table_6.alignment = WD_TABLE_ALIGNMENT.CENTER
    # 提取结果表中数据
    # 写入数据 土壤质地类型不一致的数据提取出来
    for i, row in enumerate(table_6.rows):
        for j, cell in enumerate(row.cells):
            # 获取单元格中的段落对象
            paragraph = cell.paragraphs[0]
            if i == 0:
                r = paragraph.add_run(str(table_6_data.columns[j]))
                r.font.bold = True
            else:
                r=paragraph.add_run(str(table_6_data.iloc[i-1, j]))
            paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
            paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
            paragraph.paragraph_format.line_spacing = 1 # 段落行间距
            r.font.size = Pt(10.5)
            r.font.name = 'Times New Roman'
            r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
    doc.add_heading('三、数据合理性审核', level=1)
    doc.add_heading('1、阈值法审核', level=2)
    # 插入表格
    paragraph_7 = doc.add_paragraph()
    paragraph_7.add_run(f'表7：{areaName}土壤检测数据超阈值样品统计表').bold = True
    # table_7 = doc.add_table(rows=2, cols=2)
    # paragraph_7.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # table_7.alignment = WD_TABLE_ALIGNMENT.CENTER
    # 写入数据 点击查看数据 这里也不一定写的下 最好是嵌入链接
    doc.add_heading('为避免数据量过多无法显示，请至数据保存文件夹中查看数据表:数据审核过程存疑数据一览表.xlsx', level=4)
    # todo 合并所有数据 审核结果不为空的数据 写入表格保存到指定文件夹
    doc.add_heading('2、极值法审核', level=2)
    doc.add_heading('（1）pH', level=3)
    # 插入ph分布图
    if os.path.isfile(f'{mkdir_path}/PH值分布图.png'):
        doc.add_picture(f'{mkdir_path}/PH值分布图.png', width=Inches(6.0))
    paragraph_t_1 = doc.add_paragraph()
    paragraph_t_1.add_run(f'图1：pH值分布情况').bold = True
    paragraph_t_1.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # 插入频度统计表
    paragraph_8 = doc.add_paragraph()
    paragraph_8.add_run('表8：pH数据统计表').bold = True
    table_8 = doc.add_table(rows=6, cols=2, style='Light Shading Accent 1')
    t_8 = table_8_data['频度分析']
    t_8 = t_8.reset_index()
    t_8.columns = ['指标', '数据']
    paragraph_8.alignment = WD_ALIGN_PARAGRAPH.CENTER
    table_8.alignment = WD_TABLE_ALIGNMENT.CENTER
    for i, row in enumerate(table_8.rows):
        for j, cell in enumerate(row.cells):
            # 获取单元格中的段落对象
            paragraph = cell.paragraphs[0]
            if i == 0:
                r = paragraph.add_run(str(t_8.columns[j]))
                r.font.bold = True
            else:
                r=paragraph.add_run(str(t_8.iloc[i-1, j]))
            r.font.size = Pt(10.5)
            r.font.name = 'Times New Roman'
            r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
            paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
            paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
            paragraph.paragraph_format.line_spacing = 1  # 段落行间距
    # 插入异常数据提取表格 todo 这里数据多的话也可能写不下 最好是嵌入一
    t_9 = table_8_data['异常数据']
    if not t_9.empty:
        paragraph_9 = doc.add_paragraph()
        paragraph_9.add_run('表9：pH异常数据统计表').bold = True
        table_9 = doc.add_table(rows=len(table_8_data['异常数据']) + 1, cols=6, style='Light Shading Accent 1')
        paragraph_9.alignment = WD_ALIGN_PARAGRAPH.CENTER
        table_9.alignment = WD_TABLE_ALIGNMENT.CENTER
        for i, row in enumerate(table_9.rows):
            for j, cell in enumerate(row.cells):
                # 获取单元格中的段落对象
                paragraph = cell.paragraphs[0]
                if i == 0:
                    r = paragraph.add_run(str(t_9.columns[j]))
                    r.font.bold = True
                else:
                    r=paragraph.add_run(str(t_9.iloc[i-1, j]))
                r.font.size = Pt(10.5)
                r.font.name = 'Times New Roman'
                r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
                paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
                paragraph.paragraph_format.line_spacing = 1  # 段落行间距
    doc.add_heading('3、关联分析法审核', level=2)
    if os.path.isfile(f'{mkdir_path}/有机质与全氮相关性分析图.png'):
        doc.add_picture(f'{mkdir_path}/有机质与全氮相关性分析图.png', width=Inches(6.0))
    paragraph_t_2 = doc.add_paragraph()
    paragraph_t_2.add_run(f'图2：有机质与全氮相关关系').bold = True
    paragraph_t_2.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # 插入碳氮比异常数据
    if not table_10_data.empty:
        paragraph_10 = doc.add_paragraph()
        paragraph_10.add_run('表10：碳氮比异常数据统计表').bold = True
        table_10 = doc.add_table(rows=len(table_10_data)+1, cols=8, style='Light Shading Accent 1')
        paragraph_10.alignment = WD_ALIGN_PARAGRAPH.CENTER
        table_10.alignment = WD_TABLE_ALIGNMENT.CENTER
        for i, row in enumerate(table_10.rows):
            for j, cell in enumerate(row.cells):
                # 获取单元格中的段落对象
                paragraph = cell.paragraphs[0]
                if i == 0:
                    r = paragraph.add_run(str(table_10_data.columns[j]))
                    r.font.bold = True
                else:
                    r=paragraph.add_run(str(table_10_data.iloc[i-1, j]))
                r.font.size = Pt(10.5)
                r.font.name = 'Times New Roman'
                r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
                paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
                paragraph.paragraph_format.line_spacing = 1  # 段落行间距
    doc.add_heading('4、指标综合分析', level=2)
    # 插入图片
    if os.path.isfile(f'{mkdir_path}/全磷分布图.png'):
        doc.add_picture(f'{mkdir_path}/全磷分布图.png', width=Inches(6.0))
    paragraph_t_3 = doc.add_paragraph()
    paragraph_t_3.add_run(f'图3：全磷分布图').bold = True
    paragraph_t_3.alignment = WD_ALIGN_PARAGRAPH.CENTER
    if os.path.isfile(f'{mkdir_path}/有效磷分布图.png'):
        doc.add_picture(f'{mkdir_path}/有效磷分布图.png', width=Inches(6.0))
    paragraph_t_4 = doc.add_paragraph()
    paragraph_t_4.add_run(f'图4：有效磷分布图').bold = True
    paragraph_t_4.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # 插入图片
    if os.path.isfile(f'{mkdir_path}/有效磷占全磷比分布图.png'):
        doc.add_picture(f'{mkdir_path}/有效磷占全磷比分布图.png', width=Inches(6.0))
    paragraph_t_5 = doc.add_paragraph()
    paragraph_t_5.add_run(f'图5：有效磷含量占全磷含量比例').bold = True
    paragraph_t_5.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # 插入表格
    if not table_11_data.empty:
        paragraph_11 = doc.add_paragraph()
        paragraph_11.add_run('表11：全磷与有效磷异常样品统计表').bold = True
        table_11 = doc.add_table(rows=len(table_11_data)+1, cols=7, style='Light Shading Accent 1')
        paragraph_11.alignment = WD_ALIGN_PARAGRAPH.CENTER
        table_11.alignment = WD_TABLE_ALIGNMENT.CENTER
        for i, row in enumerate(table_11.rows):
            for j, cell in enumerate(row.cells):
                # 获取单元格中的段落对象
                paragraph = cell.paragraphs[0]
                if i == 0:
                    r = paragraph.add_run(str(table_11_data.columns[j]))
                    r.font.bold = True
                else:
                    r=paragraph.add_run(str(table_11_data.iloc[i-1, j]))

                r.font.size = Pt(10.5)
                r.font.name = 'Times New Roman'
                r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
                paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
                paragraph.paragraph_format.line_spacing = 1  # 段落行间距
    else:
        paragraph_11 = doc.add_paragraph()
        paragraph_11.add_run('表11：全磷与有效磷异常样品统计表').bold = True
        paragraph_11_info = doc.add_paragraph()
        paragraph_11_info.add_run('无异常数据')
        paragraph_11.alignment = WD_ALIGN_PARAGRAPH.CENTER
        paragraph_11_info.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # 全钾、速效钾、缓效钾
    if os.path.isfile(f'{mkdir_path}/全钾与速效钾缓效钾之和关系统计图.png'):
        doc.add_picture(f'{mkdir_path}/全钾与速效钾缓效钾之和关系统计图.png', width=Inches(6.0))
    paragraph_t_6 = doc.add_paragraph()
    paragraph_t_6.add_run(f'图6：全钾与速效钾缓效钾之和关系统计图').bold = True
    paragraph_t_6.alignment = WD_ALIGN_PARAGRAPH.CENTER

    if os.path.isfile(f'{mkdir_path}/速效钾与缓效钾关系统计图.png'):
        doc.add_picture(f'{mkdir_path}/速效钾与缓效钾关系统计图.png', width=Inches(6.0))
    paragraph_t_7 = doc.add_paragraph()
    paragraph_t_7.add_run(f'图7：速效钾与缓效钾关系统计图').bold = True
    paragraph_t_7.alignment = WD_ALIGN_PARAGRAPH.CENTER
    doc.add_heading('表12：重金属超筛选值情况统计', level=4)
    doc.add_heading('为避免数据量过多无法显示，请至数据保存文件夹中查看数据表:重金属超筛选值情况统计表.xlsx', level=4)
    # todo 获取重金属数据
    # 阳离子交换量与交换性盐总量关系
    if os.path.isfile(f'{mkdir_path}/阳离子交换量与交换性盐基总量相关关系.png'):
        doc.add_picture(f'{mkdir_path}/阳离子交换量与交换性盐基总量相关关系.png', width=Inches(6.0))
    paragraph_t_8 = doc.add_paragraph()
    paragraph_t_8.add_run(f'图8：阳离子交换量与交换性盐总量关系图').bold = True
    paragraph_t_8.alignment = WD_ALIGN_PARAGRAPH.CENTER
    # 交换性盐总量与交换性盐相关关系
    if os.path.isfile(f'{mkdir_path}/交换性盐基总量与交换性盐相关关系(pH小于等于7.5).png'):
        doc.add_picture(f'{mkdir_path}/交换性盐基总量与交换性盐相关关系(pH小于等于7.5).png', width=Inches(6.0))
    paragraph_t_9 = doc.add_paragraph()
    paragraph_t_9.add_run(f'图9：交换性盐基总量和交换性钙镁钠钾分项指标关系（pH≤7.5）').bold = True
    paragraph_t_9.alignment = WD_ALIGN_PARAGRAPH.CENTER

    if os.path.isfile(f'{mkdir_path}/交换性盐基总量与交换性盐相关关系(pH大于7.5).png'):
        doc.add_picture(f'{mkdir_path}/交换性盐基总量与交换性盐相关关系(pH大于7.5).png', width=Inches(6.0))
    paragraph_t_10 = doc.add_paragraph()
    paragraph_t_10.add_run(f'图10：交换性盐基总量和交换性钙镁钠钾分项指标关系（pH大于7.5）').bold = True
    paragraph_t_10.alignment = WD_ALIGN_PARAGRAPH.CENTER

    # 水溶性盐、电导率、离子总量
    if os.path.isfile(f'{mkdir_path}/全盐量分布图.png'):
        doc.add_picture(f'{mkdir_path}/全盐量分布图.png', width=Inches(6.0))
    paragraph_t_11 = doc.add_paragraph()
    paragraph_t_11.add_run(f'图11：全盐量分布图').bold = True
    paragraph_t_11.alignment = WD_ALIGN_PARAGRAPH.CENTER

    if os.path.isfile(f'{mkdir_path}/全盐量与电导率相关性分析图.png'):
        doc.add_picture(f'{mkdir_path}/全盐量与电导率相关性分析图.png', width=Inches(6.0))
    paragraph_t_12 = doc.add_paragraph()
    paragraph_t_12.add_run(f'图12：全盐量与电导率相关性分析图').bold = True
    paragraph_t_12.alignment = WD_ALIGN_PARAGRAPH.CENTER

    if os.path.isfile(f'{mkdir_path}/离子总量与水溶性盐总量关系图.png'):
        doc.add_picture(f'{mkdir_path}/离子总量与水溶性盐总量关系图.png', width=Inches(6.0))
    paragraph_t_13 = doc.add_paragraph()
    paragraph_t_13.add_run(f'图13：水溶性盐总量与离子总量关系分析图').bold = True
    paragraph_t_13.alignment = WD_ALIGN_PARAGRAPH.CENTER

    doc.add_heading('四、审核存疑数据', level=1)
    paragraph_12 = doc.add_paragraph()
    paragraph_12.add_run(f'表13：数据审核过程存疑数据一览表').bold = True
    paragraph_12.alignment = WD_ALIGN_PARAGRAPH.CENTER
    doc.add_heading('为避免数据量过多无法显示，请至数据保存文件夹中查看数据表:数据审核过程存疑数据一览表.xlsx', level=4)
    doc.add_heading('五、附表', level=1)
    doc.add_heading('附表1：某区三普样品数量统计表（表层）', level=2)
    # 插入附表1
    table_1_f = doc.add_table(rows=lenNum_1 +1, cols=3, style='Light Shading Accent 1')
    table_1_f.alignment = WD_TABLE_ALIGNMENT.CENTER
    # 遍历表格 插入数据
    # 遍历表格的所有单元格，并填充内容
    for i, row in enumerate(table_1_f.rows):
        for j, cell in enumerate(row.cells):
            # 获取单元格中的段落对象
            paragraph = cell.paragraphs[0]
            if i == 0:
                r = paragraph.add_run(str(table_1_data.columns[j]))
                r.font.bold = True
            else:
                r = paragraph.add_run(str(table_1_data.iloc[i-1, j]))
            r.font.size = Pt(10.5)
            r.font.name = 'Times New Roman'
            r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
            paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
            paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
            paragraph.paragraph_format.line_spacing = 1  # 段落行间距
    #合并单元格 合并第3列的第二行和第三行
    if lenNum_1 >1 :
        table_1_f.cell(2, 2).text = ''
        table_1_f.cell(1, 2).merge(table_1_f.cell(2, 2))
    doc.add_heading('附表2：各指标频度分析表', level=2)
    # 插入表格 写入数据
    table_f_2_data = table_f_2_data.replace(np.nan, '')
    # table_f_2 = doc.add_table(rows=len(table_f_2_data)+1, cols=6, style='Light Shading Accent 1')
    rows = (int(len(table_f_2_data.columns) / 6)+1)
    columnsList = np.arange(0, rows * 6, 6)
    dataList = []

    for i in columnsList:
        res = table_f_2_data.iloc[:, i:i + 6]
        res = res.reset_index()
        dataList.append(res)
    table_f_2 = doc.add_table(rows=rows * 6, cols=7, style='Light Shading Accent 1')
    for i, row in enumerate(table_f_2.rows):
        for j, cell in enumerate(row.cells):
            # 获取单元格中的段落对象
            paragraph = cell.paragraphs[0]
            if i == columnsList[0]:
                # 第一行 显示前6个指标的列名
                if len(dataList[0].columns) > j:
                    r = paragraph.add_run(dataList[0].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) > 1 and i > columnsList[0] and i < columnsList[1]:
                if len(dataList[0].columns) > j:
                    r = paragraph.add_run(str(dataList[0].iloc[i - 1, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif i == columnsList[1]:
                # 第6行 显示前6个指    标的列名
                if len(dataList[1].columns) > j:
                    r = paragraph.add_run(dataList[1].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) > 2 and i > columnsList[1] and i < columnsList[2]:
                if len(dataList[1].columns) > j:
                    r = paragraph.add_run(str(dataList[1].iloc[i - 7, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif i == columnsList[2]:
                # 第6*2行 显示前6个指    标的列名
                if len(dataList[2].columns) > j:
                    r = paragraph.add_run(dataList[2].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) > 3 and i > columnsList[2] and i < columnsList[3]:
                if len(dataList[2].columns) > j:
                    r = paragraph.add_run(str(dataList[2].iloc[i - 13, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif i == columnsList[3]:
                # 第6*3行 显示前6个指    标的列名
                if len(dataList[3].columns) > j:
                    r = paragraph.add_run(dataList[3].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) > 4 and i > columnsList[3] and i < columnsList[4]:
                if len(dataList[3].columns) > j:
                    r = paragraph.add_run(str(dataList[3].iloc[i - 19, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif i == columnsList[4]:
                # 第6*4行 显示前6个指    标的列名
                if len(dataList[4].columns) > j:
                    r = paragraph.add_run(dataList[4].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) > 5 and i > columnsList[4] and i < columnsList[5]:
                if len(dataList[4].columns) > j:
                    r = paragraph.add_run(str(dataList[4].iloc[i - 25, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif i == columnsList[5]:
                # 第6*5行 显示前6个指 标的列名
                if len(dataList[5].columns) > j:
                    r = paragraph.add_run(dataList[5].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) > 6 and i > columnsList[5] and i < columnsList[6]:
                if len(dataList[5].columns) > j:
                    r = paragraph.add_run(str(dataList[5].iloc[i - 31, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif i == columnsList[6]:
                # 第6*6行 显示前6个指    标的列名
                if len(dataList[6].columns) > j:
                    r = paragraph.add_run(dataList[6].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) > 7 and i > columnsList[6] and i < columnsList[7]:
                if len(dataList[6].columns) > j:
                    r = paragraph.add_run(str(dataList[6].iloc[i - 37, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif i == columnsList[7]:
                # 第6*7行 显示前6个指    标的列名
                if len(dataList[7].columns) > j:
                    r = paragraph.add_run(dataList[7].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) >= 8 and i > columnsList[7] and i < columnsList[8]:
                if len(dataList[7].columns) > j:
                    r = paragraph.add_run(str(dataList[7].iloc[i - 43, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif i == columnsList[8]:
                if len(dataList[8].columns) > j:
                    # 第6*8行 显示前6个指    标的列名
                    r = paragraph.add_run(dataList[8].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) >= 9 and i > columnsList[8] and i < columnsList[9]:
                if len(dataList[8].columns) > j:
                    r = paragraph.add_run(str(dataList[8].iloc[i - 49, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif i == columnsList[9]:
                # 第6*9行 显示前6个指    标的列名
                if len(dataList[9].columns) > j:
                    r = paragraph.add_run(dataList[9].columns[j])
                    r.font.bold = True
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            elif len(columnsList) >= 10 and i > columnsList[9] and i <= 60:
                if len(dataList[9].columns) > j:

                    r = paragraph.add_run(str(dataList[9].iloc[i - 55, j]))
                    r.font.size = Pt(10.5)
                    r.font.name = 'Times New Roman'
                    r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
                else:
                    paragraph.add_run('')
            paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
            paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
            paragraph.paragraph_format.line_spacing = 1  # 段落行间距


    # for i, row in enumerate(table_f_2.rows):
    #     for j, cell in enumerate(row.cells):
    #         # 获取单元格中的段落对象
    #         paragraph = cell.paragraphs[0]
    #         if i == 0:
    #             r = paragraph.add_run(str(table_f_2_data.columns[j]))
    #             r.font.bold = True
    #         else:
    #             r=paragraph.add_run(str(table_f_2_data.iloc[i-1, j]))
    #         r.font.size = Pt(10.5)
    #         r.font.name = 'Times New Roman'
    #         r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
    #         paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
    #         paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
    #         paragraph.paragraph_format.line_spacing = 1  # 段落行间距

    # doc.add_heading('为避免数据量过多无法显示，请至数据保存文件夹中查看数据表:频度分析表.xlsx', level=4)
    doc.add_heading('附表3：各指标频度分析图', level=2)
    # 插入频度信息的图形
    if os.path.isfile(f'{mkdir_path}/0.002mm以下颗粒含量分析图.png'):
        doc.add_picture(f'{mkdir_path}/0.002mm以下颗粒含量分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/0.02~0.002mm颗粒含量分析图.png.png'):
        doc.add_picture(f'{mkdir_path}/0.02~0.002mm颗粒含量分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/0.2~0.02mm颗粒含量分析图.png'):
        doc.add_picture(f'{mkdir_path}/0.2~0.02mm颗粒含量分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/2~0.2mm颗粒含量分析图.png'):
        doc.add_picture(f'{mkdir_path}/2~0.2mm颗粒含量分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/pH分析图.png'):
        doc.add_picture(f'{mkdir_path}/pH分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/电导率分析图.png'):
        doc.add_picture(f'{mkdir_path}/电导率分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/风干试样含水量(分析基)分析图.png'):
        doc.add_picture(f'{mkdir_path}/风干试样含水量(分析基)分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/缓效钾分析图.png'):
        doc.add_picture(f'{mkdir_path}/缓效钾分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/交换性钙分析图.png'):
        doc.add_picture(f'{mkdir_path}/交换性钙分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/交换性钾分析图.png'):
        doc.add_picture(f'{mkdir_path}/交换性钾分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/交换性镁分析图.png'):
        doc.add_picture(f'{mkdir_path}/交换性镁分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/交换性钠分析图.png'):
        doc.add_picture(f'{mkdir_path}/交换性钠分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/交换性盐基总量分析图.png'):
        doc.add_picture(f'{mkdir_path}/交换性盐基总量分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/全氮分析图.png'):
        doc.add_picture(f'{mkdir_path}/全氮分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/全钾分析图.png'):
        doc.add_picture(f'{mkdir_path}/全钾分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/全磷分析图.png'):
        doc.add_picture(f'{mkdir_path}/全磷分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/全盐量分析图.png'):
        doc.add_picture(f'{mkdir_path}/全盐量分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/速效钾分析图.png'):
        doc.add_picture(f'{mkdir_path}/速效钾分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/洗失量（吸管法需填）分析图.png'):
        doc.add_picture(f'{mkdir_path}/洗失量（吸管法需填）分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/阳离子交换量分析图.png'):
        doc.add_picture(f'{mkdir_path}/阳离子交换量分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有机质分析图.png'):
        doc.add_picture(f'{mkdir_path}/有机质分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有效硅分析图.png'):
        doc.add_picture(f'{mkdir_path}/有效硅分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有效磷分析图.png'):
        doc.add_picture(f'{mkdir_path}/有效磷分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有效硫分析图.png'):
        doc.add_picture(f'{mkdir_path}/有效硫分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有效锰分析图.png'):
        doc.add_picture(f'{mkdir_path}/有效锰分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有效钼分析图.png'):
        doc.add_picture(f'{mkdir_path}/有效钼分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有效硼分析图.png'):
        doc.add_picture(f'{mkdir_path}/有效硼分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有效铁分析图.png'):
        doc.add_picture(f'{mkdir_path}/有效铁分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有效铜分析图.png'):
        doc.add_picture(f'{mkdir_path}/有效铜分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/有效锌分析图.png'):
        doc.add_picture(f'{mkdir_path}/有效锌分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/总镉分析图.png'):
        doc.add_picture(f'{mkdir_path}/总镉分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/总铬分析图.png'):
        doc.add_picture(f'{mkdir_path}/总铬分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/总汞分析图.png'):
        doc.add_picture(f'{mkdir_path}/总汞分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/总镍分析图.png'):
        doc.add_picture(f'{mkdir_path}/总镍分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/总砷分析图.png'):
        doc.add_picture(f'{mkdir_path}/总砷分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/总铅分析图.png'):
        doc.add_picture(f'{mkdir_path}/总铅分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/土壤容重1分析图.png'):
        doc.add_picture(f'{mkdir_path}/土壤容重1分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/土壤容重2分析图.png'):
        doc.add_picture(f'{mkdir_path}/土壤容重2分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/土壤容重3分析图.png'):
        doc.add_picture(f'{mkdir_path}/土壤容重3分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/土壤容重4分析图.png'):
        doc.add_picture(f'{mkdir_path}/土壤容重4分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/土壤容重平均值分析图.png'):
        doc.add_picture(f'{mkdir_path}/土壤容重平均值分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/水稳0.5mm~1mm分析图.png'):
        doc.add_picture(f'{mkdir_path}/水稳0.5mm~1mm分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/水稳0.25mm~0.5mm分析图.png'):
        doc.add_picture(f'{mkdir_path}/水稳0.25mm~0.5mm分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/水稳1mm~2mm分析图.png'):
        doc.add_picture(f'{mkdir_path}/水稳1mm~2mm分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/水稳2mm~3mm分析图.png'):
        doc.add_picture(f'{mkdir_path}/水稳2mm~3mm分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/水稳3mm~5mm分析图.png'):
        doc.add_picture(f'{mkdir_path}/水稳3mm~5mm分析图.png', width=Inches(6.0))
    if os.path.isfile(f'{mkdir_path}/水稳5mm分析图.png'):
        doc.add_picture(f'{mkdir_path}/水稳5mm分析图.png', width=Inches(6.0))
    doc.add_heading('附表4：数值修约标准', level=2)
    # 读取数据 插入表格 写入数据
    numData = pd.read_excel('./img/数值修约要求.xlsx', sheet_name='Sheet1')
    table_2_f = doc.add_table(rows=len(numData)+1, cols=2, style='Light Shading Accent 1')
    table_2_f.alignment = WD_TABLE_ALIGNMENT.CENTER
    for i, row in enumerate(table_2_f.rows):
        for j, cell in enumerate(row.cells):
            # 获取单元格中的段落对象
            paragraph = cell.paragraphs[0]
            if i == 0:
                r = paragraph.add_run(str(numData.columns[j]))
                r.font.bold = True
            else:
                r=paragraph.add_run(str(numData.iloc[i-1, j]))
            r.font.size = Pt(10.5)
            r.font.name = 'Times New Roman'
            r.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
            paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
            paragraph.paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER  # 对齐
            paragraph.paragraph_format.line_spacing = 1  # 段落行间距
    # 处理样式 遍历所有的段落 修改字体
    # 遍历并打印每个段落的文本
    paragraphs = doc.paragraphs
    for paragraph in paragraphs:
        for run in paragraph.runs:
            run.font.color.rgb = RGBColor(0, 0, 0)
            run.font.name = 'Times New Roman'
            run.font.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')

            # run.element.rPr.rFonts.set(qn('w:eastAsia'), u'仿宋_GB2312')
    # 保存Word文档
    doc.save(f'{mkdir_path}/{areaName}审核报告.docx')


# 预处理数据
def dealData(data):
    simpleData = data.dropna(subset=['原样品编号'])
    simpleData = simpleData[~simpleData['原样品编号'].str.contains('ZK')]
    simpleData = simpleData.replace(r'[^.\w]+', '', regex=True)
    simpleData = simpleData.replace('未检测', np.nan)
    simpleData = simpleData.replace('', np.nan)
    # simpleData.iloc[:, 3:] = simpleData.iloc[:, 3:].apply(pd.to_numeric, errors='ignore')
    strList = ['原样品编号', '样品编号', '地理位置', '土壤类型', '母质', '土地利用类型', '土壤质地']
    for i in simpleData.columns:
        if i not in strList:
            simpleData[i] = pd.to_numeric(simpleData[i], errors='coerce')
    # 处理重复样品
    res = getRepeat(simpleData)
    simpleData = simpleData._append(res).drop_duplicates(subset=['原样品编号'], keep='last')

    return simpleData


# 保存文件 可选择保存路径
def saveFile():
    # 根据选择的路径 保存文件
    folder_selected = filedialog.askdirectory()
    # 如果用户选择了文件夹，则打印路径
    try:
        if folder_selected:
            # 新建一个文件夹
            # 获取当前时间
            nowTime = time.strftime("%Y-%m-%d %H时%M分%S秒", time.localtime())
            dir_name = '土壤数据审核结果'
            mkdir_path = folder_selected + '/' + dir_name + nowTime
            global saveFileUrl
            saveFileUrl = folder_selected
            if not os.path.exists(mkdir_path):
                os.mkdir(mkdir_path)
            # 统一写入表格
            # 创建一个html文件夹 保存图表
                # 将 HTML 保存到文件
            for i in htmlContent:
                output_html_path = mkdir_path + '/'+ i['name'] + '.html'
                with open(output_html_path, 'w', encoding='utf-8') as html_file:
                    html_file.write(i['content'])
            # 生成存疑数据表
            makeNormalWord(mkdir_path)
            # 表1 表2 土壤容重机械组成数据 土壤容重与机械组成总体数据频度分析
            with pd.ExcelWriter(mkdir_path + '/土壤容重数据-' + nowTime +'.xlsx', engine='openpyxl') as writer:
                resData_1_Style.to_excel(writer, index=False, sheet_name='土壤容重数据')
                resData_2.to_excel(writer,  sheet_name='频度分析')
            autoColumns(mkdir_path + '/土壤容重数据-' + nowTime +'.xlsx')
            # 保存并写入频度统计图
            nowTable = pd.read_excel(mkdir_path + '/土壤容重数据-' + nowTime +'.xlsx',sheet_name='土壤容重数据')
            getStatisticsImg(nowTable['土壤容重平均值(g/cm3)(计算)'],'土壤容重(g/cm3)','土壤容重',mkdir_path + '/土壤容重数据-' + nowTime +'.xlsx',mkdir_path,'B19')
            getStatisticsImg(nowTable['洗失量(吸管法需填)%'],'洗失量(吸管法需填)%','洗失量',mkdir_path + '/土壤容重数据-' + nowTime +'.xlsx',mkdir_path,'C19')
            getStatisticsImg(nowTable['2-0.2mm颗粒含量%'],'2-0.2mm颗粒含量%','2-0.2mm颗粒含量%',mkdir_path + '/土壤容重数据-' + nowTime +'.xlsx',mkdir_path,'D19')
            getStatisticsImg(nowTable['0.2-0.02mm颗粒含量%'],'0.2~0.02mm颗粒含量%','0.2~0.02mm颗粒含量%',mkdir_path + '/土壤容重数据-' + nowTime +'.xlsx',mkdir_path,'E19')
            getStatisticsImg(nowTable['0.02-0.002mm颗粒含量%'],'0.02~0.002mm颗粒含量%','0.02~0.002mm颗粒含量%',mkdir_path + '/土壤容重数据-' + nowTime +'.xlsx',mkdir_path,'F19')
            getStatisticsImg(nowTable['0.002mm以下颗粒含量%'],'0.002mm以下颗粒含量%','0.002mm以下颗粒含量%',mkdir_path + '/土壤容重数据-' + nowTime +'.xlsx',mkdir_path,'G19')
            # 表3 表4
            with pd.ExcelWriter(mkdir_path + '/水稳性大团聚体数据-' + nowTime +'.xlsx', engine='openpyxl') as writer:
                resData_3_Style.to_excel(writer, index=False, sheet_name='水稳性大团聚体数据')
                resData_4.to_excel(writer,  sheet_name='频度分析')
            autoColumns(mkdir_path + '/水稳性大团聚体数据-' + nowTime +'.xlsx')
            # 保存并写入频度统计图
            nowTable_sw = pd.read_excel(mkdir_path + '/水稳性大团聚体数据-' + nowTime + '.xlsx', sheet_name='水稳性大团聚体数据')
            imgData = nowTable_sw.dropna(subset=['有机质g/kg','总和(%)'])
            if not imgData['有机质g/kg'].empty and not imgData['总和(%)'].empty:
                getImg(imgData['有机质g/kg'],imgData['总和(%)'],mkdir_path,
                       '有机质与水稳总和相关性散点图','水稳性大团聚体数据','有机质g/kg','水稳总和(%)',imgData['编号'],
                       mkdir_path + '/水稳性大团聚体数据-' + nowTime + '.xlsx','N1')
                getStatisticsImg(nowTable_sw['总和(%)'], '水稳总和(%)', '水稳总和(%)',
                                 mkdir_path + '/水稳性大团聚体数据-' + nowTime + '.xlsx', mkdir_path, 'B19')
                getStatisticsImg(nowTable_sw['＞5mm%'], '＞5mm%', '＞5mm%',
                                 mkdir_path + '/水稳性大团聚体数据-' + nowTime + '.xlsx', mkdir_path, 'C19')
                getStatisticsImg(nowTable_sw['3-5mm%'], '3-5mm%', '3-5mm%',
                                 mkdir_path + '/水稳性大团聚体数据-' + nowTime + '.xlsx', mkdir_path, 'D19')
                getStatisticsImg(nowTable_sw['2-3mm%'], '2-3mm%', '2-3mm%',
                                 mkdir_path + '/水稳性大团聚体数据-' + nowTime + '.xlsx', mkdir_path, 'E19')
                getStatisticsImg(nowTable_sw['1-2mm%'], '1-2mm%', '1-2mm%',
                                 mkdir_path + '/水稳性大团聚体数据-' + nowTime + '.xlsx', mkdir_path, 'F19')
                getStatisticsImg(nowTable_sw['0.5-1mm%'], '0.5-1mm%', '0.5-1mm%',
                                 mkdir_path + '/水稳性大团聚体数据-' + nowTime + '.xlsx', mkdir_path, 'G19')
                getStatisticsImg(nowTable_sw['0.25-0.5mm%'], '0.25-0.5mm%', '0.25-0.5mm%',
                                 mkdir_path + '/水稳性大团聚体数据-' + nowTime + '.xlsx', mkdir_path, 'H19')
            # 表5 表6

            with pd.ExcelWriter(mkdir_path + '/土壤离子数据-' + nowTime +'.xlsx', engine='openpyxl') as writer:
                resData_5_Style.to_excel(writer, index=False, sheet_name='土壤离子数据')
                resData_6.to_excel(writer, sheet_name='频度分析')
            autoColumns(mkdir_path + '/土壤离子数据-' + nowTime +'.xlsx')
            nowTable_lz= pd.read_excel(mkdir_path + '/土壤离子数据-' + nowTime + '.xlsx',
                                        sheet_name='土壤离子数据')
            getStatisticsImg(nowTable_lz['pH'], 'pH', 'pH',
                             mkdir_path + '/土壤离子数据-' + nowTime + '.xlsx', mkdir_path, 'B19')
            # 表7 表8

            with pd.ExcelWriter(mkdir_path + '/土壤水溶性盐数据-' + nowTime +'.xlsx', engine='openpyxl') as writer:
                resData_8_Style.to_excel(writer, index=False, sheet_name='水溶性盐数据')
                resData_7.to_excel(writer,  sheet_name='频度分析')
            autoColumns(mkdir_path + '/土壤水溶性盐数据-' + nowTime +'.xlsx')
            nowTable_sr = pd.read_excel(mkdir_path + '/土壤水溶性盐数据-' + nowTime + '.xlsx',
                                        sheet_name='水溶性盐数据')
            imgData_sr = nowTable_sr.dropna(subset=['水溶性全盐量g/kg', '电导率ms/cm'])
            getImg(imgData_sr['水溶性全盐量g/kg'],imgData_sr['电导率ms/cm'],mkdir_path,'全盐量与电导率相关性分析图',
                  '水溶性盐数据', '水溶性全盐量g/kg','电导率ms/cm',
                   imgData_sr['样品编号'],mkdir_path + '/土壤水溶性盐数据-' + nowTime + '.xlsx','T1')
            getStatisticsImg(nowTable_sr['水溶性全盐量g/kg'], '水溶性全盐量g/kg', '水溶性全盐量',
                             mkdir_path + '/土壤水溶性盐数据-' + nowTime + '.xlsx', mkdir_path, 'B19')
            getStatisticsImg(nowTable_sr['电导率ms/cm'], '电导率ms/cm', '电导率',
                             mkdir_path + '/土壤水溶性盐数据-' + nowTime + '.xlsx', mkdir_path, 'C19')
            # 表9 表10
            with pd.ExcelWriter(mkdir_path + '/土壤氮磷钾数据-' + nowTime +'.xlsx', engine='openpyxl') as writer:
                resData_10_Style.to_excel(writer, index=False, sheet_name='土壤氮磷钾数据')
                resData_9.to_excel(writer,  sheet_name='频度分析')
            autoColumns(mkdir_path + '/土壤氮磷钾数据-' + nowTime +'.xlsx')

            nowTable_NPK = pd.read_excel(mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx',
                                        sheet_name='土壤氮磷钾数据')
            # 插入分析图
            imgData_NPK = nowTable_NPK.dropna(subset=['有机质g/kg', '全氮g/kg'])
            cationImgData = nowTable_NPK.dropna(subset=['有机质g/kg', '阳离子交换量'])
            getImg(imgData_NPK['有机质g/kg'],imgData_NPK['全氮g/kg'],mkdir_path,'有机质和全氮相关性分析图','土壤氮磷钾数据',
                   '有机质g/kg','全氮g/kg',imgData_NPK['编号'],mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx','P1')
            getImg(cationImgData['有机质g/kg'], cationImgData['阳离子交换量'], mkdir_path, '有机质和阳离子交换量相关性分析图',
                   '土壤氮磷钾数据',
                   '有机质g/kg', '阳离子交换量', cationImgData['编号'], mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx',
                   'P6')
            getStatisticsImg(nowTable_NPK['有机质g/kg'], '有机质g/kg', '有机质',
                             mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx', mkdir_path, 'B19')
            getStatisticsImg(nowTable_NPK['全氮g/kg'], '全氮g/kg', '全氮',
                             mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx', mkdir_path, 'C19')
            getStatisticsImg(nowTable_NPK['全磷g/kg'], '全磷g/kg', '全磷',
                             mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx', mkdir_path, 'D19')
            getStatisticsImg(nowTable_NPK['有效磷g/kg'], '有效磷g/kg', '有效磷',
                             mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx', mkdir_path, 'E19')
            getStatisticsImg(nowTable_NPK['全钾g/kg'], '全钾g/kg', '全钾',
                             mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx', mkdir_path, 'F19')
            getStatisticsImg(nowTable_NPK['缓效钾mg/kg'], '缓效钾mg/kg', '缓效钾',
                             mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx', mkdir_path, 'G19')
            getStatisticsImg(nowTable_NPK['速效钾mg/kg'], '速效钾mg/kg', '速效钾',
                             mkdir_path + '/土壤氮磷钾数据-' + nowTime + '.xlsx', mkdir_path, 'H19')
            # 表11 表12

            with pd.ExcelWriter(mkdir_path + '/土壤金属指标数据-' + nowTime +'.xlsx', engine='openpyxl') as writer:
                resData_12_Style.to_excel(writer, index=False, sheet_name='土壤金属指标数据')
                resData_11.to_excel(writer,  sheet_name='频度分析')
            autoColumns(mkdir_path + '/土壤金属指标数据-' + nowTime +'.xlsx')

            nowTable_js = pd.read_excel(mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx',
                                         sheet_name='土壤金属指标数据')
            # 插入相关性分析图
            # 铁与ph相关性
            imgDataF = nowTable_js.dropna(subset=['有效铁mg/kg', 'pH'])
            getImg(imgDataF['有效铁mg/kg'], imgDataF['pH'], mkdir_path, '有效铁与ph相关性分析图',
                   '土壤金属指标数据',
                   '有效铁mg/kg', 'pH', imgDataF['编号'], mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx',
                   'T1')
            # 锰与ph相关性
            imgDataMe = nowTable_js.dropna(subset=['有效锰mg/kg', 'pH'])
            getImg(imgDataMe['有效锰mg/kg'], imgDataMe['pH'], mkdir_path, '有效锰与pH相关性分析图',
                   '土壤金属指标数据',
                   '有效锰mg/kg', 'pH', imgDataMe['编号'], mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx',
                   'W1')
            # 铜与ph相关性
            imgDataCu = nowTable_js.dropna(subset=['有效铜mg/kg', 'pH'])
            getImg(imgDataCu['有效铜mg/kg'], imgDataCu['pH'], mkdir_path, '有效铜与pH相关性分析图',
                   '土壤金属指标数据',
                   '有效铜mg/kg', 'pH', imgDataCu['编号'], mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx',
                   'Z1')
            # 锌与ph相关性
            imgDataZn = nowTable_js.dropna(subset=['有效锌mg/kg', 'pH'])
            getImg(imgDataZn['有效锌mg/kg'], imgDataZn['pH'], mkdir_path, '有效锌与pH相关性分析图',
                   '土壤金属指标数据',
                   '有效锌mg/kg', 'pH', imgDataZn['编号'], mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx',
                   'AC1')
            # 钼与ph相关性
            imgDataMu = nowTable_js.dropna(subset=['有效钼mg/kg', 'pH'])
            getImg(imgDataMu['有效钼mg/kg'], imgDataMu['pH'], mkdir_path, '有效钼与pH相关性分析图',
                   '土壤金属指标数据',
                   '有效钼mg/kg', 'pH', imgDataMu['编号'], mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx',
                   'AF1')
            getStatisticsImg(nowTable_js['有效硅mg/kg'], '有效硅mg/kg', '有效硅',
                             mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx', mkdir_path, 'B19')
            getStatisticsImg(nowTable_js['有效铁mg/kg'], '有效铁mg/kg', '有效铁',
                             mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx', mkdir_path, 'C19')
            getStatisticsImg(nowTable_js['有效锰mg/kg'], '有效锰mg/kg', '有效锰',
                             mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx', mkdir_path, 'D19')
            getStatisticsImg(nowTable_js['有效铜mg/kg'], '有效铜mg/kg', '有效铜',
                             mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx', mkdir_path, 'E19')
            getStatisticsImg(nowTable_js['有效锌mg/kg'], '有效锌mg/kg', '有效锌',
                             mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx', mkdir_path, 'F19')
            getStatisticsImg(nowTable_js['有效硼mg/kg'], '有效硼mg/kg', '有效硼',
                             mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx', mkdir_path, 'G19')
            getStatisticsImg(nowTable_js['有效钼mg/kg'], '有效钼mg/kg', '有效钼',
                             mkdir_path + '/土壤金属指标数据-' + nowTime + '.xlsx', mkdir_path, 'H19')
            # 表13 表14
            with pd.ExcelWriter(mkdir_path + '/土壤污染风险值数据-' + nowTime +'.xlsx', engine='openpyxl') as writer:
                resData_14_Style.to_excel(writer, index=False, sheet_name='土壤污染风险值数据')
                resData_13.to_excel(writer,  sheet_name='频度分析')
            autoColumns(mkdir_path + '/土壤污染风险值数据-' + nowTime +'.xlsx')
            nowTable_wr = pd.read_excel(mkdir_path + '/土壤污染风险值数据-' + nowTime + '.xlsx',
                                        sheet_name='土壤污染风险值数据')
            getStatisticsImg(nowTable_wr['镉mg/kg'], '镉mg/kg', '镉',
                             mkdir_path + '/土壤污染风险值数据-' + nowTime + '.xlsx', mkdir_path, 'B19')
            getStatisticsImg(nowTable_wr['汞mg/kg'], '汞mg/kg', '汞',
                             mkdir_path + '/土壤污染风险值数据-' + nowTime + '.xlsx', mkdir_path, 'C19')
            getStatisticsImg(nowTable_wr['砷mg/kg'], '砷mg/kg', '砷',
                             mkdir_path + '/土壤污染风险值数据-' + nowTime + '.xlsx', mkdir_path, 'D19')
            getStatisticsImg(nowTable_wr['铅mg/kg'], '铅mg/kg', '铅',
                             mkdir_path + '/土壤污染风险值数据-' + nowTime + '.xlsx', mkdir_path, 'E19')
            getStatisticsImg(nowTable_wr['铬mg/kg'], '铬mg/kg', '铬',
                             mkdir_path + '/土壤污染风险值数据-' + nowTime + '.xlsx', mkdir_path, 'F19')
            getStatisticsImg(nowTable_wr['镍mg/kg'], '镍mg/kg', '镍',
                             mkdir_path + '/土壤污染风险值数据-' + nowTime + '.xlsx', mkdir_path, 'G19')
            show_info('保存完成，点击确定开始生成审核报告。')
            readData = pd.read_excel(changeFileUrl, sheet_name='Sheet1', converters={'原样品编号': str})
            if checkType == 'HUNDRED_DATA':
                readData = readData.head(100)
            dealDataRes = dealData(readData)
            # 生成审核报告
            getReport(originData,dealDataRes,changeFileUrl, saveFileUrl, table_1_data, table_3_data, table_5_data,table_8_data,table_10_data,table_12_data,table_14_data)
            partReport.getphysicsReport(originData,dealDataRes,'物理指标', changeFileUrl, saveFileUrl, table_1_data, table_3_data, table_5_data,table_8_data,table_10_data,table_12_data,table_14_data)
            partReport.getConventionalNutrientIndicators(originData,dealDataRes,'常规养分指标', changeFileUrl, saveFileUrl, table_1_data, table_3_data, table_5_data,table_8_data,table_10_data,table_12_data,table_14_data)
            partReport.getChemicalIndicators(originData,dealDataRes,'一般化学性指标', changeFileUrl, saveFileUrl, table_1_data, table_3_data, table_5_data,table_8_data,table_10_data,table_12_data,table_14_data)
            partReport.getHeavyMetalIndicators(originData,dealDataRes,'重金属指标', changeFileUrl, saveFileUrl, table_1_data, table_3_data, table_5_data,table_8_data,table_10_data,table_12_data,table_14_data)
            show_info('审核报告已生成！')
    except Exception as err:
        print('err', err)
        show_error('出错了！')


def show_info(info):
    Messagebox.show_info(title='提示：', message=info)
def show_error(info):
    Messagebox.show_error(title='错误：', message=info)

def getPass(num):
    # 获取设备id 获取相应的注册码
    d = Querybox()
    c = d.get_string(prompt=f'本程序免费试用100条数据，使用更多请持您的申请码({num})电话联系管理员(19556503305)获取注册码，审核时输入注册码可使用全部功能。', title='输入注册码',
                     initialvalue=None, parent=None)
    if getNum() == c: # 输入的注册码正确
        # 存储标记文件
        with open('./html/code.txt', 'w') as file:
            file.write(
                '7687698709809hjkjlipomuiyoiupoimvgfghuli376d8bf8f8855ad8de997fa5dac1bd24956aef0cbfa0cf8ac04053a7043e3d90248051f6f03f02b20430949504a5556fb112131fc81205768229ffa023831b04')
        Messagebox.show_info('注册码提交成功，点击开始审核按钮进行审核！')
    else:
        Messagebox.show_error('注册码不正确！')
def main():
    # 创建一个GUI窗口
    root = ttk.Window()  # 使用 ttkbootstrap 创建窗口对象

    root.geometry('500x400')
    root.title("审核软件")
    root.resizable(False, False)
    root.iconbitmap("./img/icon.ico")
    windowX = root.winfo_screenwidth()
    windowY = root.winfo_screenheight()
    cen_x = (windowX - 600) / 2
    cen_y = (windowY - 500) / 2
    root.geometry('%dx%d+%d+%d' % (600, 500, cen_x, cen_y))
    numStr = uuid.getnode()
    # 添加个标签
    label1 = ttk.Label(root, text="土壤表层数据指标审核软件",font=("Segoe UI", 14), bootstyle=INFO)
    label1.grid(row=1, column=1, padx=10, pady=10)
    b1 = ttk.Button(root, text="选择文件", bootstyle=(INFO, OUTLINE),width=50,command=open_file)  # 使用 ttkbootstrap 的组件
    b1.grid(row=2, column=1, padx=10, pady=10)
    b2 = ttk.Button(root, text="开始审核", bootstyle= (INFO, OUTLINE),width=50, command=lambda: checkData(changeFileUrl))  # OUTLINE 是指定边框线
    b2.grid(row=3, column=1, padx=10, pady=10)
    b3 = ttk.Button(root, text="保 存", bootstyle= SUCCESS,width=50,command=saveFile)  # OUTLINE 是指定边框线
    b3.grid(row=4, column=1, padx=10, pady=10)
    b4 = ttk.Button(root, text="获取申请码", bootstyle=SUCCESS, width=50, command=lambda: getPass(numStr))  # OUTLINE 是指定边框线
    b4.grid(row=5, column=1, padx=10, pady=10)
    # 插入logo图片

    # image = ttk.PhotoImage(file="./img/logo_2.jpg")

    img = Image.open("./img/logo_2.jpg")
    new_img = img.resize((50,50))
    # 将图像转为tkinter可用的PhotoImage格式
    photo = ImageTk.PhotoImage(new_img)

    # 显示图像
    # label = ttk.Label(root, image=photo)

    # 创建一个 Label 并添加图片
    label = ttk.Label(image=photo,width=50)
    label.grid(row=7, column=1, padx=10, pady=10)
    # 写入单位名称
    label2 = ttk.Label(root, text="©2024 合肥环研生态环境科技有限公司 版权所有", bootstyle=SUCCESS)
    label2.grid(row=8, column=1, padx=10, pady=10)
    root.grid_columnconfigure(1, weight=1)
    root.grid_rowconfigure(1, weight=1)
    root.mainloop()

if __name__ == '__main__':
    main()