Sanpu_Section/partReport.py

import pandas as pd
import numpy as np
import time
import os
from docx.shared import Inches
from docx.shared import Pt, RGBColor
from docx import Document
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
import report

# 频度信息表生成
def makeInfoTable(data, doc):
    rows = (int(len(data.columns) / 6) + 1)
    columnsList = np.arange(0, rows * 6, 6)
    dataList = []
    for i in columnsList:
        res = 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 <columnsList[10]:
                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('')
            elif i == columnsList[10]:
                # 第6*9行 显示前6个指    标的列名
                if len(dataList[10].columns) > j:
                    r = paragraph.add_run(dataList[10].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) > 11 and i > columnsList[10] and i < columnsList[11]:
                if len(dataList[10].columns) > j:
                    r = paragraph.add_run(str(dataList[10].iloc[i - 61, 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[11]:
                # 第6*9行 显示前6个指    标的列名
                if len(dataList[11].columns) > j:
                    r = paragraph.add_run(dataList[11].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) > 12 and i > columnsList[11] and i < columnsList[12]:
                if len(dataList[11].columns) > j:
                    r = paragraph.add_run(str(dataList[11].iloc[i - 67, 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[12]:
                print(i)
                # 第6*9行 显示前6个指    标的列名
                if len(dataList[12].columns) > j:
                    r = paragraph.add_run(dataList[12].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) >= 13 and i > columnsList[12] and i <= 77:
                print('last---',i)
                if len(dataList[12].columns) > j:
                    r = paragraph.add_run(str(dataList[12].iloc[i - 73, 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  # 段落行间距

# 生成子报告： 物理指标 常规养分指标 一般化学性指标 重金属指标
indexClassificationList = {
    '物理指标': ['pH', '土壤质地', '土壤容重1（g/cm³）', '土壤容重2（g/cm³）', '土壤容重3（g/cm³）',	'土壤容重4（g/cm³）',	'土壤容重平均值（g/cm³）',
                 '2~0.2mm颗粒含量', '0.2~0.02mm颗粒含量',	'0.02~0.002mm颗粒含量',	'0.002mm以下颗粒含量', '水稳>5mm（%）',	'水稳3mm~5mm（%）',
                 '水稳2mm~3mm（%）',	'水稳1mm~2mm（%）',	'水稳0.5mm~1mm（%）',	'水稳0.25mm~0.5mm（%）',	'水稳性大团聚体总和（%）', '洗失量（吸管法需填）', '风干试样含水量（分析基）'],
    '常规养分指标': ['pH','有机质', '全氮', '全磷', '全钾', '有效磷', '速效钾', '有效硫', '有效硼', '有效铁', '有效锰', '有效铜', '有效锌', '有效钼', '有效硅', '缓效钾'],
    '一般化学性指标': ['pH','阳离子交换量', '交换性盐基总量', '交换性钙', '交换性镁', '交换性钠', '交换性钾', '全盐量', '电导率',
                       '水溶性Na⁺含量', '水溶性K⁺含量',	'水溶性Ca²⁺含量',	'水溶性Mg²⁺含量',	'水溶性Cl⁻含量', '水溶性CO₃²⁻含量','水溶性HCO₃⁻含量',
                       '水溶性SO₄²⁻含量', '离子总量', '碳酸钙'],
    '重金属指标': ['pH', '总汞', '总砷', '总铅', '总镉', '总铬', '总镍']
}
# 生成物理指标审核报告
def  getphysicsReport(data,type, changeFileUrl, saveFileUrl, check_1_data,
    check_3_data,
    check_5_data ,
    check_8_data,  # 样品编号替换为编号
    check_10_data,
    check_12_data,
    check_14_data ):
    """

    :param type: 指标类型
    :param changeFileUrl: 选择的数据文件路径
    :param saveFileUrl: 保存的文件路径
    :param check_1_data: 土壤容重数据
    :param check_3_data: 水稳审核数据
    :param check_5_data: 盐离子数据
    :param check_8_data: 水溶性离子数据
    :param check_10_data: 有机质及氮磷钾数据
    :param check_12_data: 有效养分数据
    :param check_14_data: 重金属数据
    :return:
    """
    # 生成报告
    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}-{type}数据审核报告'
    mkdir_path = saveFileUrl + '/' + dir_name + nowTime
    if not os.path.exists(mkdir_path):
        os.mkdir(mkdir_path)
    # 获取相应指标数据
    physicsData = data[indexClassificationList[type]]
    report.getFrequencyImage(physicsData, mkdir_path)
    physicsData['序号'] = data['序号']
    physicsData['原样品编号'] = data['原样品编号']
    physicsData['样品编号'] = data['样品编号']
    physicsData['地理位置'] = data['地理位置']
    physicsData['母质'] = data['母质']
    physicsData['土壤类型'] = data['土壤类型']
    physicsData['土地利用类型'] = data['土地利用类型']
    physicsData['土壤质地'] = data['土壤质地']
    # 生成相应审核报告
    # 根据选择的路径读取数据
    physicsData['原样品编号'] = physicsData['原样品编号'].astype(str)
    # todo 有数据后这里去掉注释
    # checkData = pd.read_excel(changeFileUrl, sheet_name='检测方法')

    # 上面这个地址，可以传递给函数中，用于保存表格和图片
    # 调用函数 开始生成报告相关内容
    # 表1相关数据
    typeData = report.getSimpleNum(physicsData)
    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(physicsData)
    table_2_data = table_2_data.reset_index()
    table_2_data.columns = ['指标名称', '实测数量', '应测数量']
    # 表3数据
    # table_3_data = report.checkMethod(checkData, mkdir_path)
    # 数据修约 表4
    report.getNum(physicsData, mkdir_path)
    # 数据填报项审核 表5
    report.dataReportResult(physicsData, mkdir_path)
    # 表6数据 土壤质地类型不一致
    middData = physicsData[['原样品编号', '样品编号']].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, physicsData[['原样品编号', '样品编号']], how='left', on="原样品编号")
    allNeedData['样品编号'] = getSimpleDataNumber['样品编号']
    allNeedData['土地利用类型'] = check_1_data['土地利用类型']
    allNeedData['审核结果'] = check_1_data['审核结果'] + check_3_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(physicsData, mkdir_path)

    # 表13 所有存疑数据
    with pd.ExcelWriter(f'{mkdir_path}/数据审核过程存疑数据一览表.xlsx', engine='openpyxl') as writer:
        allNeedData[allNeedData['审核结果'] != ''].to_excel(writer, index=False, sheet_name='存疑数据')
    # 附表： 频度分析图
    # report.getFrequencyImage(physicsData, 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))
    ############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 这里数据多的话也可能写不下 最好是嵌入一下
    if not table_8_data['异常数据'].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')
        t_9 = table_8_data['异常数据']

        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('四、审核存疑数据', level=1)
    paragraph_12 = doc.add_paragraph()
    paragraph_12.add_run(f'表10：数据审核过程存疑数据一览表').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, '')
    makeInfoTable(table_f_2_data, doc)
    # table_f_2 = doc.add_table(rows=len(table_f_2_data) + 1, cols=6, 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 == 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}/土壤容重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')
    # 保存Word文档

    doc.save(f'{mkdir_path}/{areaName}-{type}审核报告.docx')

# 生成常规养分指标审核报告
def getConventionalNutrientIndicators(data,type, changeFileUrl, saveFileUrl, check_1_data,
    check_3_data,
    check_5_data,
    check_8_data,  # 样品编号替换为编号
    check_10_data,
    check_12_data,
    check_14_data ):
    # 生成报告
    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}-{type}数据审核报告'
    mkdir_path = saveFileUrl + '/' + dir_name + nowTime
    if not os.path.exists(mkdir_path):
        os.mkdir(mkdir_path)
    # 根据选择的路径读取数据
    ConventionalNutrientData = data[indexClassificationList[type]]
    report.getFrequencyImage(ConventionalNutrientData, mkdir_path)
    ConventionalNutrientData['序号'] = data['序号']
    ConventionalNutrientData['原样品编号'] = data['原样品编号']
    ConventionalNutrientData['样品编号'] = data['样品编号']
    ConventionalNutrientData['地理位置'] = data['地理位置']
    ConventionalNutrientData['母质'] = data['母质']
    ConventionalNutrientData['土壤类型'] = data['土壤类型']
    ConventionalNutrientData['土地利用类型'] = data['土地利用类型']
    # ConventionalNutrientData['土壤质地'] = data['土壤质地']
    # 生成相应审核报告
    ConventionalNutrientData['原样品编号'] = ConventionalNutrientData['原样品编号'].astype(str)
    data['原样品编号'] = data['原样品编号'].astype(str)
    # checkData = pd.read_excel(changeFileUrl, sheet_name='检测方法')

    # 上面这个地址，可以纯递给函数中，用于保存表格和图片
    # 调用函数 开始生成报告相关内容
    # 表1相关数据
    typeData = report.getSimpleNum(ConventionalNutrientData)
    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(ConventionalNutrientData)
    table_2_data = table_2_data.reset_index()
    table_2_data.columns = ['指标名称', '实测数量', '应测数量']
    # 表3数据
    # table_3_data = report.checkMethod(checkData, mkdir_path)
    # 数据修约 表4
    report.getNum(ConventionalNutrientData, mkdir_path)
    # 数据填报项审核 表5
    report.dataReportResult(ConventionalNutrientData, 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, ConventionalNutrientData[['原样品编号', '样品编号']], how='left', on="原样品编号")
    allNeedData['样品编号'] = getSimpleDataNumber['样品编号']
    allNeedData['土地利用类型'] = check_1_data['土地利用类型']
    allNeedData['审核结果'] = check_10_data['审核结果'] + check_12_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(ConventionalNutrientData, mkdir_path)

    # 表10 数据
    table_10_data = report.getNAndC(ConventionalNutrientData, mkdir_path)

    # 表11 数据：全磷和有效磷异常数据统计
    table_11_data = report.getPData(ConventionalNutrientData, mkdir_path)

    report.getKData(ConventionalNutrientData, mkdir_path)

    # 表13 所有存疑数据
    with pd.ExcelWriter(f'{mkdir_path}/数据审核过程存疑数据一览表.xlsx', engine='openpyxl') as writer:
        allNeedData[allNeedData['审核结果'] != ''].to_excel(writer, index=False, sheet_name='存疑数据')
    # 附表： 频度分析图
    # report.getFrequencyImage(ConventionalNutrientData, 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))
    ############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'表6：{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('表7：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 这里数据多的话也可能写不下 最好是嵌入一下
    if not table_8_data['异常数据'].empty:
        paragraph_9 = doc.add_paragraph()
        paragraph_9.add_run('表8：pH异常数据统计表').bold = True
        table_9 = doc.add_table(rows=len(table_8_data['异常数据']) + 1, cols=6, style='Light Shading Accent 1')
        t_9 = table_8_data['异常数据']

        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('表9：碳氮比异常数据统计表').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('表10：全磷与有效磷异常样品统计表').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('表10：全磷与有效磷异常样品统计表').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('四、审核存疑数据', level=1)
    paragraph_12 = doc.add_paragraph()
    paragraph_12.add_run(f'表11：数据审核过程存疑数据一览表').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, '')
    makeInfoTable(table_f_2_data, doc)
    # table_f_2 = doc.add_table(rows=len(table_f_2_data) + 1, cols=6, 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 == 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}/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))
    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')
    # 保存Word文档

    doc.save(f'{mkdir_path}/{areaName}-{type}审核报告.docx')



# 生成一般化学性指标审核报告
def getChemicalIndicators(data,type, changeFileUrl, saveFileUrl, check_1_data,
    check_3_data,
    check_5_data,
    check_8_data,  # 样品编号替换为编号
    check_10_data,
    check_12_data,
    check_14_data):
    # 生成报告
    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}-{type}数据审核报告'
    mkdir_path = saveFileUrl + '/' + dir_name + nowTime
    if not os.path.exists(mkdir_path):
        os.mkdir(mkdir_path)
    # 根据选择的路径读取数据

    cheemicalData = data[indexClassificationList[type]]
    report.getFrequencyImage(cheemicalData, mkdir_path)
    cheemicalData['序号'] = data['序号']
    cheemicalData['原样品编号'] = data['原样品编号']
    cheemicalData['样品编号'] = data['样品编号']
    cheemicalData['地理位置'] = data['地理位置']
    cheemicalData['母质'] = data['母质']
    cheemicalData['土壤类型'] = data['土壤类型']
    cheemicalData['土地利用类型'] = data['土地利用类型']
    # cheemicalData['土壤质地'] = data['土壤质地']
    cheemicalData['原样品编号'] = cheemicalData['原样品编号'].astype(str)
    # checkData = pd.read_excel(changeFileUrl, sheet_name='检测方法')

    # 上面这个地址，可以纯递给函数中，用于保存表格和图片
    # 调用函数 开始生成报告相关内容
    # 表1相关数据
    typeData = report.getSimpleNum(cheemicalData)
    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(cheemicalData)
    table_2_data = table_2_data.reset_index()
    table_2_data.columns = ['指标名称', '实测数量', '应测数量']
    # 表3数据
    # table_3_data = report.checkMethod(checkData, mkdir_path)
    # 数据修约 表4
    report.getNum(cheemicalData, mkdir_path)
    # 数据填报项审核 表5
    report.dataReportResult(cheemicalData, 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_5_data['审核结果'] + check_8_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(cheemicalData, mkdir_path)


    report.cationExchangeCapacity(cheemicalData, mkdir_path)
    report.changeCation(cheemicalData, mkdir_path)
    report.manyTypes(cheemicalData, mkdir_path)
    # 有效态异常数据
    errObj = report.orderData(data)
    # 表13 所有存疑数据
    with pd.ExcelWriter(f'{mkdir_path}/数据审核过程存疑数据一览表.xlsx', engine='openpyxl') as writer:
        allNeedData[allNeedData['审核结果'] != ''].to_excel(writer, index=False, sheet_name='存疑数据')
    # 附表： 频度分析图
    # report.getFrequencyImage(cheemicalData, 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))


    ############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'表6：{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('表7：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 这里数据多的话也可能写不下 最好是嵌入一下
    if not table_8_data['异常数据'].empty:
        paragraph_9 = doc.add_paragraph()
        paragraph_9.add_run('表8：pH异常数据统计表').bold = True
        table_9 = doc.add_table(rows=len(table_8_data['异常数据']) + 1, cols=6, style='Light Shading Accent 1')
        t_9 = table_8_data['异常数据']

        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_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

    # 这里添加新增的剖面数据指标 添加异常数据表和折线图
    # if not errObj['errData'].empty:
    #     errData = errObj['errData']
    #     errName = errObj['errName']
    #     errName.insert(0, '原样品编号')
    #     paragraph_12 = doc.add_paragraph()
    #     paragraph_12.add_run('表9：有效态元素异常样品统计表').bold = True
    #     table_12 = doc.add_table(rows=len(errData)+1, cols=len(errName), style='Light Shading Accent 1')
    #     paragraph_12.alignment = WD_ALIGN_PARAGRAPH.CENTER
    #     table_12.alignment = WD_TABLE_ALIGNMENT.CENTER
    #     for i, row in enumerate(table_12.rows):
    #         for j, cell in enumerate(row.cells):
    #             # 获取单元格中的段落对象
    #             paragraph = cell.paragraphs[0]
    #             if i == 0:
    #                 r = paragraph.add_run(str(errData[errName].columns[j]))
    #                 r.font.bold = True
    #             else:
    #                 r=paragraph.add_run(str(errData[errName].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_12 = doc.add_paragraph()
    #     paragraph_12.add_run('表9：有效态元素异常样品统计表').bold = True
    #     paragraph_12_info = doc.add_paragraph()
    #     paragraph_12_info.add_run('无异常数据')
    #     paragraph_12.alignment = WD_ALIGN_PARAGRAPH.CENTER
    #     paragraph_12_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_14 = doc.add_paragraph()
    paragraph_t_14.add_run(f'图14：有效态指标异常统计图').bold = True
    paragraph_t_14.alignment = WD_ALIGN_PARAGRAPH.CENTER
    doc.add_heading('四、审核存疑数据', level=1)
    paragraph_12 = doc.add_paragraph()
    paragraph_12.add_run(f'表9：数据审核过程存疑数据一览表').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, '')
    makeInfoTable(table_f_2_data, doc)
    # table_f_2 = doc.add_table(rows=len(table_f_2_data) + 1, cols=6, 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 == 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}/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))
    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 getHeavyMetalIndicators(data, type, changeFileUrl, saveFileUrl, check_1_data,
    check_3_data,
    check_5_data,
    check_8_data,  # 样品编号替换为编号
    check_10_data,
    check_12_data,
    check_14_data):
    # 生成报告
    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}-{type}数据审核报告'
    mkdir_path = saveFileUrl + '/' + dir_name + nowTime
    if not os.path.exists(mkdir_path):
        os.mkdir(mkdir_path)
    heavyMetaData = data[indexClassificationList[type]]
    report.getFrequencyImage(heavyMetaData, mkdir_path)
    heavyMetaData['序号'] = data['序号']
    heavyMetaData['原样品编号'] = data['原样品编号']
    heavyMetaData['样品编号'] = data['样品编号']
    heavyMetaData['地理位置'] = data['地理位置']
    heavyMetaData['母质'] = data['母质']
    heavyMetaData['土壤类型'] = data['土壤类型']
    heavyMetaData['土地利用类型'] = data['土地利用类型']
    # heavyMetaData['土壤质地'] = data['土壤质地']
    heavyMetaData['原样品编号'] = heavyMetaData['原样品编号'].astype(str)
    # checkData = pd.read_excel(changeFileUrl, sheet_name='检测方法')

    # 上面这个地址，可以纯递给函数中，用于保存表格和图片
    # 调用函数 开始生成报告相关内容
    # 表1相关数据
    typeData = report.getSimpleNum(heavyMetaData)
    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(heavyMetaData)
    table_2_data = table_2_data.reset_index()
    table_2_data.columns = ['指标名称', '实测数量', '应测数量']
    # 表3数据
    # table_3_data = report.checkMethod(checkData, mkdir_path)
    # 数据修约 表4
    report.getNum(heavyMetaData, mkdir_path)
    # 数据填报项审核 表5
    report.dataReportResult(heavyMetaData, mkdir_path)
    # 表6数据 土壤质地类型不一致
    middData = heavyMetaData[['原样品编号', '样品编号']].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, heavyMetaData[['原样品编号', '样品编号']], how='left', on="原样品编号")
    allNeedData['样品编号'] = getSimpleDataNumber['样品编号']
    allNeedData['土地利用类型'] = check_1_data['土地利用类型']
    allNeedData['审核结果'] = 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(heavyMetaData, 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, heavyMetaData[['原样品编号', '样品编号']], how='left', on='原样品编号')
    first_column = caOverData.pop('样品编号')
    caOverData.insert(0, '样品编号', first_column)
    caOverData_need = caOverData[caOverData['审核结果'] != '']

    # 写进表格
    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(heavyMetaData, 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))

    ############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'表6：{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('表7：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 这里数据多的话也可能写不下 最好是嵌入一下
    if not table_8_data['异常数据'].empty:
        paragraph_9 = doc.add_paragraph()
        paragraph_9.add_run('表8：pH异常数据统计表').bold = True
        table_9 = doc.add_table(rows=len(table_8_data['异常数据']) + 1, cols=6, style='Light Shading Accent 1')
        t_9 = table_8_data['异常数据']

        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('4、指标综合分析', level=2)
    doc.add_heading('表9：重金属超筛选值情况统计', level=4)
    # todo 获取重金属数据


    doc.add_heading('四、审核存疑数据', level=1)
    paragraph_12 = doc.add_paragraph()
    paragraph_12.add_run(f'表10：数据审核过程存疑数据一览表').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, '')
    makeInfoTable(table_f_2_data, doc)
    # table_f_2 = doc.add_table(rows=len(table_f_2_data) + 1, cols=6, 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 == 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}/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))
    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}-{type}审核报告.docx')