package com.zksy.common.utils.file;

/**
 * @author Administrator
 * @version 1.0
 * @project credit-rating
 * @description Excel导入导出工具类
 * @date 2024/3/26 17:18:10
 */

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.zksy.common.exception.ServiceException;
import jodd.io.FileUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URL;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.*;
import java.util.Map.Entry;
import java.util.regex.Pattern;
@SuppressWarnings("unused")
@Slf4j
public class ExcelUtils {

    private static final String XLSX = ".xlsx";
    private static final String XLS = ".xls";
    public static final String ROW_MERGE = "row_merge";
    public static final String COLUMN_MERGE = "column_merge";
    private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final String ROW_NUM = "rowNum";
    private static final String ROW_DATA = "rowData";
    private static final String ROW_TIPS = "rowTips";
    private static final int CELL_OTHER = 0;
    private static final int CELL_ROW_MERGE = 1;
    private static final int CELL_COLUMN_MERGE = 2;
    private static final int IMG_HEIGHT = 30;
    private static final int IMG_WIDTH = 30;
    private static final char LEAN_LINE = '/';
    private static final int BYTES_DEFAULT_LENGTH = 10240;
    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();

    public static <T> List<T> readFile(File file, Class<T> clazz,Integer cellNum) throws Exception {
        JSONArray array = readFile(file,cellNum);
        return getBeanList(array, clazz);
    }

    public static <T> List<T> readMultipartFile(MultipartFile mFile, Class<T> clazz,Integer cellNum) throws Exception {
        JSONArray array = readMultipartFile(mFile,cellNum);
        return getBeanList(array, clazz);
    }
    /**
     * TODO 商标信息导入（带图片）
      * @param mFile
     * @param clazz
     * @param cellNum
     * @return java.util.List<T>
     * @author Administrator
     * @date 2024/4/2 16:52:04
     */
    public static <T> List<T> readCrmEnterpriseTrademarkInformationMultipartFile(MultipartFile mFile, Class<T> clazz,Integer cellNum,Integer column,String localFileServerUploadDir,String localFileServerMappingPath) throws Exception {
        JSONArray array = readCrmEnterpriseTrademarkInformationMultipartFile(mFile,cellNum,column, localFileServerUploadDir, localFileServerMappingPath);
        return getBeanList(array, clazz);
    }

    public static JSONArray readFile(File file,Integer cellNum) throws Exception {
        return readExcel(null, file,cellNum);
    }

    public static JSONArray readMultipartFile(MultipartFile mFile,Integer cellNum) throws Exception {
        return readExcel(mFile, null,cellNum);
    }
    public static JSONArray readCrmEnterpriseTrademarkInformationMultipartFile(MultipartFile mFile,Integer cellNum,Integer column,String localFileServerUploadDir,String localFileServerMappingPath) throws Exception {
        return readCrmEnterpriseTrademarkInformationExcel(mFile, null,cellNum,column, localFileServerUploadDir, localFileServerMappingPath);
    }

    public static Map<String, JSONArray> readFileManySheet(File file,Integer cellNum) throws Exception {
        return readExcelManySheet(null, file,cellNum);
    }

    public static Map<String, JSONArray> readFileManySheet(MultipartFile file,Integer cellNum) throws Exception {
        return readExcelManySheet(file, null,cellNum);
    }

    private static <T> List<T> getBeanList(JSONArray array, Class<T> clazz) throws Exception {
        List<T> list = new ArrayList<>();
        Map<Integer, String> uniqueMap = new HashMap<>(50);
        for (int i = 0; i < array.size(); i++) {
            list.add(getBean(clazz, array.getJSONObject(i), uniqueMap));
        }
        return list;
    }

