package cn.com.capcloud.api.action.service.util;

import cn.com.capcloud.api.action.service.ApiDao;
import cn.com.capcloud.ccui.core.common.json.JackJSONObject;
import cn.com.capcloud.ccui.core.common.utils.StringUtil;
import cn.com.capcloud.core.noentitydb.BaseDao;
import cn.com.capcloud.core.system.user.User;
import cn.com.capcloud.core.system.user.UserService;
import cn.com.capcloud.core.websocket.RedisPublication;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Pattern;

/**
 * @Author LiaoJiaHui
 * @Create 2026/04/27
 */
public class ImportExportUtil {

    public static Map<String,Object> importExcelData(String jsonStr, JackJSONObject retJson, Map<String,Object>  returnMap, Map<String,Object> fileInfo,
                                                     List<Map<String, Object>> dataMaps, List<Map<String, Object>> queryFieldList,
                                                     List<Map<String, Object>> formFieldList, InputStream inputStream, UserService userService,
                                                     User user, JdbcTemplate jdbcTemplate, RedisPublication redisPublication) {
        String suffix=String.valueOf(fileInfo.get("suffix"));
        if("xls".equals(suffix)){
            return importExcelDataByXls(jsonStr,retJson,returnMap,fileInfo,dataMaps,queryFieldList,formFieldList,inputStream,userService,user,jdbcTemplate,redisPublication);
        }else{
            return importExcelDataByXlsx(jsonStr,retJson,returnMap,fileInfo,dataMaps,queryFieldList,formFieldList,inputStream,userService,user,jdbcTemplate,redisPublication);
        }//todo csv待开发，目前仅支持xls和xlsx两种，csv待开发
    }

