package cc.smtweb.framework.core.util;

import cc.smtweb.framework.core.common.SwMap;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import sun.misc.BASE64Decoder;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;

/**
 * Excel导出工具类
 */
public class ExcelExportUtil {
    public static Integer MAX_EXCEL_COUNT = 60000;
    private static Map<Workbook, Map<String, CellStyle>> cacheMaps = null;

    public static void clearCellStyleCache(Workbook web) {
        if (cacheMaps != null) {
            cacheMaps.remove(web);
        }
    }

    public static CellStyle createCellStyle(Workbook wb, short fontSize, boolean isBold, boolean hasBorder, HorizontalAlignment align) {
        CellStyle cs = getCellStyle(wb, fontSize + "-" + isBold + "-" + hasBorder + "-" + align);
        Font f = wb.createFont();
        f.setFontHeightInPoints(fontSize);
        f.setBold(isBold);
        cs.setFont(f);
        if (hasBorder) {
            cs.setBorderBottom(BorderStyle.THIN);
            cs.setBorderLeft(BorderStyle.THIN);
            cs.setBorderRight(BorderStyle.THIN);
            cs.setBorderTop(BorderStyle.THIN);
        }
        cs.setAlignment(align);
        cs.setVerticalAlignment(VerticalAlignment.CENTER);
        cs.setWrapText(true);
        return cs;
    }

    private static CellStyle getCellStyle(Workbook wb, String key) {
        if (cacheMaps == null) {
            cacheMaps = new HashMap<>();
        }
        if (cacheMaps.size() > 1000) {
            cacheMaps = new HashMap<>();
        }
        Map<String, CellStyle> map = cacheMaps.computeIfAbsent(wb, k -> new HashMap<>());
        CellStyle cellStyle = map.get(key);
        if (cellStyle == null) {
            cellStyle = wb.createCellStyle();
            map.put(key, cellStyle);
        }
        return cellStyle;
    }

    public static Row createRow(Sheet sheet, int rowNum, int rowHeight) {
        Row row = sheet.getRow(rowNum);
        if (row != null) return row;
        row = sheet.createRow(rowNum);
        if (rowHeight > 0) row.setHeightInPoints(rowHeight);
        return row;
    }

    public static Cell createCell(Row row, int column) {
        Cell cell = row.getCell(column);
        if (cell != null) return cell;
        return row.createCell(column);
    }

    public static void writeMergeCell(Sheet sheet, int rowHeight, int fromRow, int endRow, int fromCol, int entCol, CellStyle cs, String text) {
        Row row = createRow(sheet, fromRow, rowHeight);
        Cell cell = createCell(row, fromCol);
        cell.setCellStyle(cs);
        cell.setCellValue(text);
        if (endRow != fromRow || entCol != fromCol) {
            sheet.addMergedRegion(new CellRangeAddress(fromRow, endRow, fromCol, entCol));
            for (int i = fromRow; i <= endRow; i++) {
                row = CellUtil.getRow(i, sheet);
                for (int j = fromCol; j <= entCol; j++) {
                    cell = CellUtil.getCell(row, (short) j);
                    cell.setCellStyle(cs);
                }
            }
        }
    }

    public static void writeMergeCell(Sheet sheet, int fromRow, int endRow, int fromCol, int entCol, CellStyle cs, String text) {
        writeMergeCell(sheet, 0, fromRow, endRow, fromCol, entCol, cs, text);
    }

    /**
     * @param row        行
     * @param colIndex   列号
     * @param cs         样式
     * @param text       内容
     * @param colType    列类型
     * @param ignoreZero 是否忽略零
     */
    public static void writeCell(Row row, int colIndex, CellStyle cs, String text, ColType colType, boolean ignoreZero) {
        Cell cell = createCell(row, colIndex);
        if (StringUtil.isNotEmpty(text)) text = text.replace("&nbsp;", " ");
        if (ColType.AMOUNT.equals(colType)) {//金额
            if (ignoreZero && MathUtil.isEqualsZero(text)) cell.setCellValue("");
            else {
                cell.setCellValue(StringUtil.getDoubleIgnoreErr(text));
            }
        } else if (ColType.NUMBER.equals(colType)) {//数字
            final int v = StringUtil.getIntIgnoreErr(text);
            if (ignoreZero && 0 == v) cell.setCellValue("");
            else {
                cell.setCellValue(v);
            }
        } else {//文本
            cell.setCellValue(text);
        }
        cell.setCellStyle(cs);
    }

    /**
     * 根据前端传回的导出模型导出
     *
     * @param modelJson 前端传回的导出模型
     * @throws Exception
     */
    public static String exportToExcel(List<Map<String, String>> data, String modelJson) throws Exception {
        return exportToExcel(data, modelJson, "");
    }

    public static String exportToExcel(List<Map<String, String>> data, String modelJson, Map<String, String> mapMergeField) throws Exception {
        SwMap model = new SwMap();
        model.putAll(JsonUtil.parseMap(modelJson));
        return exportToExcel(data, model, mapMergeField, null, (short) 9);
    }

    public static String exportToExcel(List<Map<String, String>> data, SwMap model, short defFontSize) throws Exception {
        return exportToExcel(data, model, null, defFontSize);
    }

    public static String exportToExcel(List<Map<String, String>> data, String modelJson, String fileName) throws Exception {
        SwMap model = new SwMap();
        model.putAll(JsonUtil.parseMap(modelJson));
        return exportToExcel(data, model, fileName, (short) 9);
    }

    public static String exportToExcel(List<Map<String, String>> data, SwMap model, String fileName, short defFontSize) throws Exception {
        return exportToExcel(data, model, null, fileName, defFontSize);
    }