    /**
     * 获取每个对象的数据
     */
    private static <T> T getBean(Class<T> c, JSONObject obj, Map<Integer, String> uniqueMap) throws Exception {
        T t = c.newInstance();
        Field[] fields = c.getDeclaredFields();
        List<String> errMsgList = new ArrayList<>();
        boolean hasRowTipsField = false;
        StringBuilder uniqueBuilder = new StringBuilder();
        int rowNum = 0;
        for (Field field : fields) {
            // 行号
            if (field.getName().equals(ROW_NUM)) {
                rowNum = obj.getInteger(ROW_NUM);
                field.setAccessible(true);
                field.set(t, rowNum);
                continue;
            }
            // 是否需要设置异常信息
            if (field.getName().equals(ROW_TIPS)) {
                hasRowTipsField = true;
                continue;
            }
            // 原始数据
            if (field.getName().equals(ROW_DATA)) {
                field.setAccessible(true);
                field.set(t, obj.toString());
                continue;
            }
            // 设置对应属性值
            setFieldValue(t, field, obj, uniqueBuilder, errMsgList);
        }
        // 数据唯一性校验
        if (uniqueBuilder.length() > 0) {
            if (uniqueMap.containsValue(uniqueBuilder.toString())) {
                Set<Integer> rowNumKeys = uniqueMap.keySet();
                for (Integer num : rowNumKeys) {
                    if (uniqueMap.get(num).equals(uniqueBuilder.toString())) {
                        errMsgList.add(String.format("数据唯一性校验失败,(%s)与第%s行重复)", uniqueBuilder, num));
                    }
                }
            } else {
                uniqueMap.put(rowNum, uniqueBuilder.toString());
            }
        }
        // 失败处理
        if (errMsgList.isEmpty() && !hasRowTipsField) {
            return t;
        }
        StringBuilder sb = new StringBuilder();
        int size = errMsgList.size();
        for (int i = 0; i < size; i++) {
            if (i == size - 1) {
                sb.append(errMsgList.get(i));
            } else {
                sb.append(errMsgList.get(i)).append(";");
            }
        }
        // 设置错误信息
        for (Field field : fields) {
            if (field.getName().equals(ROW_TIPS)) {
                field.setAccessible(true);
                field.set(t, sb.toString());
            }
        }
        return t;
    }
    private static <T> void setFieldValue(T t, Field field, JSONObject obj, StringBuilder uniqueBuilder, List<String> errMsgList) {
        // 获取 ExcelImport 注解属性
        ExcelImport annotation = field.getAnnotation(ExcelImport.class);
        if (annotation == null) {
            return;
        }
        String cname = annotation.value();
        if (cname.trim().length() == 0) {
            return;
        }
        // 获取具体值
        String val = null;
        //截取表头数据
        JSONObject jsonObject = new JSONObject();
        JSONObject resultJson = new JSONObject();
        StringBuffer sb = new StringBuffer();
        for(Entry<String,Object> entry : obj.entrySet()){
            String output = null;
            String result = null;
            if(entry.getKey() != null) {
                int newlineIndex = entry.getKey().indexOf('\n');
                if (newlineIndex != -1) {
                    output = entry.getKey().substring(0, newlineIndex);
                } else {
                    output = entry.getKey(); // 如果没有找到\n，则返回整个字符串
                }
                result = entry.getKey().replaceAll("\\s","");
            }
            jsonObject.put(output,entry.getValue());
            resultJson.put(result,entry.getValue());
        }

        if (jsonObject.containsKey(cname)) {
            //去掉换行后的表头
            val = getString(jsonObject.getString(cname));
        }else{
            val = getString(resultJson.getString(cname));
        }
        if (val == null) {
            return;
        }
        field.setAccessible(true);
        // 判断是否必填
        boolean require = annotation.required();
        if (require && val.isEmpty()) {
            errMsgList.add(String.format("[%s]不能为空", cname));
            return;
        }
        // 数据唯一性获取
        boolean unique = annotation.unique();
        if (unique) {
            if (uniqueBuilder.length() > 0) {
                uniqueBuilder.append("--").append(val);
            } else {
                uniqueBuilder.append(val);
            }
        }
        // 判断是否超过最大长度
        int maxLength = annotation.maxLength();
        if (maxLength > 0 && val.length() > maxLength) {
            errMsgList.add(String.format("[%s]长度不能超过%s个字符(当前%s个字符)", cname, maxLength, val.length()));
        }
        // 判断当前属性是否有映射关系
        LinkedHashMap<String, String> kvMap = getKvMap(annotation.kv());
        if (!kvMap.isEmpty()) {
            boolean isMatch = false;
            for (String key : kvMap.keySet()) {
                if (kvMap.get(key).equals(val)) {
                    val = key;
                    isMatch = true;
                    break;
                }
            }
            if (!isMatch) {
                errMsgList.add(String.format("[%s]的值不正确(当前值为%s)", cname, val));
                return;
            }
        }
        // 其余情况根据类型赋值
        String fieldClassName = field.getType().getSimpleName();
        try {
            if ("String".equalsIgnoreCase(fieldClassName)) {
                field.set(t, val);
            } else if ("boolean".equalsIgnoreCase(fieldClassName)) {
                field.set(t, Boolean.valueOf(val));
            } else if ("int".equalsIgnoreCase(fieldClassName) || "Integer".equals(fieldClassName)) {
                try {
                    field.set(t, Integer.valueOf(val));
                } catch (NumberFormatException e) {
                    errMsgList.add(String.format("[%s]的值格式不正确(当前值为%s)", cname, val));
                }
            } else if ("double".equalsIgnoreCase(fieldClassName)) {
                field.set(t, Double.valueOf(val));
            } else if ("long".equalsIgnoreCase(fieldClassName)) {
                field.set(t, Long.valueOf(val));
            } else if ("BigDecimal".equalsIgnoreCase(fieldClassName)) {
                field.set(t, new BigDecimal(val));
            } else if ("Date".equalsIgnoreCase(fieldClassName)) {
                val = val.trim().replaceAll("\\u00A0", " ").trim();
                val = val.replace("/", "-");
                if (!val.contains("/") && !val.contains("-")) {
                    String stringDate = convertExcelNumberToDate(Long.parseLong(val));
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
                    try {
                        Date date = formatter.parse(stringDate);
                        field.set(t, date); // 直接将 Date 对象赋值给 field
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                } else {
                    // 如果 val 包含了分隔符，你可能需要解析这个字符串
                    SimpleDateFormat sdf;
                    if (val.contains(" ")) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    } else {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    try {
                        Date parsedDate = sdf.parse(val);
                        field.set(t, parsedDate);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            } else if ("LocalDate".equalsIgnoreCase(fieldClassName)) {
                val = val.trim().replaceAll("\\u00A0", " ").trim();
                val = val.replace("/", "-");
                if (!val.contains("/") && !val.contains("-")) {
                    String stringDate = convertExcelNumberToDate(Long.parseLong(val));
                    try {
                        LocalDate date = LocalDate.parse(stringDate);
                        field.set(t, date); // 直接将 Date 对象赋值给 field
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                } else {
                    // 如果 val 包含了分隔符，你可能需要解析这个字符串
                    DateTimeFormatter sdf;
                    if (val.contains(" ")) {
                        sdf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                    } else {
                        sdf = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                    }
                    try {
                        LocalDate parsedDate = LocalDate.parse(val,sdf);
                        field.set(t, parsedDate);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static String convertExcelNumberToDate(long excelDate) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(DateUtil.getJavaDate(excelDate));
        return String.format("%d/%d/%d", cal.get(Calendar.YEAR), cal.get(Calendar.MONTH) + 1, cal.get(Calendar.DAY_OF_MONTH));
    }
    private static Map<String, JSONArray> readExcelManySheet(MultipartFile mFile, File file,Integer cellNum) throws IOException {
        Workbook book = getWorkbook(mFile, file);
        if (book == null) {
            return Collections.emptyMap();
        }
        Map<String, JSONArray> map = new LinkedHashMap<>();
        for (int i = 0; i < book.getNumberOfSheets(); i++) {
            Sheet sheet = book.getSheetAt(i);
            JSONArray arr = readSheet(sheet,cellNum);
            map.put(sheet.getSheetName(), arr);
        }
        book.close();
        return map;
    }

    private static JSONArray readExcel(MultipartFile mFile, File file,Integer cellNum) throws IOException {
        Workbook book = getWorkbook(mFile, file);
        if (book == null) {
            return new JSONArray();
        }
        JSONArray array = readSheet(book.getSheetAt(0),cellNum);
        book.close();
        return array;
    }

    private static JSONArray readCrmEnterpriseTrademarkInformationExcel(MultipartFile mFile, File file,Integer cellNum,Integer column,String localFileServerUploadDir,String localFileServerMappingPath) throws IOException {
        Workbook book = getWorkbook(mFile, file);
        if (book == null) {
            return new JSONArray();
        }
        JSONArray array = readCrmEnterpriseTrademarkInformationSheet(mFile,book.getSheetAt(0),cellNum,column, localFileServerUploadDir, localFileServerMappingPath);
        book.close();
        return array;
    }

public static Map<String, PictureData> readPicture(MultipartFile mFile) throws IOException {
    if (mFile == null) {
        return null;
    }
        XSSFWorkbook workbook = new XSSFWorkbook(mFile.getInputStream());
        XSSFSheet sheet = workbook.getSheetAt(0);
        Map<String, PictureData> pictures = getPictures(sheet);
        return pictures;
    }
    private static String readPath(Map<String, PictureData> map,int row,Integer column,String localFileServerUploadDir,String localFileServerMappingPath) {
        String fileName = UUID.randomUUID()+".jpg";
        String path = "";
        String url = "";
        try {
            // 获取对应列
            String imageKey = row + ":" + column;
            if (map.containsKey(imageKey)) {
                PictureData pictureData = map.get(imageKey);
                byte[] image = pictureData.getData();
                // 通过hutool工具把图片保存到指定位置
                if (image != null) {
                    //加个时间戳，尽量避免文件名称重复
                    path = localFileServerUploadDir + fileName;
                    //创建文件路径
                    File dest = new File(path);
                    //判断文件是否已经存在
                    if (dest.exists()) {
                        throw new Exception("文件夹已存在");
                    }
                    //判断文件父目录是否存在
                    if (!dest.getParentFile().exists()) {
                        dest.getParentFile().mkdir();
                    }
                    FileUtil.writeBytes(path, image);
                    url = localFileServerMappingPath+"/"+fileName;
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return url;
    }
    private static Workbook getWorkbook(MultipartFile mFile, File file) throws IOException {
        InputStream in = null;
        PushbackInputStream pushbackIn = null;
        try {
            if (mFile != null) {
                in = mFile.getInputStream();
            } else if (file != null && file.exists()) {
                in = new FileInputStream(file);
            } else {
                throw new IllegalArgumentException("Both MultipartFile and File are null or do not exist.");
            }

            // 使用PushbackInputStream包装原始输入流，以便在检测文件类型后回退到开始
            pushbackIn = new PushbackInputStream(in, 8);

            // 尝试读取前几个字节来判断文件类型，而不是直接创建POIFSFileSystem
            byte[] header = new byte[8];
            int bytesRead = pushbackIn.read(header);
            if (bytesRead == 8 && isOOXMLHeader(header)) {
                // 是.xlsx文件
                pushbackIn.unread(header); // 回退读取的字节
                return new XSSFWorkbook(pushbackIn);
            } else if (bytesRead > 0) {
                // 重置输入流到开始，可能是其他类型或旧版Excel文件
                pushbackIn.unread(header, 0, bytesRead);
                try {
                    return new HSSFWorkbook(pushbackIn);
                } catch (NotOLE2FileException e) {
                    throw new IllegalArgumentException("Unsupported file format. The file is neither OLE2 (.xls) nor OOXML (.xlsx).", e);
                }
            } else {
                throw new IOException("Failed to read file header.");
            }
        } catch (IOException e) {
            throw new ServiceException("无法读取文件或文件格式不支持，请确保它是有效的Excel文件（.xls或.xlsx）", e);
        } finally {
            if (pushbackIn != null) {
                pushbackIn.close(); // 关闭PushbackInputStream会自动关闭底层流
            } else if (in != null) {
                in.close();
            }
        }
    }

    private static boolean isOOXMLHeader(byte[] header) {
        // OOXML（.xlsx）文件的前几个字节应该是：50 4B 03 04
        return header[0] == 0x50 && header[1] == 0x4B && header[2] == 0x03 && header[3] == 0x04;
    }

    private static JSONArray readSheet(Sheet sheet,Integer cellNum) throws IOException {
        boolean isFirstRowMerged = false;
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellAddresses = sheet.getMergedRegion(i);
            if (cellAddresses.getFirstRow() == sheet.getFirstRowNum()) {
                isFirstRowMerged = true;
                break;
            }
        }

        int rowStart;
        if (isFirstRowMerged) {
            rowStart = sheet.getFirstRowNum() + 1;
        } else {
            rowStart = sheet.getFirstRowNum();
        }
        // 尾行下标
        int rowEnd = sheet.getLastRowNum();
        // 获取表头行
        Row headRow = sheet.getRow(rowStart);

        if (headRow == null) {
            return new JSONArray();
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<>();
        for (int j = cellStart; j < cellEnd; j++) {
            // 获取表头数据
            String val = getCellValue(headRow.getCell(j));
            if (val != null && val.trim().length() != 0 && !"序号".equals(val)) {
                keyMap.put(j, val);
            }
        }
        // 如果表头没有数据则不进行解析
        if (keyMap.isEmpty()) {
            return (JSONArray) Collections.emptyList();
        }
        //根据表头列数判断是否为正确表格
        if(keyMap.size() != cellNum && cellEnd != cellNum){
            throw new ServiceException("导入模板不正确");
        }
        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        // 如果首行与尾行相同，表明只有一行，返回表头数据
        if (rowStart == rowEnd) {
            /*JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, 1);
            for (int i : keyMap.keySet()) {
                obj.put(keyMap.get(i), "");
            }
            array.add(obj);*/
            return array;
        }
        // 获取工作表中所有合并单元格的信息
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();

        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            if (eachRow == null) {
                // 根据实际情况决定是否需要处理或记录这种情况，比如打印日志或者跳过
                continue; // 直接跳过这一行
            }
            JSONObject obj = new JSONObject();
            StringBuilder sb = new StringBuilder();

            // 遍历列前，先初始化一个变量来存储当前单元格是否属于合并单元格
            boolean isMergedCell = false;

            for (int k = cellStart; k < cellEnd; k++) {
                    Cell cell = eachRow.getCell(k);
                    if (cell != null) {
                        String val = getCellValue(cell);

                        // 检查当前单元格是否属于任何合并单元格
                        for (CellRangeAddress region : mergedRegions) {
                            if (region.isInRange(i, k)) { // 如果当前单元格在合并单元格内
                                // 获取合并单元格的左上角单元格的实际值（假设数据只填写在左上角）
                                val = getCellValue(sheet.getRow(region.getFirstRow()).getCell(region.getFirstColumn()));
                                isMergedCell = true; // 标记此单元格已处理过合并情况
                                break;
                            }
                        }

                        // 所有数据添加到里面，用于判断该行是否为空
                        sb.append(val);
                        obj.put(keyMap.get(k), val);

                        // 如果这是一个新的合并单元格的开始，重置isMergedCell为false，以便后续循环能正确处理下一个合并单元格或普通单元格
                        if (isMergedCell && k == cellStart) {
                            isMergedCell = false;
                        }
                    }
            }

            if (sb.length() > 0 || isMergedCell) { // 确保即使行数据仅由合并单元格组成也能被加入
                array.add(obj);
            }
        }

        return array;
    }

    private static JSONArray readCrmEnterpriseTrademarkInformationSheet(MultipartFile file,Sheet sheet,Integer cellNum,Integer column,String localFileServerUploadDir,String localFileServerMappingPath) throws IOException {
        Map<Integer ,CellRangeAddress> map = new HashMap<>();
        for(int i=0;i<sheet.getNumMergedRegions();i++){
            CellRangeAddress cellAddresses = sheet.getMergedRegion(i);
            map.put(cellAddresses.getFirstColumn(),cellAddresses);
        }
        int rowStart;
        if(sheet.getNumMergedRegions() > 0){
            rowStart = sheet.getFirstRowNum()+1;
        }else{
            // 首行下标
            rowStart = sheet.getFirstRowNum();
        }
        // 尾行下标
        int rowEnd = sheet.getLastRowNum();
        // 获取表头行
        Row headRow = sheet.getRow(rowStart);
        if (headRow == null) {
            return new JSONArray();
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<>();
        for (int j = cellStart; j < cellEnd; j++) {
            // 获取表头数据
            String val = getCellValue(headRow.getCell(j));
            if (val != null && val.trim().length() != 0 && !"序号".equals(val)) {
                keyMap.put(j, val);
            }
        }
        // 如果表头没有数据则不进行解析
        if (keyMap.isEmpty()) {
            return (JSONArray) Collections.emptyList();
        }
        //根据表头列数判断是否为正确表格
        if(keyMap.size() != cellNum){
            throw new ServiceException("导入模板不正确");
        }
        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        // 如果首行与尾行相同，表明只有一行，返回表头数据
        if (rowStart == rowEnd) {
            /*JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, 1);
            for (int i : keyMap.keySet()) {
                obj.put(keyMap.get(i), "");
            }
            array.add(obj);*/
            return array;
        }
        Map<String, PictureData> map1 = readPicture(file);
        for (int i = rowStart + 1; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            // 添加行号
            obj.put(ROW_NUM, i + 1);
            StringBuilder sb = new StringBuilder();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    String val = getCellValue(eachRow.getCell(k));
                    if(k==column){
                        val = readPath(map1,eachRow.getCell(k).getRowIndex(),column, localFileServerUploadDir, localFileServerMappingPath);
                    }
                    // 所有数据添加到里面，用于判断该行是否为空
                    sb.append(val);
                    obj.put(keyMap.get(k), val);
                }
            }
            if (sb.length() > 0) {
                array.add(obj);
            }
        }
        return array;
    }


    private static Map<String, PictureData> getPictures(XSSFSheet sheet) {
        Map<String, PictureData> picMap = new HashMap<>();
        // 获取绘图父级
        Drawing<?> drawing = sheet.getDrawingPatriarch();
        // 检查绘图父级是否为 null
        if (drawing == null) {
            return picMap; // 如果没有绘图父级，直接返回空的图片映射
        }
        List<XSSFShape> list = sheet.getDrawingPatriarch().getShapes();

        if (CollectionUtils.isEmpty(list)) {
            return picMap;
        }
        for (XSSFShape hssfShape : list) {
            // 过滤出图片的数据
            if (hssfShape instanceof XSSFPicture) {
                XSSFPicture xssfPicture = (XSSFPicture) hssfShape;
                XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor) xssfPicture.getAnchor();
                PictureData pictureData = xssfPicture.getPictureData();
                picMap.put(xssfClientAnchor.getRow1() + ":" + xssfClientAnchor.getCol1(), pictureData);
            }
        }
        return picMap;
    }
    private static String getCellValue(Cell cell) {
        // 空白或空
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return "";
        }
        // String类型
        if (cell.getCellType() == CellType.STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                return "";
            }
            return val.trim();
        }
        // 数字类型
        if (cell.getCellType() == CellType.NUMERIC) {
                try {
                    //时间格式
                    DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("d-LLL-yyyy", Locale.CHINESE);
                    LocalDate date = LocalDate.parse(cell.toString(), inputFormatter);
                    return date.toString();
                } catch (DateTimeParseException e) {
                    if(String.valueOf(cell).contains("E")) {
                        DataFormatter dataFormatter = new DataFormatter();
                        String formattedValue = dataFormatter.formatCellValue(cell); // 使用 DataFormatter 获取格式化后的值
                        return formattedValue;
                    }else {
                        String s = cell.getNumericCellValue() + "";
                        // 去掉尾巴上的小数点0
                        if (Pattern.matches(".*\\.0*", s)) {
                            return s.split("\\.")[0];
                        } else {
                            return s;
                        }
                    }
                }
        }
        // 布尔值类型
        if (cell.getCellType() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue() + "";
        }
        // 公式
        if (cell.getCellType() == CellType.FORMULA) {
            String s = cell.getNumericCellValue() + "";
            // 去掉尾巴上的小数点0
            if (Pattern.matches(".*\\.0*", s)) {
                return s.split("\\.")[0];
            } else if(s.contains("E")){
                DecimalFormat decimalFormat = new DecimalFormat("0");
                String decimalNumber = decimalFormat.format(Double.parseDouble(s));
                return decimalNumber;
            }else{
                return s;
            }
        }
        /*if (cell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        }*/
        return "";
    }

    // 新增方法用于判断是否可能是日期
    private static boolean isLikelyDate(Cell cell) {
        // 这里可以根据实际情况调整判断逻辑，例如检查是否包含特定的分隔符、月份名称等
        // 下面的例子简单检查是否为负数，避免误将负数识别为日期
        return !cell.toString().startsWith("-") && cell.toString().matches("\\d{1,2}-\\p{L}{3}-\\d{4}"); // 或者直接尝试转换看是否成功，这需要根据实际情况调整
    }

    public static <T> void exportTemplate(HttpServletResponse response, String fileName, Class<T> clazz) {
        exportTemplate(response, fileName, fileName, clazz, false);
    }

    public static <T> void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
                                          Class<T> clazz) {
        exportTemplate(response, fileName, sheetName, clazz, false);
    }

    public static <T> void exportTemplate(HttpServletResponse response, String fileName, Class<T> clazz,
                                          boolean isContainExample) {
        exportTemplate(response, fileName, fileName, clazz, isContainExample);
    }

    public static <T> void exportTemplate(HttpServletResponse response, String fileName, String sheetName,
                                          Class<T> clazz, boolean isContainExample) {
        // 获取表头字段
        List<ExcelClassField> headFieldList = getExcelClassFieldList(clazz);
        // 获取表头数据和示例数据
        List<List<Object>> sheetDataList = new ArrayList<>();
        List<Object> headList = new ArrayList<>();
        List<Object> exampleList = new ArrayList<>();
        Map<Integer, List<String>> selectMap = new LinkedHashMap<>();
        for (int i = 0; i < headFieldList.size(); i++) {
            ExcelClassField each = headFieldList.get(i);
            headList.add(each.getName());
            exampleList.add(each.getExample());
            LinkedHashMap<String, String> kvMap = each.getKvMap();
            if (kvMap != null && kvMap.size() > 0) {
                selectMap.put(i, new ArrayList<>(kvMap.values()));
            }
        }
        sheetDataList.add(headList);
        if (isContainExample) {
            sheetDataList.add(exampleList);
        }
        // 导出数据
        export(response, fileName, sheetName, sheetDataList, selectMap);
    }

    private static <T> List<ExcelClassField> getExcelClassFieldList(Class<T> clazz) {
        // 解析所有字段
        Field[] fields = clazz.getDeclaredFields();
        boolean hasExportAnnotation = false;
        Map<Integer, List<ExcelClassField>> map = new LinkedHashMap<>();
        List<Integer> sortList = new ArrayList<>();
        for (Field field : fields) {
            ExcelClassField cf = getExcelClassField(field);
            if (cf.getHasAnnotation() == 1) {
                hasExportAnnotation = true;
            }
            int sort = cf.getSort();
            if (map.containsKey(sort)) {
                map.get(sort).add(cf);
            } else {
                List<ExcelClassField> list = new ArrayList<>();
                list.add(cf);
                sortList.add(sort);
                map.put(sort, list);
            }
        }
        Collections.sort(sortList);
        // 获取表头
        List<ExcelClassField> headFieldList = new ArrayList<>();
        if (hasExportAnnotation) {
            for (Integer sort : sortList) {
                for (ExcelClassField cf : map.get(sort)) {
                    if (cf.getHasAnnotation() == 1) {
                        headFieldList.add(cf);
                    }
                }
            }
        } else {
            headFieldList.addAll(map.get(0));
        }
        return headFieldList;
    }

    private static ExcelClassField getExcelClassField(Field field) {
        ExcelClassField cf = new ExcelClassField();
        String fieldName = field.getName();
        cf.setFieldName(fieldName);
        ExcelExport annotation = field.getAnnotation(ExcelExport.class);
        // 无 ExcelExport 注解情况
        if (annotation == null) {
            cf.setHasAnnotation(0);
            cf.setName(fieldName);
            cf.setSort(0);
            return cf;
        }
        // 有 ExcelExport 注解情况
        cf.setHasAnnotation(1);
        cf.setName(annotation.value());
        String example = getString(annotation.example());
        if (!example.isEmpty()) {
            if (isNumeric(example) && example.length() < 8) {
                cf.setExample(Double.valueOf(example));
            } else {
                cf.setExample(example);
            }
        } else {
            cf.setExample("");
        }
        cf.setSort(annotation.sort());
        // 解析映射
        String kv = getString(annotation.kv());
        cf.setKvMap(getKvMap(kv));
        return cf;
    }

    private static LinkedHashMap<String, String> getKvMap(String kv) {
        LinkedHashMap<String, String> kvMap = new LinkedHashMap<>();
        if (kv.isEmpty()) {
            return kvMap;
        }
        String[] kvs = kv.split(";");
        if (kvs.length == 0) {
            return kvMap;
        }
        for (String each : kvs) {
            String[] eachKv = getString(each).split("-");
            if (eachKv.length != 2) {
                continue;
            }
            String k = eachKv[0];
            String v = eachKv[1];
            if (k.isEmpty() || v.isEmpty()) {
                continue;
            }
            kvMap.put(k, v);
        }
        return kvMap;
    }

    /**
     * 导出表格到本地
     *
     * @param file      本地文件对象
     * @param sheetData 导出数据
     */
    public static void exportFile(File file, List<List<Object>> sheetData) {
        if (file == null) {
            System.out.println("文件创建失败");
            return;
        }
        if (sheetData == null) {
            sheetData = new ArrayList<>();
        }
        Map<String, List<List<Object>>> map = new HashMap<>();
        map.put(file.getName(), sheetData);
        export(null, file, file.getName(), map, null);
    }

    /**
     * 导出表格到本地
     *
     * @param <T>      导出数据类似，和K类型保持一致
     * @param filePath 文件父路径（如：D:/doc/excel/）
     * @param fileName 文件名称（不带尾缀，如：学生表）
     * @param list     导出数据
     * @throws IOException IO异常
     */
    public static <T> File exportFile(String filePath, String fileName, List<T> list) throws IOException {
        File file = getFile(filePath, fileName);
        List<List<Object>> sheetData = getSheetData(list);
        exportFile(file, sheetData);
        return file;
    }

    /**
     * 获取文件
     *
     * @param filePath filePath 文件父路径（如：D:/doc/excel/）
     * @param fileName 文件名称（不带尾缀，如：用户表）
     * @return 本地File文件对象
     */
    private static File getFile(String filePath, String fileName) throws IOException {
        String dirPath = getString(filePath);
        String fileFullPath;
        if (dirPath.isEmpty()) {
            fileFullPath = fileName;
        } else {
            // 判定文件夹是否存在，如果不存在，则级联创建
            File dirFile = new File(dirPath);
            if (!dirFile.exists()) {
                boolean mkdirs = dirFile.mkdirs();
                if (!mkdirs) {
                    return null;
                }
            }
            // 获取文件夹全名
            if (dirPath.endsWith(String.valueOf(LEAN_LINE))) {
                fileFullPath = dirPath + fileName + XLSX;
            } else {
                fileFullPath = dirPath + LEAN_LINE + fileName + XLSX;
            }
        }
        System.out.println(fileFullPath);
        File file = new File(fileFullPath);
        if (!file.exists()) {
            boolean result = file.createNewFile();
            if (!result) {
                return null;
            }
        }
        return file;
    }

    private static <T> List<List<Object>> getSheetData(List<T> list) {
        // 获取表头字段
        List<ExcelClassField> excelClassFieldList = getExcelClassFieldList(list.get(0).getClass());
        List<String> headFieldList = new ArrayList<>();
        List<Object> headList = new ArrayList<>();
        Map<String, ExcelClassField> headFieldMap = new HashMap<>();
        for (ExcelClassField each : excelClassFieldList) {
            String fieldName = each.getFieldName();
            headFieldList.add(fieldName);
            headFieldMap.put(fieldName, each);
            headList.add(each.getName());
        }
        // 添加表头名称
        List<List<Object>> sheetDataList = new ArrayList<>();
        sheetDataList.add(headList);
        // 获取表数据
        for (T t : list) {
            Map<String, Object> fieldDataMap = getFieldDataMap(t);
            Set<String> fieldDataKeys = fieldDataMap.keySet();
            List<Object> rowList = new ArrayList<>();
            for (String headField : headFieldList) {
                if (!fieldDataKeys.contains(headField)) {
                    continue;
                }
                Object data = fieldDataMap.get(headField);
                if (data == null) {
                    rowList.add("");
                    continue;
                }
                ExcelClassField cf = headFieldMap.get(headField);
                // 判断是否有映射关系
                LinkedHashMap<String, String> kvMap = cf.getKvMap();
                if (kvMap == null || kvMap.isEmpty()) {
                    rowList.add(data);
                    continue;
                }
                String val = kvMap.get(data.toString());
                if (isNumeric(val)) {
                    rowList.add(Double.valueOf(val));
                } else {
                    rowList.add(val);
                }
            }
            sheetDataList.add(rowList);
        }
        return sheetDataList;
    }

    private static <T> Map<String, Object> getFieldDataMap(T t) {
        Map<String, Object> map = new HashMap<>();
        Field[] fields = t.getClass().getDeclaredFields();
        try {
            for (Field field : fields) {
                String fieldName = field.getName();
                field.setAccessible(true);
                Object object = field.get(t);
                map.put(fieldName, object);
            }
        } catch (IllegalArgumentException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return map;
    }

    public static void exportEmpty(HttpServletResponse response, String fileName) {
        List<List<Object>> sheetDataList = new ArrayList<>();
        List<Object> headList = new ArrayList<>();
        headList.add("导出无数据");
        sheetDataList.add(headList);
        export(response, fileName, sheetDataList);
    }

    public static void export(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList) {
        export(response, fileName, fileName, sheetDataList, null);
    }

    public static void exportManySheet(HttpServletResponse response, String fileName, Map<String, List<List<Object>>> sheetMap) {
        export(response, null, fileName, sheetMap, null);
    }


    public static void export(HttpServletResponse response, String fileName, String sheetName,
                              List<List<Object>> sheetDataList) {
        export(response, fileName, sheetName, sheetDataList, null);
    }

    public static void export(HttpServletResponse response, String fileName, String sheetName,
                              List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap) {

        Map<String, List<List<Object>>> map = new HashMap<>();
        map.put(sheetName, sheetDataList);
        export(response, null, fileName, map, selectMap);
    }

    public static <T, K> void export(HttpServletResponse response, String fileName, List<T> list, Class<K> template) {
        // list 是否为空
        boolean lisIsEmpty = list == null || list.isEmpty();
        // 如果模板数据为空，且导入的数据为空，则导出空文件
        if (template == null && lisIsEmpty) {
            exportEmpty(response, fileName);
            return;
        }
        // 如果 list 数据，则导出模板数据
        if (lisIsEmpty) {
            exportTemplate(response, fileName, template);
            return;
        }
        // 导出数据
        List<List<Object>> sheetDataList = getSheetData(list);
        export(response, fileName, sheetDataList);
    }

    public static void export(HttpServletResponse response, String fileName, List<List<Object>> sheetDataList, Map<Integer, List<String>> selectMap) {
        export(response, fileName, fileName, sheetDataList, selectMap);
    }

    private static void export(HttpServletResponse response, File file, String fileName,
                               Map<String, List<List<Object>>> sheetMap, Map<Integer, List<String>> selectMap) {
        // 整个 Excel 表格 book 对象
        SXSSFWorkbook book = new SXSSFWorkbook();
        // 每个 Sheet 页
        Set<Entry<String, List<List<Object>>>> entries = sheetMap.entrySet();
        for (Entry<String, List<List<Object>>> entry : entries) {
            List<List<Object>> sheetDataList = entry.getValue();
            Sheet sheet = book.createSheet(entry.getKey());
            Drawing<?> patriarch = sheet.createDrawingPatriarch();
            // 设置表头背景色（灰色）
            CellStyle headStyle = book.createCellStyle();
            headStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.index);
            headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headStyle.setAlignment(HorizontalAlignment.CENTER);
            headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
            // 设置表身背景色（默认色）
            CellStyle rowStyle = book.createCellStyle();
            rowStyle.setAlignment(HorizontalAlignment.CENTER);
            rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置表格列宽度（默认为15个字节）
            sheet.setDefaultColumnWidth(15);
            // 创建合并算法数组
            int rowLength = sheetDataList.size();
            int columnLength = sheetDataList.get(0).size();
            int[][] mergeArray = new int[rowLength][columnLength];
            for (int i = 0; i < sheetDataList.size(); i++) {
                // 每个 Sheet 页中的行数据
                Row row = sheet.createRow(i);
                List<Object> rowList = sheetDataList.get(i);
                for (int j = 0; j < rowList.size(); j++) {
                    // 每个行数据中的单元格数据
                    Object o = rowList.get(j);
                    int v = 0;
                    if (o instanceof URL) {
                        // 如果要导出图片的话, 链接需要传递 URL 对象
                        setCellPicture(book, row, patriarch, i, j, (URL) o);
                    } else {
                        Cell cell = row.createCell(j);
                        if (i == 0) {
                            // 第一行为表头行，采用灰色底背景
                            v = setCellValue(cell, o, headStyle);
                        } else {
                            // 其他行为数据行，默认白底色
                            v = setCellValue(cell, o, rowStyle);
                        }
                    }
                    mergeArray[i][j] = v;
                }
            }
            // 合并单元格
            mergeCells(sheet, mergeArray);
            // 设置下拉列表
            setSelect(sheet, selectMap);
        }
        // 写数据
        if (response != null) {
            // 前端导出
            try {
                write(response, book, fileName);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            // 本地导出
            FileOutputStream fos;
            try {
                fos = new FileOutputStream(file);
                ByteArrayOutputStream ops = new ByteArrayOutputStream();
                book.write(ops);
                fos.write(ops.toByteArray());
                fos.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 合并当前Sheet页的单元格
     *
     * @param sheet      当前 sheet 页
     * @param mergeArray 合并单元格算法
     */
    private static void mergeCells(Sheet sheet, int[][] mergeArray) {
        // 横向合并
        for (int x = 0; x < mergeArray.length; x++) {
            int[] arr = mergeArray[x];
            boolean merge = false;
            int y1 = 0;
            int y2 = 0;
            for (int y = 0; y < arr.length; y++) {
                int value = arr[y];
                if (value == CELL_COLUMN_MERGE) {
                    if (!merge) {
                        y1 = y;
                    }
                    y2 = y;
                    merge = true;
                } else {
                    merge = false;
                    if (y1 > 0) {
                        sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
                    }
                    y1 = 0;
                    y2 = 0;
                }
            }
            if (y1 > 0) {
                sheet.addMergedRegion(new CellRangeAddress(x, x, (y1 - 1), y2));
            }
        }
        // 纵向合并
        int xLen = mergeArray.length;
        int yLen = mergeArray[0].length;
        for (int y = 0; y < yLen; y++) {
            boolean merge = false;
            int x1 = 0;
            int x2 = 0;
            for (int x = 0; x < xLen; x++) {
                int value = mergeArray[x][y];
                if (value == CELL_ROW_MERGE) {
                    if (!merge) {
                        x1 = x;
                    }
                    x2 = x;
                    merge = true;
                } else {
                    merge = false;
                    if (x1 > 0) {
                        sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
                    }
                    x1 = 0;
                    x2 = 0;
                }
            }
            if (x1 > 0) {
                sheet.addMergedRegion(new CellRangeAddress((x1 - 1), x2, y, y));
            }
        }
    }

    private static void write(HttpServletResponse response, SXSSFWorkbook book, String fileName) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String name = new String(fileName.getBytes("GBK"), "ISO8859_1") + XLSX;
        response.addHeader("Content-Disposition", "attachment;filename=" + name);
        ServletOutputStream out = response.getOutputStream();
        book.write(out);
        out.flush();
        out.close();
    }

    private static int setCellValue(Cell cell, Object o, CellStyle style) {
        // 设置样式
        cell.setCellStyle(style);
        // 数据为空时
        if (o == null) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue("");
            return CELL_OTHER;
        }
        // 是否为字符串
        if (o instanceof String) {
            String s = o.toString();
            // 当数字类型长度超过8位时，改为字符串类型显示（Excel数字超过一定长度会显示为科学计数法）
            if (isNumeric(s) && s.length() < 8) {
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(Double.parseDouble(s));
                return CELL_OTHER;
            } else {
                cell.setCellType(CellType.STRING);
                cell.setCellValue(s);
            }
            if (s.equals(ROW_MERGE)) {
                return CELL_ROW_MERGE;
            } else if (s.equals(COLUMN_MERGE)) {
                return CELL_COLUMN_MERGE;
            } else {
                return CELL_OTHER;
            }
        }
        // 是否为字符串
        if (o instanceof Integer || o instanceof Long || o instanceof Double || o instanceof Float) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.parseDouble(o.toString()));
            return CELL_OTHER;
        }
        // 是否为Boolean
        if (o instanceof Boolean) {
            cell.setCellType(CellType.BOOLEAN);
            cell.setCellValue((Boolean) o);
            return CELL_OTHER;
        }
        // 如果是BigDecimal，则默认3位小数
        if (o instanceof BigDecimal) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(((BigDecimal) o).setScale(3, RoundingMode.HALF_UP).doubleValue());
            return CELL_OTHER;
        }
        // 如果是Date数据，则显示格式化数据
        if (o instanceof Date) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(formatDate((Date) o));
            return CELL_OTHER;
        }
        // 如果是其他，则默认字符串类型
        cell.setCellType(CellType.STRING);
        cell.setCellValue(o.toString());
        return CELL_OTHER;
    }

    private static void setCellPicture(SXSSFWorkbook wb, Row sr, Drawing<?> patriarch, int x, int y, URL url) {
        // 设置图片宽高
        sr.setHeight((short) (IMG_WIDTH * IMG_HEIGHT));
        // （jdk1.7版本try中定义流可自动关闭）
        try (InputStream is = url.openStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            byte[] buff = new byte[BYTES_DEFAULT_LENGTH];
            int rc;
            while ((rc = is.read(buff, 0, BYTES_DEFAULT_LENGTH)) > 0) {
                outputStream.write(buff, 0, rc);
            }
            // 设置图片位置
            XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, y, x, y + 1, x + 1);
            // 设置这个，图片会自动填满单元格的长宽
            anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
            patriarch.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String formatDate(Date date) {
        if (date == null) {
            return "";
        }
        SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
        return format.format(date);
    }

    private static void setSelect(Sheet sheet, Map<Integer, List<String>> selectMap) {
        if (selectMap == null || selectMap.isEmpty()) {
            return;
        }
        Set<Entry<Integer, List<String>>> entrySet = selectMap.entrySet();
        for (Entry<Integer, List<String>> entry : entrySet) {
            int y = entry.getKey();
            List<String> list = entry.getValue();
            if (list == null || list.isEmpty()) {
                continue;
            }
            String[] arr = new String[list.size()];
            for (int i = 0; i < list.size(); i++) {
                arr[i] = list.get(i);
            }
            DataValidationHelper helper = sheet.getDataValidationHelper();
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65000, y, y);
            DataValidationConstraint dvc = helper.createExplicitListConstraint(arr);
            DataValidation dv = helper.createValidation(dvc, addressList);
            if (dv instanceof HSSFDataValidation) {
                dv.setSuppressDropDownArrow(false);
            } else {
                dv.setSuppressDropDownArrow(true);
                dv.setShowErrorBox(true);
            }
            sheet.addValidationData(dv);
        }
    }

    private static boolean isNumeric(String str) {
        if (Objects.nonNull(str) && "0.0".equals(str)) {
            return true;
        }
        if ("".equals(str)){
            return false;
        }
        for (int i = str.length(); --i >= 0; ) {
            if (!Character.isDigit(str.charAt(i))) {
                return false;
            }
        }
        return true;
    }

    private static String getString(String s) {
        if (s == null) {
            return "";
        }
        if (s.isEmpty()) {
            return s;
        }
        return s.trim();
    }

}