dataTools/excelToJson.py

# import xlrd
# import json
# import re
# data1 = xlrd.open_workbook("../excel/进水总氮限制答案合并.xlsx")
# sheet1 = data1.sheets()[0]
# rows = sheet1.nrows
# cols = sheet1.ncols
# header = sheet1.row_values(0)

# callTables = []


# def mergefun(n,idx):
#     # 判断当前单元格是否属于合并单元格
#     is_merged = False
#     merged_start_row = merged_end_row = None
#     for rlo, rhi, clo, chi in sheet1.merged_cells:
#         if clo == n and rlo <= idx < rhi:
#             # print(rlo, rhi, clo, chi, n)
#             is_merged = True
#             merged_start_row = rlo
#             merged_end_row = rhi
#             break
#     return [is_merged,merged_start_row,merged_end_row]


# for n in range(cols):
#     if n > 1:
#         # 获取第 n 列的所有值
#         col_data = sheet1.col_values(n)
#         # 循环每一行
#         for idx, cell_value in enumerate(col_data):
#             # 剔除为空的值和第一行的值
#             if idx == 0 or cell_value == '':
#                 continue
#             # 获取当前单元格
#             cell = sheet1.cell(idx, n)
            
#             id_val = str(n) + '_' + str(idx + 1)  # 使用列号和行号组合作为唯一ID
#             mainType = "alert" if "alert" in cell_value else "export" if "export" in cell_value else "text"
            
#             options = []
#             twoDatas = []
#             threedDatas=[]
#             # 判断当前单元格是否属于合并单元格
#             is_merged = False
#             merged_start_row = merged_end_row = None
#             for rlo, rhi, clo, chi in sheet1.merged_cells:
#                 if clo == n and rlo <= idx+1 < rhi:
#                     is_merged = True
#                     merged_start_row = rlo
#                     merged_end_row = rhi
#                     break
#             # 处理合并单元格
#             if is_merged and "export" not in cell_value:
#                 # print(f"第{n}列 第{idx+ 1}行 是合并单元格，合并范围：{merged_start_row + 1}行到{merged_end_row}行")
#                 options_cells = sheet1.col_slice(n + 1, start_rowx=merged_start_row, end_rowx=merged_end_row)
#                 twoDatas.extend([cell_value.value for cell_value in options_cells])
#                 # print(twoDatas, n)
#                 for indTwo, item in enumerate(twoDatas):
#                     if not item.strip():
#                         continue
#                     itemTwo = {
#                         "option": re.sub(r'{{(?:export|alert)}}\n', "", item),
#                         "next": []
#                     }
#                     # 找到当前单元格的合并信息 

#                     isTwo_merged, merged_start, merged_end = mergefun(n+1, indTwo + merged_start_row)
#                     if isTwo_merged:
#                         threed_cells = sheet1.col_slice(n + 2, start_rowx=merged_start, end_rowx=merged_end)
#                         threedDatas.clear()
#                         threedDatas.extend([newCell.value for newCell in threed_cells])
                        
#                         for indThree, itemThree in enumerate(threedDatas):
#                             if not itemThree.strip():
#                                 continue
#                             itemTwo['next'].append(str(n + 2) + '_' + str(indThree + merged_start + 1))
#                     else:
#                         itemTwo['next'].append(str(n + 2) + '_' + str(indTwo + merged_start_row + 1))
#                     options.append(itemTwo)
#             else:
#                 options= []
#             item = {
#                 "id": id_val,
#                 "mainType": mainType,
#                 "mainContent": re.sub(r'{{(?:export|alert)}}\n', "", cell_value),
#                 "options": options
#             }
#             callTables.append(item)
            
# # 将解析的数据保存为JSON文件
# with open("parsed_dataTwo.json", "w", encoding="utf-8") as json_file:
#     json.dump(callTables, json_file, ensure_ascii=False, indent=4)




import xlrd
import json
import re
import os
def check_merged(sheet, row, col):
    for rlo, rhi, clo, chi in sheet.merged_cells:
        if clo == col and rlo <= row < rhi:
            return True, rlo, rhi
    return False, None, None