    public static String exportToExcel(List<Map<String, String>> data, SwMap model, Map<String, String> mapMergeField, String fileName, short defFontSize) throws Exception {
//        File tempFile = File.createTempFile("xls_", ".xls", new File(System.getProperty("java.io.tmpdir")));
        // TODO: 2022/9/27 路径作测试用
        File tempFile = File.createTempFile("xls_", ".xls", new File("D:\\jjkj\\"));
        try (FileOutputStream fs = new FileOutputStream(tempFile)) {
            exportToExcel(fs, data, model, mapMergeField, defFontSize);
            fs.flush();
        }
        if (StringUtil.isEmpty(fileName)) fileName = model.readString("title");
        fileName = fileName + "_" + DateUtil.nowDateTimeString() + ".xls";
        return fileName + "@@" + tempFile.getAbsolutePath();
    }

    public static int getStrLength(String text) {
        if (StringUtil.isEmpty(text)) return 1;
        if (!text.contains("\n")) return text.getBytes().length;
        String[] ss = text.split("\n");
        int len = 0;
        for (String s : ss) {
            len = Math.max(len, s.getBytes().length);
        }
        return len;
    }

    public static void exportToExcel(OutputStream os, List<Map<String, String>> data, SwMap model, Map<String, String> mapMergeField, short defFontSize) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        int detSize = data.size();
        if (detSize == 0) {
            Sheet sheet = wb.createSheet();
            exportToExcelEx(sheet, wb, data, model, mapMergeField, defFontSize);
        } else {
            int sheetCount = detSize % MAX_EXCEL_COUNT;
            sheetCount = sheetCount != 0 ? detSize / MAX_EXCEL_COUNT + 1 : detSize / MAX_EXCEL_COUNT;
            List<String> listSheetName = new ArrayList<>();
            for (int i = 0; i < sheetCount; i++) {
                List<Map<String, String>> listDetailPage = new ArrayList<>();
                int endIndex = (i + 1) * MAX_EXCEL_COUNT;
                if (endIndex > detSize) {
                    endIndex = detSize;
                }
                for (int index = i * MAX_EXCEL_COUNT; index < endIndex; index++) {
                    listDetailPage.add(data.get(index));
                }
                String sheetName = sheetCount + "-" + (i + 1);
                listSheetName.add(sheetName);
                Sheet sheet = wb.createSheet(sheetName);
                exportToExcelEx(sheet, wb, listDetailPage, model, mapMergeField, defFontSize);
            }
            //删除非我们生成的sheet
            while (listSheetName.size() != wb.getNumberOfSheets()) {
                if (!listSheetName.contains(wb.getSheetName(0))) {
                    wb.removeSheetAt(0);
                }
            }
        }
        wb.write(os);
        clearCellStyleCache(wb);
    }

    private static String getMergeFieldValue(String keyField, Map<String, String> curRow, Map<String, String> mapMergeField) {
        if (mapMergeField == null || !mapMergeField.containsKey(keyField)) {
            return StringUtil.checkNull(curRow.get(keyField));
        }
        return StringUtil.checkNull(curRow.get(mapMergeField.get(keyField)));
    }

    private static void exportToExcelEx(Sheet sheet, HSSFWorkbook wb, List<Map<String, String>> data, SwMap model, Map<String, String> mapMergeField, short defFontSize) throws Exception {
        CellStyle colTitleStyle;
        if (model.containsKey("colTitleBold") && model.readString("colTitleBold").equals("1")) {
            colTitleStyle = createCellStyle(wb, defFontSize, true, true, HorizontalAlignment.CENTER);
        } else {
            colTitleStyle = createCellStyle(wb, defFontSize, false, true, HorizontalAlignment.CENTER);
        }
        CellStyle dataStyle_left = createCellStyle(wb, defFontSize, false, true, HorizontalAlignment.LEFT);
        CellStyle dataStyle_right = createCellStyle(wb, defFontSize, false, true, HorizontalAlignment.RIGHT);
        CellStyle dataStyle_center = createCellStyle(wb, defFontSize, false, true, HorizontalAlignment.CENTER);

        CellStyle dataStyle_amount = createCellStyle(wb, defFontSize, false, true, HorizontalAlignment.RIGHT);
        dataStyle_amount.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//两位小数

        CellStyle dataStyle_number = createCellStyle(wb, defFontSize, false, true, HorizontalAlignment.CENTER);
        dataStyle_number.setDataFormat(HSSFDataFormat.getBuiltinFormat("#"));

        Map<Integer, CellStyle> mapIndention = new HashMap<>();
        mapIndention.put(0, dataStyle_left);

        //总列数，加上序号列
        int colCount = 0;
        if (model.containsKey("needSeqCol")) {
            colCount = model.readListMap("columns").size();
        } else {
            colCount = model.readListMap("columns").size() + 1;
        }
        //导出表头
        int rowIndex = 0, colIndex = 0;
        //写入标题行
        String s = model.readString("top");
        if (StringUtil.isNotEmpty(s)) {
            CellStyle csh = createCellStyle(wb, (short) 16, true, false, HorizontalAlignment.CENTER);
            writeMergeCell(sheet, 35, rowIndex, rowIndex, colIndex, colIndex + colCount - 1, csh, s);
            rowIndex++;
        }
        s = model.readString("title");
        if (StringUtil.isNotEmpty(s)) {
            CellStyle csh = createCellStyle(wb, (short) 16, true, false, HorizontalAlignment.CENTER);
            writeMergeCell(sheet, 35, rowIndex, rowIndex, colIndex, colIndex + colCount - 1, csh, s);
            rowIndex++;
        }
        //写入表头上方说明文字,一般为查询条件等
        s = model.readString("headerStr");
        if (StringUtil.isNotEmpty(s)) {
            HorizontalAlignment align;
            switch (s){
                case "center":{
                    align = HorizontalAlignment.CENTER;
                    break;
                }
                case "right":{
                    align = HorizontalAlignment.RIGHT;
                    break;
                }
                default:align = HorizontalAlignment.LEFT;
            }
            CellStyle csh = createCellStyle(wb, defFontSize, false, false, align);
            writeMergeCell(sheet, rowIndex, rowIndex, colIndex, colIndex + colCount - 1, csh, s);
            rowIndex++;
        }
        final List<Map<String, Object>> columns = model.readListMap("columns");
        final int collen = columns.size();
        int[] colMaxCharLen = new int[collen + 1];
        //写列标题
        int i, len;
        List<Map<String, Object>> header = model.readListMap("header");
        for (i = 0, len = header.size(); i < len; i++) {
            Map<String, Object> hm = header.get(i);
            SwMap h = new SwMap();
            h.putAll(hm);
            //
            int hRow = h.readInt("row"), endRow, hCol = h.readInt("col"), colspan = h.readInt("colspan");
            if (h.readBool("isLeaf")) endRow = model.readInt("headerRowCount") - 1 + rowIndex;
            else endRow = hRow + h.readInt("rowspan") - 1 + rowIndex;
            String text = h.readString("title");
            int fromCol = hCol + colIndex;
            writeMergeCell(sheet, hRow + rowIndex, endRow, fromCol, fromCol + colspan - 1, colTitleStyle, text);
            if (colspan == 1 && fromCol < colMaxCharLen.length) colMaxCharLen[fromCol] = getStrLength(text);
        }
        rowIndex += model.readInt("headerRowCount");

        int curRowIndex;
        Row row;
        Map<String, String> curRow;
        AutoMergeCols amcs = new AutoMergeCols();
        AutoMergeColInfo mci;
        boolean isIgnoreZero = model.readBool("ignoreZero");
        for (i = 0, len = data.size(); i < len; i++) {
            curRowIndex = rowIndex + i;
            curRow = data.get(i);
            row = sheet.createRow(curRowIndex);
            boolean isEnd = curRowIndex == data.size() + rowIndex - 1;
            //写序号列
            String rowNum = "";
            int indention = curRow.containsKey("indention") ? StringUtil.getIntIgnoreErr(curRow.get("indention")) : 0;
            if (StringUtil.isEmpty(curRow.get("__sys_footer"))) rowNum = String.valueOf(i + 1);
            if (!model.containsKey("needSeqCol")) {
                writeCell(row, 0, dataStyle_number, rowNum, ColType.TEXT, isIgnoreZero);
            }
            for (int j = 0; j < collen; j++) {
                Map<String, Object> cm = columns.get(j);
                SwMap col = new SwMap();
                col.putAll(cm);
                //
                String keyField = col.readString("field");
                String key = StringUtil.checkNull(curRow.get(keyField));
                String value = getMergeFieldValue(keyField, curRow, mapMergeField);
                CellStyle cs;
                ColType ct = ColType.getColType(col.readString("colType"));
                if (ct == ColType.AMOUNT) {
                    cs = dataStyle_amount;
                } else if (ct == ColType.NUMBER) {
                    cs = dataStyle_number;
                } else if (ct == ColType.MAIN) {
                    cs = mapIndention.computeIfAbsent(indention, k -> {
                        CellStyle cst = createCellStyle(wb, defFontSize, false, true, HorizontalAlignment.LEFT);
                        cst.setIndention((short) (indention * 2));
                        return cst;
                    });
                } else {
                    String align = col.readString("align");
                    if ("CENTER".equalsIgnoreCase(align)) cs = dataStyle_center;
                    else if ("RIGHT".equalsIgnoreCase(align)) cs = dataStyle_right;
                    else cs = dataStyle_left;
                }
                if (!model.containsKey("needSeqCol")) {
                    writeCell(row, j + 1, cs, value, ct, col.readBool("ignoreZero", isIgnoreZero));
                    colMaxCharLen[j + 1] = Math.max(colMaxCharLen[j + 1], getStrLength(value));
                    if (col.readBool("autoMerge")) {//需要自动合并单元格
                        mci = amcs.checkMerge(j + 1, curRowIndex, key, isEnd);
                        if (mci != null) {
                            writeMergeCell(sheet, mci.row, curRowIndex - 1, j + 1, j + 1, cs, mci.value);
                        }
                        amcs.addCol(j + 1, curRowIndex, key, value);
                        if (i == len - 1) {
                            //最后一行了，也要看下有合并行没，有的话要写上
                            mci = amcs.checkMergeLast(j + 1);
                            if (mci != null) {
                                writeMergeCell(sheet, mci.row, curRowIndex, j + 1, j + 1, cs, mci.value);
                            }
                        }
                    }
                } else {
                    writeCell(row, j, cs, value, ct, col.readBool("ignoreZero", isIgnoreZero));
                    colMaxCharLen[j] = Math.max(colMaxCharLen[j], getStrLength(value));
                    if (col.readBool("autoMerge")) {//需要自动合并单元格
                        mci = amcs.checkMerge(j, curRowIndex, key, isEnd);
                        if (mci != null) writeMergeCell(sheet, mci.row, curRowIndex - 1, j, j, cs, mci.value);
                        amcs.addCol(j, curRowIndex, key, value);
                    }
                }

            }
        }
        //去除最后一行
        if (model.readBool("removeLastRow")) {
            sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
            len = 1;
        } else {
            len = 0;
        }

        s = model.readString("footer");
        if (StringUtil.isNotEmpty(s)) {
            rowIndex += data.size();
            rowIndex -= len;
            CellStyle csh = createCellStyle(wb, (short) (defFontSize + 2), true, false, HorizontalAlignment.LEFT);
            writeMergeCell(sheet, 30, rowIndex, rowIndex, colIndex, colIndex + colCount - 1, csh, s);
        }
        if (model.containsKey("footers")) {
            List<String> footers = model.readStringList("footers");
            for (int j = 0; j < footers.size(); j++) {
                if (StringUtil.isNotEmpty(s)) {
                    rowIndex++;
                } else {
                    if (j == 0) {
                        rowIndex += data.size();
                        rowIndex -= len;
                    } else {
                        rowIndex++;
                    }
                }
                CellStyle csh = createCellStyle(wb, (short) (defFontSize + 2), true, false, HorizontalAlignment.LEFT);
                writeMergeCell(sheet, 30, rowIndex, rowIndex, colIndex, colIndex + colCount - 1, csh, footers.get(j));
            }
        }

        //自动列宽
        for (int j = 0; j <= collen; j++) {
            int l = colMaxCharLen[j] + 3;
            if (l < 255) {
                sheet.setColumnWidth(j, l * 256);
            } else {
                sheet.setColumnWidth(j, 6000);
            }
        }
    }

    public enum ColType {
        AMOUNT,
        NUMBER,
        TEXT,
        MAIN;

        public static ColType getColType(String colType) {
            switch (colType.toUpperCase()) {
                case "AMOUNT": //金额
                    return AMOUNT;
                case "NUMBER":
                    return NUMBER;
                case "MAIN":
                    return MAIN;
                default:
                    return TEXT;
            }

        }
    }

    public static void exportToExcelModelFile(Map<String, String> header, List<Map<String, String>> listDetail, String modelFile, String savePath, int rowCount) throws Exception {
        checkTemplateFile(modelFile);
        boolean isExcel2003 = true;

        // 检查文件名是否为空或者是否是Excel格式的文件
        if (!modelFile.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
            throw new IOException("Excel文件后缀必须是xls或xlsx！");
        }
        // 对文件的合法性进行验证
        if (modelFile.matches("^.+\\.(?i)(xlsx)$")) isExcel2003 = false;


        InputStream iso = null;
        File tempFile = new File(savePath);
        if (tempFile.exists()) tempFile.delete();
        tempFile.createNewFile();

        FileOutputStream fs = new FileOutputStream(tempFile);
        try {
            iso = Files.newInputStream(Paths.get(modelFile));
            Workbook book = isExcel2003 ? new HSSFWorkbook(iso) : new XSSFWorkbook(iso);
            if (CommUtil.isEmpty(listDetail)) return;
            int detSize = listDetail.size();
            int sheetCount = detSize % rowCount;
            sheetCount = sheetCount != 0 ? detSize / rowCount + 1 : detSize / rowCount;
            List<String> listSheetName = new ArrayList<>();
            for (int i = 0; i < sheetCount; i++) {
                List<Map<String, String>> listDetailPage = new ArrayList<>();
                int endIndex = (i + 1) * rowCount;
                if (endIndex > detSize) {
                    endIndex = detSize;
                }
                for (int index = i * rowCount; index < endIndex; index++) {
                    listDetailPage.add(listDetail.get(index));
                }
                String sheetName = sheetCount + "-" + (i + 1);
                listSheetName.add(sheetName);
                fillSheet(book.cloneSheet(0), header, listDetailPage, sheetName, isExcel2003);
            }
            //删除非我们生成的sheet
            while (listSheetName.size() != book.getNumberOfSheets()) {
                if (!listSheetName.contains(book.getSheetName(0))) {
                    book.removeSheetAt(0);
                }
            }
            book.write(fs);
            fs.flush();
        } catch (Exception ioe) {
            throw new Exception("读取文件出现错误:" + ioe.getMessage());
        } finally {
            IOUtils.closeQuietly(iso);
            IOUtils.closeQuietly(fs);
        }
    }

    public static void exportToExcelModelFile(Map<String, String> header, List<Map<String, String>> listDetail, String modelFile, String savePath) throws Exception {
        checkTemplateFile(modelFile);
        boolean isExcel2003 = true;

        // 检查文件名是否为空或者是否是Excel格式的文件
        if (!modelFile.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
            throw new IOException("Excel文件后缀必须是xls或xlsx！");
        }
        // 对文件的合法性进行验证
        if (modelFile.matches("^.+\\.(?i)(xlsx)$")) isExcel2003 = false;


        InputStream iso = null;
        File tempFile = new File(savePath);
        if (tempFile.exists()) tempFile.delete();
        tempFile.createNewFile();

        FileOutputStream fs = new FileOutputStream(tempFile);
        try {
            iso = Files.newInputStream(Paths.get(modelFile));
            Workbook book = isExcel2003 ? new HSSFWorkbook(iso) : new XSSFWorkbook(iso);

            fillSheet(book.getSheetAt(0), header, listDetail, isExcel2003);
            book.write(fs);
            fs.flush();
        } catch (Exception ioe) {
            throw new Exception("读取文件出现错误:" + ioe.getMessage());
        } finally {
            IOUtils.closeQuietly(iso);
            IOUtils.closeQuietly(fs);
        }
    }

    public static String exportToExcelModel(Map<String, String> header, List<Map<String, String>> listDetail, String modelFile, String fileName) throws Exception {
        checkTemplateFile(modelFile);
        boolean isExcel2003 = true;

        // 检查文件名是否为空或者是否是Excel格式的文件
        if (!modelFile.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
            throw new IOException("Excel文件后缀必须是xls或xlsx！");
        }
        // 对文件的合法性进行验证
        if (modelFile.matches("^.+\\.(?i)(xlsx)$")) isExcel2003 = false;


        InputStream iso = null;
        File tempFile = File.createTempFile("xls_", ".xls", new File(System.getProperty("java.io.tmpdir")));
        FileOutputStream fs = new FileOutputStream(tempFile);
        try {
            iso = Files.newInputStream(Paths.get(modelFile));
            Workbook book = isExcel2003 ? new HSSFWorkbook(iso) : new XSSFWorkbook(iso);
            int detSize = listDetail.size();
            if (detSize == 0) {
                Sheet sheet = book.createSheet();
                fillSheet(sheet, header, listDetail, isExcel2003);
            } else {
                int sheetCount = detSize % MAX_EXCEL_COUNT;
                sheetCount = sheetCount != 0 ? detSize / MAX_EXCEL_COUNT + 1 : detSize / MAX_EXCEL_COUNT;
                List<String> listSheetName = new ArrayList<>();
                List<Sheet> list = new ArrayList<>();
                for (int i = 0; i < sheetCount; i++) {
                    String sheetName = sheetCount + "-" + (i + 1);
                    listSheetName.add(sheetName);
                    Sheet sheet = null;
                    if (i > 0) {
                        sheet = book.cloneSheet(0);
                        book.setSheetName(i, sheetName);
                    } else {
                        sheet = book.getSheetAt(0);
                    }
                    list.add(sheet);
                }
                for (int i = 0; i < list.size(); i++) {
                    List<Map<String, String>> listDetailPage = new LinkedList<>();
                    int endIndex = (i + 1) * MAX_EXCEL_COUNT;
                    if (endIndex > detSize) {
                        endIndex = detSize;
                    }
                    for (int index = i * MAX_EXCEL_COUNT; index < endIndex; index++) {
                        listDetailPage.add(listDetail.get(index));
                    }

                    fillSheet(list.get(i), header, listDetailPage, isExcel2003);
                }
                //删除非我们生成的sheet
                while (listSheetName.size() != book.getNumberOfSheets()) {
                    if (!listSheetName.contains(book.getSheetName(0))) {
                        book.removeSheetAt(0);
                    }
                }
                book.write(fs);
            }
            fs.flush();
        } catch (Exception ioe) {
            throw new Exception("读取文件出现错误:" + ioe.getMessage());
        } finally {
            IOUtils.closeQuietly(iso);
            IOUtils.closeQuietly(fs);
        }

        fileName = fileName + "_" + DateUtil.nowDateTimeString() + ".xls";
        return fileName + "@@" + tempFile.getAbsolutePath();
    }

    public static String exportToExcelModel(Map<String, String> header, List<Map<String, String>> listSon, Map<String, List<Map<String, String>>> mapSonDetail, String modelFile, String fileName) throws Exception {
        checkTemplateFile(modelFile);
        boolean isExcel2003 = true;

        // 检查文件名是否为空或者是否是Excel格式的文件
        if (!modelFile.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
            throw new IOException("Excel文件后缀必须是xls或xlsx！");
        }
        // 对文件的合法性进行验证
        if (modelFile.matches("^.+\\.(?i)(xlsx)$")) isExcel2003 = false;


        InputStream iso = null;
//        File tempFile = File.createTempFile("xls_", ".xls", new File(System.getProperty("java.io.tmpdir")));
        File tempFile = File.createTempFile("xls_", ".xls", new File("D:\\jjkj\\"));
        FileOutputStream fs = new FileOutputStream(tempFile);
        try {
            iso = Files.newInputStream(Paths.get(modelFile));
            Workbook book = isExcel2003 ? new HSSFWorkbook(iso) : new XSSFWorkbook(iso);
            for (int i = 0, len = book.getNumberOfSheets(); i < len; i++) {
                Sheet sheet = book.getSheetAt(i);
                final String sheetName = sheet.getSheetName();
                if (!sheetName.contains("{@dyn@}")) fillSheet(sheet, header, listSon, isExcel2003);
                else {
                    int cloneCount = 0;
                    for (Map<String, String> stringStringMap : listSon) {
                        String s = sheetName.replace("{@dyn@}", "").replace("{#", "").replace("#}", "");
                        s = stringStringMap.get(s);
                        if (!mapSonDetail.containsKey(s)) continue;
                        cloneCount++;
                        Sheet newSheet = book.cloneSheet(i);

                        book.setSheetName(i + cloneCount, s);
                        stringStringMap.putAll(header);
                        fillSheet(newSheet, stringStringMap, mapSonDetail.get(s), isExcel2003);
                    }
                    book.removeSheetAt(i);
                    break;
                }
            }

            book.write(fs);
            fs.flush();
        } catch (Exception ioe) {
            throw new Exception("读取文件出现错误:" + ioe.getMessage());
        } finally {
            IOUtils.closeQuietly(iso);
            IOUtils.closeQuietly(fs);
        }

        fileName = fileName + "_" + DateUtil.nowDateTimeString() + ".xls";
        return fileName + "@@" + tempFile.getAbsolutePath();
    }

    //读sheet
    private static void fillSheet(Sheet sheet, Map<String, String> header, List<Map<String, String>> listDetail, String sheetName, boolean isExcel2003) throws Exception {
        //String sheetName = sheet.getSheetName();
        int index = sheet.getWorkbook().getSheetIndex(sheet);
        sheet.getWorkbook().setSheetName(index, sheetName);
        fillSheetEx(sheet, header, listDetail, isExcel2003);
    }

    //读sheet
    private static void fillSheet(Sheet sheet, Map<String, String> header, List<Map<String, String>> listDetail, boolean isExcel2003) throws Exception {
        String sheetName = sheet.getSheetName();
        if (sheetName.contains("{#") && sheetName.contains("#}")) {
            final int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
            sheet.getWorkbook().setSheetName(sheetIndex, ExportUtil.myReplaceStrEx(sheetName, "{#", "#}", header));
        }
        fillSheetEx(sheet, header, listDetail, isExcel2003);
    }

    private static void setCellValue(Cell cell, String value, String org_txt) {
        if (org_txt.startsWith("[@expri@]") || org_txt.startsWith("[@i@]")) {
            CellStyle cs = cell.getCellStyle();
            if (cs == null) {
                cs = cell.getSheet().getWorkbook().createCellStyle();
                cell.setCellStyle(cs);
            }
            cell.setCellValue(StringUtil.getIntIgnoreErr(value));
            cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
            cell.setCellStyle(cs);
        } else if (org_txt.startsWith("[@exprd") || org_txt.startsWith("[@d")) {
            String s = org_txt.replace("[@exprd", "").replace("[@d", "");
            int c = StringUtil.getIntIgnoreErr(s.substring(0, s.indexOf("@]")));
            if (c == 0) c = 2;
            CellStyle cs = cell.getCellStyle();
            if (cs == null) {
                cs = cell.getSheet().getWorkbook().createCellStyle();
                cell.setCellStyle(cs);
            }
            cell.setCellValue(StringUtil.getDoubleIgnoreErr(value));
            cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("0." + StringUtil.repeatString("0", c)));
            cell.setCellStyle(cs);

        } else if (org_txt.startsWith("[@expfml@]")) {//公式格式
            CellStyle cs = cell.getCellStyle();
            if (cs == null) {
                cs = cell.getSheet().getWorkbook().createCellStyle();
                cell.setCellStyle(cs);
            }
            cell.setCellStyle(cs);
            cell.setCellFormula("HYPERLINK(\"" + value + "\",\"" + value + "\")");
        } else {
            cell.setCellValue(value);
        }
    }

    private static void fillSheetEx(Sheet sheet, Map<String, String> header, List<Map<String, String>> listDetail, boolean isExcel2003) throws Exception {
        int totalRows = sheet.getLastRowNum() + 1;
        Drawing drawing = sheet.createDrawingPatriarch();
        // excel中的3行，代码中要-1.
        for (int i = 0; i < totalRows; i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            if (!isDynRow(row)) {//非动态行
                for (int j = row.getFirstCellNum(), endCell = row.getLastCellNum(); j <= endCell; j++) {
                    Cell cell = row.getCell(j);
                    if (null == cell) continue;
                    if (cell.getCellType() == CellType.STRING) {
                        String value = cell.getStringCellValue();
                        if (StringUtil.isEmpty(value) || !value.contains("[#") || !value.contains("#]")) continue;
                        String rv = ExportUtil.myReplaceStr(value, header);
                        if (FileUtil.isImageFromBase64(rv)) {
                            createPic(sheet, drawing, cell, rv, value, j, i, isExcel2003);
                        } else {
                            setCellValue(cell, rv, value);
                        }
                    }
                }
            } else {
                if (CommUtil.isEmpty(listDetail)) {
                    sheet.removeRow(row);//删除行内容
                    continue;
                }
                final int detailSize = listDetail.size();

                //移动行，插入空行
                final int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum > i) sheet.shiftRows(i + 1, lastRowNum, detailSize - 1, true, false);

                List<Map<String, String>> list = new ArrayList<>();
                for (int kk = 0; kk < detailSize; kk++) {
                    Map<String, String> m = listDetail.get(kk);
                    Map<String, String> map = new HashMap<>(m);
                    map.putAll(header);
                    list.add(map);
                    map.put("序号", String.valueOf(kk + 1));
                }

                AutoMergeCols amcs = new AutoMergeCols();
                AutoMergeColInfo mci = null;
                for (int j = row.getFirstCellNum(), endCell = row.getLastCellNum(); j <= endCell; j++) {
                    Cell cell = row.getCell(j);
                    if (null == cell) continue;
                    String value = ExcelUtil.getStr(row, j);
//                    if (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
//                        String value = cell.getStringCellValue();
//                        if (CommUtil.isEmpty(value)) continue;
                    value = value.replace("[@dyn@]", "");
                    boolean automerge = value.contains("[@am@]");
                    boolean mergeCell = value.contains("[@mc@]");
                    value = value.replace("[@am@]", "").replace("[@mc@]", "");
//                        if (!value.contains("[#") || !value.contains("#]")) continue;

                    String sValue = ExportUtil.myReplaceStr(value, list.get(0));
//                        cell.setCellValue(sValue);
                    if (FileUtil.isImageFromBase64(sValue)) {
                        createPic(sheet, drawing, cell, sValue, value, j, i, isExcel2003);
                    } else {
                        setCellValue(cell, sValue, value);
                    }
                    if (automerge) amcs.addCol(j, i, sValue);
                    for (int kk = 1; kk < detailSize; kk++) {
                        boolean isEnd = kk == detailSize - 1;
                        int end_index = isEnd ? 0 : 1;
                        Row dr = sheet.getRow(i + kk);
                        if (dr == null) dr = sheet.createRow(i + kk);
                        dr.setHeight(row.getHeight());

                        Cell dCell = dr.createCell(j);

                        // 风格一样
                        dCell.setCellStyle(cell.getCellStyle());
                        dCell.setCellType(cell.getCellType());
                        sValue = ExportUtil.myReplaceStr(value, list.get(kk));
//                            dCell.setCellValue(sValue);
                        setCellValue(dCell, sValue, value);
                        if (automerge) {//需要自动合并单元格
                            mci = amcs.checkMerge(j, i + kk, sValue, isEnd);
                            if (mci != null && StringUtil.isNotEmpty(mci.value))
                                writeMergeCell(sheet, mci.row, i + kk - end_index, j, j, cell.getCellStyle(), mci.value);
                            amcs.addCol(j, i + kk, sValue);
                        }
                        if (mergeCell) {
                            writeMergeCell(sheet, i + kk, i + kk, j, getMergeCellEndIndex(j, row), cell.getCellStyle(), sValue);
                        }
                    }
//                    }
                }
                totalRows += detailSize - 1;
                i += detailSize - 1;
            }
        }
    }

    private static int getMergeCellEndIndex(int col, Row row) {
        int begin = col;
        String value = "";
        while (true) {
            begin++;
            value = ExcelUtil.getStr(row, begin);
            if (!"".equals(value)) {
                return begin - 1;
            }
            if (begin == row.getLastCellNum() - 1) {
                return begin;
            }
        }
    }

    public static void createPic(Sheet sheet, Drawing drawing, Cell cell, String value, String org_value, int cell_index, int row_index, boolean isExcel2003) {
        try {
            BufferedImage bufImg = ImageIO.read(new ByteArrayInputStream(new BASE64Decoder().decodeBuffer(value)));
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            ImageIO.write(bufImg, "png", byteArrayOut);
            byte[] data = byteArrayOut.toByteArray();
            ClientAnchor anchor = null;
            if (isExcel2003)
                anchor = new HSSFClientAnchor(5, 5, 1000, 250, (short) cell_index, row_index, (short) cell_index, row_index);
            else
                anchor = new XSSFClientAnchor(0, 0, 1000, 250, (short) cell_index, row_index, (short) cell_index, row_index);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
            drawing.createPicture(anchor, sheet.getWorkbook().addPicture(data, Workbook.PICTURE_TYPE_PNG));
            setCellValue(cell, "", org_value);
        } catch (Exception e) {
//            UtilLogger.error("插入图片到Excel失败！", e);
            System.err.println("插入图片到Excel失败");
        }
    }

    public static String createExcelModel(String planName, List<String> head, List<String> detail) throws Exception {
        File tempFile = File.createTempFile("xls_", ".xls", new File(System.getProperty("java.io.tmpdir")));
        FileOutputStream fs = new FileOutputStream(tempFile);
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet("sheet1");
            Row row = null;

            CellStyle csh = createCellStyle(wb, (short) 16, true, false, HorizontalAlignment.CENTER);
            writeMergeCell(sheet, 35, 0, 0, 0, detail.size(), csh, planName);

            int rowIndex = 0;
//            if (head != null && head.size() > 0) {
//                int i = 0;
//                for (String key : head) {
//                    if (i % 2 == 0) {
//                        row = createRow(sheet, rowIndex + 1, 20);
//                        setModelCellValue(row, 0, key);
//                        setModelCellValue(row, 1, "[#" + key + "#]");
//                        rowIndex++;
//                    } else {
//                        setModelCellValue(row, 2, key);
//                        setModelCellValue(row, 3, "[#" + key + "#]");
//                    }
//                    i++;
//                }
//            }
            CellStyle dataStyle_center = createCellStyle(wb, (short) 9, false, true, HorizontalAlignment.CENTER);
//            CellStyle dataStyle_left = createCellStyle(wb, (short) 9, false, true, CellStyle.ALIGN_LEFT);
            if (detail != null && detail.size() > 0) {
                Row title = createRow(sheet, rowIndex + 1, 20);
                Row value = createRow(sheet, rowIndex + 2, 20);
                Row sum = createRow(sheet, rowIndex + 3, 20);
                writeCell(title, 0, dataStyle_center, "序号", ColType.TEXT, false);
                writeCell(value, 0, dataStyle_center, "[@dyn@][#序号#]", ColType.TEXT, false);
                writeCell(sum, 0, dataStyle_center, "合计", ColType.TEXT, false);
                int i = 1;
                for (String key : detail) {
                    writeCell(title, i, dataStyle_center, key, ColType.TEXT, false);
                    writeCell(value, i, dataStyle_center, "[#" + key + "#]", ColType.TEXT, false);
                    writeCell(sum, i, dataStyle_center, "[#" + key + "_合计#]", ColType.TEXT, false);
                    i++;
                }
            }

            rowIndex += 10;
            csh = createCellStyle(wb, (short) 9, false, false, HorizontalAlignment.LEFT);
            writeMergeCell(sheet, 20, rowIndex, rowIndex, 0, detail.size(), csh, "支持的变量参数");
            rowIndex++;
            writeMergeCell(sheet, 20, rowIndex, rowIndex, 0, detail.size(), csh, "头：" + toText(head));
            rowIndex++;
            writeMergeCell(sheet, 20, rowIndex, rowIndex, 0, detail.size(), csh, "明细：" + toText(detail));

            /*row = createRow(sheet, rowIndex + 4, 20);
            writeCell(row, 0, dataStyle_left, "支持的变量参数", ColType.TEXT, false);
            row = createRow(sheet, rowIndex + 5, 20);
            writeCell(row, 0, dataStyle_left, "头：", ColType.TEXT, false);
            writeCell(row, 1, dataStyle_left, toText(head), ColType.TEXT, false);
            row = createRow(sheet, rowIndex + 6, 20);
            writeCell(row, 0, dataStyle_left, "明细：", ColType.TEXT, false);
            writeCell(row, 1, dataStyle_left, toText(detail), ColType.TEXT, false);*/

            wb.write(fs);
            clearCellStyleCache(wb);
            fs.flush();
        } catch (Exception ioe) {
//            UtilLogger.error("读取文件出现错误：", ioe);
            System.err.println("读取文件出现错误");
            throw new Exception("读取文件出现错误:" + ioe.getMessage());
        } finally {
            IOUtils.closeQuietly(fs);
        }
        planName = planName + "_" + DateUtil.nowDateTimeString() + ".xls";
        return planName + "@@" + tempFile.getAbsolutePath();
    }

    private static String toText(List<String> list) {
        if (CommUtil.isEmpty(list)) return "";
        StringBuilder sb = new StringBuilder(256);
        for (String s : list) {
            sb.append(",").append(s);
        }
        return sb.substring(1);
    }

    private static void setModelCellValue(Row row, int cellIndex, String value) throws Exception {
        Cell cell = createCell(row, cellIndex);
        cell.setCellValue(value);
    }

    //是否动态行
    private static boolean isDynRow(Row row) {
        final short firstCellNum = row.getFirstCellNum();
        if (firstCellNum < 0) return false;
        Cell cell = row.getCell(firstCellNum);
        if (cell.getCellType() == CellType.STRING) {
            String value = cell.getStringCellValue();
            return !(StringUtil.isEmpty(value) || !value.startsWith("[@dyn@]"));
        }
        return false;
    }


    //需要自动合并单元格的字段列信息
    private static class AutoMergeColInfo {
        private int col = -1;//列索引
        private String key = null;// 合并的值
        private String value = null;//合并的值的format值
        private int row = -1;//开始行索引
    }

    private static class AutoMergeCols {
        private Map<Integer, AutoMergeColInfo> map = new HashMap<>();

        private void addCol(int col, int row, String value) {
            addCol(col, row, value, value);
        }

        private void addCol(int col, int row, String key, String value) {
            AutoMergeColInfo colInfo = map.get(col);
            if (colInfo == null) {
                colInfo = new AutoMergeColInfo();
                map.put(col, colInfo);
            }
            colInfo.col = col;
            if (colInfo.key == null || !colInfo.key.equals(key)) colInfo.row = row;
            colInfo.key = key;
            colInfo.value = value;
        }

        //校验是否需要合并单元格，返回null，不需要，其他则返回合并的开始列信息
        private AutoMergeColInfo checkMerge(int col, int row, String key, boolean isEnd) {
            AutoMergeColInfo colInfo = map.get(col);
            if (colInfo == null) return null;
            if (colInfo.key == null) return null;
            if (colInfo.row == row - 1) return null;//上一行开始，表示只有一行是此数据， 不需要考虑合并
            if (isEnd && colInfo.key.equals(key)) return colInfo;
            if (colInfo.key.equals(key)) return null;
            return colInfo;
        }

        private AutoMergeColInfo checkMergeLast(int col) {
            AutoMergeColInfo colInfo = map.get(col);
            if (colInfo == null) return null;
            if (colInfo.value == null) return null;
            return colInfo;
        }
    }

    private static void checkTemplateFile(String modelFile) throws Exception {
        if (StringUtil.isEmpty(modelFile) || !FileUtil.isFileExist(modelFile)) throw new Exception("模板文件不存在：" + modelFile);
    }

    public static void main(String[] args) throws Exception {
        Map<String, String> map = new HashMap<>();
        map.put("户数-永顺镇", "1");
        map.put("人数-永顺镇", "100");
        map.put("低保金-永顺镇", "1000");
        List<Map<String, String>> listSon = new ArrayList<>();
        HashMap<String, String> m = new HashMap<>();
        m.put("区划", "永顺镇");
        m.put("p-人数", "100");
        m.put("p-低保金", "10000");
        listSon.add(m);

        m = new HashMap<>();
        m.put("区划", "宋庄镇");
        m.put("p-人数", "200");
        m.put("p-低保金", "20000");
        listSon.add(m);

        Map<String, List<Map<String, String>>> mapSonDetail = new HashMap<>();
        List<Map<String, String>> listSonD = new ArrayList<>();
        mapSonDetail.put("永顺镇", listSonD);
        m = new HashMap<>();
        m.put("姓名", "永顺镇1");
        m.put("人数", "50");
        m.put("低保金", "5000");
        listSonD.add(m);

        m = new HashMap<>();
        m.put("姓名", "永顺镇2");
        m.put("人数", "40");
        m.put("低保金", "4000");
        listSonD.add(m);

        listSonD = new ArrayList<>();
        mapSonDetail.put("宋庄镇", listSonD);
        m = new HashMap<>();
        m.put("姓名", "宋庄镇1");
        m.put("人数", "10");
        m.put("低保金", "1000");
        listSonD.add(m);

        m = new HashMap<>();
        m.put("姓名", "宋庄镇2");
        m.put("人数", "20");
        m.put("低保金", "2000");
        listSonD.add(m);

        //
        List<Map<String, String>> rows = new ArrayList<>();
        HashMap<String, String> row = new HashMap<>();
        row.put("F1", "宋庄镇2");
        row.put("F2", "20");
        row.put("F3", "2000");
        rows.add(row);

        row = new HashMap<>();
        row.put("F1", "宋庄镇22");
        row.put("F2", "220");
        row.put("F3", "22000");
        rows.add(row);

        row = new HashMap<>();
        row.put("F1", "宋庄镇222");
        row.put("F2", "2220");
        row.put("F3", "222000");
        rows.add(row);

        row = new HashMap<>();
        row.put("F1", "宋庄镇2222");
        row.put("F2", "22220");
        row.put("F3", "2222000");
        rows.add(row);

        String model = "{\"header\":[{\"title\":\"姓名\",\"colspan\":1,\"rowspan\":1,\"row\":0,\"col\":0,\"isLeaf\":true},{\"title\":\"人数\",\"colspan\":1,\"rowspan\":1,\"row\":0,\"col\":1,\"isLeaf\":true},{\"title\":\"低保金\",\"colspan\":1,\"rowspan\":1,\"row\":0,\"col\":2,\"isLeaf\":true}],\"columns\":[{\"field\":\"F1\",\"align\":\"center\",\"title\":\"姓名1\",\"chname\":\"姓名1\"},{\"field\":\"F2\",\"align\":\"center\",\"title\":\"人数1\",\"chname\":\"人数1\"},{\"field\":\"F3\",\"align\":\"center\",\"title\":\"低保金1\",\"chname\":\"低保金1\"}],\"headerRowCount\":1,\"title\":\"收款台列表\",\"rownum_\":0}";

        String ss = exportToExcel(rows, model);
//        String ss = exportToExcelModel(map, listSonD, mapSonDetail, "D:\\jjkj\\test.xlsx", "a");
        System.out.println(ss);

    }
}