    public static Map<String,Object> importExcelDataByXls(String jsonStr,JackJSONObject retJson,Map<String,Object>  returnMap,Map<String,Object> fileInfo,
                                                          List<Map<String, Object>> dataMaps,List<Map<String, Object>> queryFieldList,
                                                          List<Map<String, Object>> formFieldList,InputStream inputStream,UserService userService,
                                                          User user,JdbcTemplate jdbcTemplate, RedisPublication redisPublication) {
        Date date = new Date();
        SimpleDateFormat timeFormat= new SimpleDateFormat("yyyy-MM-dd HH:mm"+":00");
        SimpleDateFormat dateFormat= new SimpleDateFormat("yyyy-MM-dd");
        // 创建一个 Map 来存储特定的字段名与布尔值的映射
        Map<String, Boolean> fieldMap = new HashMap<>();
        fieldMap.put("create_time", false);
        fieldMap.put("create_user", false);
        fieldMap.put("uuid", false);
        fieldMap.put("page_parent_uuid", false);
        //获取第0行 （标题第一行） 有多少列
        boolean isLegalFieldName=true;//是否是合法字段名。
        String field="";
        Map<String,Object> lableNameMap=new HashMap<>();
        List filedNameList=new ArrayList();
        HSSFWorkbook wb = null;
        try {
            wb = new HSSFWorkbook(inputStream);
        } catch (IOException e) {
        }
        HSSFSheet sheet = wb.getSheetAt(0);
        JackJSONObject jsonObject = JackJSONObject.fromObject(jsonStr);
        String wsCode=String.valueOf(jsonObject.get("queryCode"));
        redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\"开始解析表头...\",\"status\":\"ok\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
        Map<String,Map<String,Object>> dictList=new HashMap<>();
        Map<String,Object> canNullMap=new HashMap<>();
        if(formFieldList.size()>0){
            for (int i = 0; i < formFieldList.size(); i++) {
                String canNull=String.valueOf(formFieldList.get(i).get("can_null"));
                if(canNull.equals("Y")){
                    String fieldName=String.valueOf(formFieldList.get(i).get("name"));
                    canNullMap.put(fieldName,"Y");//标记不能为空的字段
                }
            }
        }
        //start 取出字段的中文名
        Map<String,Object> labelNameMap=new HashMap<>();
        for (int i = 0; i < queryFieldList.size(); i++) {
            String fieldName=String.valueOf(queryFieldList.get(i).get("name"));
            labelNameMap.put(fieldName,queryFieldList.get(i).get("label_name"));
        }
        //end 取出字段的中文名
        //第一次循环 循环第0行A列到第N列结束
        Integer readRow=Integer.valueOf(String.valueOf(jsonObject.get("readRow")));
        for (int xxx = 0; xxx <sheet.getRow(readRow-2).getLastCellNum() ; xxx++) {
            if(isLegalFieldName){
                //每一次循环第0行第n列的时候赋值给field
                field= String.valueOf(sheet.getRow(readRow-2).getCell(xxx));
                for (int i = 0; i < queryFieldList.size(); i++) {
                    String filedName=String.valueOf(queryFieldList.get(i).get("label_name")).replaceAll("\\*", "");
                    isLegalFieldName=false;
                    if(field.equals(filedName)){
                        String fieldName=String.valueOf(queryFieldList.get(i).get("name"));
                        lableNameMap.put(fieldName,field);
                        isLegalFieldName=true;
                        filedNameList.add(fieldName);
                        if(fieldMap.containsKey(fieldName)){
                            fieldMap.put(fieldName, true);
                        }
                        String dictCode=String.valueOf(queryFieldList.get(i).get("dict_code"));
                        if("null".equals(dictCode)||dictCode.length()==0){
                            break;
                        }
                        if(dictCode.indexOf("tree_")==0){//tree字典
                            String str=String.valueOf(dictList.get(dictCode));
                            if(str.equals("null")){
                                String treeShowValueType=String.valueOf(queryFieldList.get(i).get("tree_show_value_type"));
                                Map<String,Object> dictMap=new HashMap<>();
                                if(treeShowValueType.equals("full")){//full 代表完整名称
                                    String treeShowFullSplit=String.valueOf(queryFieldList.get(i).get("tree_show_full_split"));
                                    dictMap=DictUtil.getTreeDictInfoByDictCodeToFullMap(dictCode,true,null,jdbcTemplate,treeShowFullSplit);
                                }else{
                                    dictMap=DictUtil.getTreeDictInfoByDictCodeToMap(dictCode,true,null,jdbcTemplate);
                                }
                                dictList.put(dictCode,dictMap);
                            }
                        }else if(dictCode.indexOf("o_")==0){
                            Map<String,Object> dictMap=DictUtil.getOutDictInfoByDictCodeToMap(dictCode,true,jdbcTemplate);
                            dictList.put(dictCode,dictMap);
                        }else{
                            Map<String,Object> dictMap=DictUtil.getDictInfoByDictCodeToMap(dictCode,true,jdbcTemplate);
                            dictList.put(dictCode,dictMap);
                        }
                        break;
                    }
                }
                if(isLegalFieldName==false){
                    retJson.putAll(JackJSONObject.fromObject(ApiDao.getReturnDataByCode("5020",jdbcTemplate)));
                    redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\""+retJson.get("message")+"："+field+"\",\"status\":\"5020\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
                    return new HashMap<>();
                }
            }else{
                break;
            }
        }
        String parentUUID="";
        if(!fieldMap.get("page_parent_uuid")){
            parentUUID=String.valueOf(jsonObject.get("parentUUID"));
        }
        //循环第一行 就是标题行的下一行 xxx = 1 从第一行开始 到工作表的行数。 导入 就是读取 ，获得有效行数 sheet.getPhysicalNumberOfRows()
        for (int xxx = (readRow-1); xxx <sheet.getPhysicalNumberOfRows() ; xxx++) {
            //创建dataMap 用于存放需要执行的数据
            Map<String, Object> dataMap=new HashMap<>();
            dataMap.put("status","0");
            //start 这个循环是提前过滤数据，如果有一行所有单元格都是空则不入库
            boolean isCellAllNull=true;
            for (int i = 0; i <sheet.getRow(xxx).getLastCellNum() ; i++) {
                String cellStr= String.valueOf(sheet.getRow(xxx).getCell(i));
                if(cellStr.trim().length()!=0&&!cellStr.equals("null")){
                    isCellAllNull=false;
                }
            }
            if(isCellAllNull){
                break;
            }
            if((xxx+1) % 1000 == 0){
                redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\"解析数据中，总共"+sheet.getPhysicalNumberOfRows()+"条，当前解析到"+(xxx+1)+"条...\",\"status\":\"ok\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
            }
            //end 这个循环是提前过滤数据，如果有一行所有单元格都是空则不入库
            //getPhysicalNumberOfCells() 只会返回实际包含数据的单元格数量，如果某一列为空，它就不会计入。
            //为了确保读取到所有的列，可以使用 getLastCellNum() 来获取工作表中最后一个列的编号，即使某些列为空，也能被包括在内。
            for (int i = 0; i <sheet.getRow(xxx).getLastCellNum() ; i++) {
                //把每次第N列的值 获取 赋值给 fieldValue变量
                String fieldValue= String.valueOf(sheet.getRow(xxx).getCell(i));
                //如果再有效列数内获取到的值不为空就往下走  反之 把值赋为 "" 空字符串 fieldValue="";
                if(!fieldValue.equals("null")&&fieldValue!=null){
                    //对每个单元格的 数值类型进行操作 一行每一列的数值 setCellType(CellType.STRING); set为String 类型
                    //获取每一行的每一列 数据 的类型   这里判断的是  如果每一行每一列的值 == CellType.NUMERIC  NUMERIC  数值型  0
                    if (sheet.getRow(xxx).getCell(i).getCellTypeEnum() == CellType.NUMERIC){
                        try {//时间单元格
                            DateFormat df = new SimpleDateFormat("dd-MM-yyyy");
                            Date parseDate = df.parse(fieldValue);//有异常要捕获
                            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                            fieldValue=format.format(parseDate);
                        } catch (ParseException e) {//非时间单元格
                            //获取到的每行每列 数值型 的值 转换为String 赋值给 fieldValue
                            sheet.getRow(xxx).getCell(i).setCellType(CellType.STRING);
                            //获取值
                            fieldValue = sheet.getRow(xxx).getCell(i).getStringCellValue();
                        }
                    }
                }else{
                    fieldValue="";
                }
                //从excel读取整数要去小数点0
                Pattern pattern=Pattern.compile("^([1-9]\\d*(\\.[0-9]*[1-9])?)|(0\\.[0-9]*[1-9])|0$");
                if(!pattern.matcher(fieldValue).matches()){
                    try {
                        Double.parseDouble(fieldValue);//转一下double，
                        if(fieldValue.contains(".")) {
                            int indexOf = fieldValue.indexOf(".");
                            fieldValue = fieldValue.substring(0, indexOf);
                        }
                    } catch (NumberFormatException e) {
                        //转失败则说明是普通字符串
                    }
                }
                //每一次循环第0行第n列的时候赋值给field  标题行
                field= String.valueOf(sheet.getRow(readRow-2).getCell(i));
                //把之前取到的服务信息 循环遍历
                for (int j = 0; j < queryFieldList.size(); j++) {
                    String filedName = String.valueOf(queryFieldList.get(j).get("label_name")).replaceAll("\\*", "");
                    //从fieldList 中获取到字段名称和 field 标题行中字段是对应
                    if (field.equals(filedName)) {
                        //验证是否启用 字典值
                        String dictCode=String.valueOf(queryFieldList.get(j).get("dict_code"));
                        String showType=String.valueOf(queryFieldList.get(j).get("show_type"));
                        if (StringUtil.isNotEmpty(dictCode)) {
                            // 使用 Set 存储 showType 的合法值，减少多个 equals 的判断
                            Set<String> validShowTypes = new HashSet<>(Arrays.asList("checkbox", "selectCheckbox", "selectSearch"));
                            if(validShowTypes.contains(showType)){
                                String[] fieldDataValueArray=fieldValue.split(",");
                                String showValue="";
                                for(int yyy=0;yyy<fieldDataValueArray.length;yyy++) {
                                    showValue+= dictList.get(dictCode).get(fieldDataValueArray[yyy])+",";
                                }
                                if(!showValue.equals("")){
                                    fieldValue=","+showValue;
                                }else{
                                    fieldValue=showValue;
                                }
                            }else{
                                String dictValue=dictList.get(dictCode).get(fieldValue)+"";
                                if(!dictValue.equals("null")){
                                    fieldValue=dictValue;
                                }
                            }
                        }else if(showType.equals("date")){
                            // 检查是否已经是正确的格式，如果是则不转换
                            fieldValue = fieldValue.matches("\\d{8}") ?  // 检查是否是yyyyMMdd格式
                                    LocalDate.parse(fieldValue, DateTimeFormatter.ofPattern("yyyyMMdd"))
                                            .format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))
                                    : fieldValue;  // 如果不是，直接使用原始字符串

                        }
                        break;
                    }
                }
                if(filedNameList.size()<=i){//大于或等于的时候说明数据列比标题列多，说明该列是无需入库
                    continue;
                }
                String fieldName=String.valueOf(filedNameList.get(i));
                String canNull=String.valueOf(canNullMap.get(fieldName));
                if(canNull.equals("Y")){//必填项
                    if(fieldValue.equals("")){
                        retJson.putAll(JackJSONObject.fromObject(ApiDao.getReturnDataByCode("5024",jdbcTemplate)));
                        retJson.put("message","\""+labelNameMap.get(fieldName)+"\"为必填项，请检查文件第"+(xxx+1)+"行数据后再重新上传");
                        redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\""+labelNameMap.get(fieldName)+"为必填项，请检查文件第"+(xxx+1)+"行数据后再重新上传\",\"status\":\"5020\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
                        return new HashMap<>();
                    }else{
                        dataMap.put(fieldName,fieldValue);
                    }
                }else{//非必填项
                    dataMap.put(fieldName,fieldValue);
                }
            }
            if(fieldMap.get("create_time")){
                //导入有create_time列,但未填写值 默认给当前时间
                if(dataMap.get("create_time")==null){
                    dataMap.put("create_time",timeFormat.format(date));
                    dataMap.put("create_date",dateFormat.format(date));
                }else{
                    try {
                        //导入有create_time列 有值，验证格式是否符合否则 走catch 获取系统当前时间
                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        format.setLenient(false);
                        format.parse((String) dataMap.get("create_time"));
                    } catch (ParseException e) {
                        dataMap.put("create_time",timeFormat.format(date));
                        dataMap.put("create_date",dateFormat.format(date));
                    }
                }
            }else{
                dataMap.put("create_time",timeFormat.format(date));
                dataMap.put("create_date",dateFormat.format(date));
            }
            if(!fieldMap.get("create_user")){
                BaseDao.setDefaultValue(dataMap,user);
            }else{
                String createUser=String.valueOf(dataMap.get("create_user"));
                String sql="select * from tbl_system_user where user_name='"+createUser+"'";
                List<Map<String, Object>> userList=jdbcTemplate.queryForList(sql);
                dataMap.put("create_user",userList.get(0).get("user_name"));
                dataMap.put("create_user_id",userList.get(0).get("id"));
                dataMap.put("create_org_id",userList.get(0).get("org_id"));
                dataMap.put("create_org_name",userList.get(0).get("org_name"));
            }
            if(!fieldMap.get("page_parent_uuid")&&(parentUUID.length()>0&&!parentUUID.equals("null"))){
                dataMap.put("page_parent_uuid",parentUUID);
            }
            dataMap.put("data_source","excel");
            dataMaps.add(dataMap);
            Integer sumRow=sheet.getPhysicalNumberOfRows();
            Integer errorRow=sumRow-xxx-(readRow-1);//总行数减去成功和标题行得出未导入的。
            String errorText="";
            if(errorRow>0){
                errorText="，失败："+errorRow+"条，请检查文件第"+(xxx+2)+"行";//加2是因为从0开始和去掉标题列
            }
            returnMap.put("importMessage"," 导入了"+(xxx-readRow+2)+"条"+errorText);//提前设置导入成功message
        }
        return returnMap;
    }
    public static Map<String,Object> importExcelDataByXlsx(String jsonStr,JackJSONObject retJson,Map<String,Object>  returnMap,Map<String,Object> fileInfo,
                                                           List<Map<String, Object>> dataMaps,List<Map<String, Object>> queryFieldList,
                                                           List<Map<String, Object>> formFieldList,InputStream inputStream,UserService userService,
                                                           User user,JdbcTemplate jdbcTemplate, RedisPublication redisPublication) {
        Date date = new Date();
        SimpleDateFormat timeFormat= new SimpleDateFormat("yyyy-MM-dd HH:mm"+":00");
        SimpleDateFormat dateFormat= new SimpleDateFormat("yyyy-MM-dd");
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(inputStream);
        } catch (IOException e) {
        }
        Sheet sheet = wb.getSheetAt(0);
        // 创建一个 Map 来存储特定的字段名与布尔值的映射
        Map<String, Boolean> fieldMap = new HashMap<>();
        fieldMap.put("create_time", false);
        fieldMap.put("create_user", false);
        fieldMap.put("uuid", false);
        fieldMap.put("page_parent_uuid", false);
        //获取第0行 （标题第一行） 有多少列
        boolean isLegalFieldName=true;//是否是合法字段名。
        String field="";
        Map<String,Object> lableNameMap=new HashMap<>();
        JackJSONObject jsonObject = JackJSONObject.fromObject(jsonStr);
        String wsCode=String.valueOf(jsonObject.get("queryCode"));
        redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\"开始解析表头...\",\"status\":\"ok\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
        List filedNameList=new ArrayList();
        Map<String,Map<String,Object>> dictList=new HashMap<>();
        Map<String,Object> canNullMap=new HashMap<>();
        if(formFieldList.size()>0){
            for (int i = 0; i < formFieldList.size(); i++) {
                String canNull=String.valueOf(formFieldList.get(i).get("can_null"));
                if(canNull.equals("Y")){
                    String fieldName=String.valueOf(formFieldList.get(i).get("name"));
                    canNullMap.put(fieldName,"Y");
                }
            }
        }
        //start 取出字段的中文名
        Map<String,Object> labelNameMap=new HashMap<>();
        for (int i = 0; i < queryFieldList.size(); i++) {
            String fieldName=String.valueOf(queryFieldList.get(i).get("name"));
            labelNameMap.put(fieldName,queryFieldList.get(i).get("label_name"));
        }
        //end 取出字段的中文名
        //第一次循环 循环第0行A列到第N列结束
        Integer readRow=Integer.valueOf(String.valueOf(jsonObject.get("readRow")));
        for (int xxx = 0; xxx <sheet.getRow(readRow-2).getLastCellNum() ; xxx++) {
            if(isLegalFieldName){
                //每一次循环第0行第n列的时候赋值给field
                field= String.valueOf(sheet.getRow(readRow-2).getCell(xxx));
                for (int i = 0; i < queryFieldList.size(); i++) {
                    String filedName=String.valueOf(queryFieldList.get(i).get("label_name")).replaceAll("\\*", "");
                    isLegalFieldName=false;
                    if(field.equals(filedName)){
                        String fieldName=String.valueOf(queryFieldList.get(i).get("name"));
                        lableNameMap.put(fieldName,field);
                        isLegalFieldName=true;
                        filedNameList.add(fieldName);
                        if(fieldMap.containsKey(fieldName)){
                            fieldMap.put(fieldName, true);
                        }
                        String dictCode=String.valueOf(queryFieldList.get(i).get("dict_code"));
                        if("null".equals(dictCode)||dictCode.length()==0){
                            break;
                        }
                        if(dictCode.indexOf("tree_")==0){//tree字典
                            String str=String.valueOf(dictList.get(dictCode));
                            if(str.equals("null")){
                                String treeShowValueType=String.valueOf(queryFieldList.get(i).get("tree_show_value_type"));
                                Map<String,Object> dictMap=new HashMap<>();
                                if(treeShowValueType.equals("full")){//full 代表完整名称
                                    String treeShowFullSplit=String.valueOf(queryFieldList.get(i).get("tree_show_full_split"));
                                    dictMap=DictUtil.getTreeDictInfoByDictCodeToFullMap(dictCode,true,null,jdbcTemplate,treeShowFullSplit);
                                }else{
                                    dictMap=DictUtil.getTreeDictInfoByDictCodeToMap(dictCode,true,null,jdbcTemplate);
                                }
                                dictList.put(dictCode,dictMap);
                            }
                        }else if(dictCode.indexOf("o_")==0){
                            Map<String,Object> dictMap=DictUtil.getOutDictInfoByDictCodeToMap(dictCode,false,jdbcTemplate);
                            dictList.put(dictCode,dictMap);
                        }else{
                            Map<String,Object> dictMap=DictUtil.getDictInfoByDictCodeToMap(dictCode,true,jdbcTemplate);
                            dictList.put(dictCode,dictMap);
                        }
                        break;
                    }
                }
                if(isLegalFieldName==false){
                    retJson.putAll(JackJSONObject.fromObject(ApiDao.getReturnDataByCode("5020",jdbcTemplate)));
                    redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\""+retJson.get("message")+"："+field+"\",\"status\":\"5020\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
                    return new HashMap<>();
                }
            }else{
                break;
            }
        }
        String parentUUID="";
        if(!fieldMap.get("page_parent_uuid")){
            parentUUID=String.valueOf(jsonObject.get("parentUUID"));
        }
        //循环第一行 就是标题行的下一行 xxx = 1 从第一行开始 到工作表的行数。 导入 就是读取 ，获得有效行数 sheet.getPhysicalNumberOfRows()
        for (int xxx = (readRow-1); xxx <sheet.getPhysicalNumberOfRows() ; xxx++) {
            //创建dataMap 用于存放需要执行的数据
            Map<String, Object> dataMap=new HashMap<>();
            dataMap.put("status","0");
            //start 这个循环是提前过滤数据，如果有一行所有单元格都是空则不入库
            boolean isCellAllNull=true;
            for (int i = 0; i <sheet.getRow(xxx).getLastCellNum() ; i++) {
                String cellStr= String.valueOf(sheet.getRow(xxx).getCell(i));
                if(cellStr.trim().length()!=0&&!cellStr.equals("null"))isCellAllNull=false;
            }
            if(isCellAllNull)break;
            if((xxx+1) % 1000 == 0){
                redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\"解析数据中，总共"+sheet.getPhysicalNumberOfRows()+"条，当前解析到"+(xxx+1)+"条...\",\"status\":\"ok\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
            }
            //end 这个循环是提前过滤数据，如果有一行所有单元格都是空则不入库
            //getPhysicalNumberOfCells() 只会返回实际包含数据的单元格数量，如果某一列为空，它就不会计入。
            //为了确保读取到所有的列，可以使用 getLastCellNum() 来获取工作表中最后一个列的编号，即使某些列为空，也能被包括在内。
            for (int i = 0; i <sheet.getRow(xxx).getLastCellNum() ; i++) {
                //把每次第N列的值 获取 赋值给 fieldValue变量
                String fieldValue= String.valueOf(sheet.getRow(xxx).getCell(i));
                //如果再有效列数内获取到的值不为空就往下走  反之 把值赋为 "" 空字符串 fieldValue="";
                if(!fieldValue.equals("null")&&fieldValue!=null){
                    //对每个单元格的 数值类型进行操作 一行每一列的数值 setCellType(CellType.STRING); set为String 类型
                    //获取每一行的每一列 数据 的类型   这里判断的是  如果每一行每一列的值 == CellType.NUMERIC  NUMERIC  数值型  0
                    if (sheet.getRow(xxx).getCell(i).getCellTypeEnum() == CellType.NUMERIC){
                        try {//时间单元格
                            DateFormat df = new SimpleDateFormat("dd-MM-yyyy");
                            Date parseDate = df.parse(fieldValue);//有异常要捕获
                            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                            fieldValue=format.format(parseDate);
                        } catch (ParseException e) {//非时间单元格
                            if(fieldValue.indexOf("E")!=-1){//”4000243621“长数值读取为”4.000243621E9“需要转成字符串读取
                                //获取到的每行每列 数值型 的值 转换为String 赋值给 fieldValue
                                sheet.getRow(xxx).getCell(i).setCellType(CellType.STRING);
                                //获取值
                                fieldValue = sheet.getRow(xxx).getCell(i).getStringCellValue();
                            }
                        }
                    }
                }else{
                    fieldValue="";
                }
                //从excel读取整数要去小数点0
                Pattern pattern=Pattern.compile("^([1-9]\\d*(\\.[0-9]*[1-9])?)|(0\\.[0-9]*[1-9])|0$");
                if(!pattern.matcher(fieldValue).matches()){
                    try {
                        Double.parseDouble(fieldValue);//转一下double，
                        if(fieldValue.contains(".")) {
                            int indexOf = fieldValue.indexOf(".");
                            fieldValue = fieldValue.substring(0, indexOf);
                        }
                    } catch (NumberFormatException e) {
                        //转失败则说明是普通字符串
                    }
                }
                //每一次循环第0行第n列的时候赋值给field  标题行
                field= String.valueOf(sheet.getRow(readRow-2).getCell(i));
                //把之前取到的服务信息 循环遍历
                for (int j = 0; j < queryFieldList.size(); j++) {
                    String filedName = String.valueOf(queryFieldList.get(j).get("label_name")).replaceAll("\\*", "");
                    //从fieldList 中获取到字段名称和 field 标题行中字段是对应
                    if (field.equals(filedName)) {
                        //验证是否启用 字典值
                        String dictCode=String.valueOf(queryFieldList.get(j).get("dict_code"));
                        String showType=String.valueOf(queryFieldList.get(j).get("show_type"));
                        if (StringUtil.isNotEmpty(dictCode)) {
                            // 使用 Set 存储 showType 的合法值，减少多个 equals 的判断
                            Set<String> validShowTypes = new HashSet<>(Arrays.asList("checkbox", "selectCheckbox", "selectSearch"));
                            if(validShowTypes.contains(showType)){
                                String[] fieldDataValueArray=fieldValue.split(",");
                                String showValue="";
                                for(int yyy=0;yyy<fieldDataValueArray.length;yyy++) {
                                    showValue+= dictList.get(dictCode).get(fieldDataValueArray[yyy])+",";
                                }
                                if(!showValue.equals("")){
                                    fieldValue=","+showValue;
                                }else{
                                    fieldValue=showValue;
                                }
                            }else{
                                String dictValue=dictList.get(dictCode).get(fieldValue)+"";
                                if(!dictValue.equals("null")){
                                    fieldValue=dictValue;
                                }
                            }
                        }else if(showType.equals("date")){
                            // 检查是否已经是正确的格式，如果是则不转换
                            fieldValue = fieldValue.matches("\\d{8}") ?  // 检查是否是yyyyMMdd格式
                                    LocalDate.parse(fieldValue, DateTimeFormatter.ofPattern("yyyyMMdd"))
                                            .format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))
                                    : fieldValue;  // 如果不是，直接使用原始字符串

                        }
                        break;
                    }
                }
                if(filedNameList.size()<=i){//大于或等于的时候说明数据列比标题列多，说明该列是无需入库
                    continue;
                }
                String fieldName=String.valueOf(filedNameList.get(i));
                String canNull=String.valueOf(canNullMap.get(fieldName));
                if(canNull.equals("Y")){//必填项
                    if(fieldValue.equals("")){
                        retJson.putAll(JackJSONObject.fromObject(ApiDao.getReturnDataByCode("5024",jdbcTemplate)));
                        retJson.put("message","\""+labelNameMap.get(fieldName)+"\"为必填项，请检查文件第"+(xxx+1)+"行数据后再重新上传");
                        redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\""+labelNameMap.get(fieldName)+"为必填项，请检查文件第"+(xxx+1)+"行数据后再重新上传\",\"status\":\"5020\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
                        return new HashMap<>();
                    }else{
                        dataMap.put(fieldName,fieldValue);
                    }
                }else{//非必填项
                    dataMap.put(fieldName,fieldValue);
                }
            }
            if(fieldMap.get("create_time")){
                //导入有create_time列,但未填写值 默认给当前时间
                if(dataMap.get("create_time")==null){
                    dataMap.put("create_time",timeFormat.format(date));
                    dataMap.put("create_date",dateFormat.format(date));
                }else{
                    try {
                        //导入有create_time列 有值，验证格式是否符合否则 走catch 获取系统当前时间
                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        format.setLenient(false);
                        format.parse((String) dataMap.get("create_time"));
                    } catch (ParseException e) {
                        dataMap.put("create_time",timeFormat.format(date));
                        dataMap.put("create_date",dateFormat.format(date));
                    }
                }
            }else{
                dataMap.put("create_time",timeFormat.format(date));
                dataMap.put("create_date",dateFormat.format(date));
            }
            if(!fieldMap.get("create_user")){
                BaseDao.setDefaultValue(dataMap,user);
            }else{
                String createUser=String.valueOf(dataMap.get("create_user"));
                String sql="select * from tbl_system_user where user_name='"+createUser+"'";
                List<Map<String, Object>> userList=jdbcTemplate.queryForList(sql);//todo 导入用户不存在校验
                dataMap.put("create_user",userList.get(0).get("user_name"));
                dataMap.put("create_user_id",userList.get(0).get("id"));
                dataMap.put("create_org_id",userList.get(0).get("org_id"));
                dataMap.put("create_org_name",userList.get(0).get("org_name"));
            }
            if(!fieldMap.get("page_parent_uuid")&&(parentUUID.length()>0&&!parentUUID.equals("null"))){
                dataMap.put("page_parent_uuid",parentUUID);
            }
            dataMap.put("data_source","excel");
            dataMaps.add(dataMap);
            Integer sumRow=sheet.getPhysicalNumberOfRows();
            Integer errorRow=sumRow-xxx-(readRow-1);//总行数减去成功和标题行得出未导入的。
            String errorText="";
            if(errorRow>0){
                errorText="，失败："+errorRow+"条，请检查文件第"+(xxx+2)+"行";//加2是因为从0开始和去掉标题列
            }
            returnMap.put("importMessage"," 导入了"+(xxx-readRow+2)+"条"+errorText);//提前设置导入成功message
        }
        redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\"解析完成共"+(sheet.getPhysicalNumberOfRows()-readRow+1)+"条，开始检测数据是否规范\",\"status\":\"ok\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
        return returnMap;
    }

    /**
     * @param fieldList 字段信息
     * @param headMap   存取labelName做为列标题
     * @param headList  列标题顺序
     * @param dataList  查询的结果集
     * @param list      最终导出的结果集（将字典转换为对应的值）
     * @param jdbcTemplate
     */
    public static void exportExcelDataByXlsx(List<Map<String,Object>> fieldList,Map<String,Object>  headMap,List<String> headList,List<Map<String, Object>> dataList,List<Map<String,Object>> list,JdbcTemplate jdbcTemplate) {
        Map<String, Object> fieldInfoMap=new HashMap();
        Map<String,Map<String,Object>> fieldToDictItemsMap=new HashMap();
        for (int i = 0; i < fieldList.size(); i++) {
            if (fieldList.get(i).get("is_export_excel") != null && fieldList.get(i).get("is_export_excel").equals("Y")) {
                String fieldName=String.valueOf(fieldList.get(i).get("name"));
                String labelName=String.valueOf(fieldList.get(i).get("label_name")).replaceAll("\\*", "");
                headMap.put(fieldName,labelName);
                headList.add(fieldName);
            }
        }
        headMap.put("headList",null);//headList作用是排序。
        for(int i=0;i<fieldList.size();i++) {
            Map<String,Object> fieldInfo=fieldList.get(i);
            fieldInfoMap.put(String.valueOf(fieldInfo.get("name")), fieldInfo);
            String dictCode=String.valueOf(fieldInfo.get("dict_code"));
            if(StringUtil.isEmpty(dictCode)){
                continue;
            }
            if(fieldToDictItemsMap.get(dictCode)==null) {
                if(dictCode.indexOf("o_")==0) {
                    Map<String,Object> dictMap= DictUtil.getOutDictInfoByDictCodeToMap(dictCode,false,jdbcTemplate);
                    fieldToDictItemsMap.put(dictCode,dictMap);
                }else if(dictCode.indexOf("tree_")==0){
                    String treeShowValueType=String.valueOf(fieldInfo.get("tree_show_value_type"));
                    Map<String,Object> dictMap=new HashMap<>();
                    if(treeShowValueType.equals("full")){
                        String treeShowFullSplit=String.valueOf(fieldInfo.get("tree_show_full_split"));
                        dictMap=DictUtil.getTreeDictInfoByDictCodeToFullMap(dictCode,false,null,jdbcTemplate,treeShowFullSplit);
                    }else{
                        dictMap=DictUtil.getTreeDictInfoByDictCodeToMap(dictCode,false,null,jdbcTemplate);
                    }

                    fieldToDictItemsMap.put(dictCode,dictMap);
                }else if(dictCode.indexOf("tp_")==0){
                    //第三方字典
                }else{//内部字典
                    Map<String,Object> dictMap=DictUtil.getDictInfoByDictCodeToMap(dictCode,false,jdbcTemplate);
                    fieldToDictItemsMap.put(dictCode,dictMap);
                }
            }
        }
        // 输出表格内容
        for (int i = 0; i < dataList.size(); i++) {
            Map<String,Object> map=new HashMap<>();
            for (int xxx = 0; xxx < fieldList.size(); xxx++) {
                Map<String,Object> fieldMap=fieldList.get(xxx);
                if (fieldMap.get("is_export_excel") != null
                        && fieldMap.get("is_export_excel").equals("Y")) {
                    String name=String.valueOf(fieldMap.get("name"));
                    String value="";
                    String dictCode=String.valueOf(fieldMap.get("dict_code"));
                    String showType=String.valueOf(fieldMap.get("show_type"));
                    String valueKey=String.valueOf(dataList.get(i).get(name));
                    if(StringUtil.isNotEmpty(dictCode)&&!showType.equals("autocomplete")) {
                        if(dictCode.indexOf("tree_")==0){//tree字典
                            String str=String.valueOf(fieldToDictItemsMap.get(dictCode));
                            if(str.equals("null")){
                                String treeShowValueType=String.valueOf(fieldList.get(i).get("tree_show_value_type"));
                                Map<String,Object> dictMap=new HashMap<>();
                                if(treeShowValueType.equals("full")){
                                    dictMap=DictUtil.getTreeDictInfoByDictCodeToFullMap(dictCode,false,jdbcTemplate);
                                }else{
                                    dictMap=DictUtil.getTreeDictInfoByDictCodeToMap(dictCode,false,null,jdbcTemplate);
                                }

                                fieldToDictItemsMap.put(dictCode,dictMap);
                            }
                            value=String.valueOf(fieldToDictItemsMap.get(dictCode).get(valueKey));
                        }else{//内部字典
                            if(fieldMap.get("show_type").equals("checkbox")||fieldMap.get("show_type").equals("selectCheckbox")) {
                                String fieldDataValue=valueKey;
                                String[] fieldDataValueArray=fieldDataValue.split(",");
                                String showValue="";
                                for(int yyy=0;yyy<fieldDataValueArray.length;yyy++) {
                                    if(fieldDataValueArray[yyy].equals(""))continue;
                                    showValue+=fieldToDictItemsMap.get(dictCode).get(fieldDataValueArray[yyy])+",";
                                }
                                if(!showValue.equals(""))value=showValue.substring(0,showValue.length()-1);
                            }else {
                                value=fieldToDictItemsMap.get(dictCode).get(valueKey)+"";
                            }
                        }
                    }else {
                        value=TextUtil.htmlDecode(valueKey);
                    }
                    if(value.equals("null")){
                        value="";
                    }
                    map.put(name,value);
                }
            }
            list.add(map);
        }
    }

}