prompt = {
    "出水氨氮":"<begin>\n{% for item in doo_items -%}\n池{{loop.index}}好氧池溶解氧：当前值为{{item.DO_O}}，建议调整值是{{item.DO_O_wentai}}。\n{%- endfor %}\n{% for item in mlss_items -%}\n池{{loop.index}}生化池污泥浓度 MLSS：当前值{{item.MLSS}}，建议调整值是{{item.MLSS_wentai}}。\n{%- endfor %}<end>",
    "出水氨氮连续检测":"<begin>\n{% for item in doo_items -%}\n池{{loop.index}}好氧池溶解氧：当前值为{{item.DO_O}}，建议调整值是{{item.DO_O_wentai}}。\n{%- endfor %}\n{% for item in mlss_items -%}\n池{{loop.index}}生化池污泥浓度 MLSS：当前值{{item.MLSS}}，建议调整值是{{item.MLSS_wentai}}。\n{%- endfor %}<end>",
    "出水总氮连续检测1":"<begin>\n碳源投加量: 当前值是{{tyjyl}}，建议调整值是{{tyjyl_wentai}}。\n内回流⽐：当前值是{{r}}，建议调整值是{{r_wentai}}。<end>",
    "出水总氮连续检测2":"<begin>\n碳源投加量: 当前值是{{tyjyl}}，建议调整值是{{tyjyl_wentai}}。\n内回流⽐：当前值是{{r}}，建议调整值是{{r_wentai}}。<end>",
    "出水总氮":"<begin>\n碳源投加量: 当前值是{{tyjyl}}，建议调整值是{{tyjyl_wentai}}。\n内回流⽐：当前值是{{r}}，建议调整值是{{r_wentai}}。<end>",
    "出水总磷":"<begin>\n除磷药剂投加量: 当前值是{{cltjl}}，建议调整值是{{cltjl_wentai}}。\n干污泥量: 当前值是{{gwnl}}，建议调整值是{{gwnl_wentai}}。<end>",
    "出水正磷酸盐":"<begin>\n除磷药剂投加量: 当前值是{{cltjl}}，建议调整值是{{cltjl_wentai}}。\n干污泥量: 当前值是{{gwnl}}，建议调整值是{{gwnl_wentai}}。<end>",
    "出水COD":"<begin>\n{% for item in doo_items -%}\n池{{loop.index}}好氧池溶解氧：当前值为{{item.DO_O}}，建议调整值为{{item.DO_O_wentai}}。\n{%- endfor %}\n{% for item in mlss_items -%}\n池{{loop.index}}生化池污泥浓度 MLSS：当前值{{item.MLSS}}，建议调整值为{{item.MLSS_wentai}}。\n{%- endfor %}<end>",
    "出水SS":"<begin>\n{% for item in mlss_items -%}\n池{{loop.index}}生化池污泥浓度 MLSS：当前值{{item.MLSS}}，建议调整值{{item.MLSS_wentai}}。\n{%- endfor %}<end>",
    "进水氨氮":"<begin>\n{% for item in doo_items -%}\n好氧池{{loop.index}}溶解氧：当前值是{{item.DO_O}}，建议调整值是{{item.DO_O_wentai}}。\n{%- endfor %}\n{% for item in mlss_items -%}\n池{{loop.index}}生化池污泥浓度 MLSS：当前值{{item.MLSS}}，建议调整值{{item.MLSS_wentai}}。\n{%- endfor %}\n内回流⽐：当前值是{{r}}，建议调整值是{{r_wentai}}。\n进水水量：当前值是{{Q_in}}，建议调整值是{{Q_in_wentai}}。\n<end>",
    "进水总氮":"<begin>\n内回流⽐：当前值是{{r}}，建议调整值是{{r_wentai}}。\n{% for item in mlss_items -%}\n池{{loop.index}}生化池污泥浓度 MLSS：当前值{{item.MLSS}}，建议调整值{{item.MLSS_wentai}}。\n{%- endfor %}\n进水水量：当前值是{{Q_in}}，建议调整值是{{Q_in_wentai}}。\n<end>",
    "进水总磷":"<begin>\n{% for item in mlss_items -%}\n池{{loop.index}}生化池污泥浓度 MLSS：当前值{{item.MLSS}}，建议调整值{{item.MLSS_wentai}}。\n{%- endfor %}\n外回流⽐：当前值是{{R}}，建议调整值是{{R_wentai}}。\n进水水量：当前值是{{Q_in}}，建议调整值是{{Q_in_wentai}}。\n<end>",
    "进水COD":"<begin>\n{% for item in doo_items -%}\n好氧池{{loop.index}}溶解氧：当前值是{{item.DO_O}}，建议调整值是{{item.DO_O_wentai}}。\n{%- endfor %}\n{% for item in mlss_items -%}\n池{{loop.index}}生化池污泥浓度 MLSS：当前值{{item.MLSS}}，建议调整值{{item.MLSS_wentai}}。\n{%- endfor %}\n外回流⽐：当前值是{{R}}，建议调整值是{{R_wentai}}。\n进水水量：当前值是{{Q_in}}，建议调整值是{{Q_in_wentai}}。\n<end>",
    "进水SS":"<begin>\n{% for item in mlss_items -%}\n池{{loop.index}}生化池污泥浓度 MLSS：当前值{{item.MLSS}}，建议调整值{{item.MLSS_wentai}}。\n{%- endfor %}\n外回流⽐：当前值是{{R}}，稳态值是{{R_wentai}}。\n进水水量：当前值是{{Q_in}}，建议调整值是{{Q_in_wentai}}。<end>",
}
data_desc= {
    "进水COD":'',
    "进水总磷":'',
    "进水总氮":'',
    "进水氨氮":'',
    "进水SS":'',
    "出水COD":'已知数据:出水COD标准值是40;历史7天进水COD数据{{COD_DATA}};历史7天进水数量{WATER_DATA}}; ',
    "出水总磷":'已知数据:出水总磷标准值是0.5;历史7天二沉池正磷酸盐数据:{{ZL_DATA}};历史7天进水总磷数据{{TP_DATA}};历史7天进水数量{{WATER_DATA}};',
    "出水总氮":'已知数据:出水总氮标准值是15;历史7天进水总氮数据{{TN_DATA}};历史7天进水数量{{WATER_DATA}};',
    "出水氨氮":'已知数据:出水氨氮标准值是4;历史7天进水氨氮数据{{andan_DATA}};历史7天进水数量{{WATER_DATA}}; ',
    "出水SS":'已知数据:出水SS标准值是10;历史7天进水SS数据{{SS_DATA}};历史7天进水数量{{WATER_DATA}}; ',
    "出水总氮连续检测1":'已知数据:出水总氮标准值是15;历史7天进水总氮数据{{TN_DATA}};历史7天进水数量{{WATER_DATA}};',
    "出水总氮连续检测2":'已知数据:出水总氮标准值是15;历史7天进水总氮数据{{TN_DATA}};历史7天进水数量{{WATER_DATA}};',
    "出水氨氮连续检测":'已知数据:出水氨氮标准值是4;历史7天进水氨氮数据{{andan_DATA}};历史7天进水数量{{WATER_DATA}}; ',
    "出水正磷酸盐":'已知数据:出水总磷标准值是0.5;历史7天二沉池正磷酸盐数据:{{ZL_DATA}};历史7天进水总磷数据{{TP_DATA}};历史7天进水数量{{WATER_DATA}};'
}
nameMap = {
    "进水COD":'js_cod',
    "进水总磷":'js_tp',
    "进水总氮":'js_tn',
    "进水氨氮":'js_andan',
    "进水SS":'js_ss',
    "出水COD":'cs_cod',
    "出水总磷":'cs_tp',
    "出水总氮":'cs_tn',
    "出水氨氮":'cs_andan',
    "出水SS":'cs_ss',
    "出水总氮连续检测1":'cs_tn_lxjc1',
    "出水总氮连续检测2":'cs_tn_lxjc2',
    "出水氨氮连续检测":'cs_andan_lxjc1',
    "出水正磷酸盐":'cs_tp_lxjc'
}

def parse_excel(sheet):
    cols = sheet.ncols
    call_tables = []
    system = ''
    boot=[]
    n=0
    for col in range(cols):
        if sheet.cell(0, col).value in ['rule', 'system']:
            system = sheet.cell(1, col).value
            n=col
            continue
        col_data = sheet.col_values(col)
        
        for idx, cell_value in enumerate(col_data):
            if idx == 0 or cell_value == '':
                continue
            id_val = f"{col}_{idx + 1}"
            if col== n+1:
               boot.append(id_val)
            main_type = "alert" if "alert" in cell_value else "export" if "export" in cell_value else "text"
            options = []

            is_merged, merged_start_row, merged_end_row = check_merged(sheet, idx, col)
            
            if is_merged and "export" not in cell_value:
                options_cells = sheet.col_slice(col + 1, start_rowx=merged_start_row, end_rowx=merged_end_row)
                two_datas = [cell_value.value for cell_value in options_cells]
                
                for indTwo, item in enumerate(two_datas):
                    if not item.strip():
                        continue
                    
                    item_two = {
                        "option": re.sub(r'{{(?:export|alert)}}\n', "", item.strip()),
                        "next": []
                    }
                    
                    is_two_merged, merged_start, merged_end = check_merged(sheet, indTwo + merged_start_row, col + 1)
                    
                    if is_two_merged:
                        threed_cells = sheet.col_slice(col + 2, start_rowx=merged_start, end_rowx=merged_end)
                        threed_datas = [new_cell.value for new_cell in threed_cells]
                        
                        for ind_three, item_three in enumerate(threed_datas):
                            if not item_three.strip():
                                continue
                            item_two['next'].append(f"{col + 2}_{ind_three + merged_start + 1}")
                    else:
                        item_two['next'].append(f"{col + 2}_{indTwo + merged_start_row + 1}")
                    
                    options.append(item_two)
            
            item = {
                "id": id_val,
                "mainType": main_type,
                "mainContent": re.sub(r'{{(?:export|alert)}}', "", cell_value.strip()).strip(),
                "options": options
            }
            call_tables.append(item)
            
    result = {
         "norm": nameMap[sheet.name],
         "system": system,
         "prompt": prompt[sheet.name],
         'data_desc': data_desc[sheet.name],
         "boot": boot,
         "questions": call_tables
        }
    return result

def getAllSheets(file_path):
    data = xlrd.open_workbook(file_path)
    sheets = data.sheets()
    directory = "./excel/json/"
    
    for item in sheets: 
        sheet_name = nameMap[item.name]
        parsed_data = parse_excel(item)
        out_file_path = os.path.join(directory, str(sheet_name+'.json'))
        with open(out_file_path, "w", encoding="utf-8") as json_file:
            json.dump(parsed_data, json_file, ensure_ascii=False, indent=4)
            
getAllSheets("./excel/juece2.0/报警决策流_new.xlsx")