一 、引入依赖

<!-- 阿里开源easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>
<!-- lombok省去写get、set步骤-->
		<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

二、工具类

一、大工具集合

package org.springblade.modules.api.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springblade.common.config.BladeRemoteConfig;
import org.springblade.core.tool.utils.Func;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

public class ExcelMergeUtil implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelMergeUtil() {
    }

    public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意：因为我是按照主体名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是主体名称所在列的下标，不能大于需要合并列数组的第一个下标
        Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
        if (dataBool && bool) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }

    /**
     * 头部样式
     *
     * @return
     */
    public static WriteCellStyle getHeadWriteCellStyle() {
        // 这里需要设置不关闭流
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        //字体大小
        headWriteFont.setFontHeightInPoints((short) 13);
        //是否加粗
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        return headWriteCellStyle;
    }

    /**
     * 内容样式
     *
     * @return
     */
    public static WriteCellStyle getContentWriteCellStyle() {
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //自动换行
        contentWriteCellStyle.setWrapped(true);
        //垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置左边框
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        //设置右边框
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        //设置上边框
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //设置下边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        return contentWriteCellStyle;
    }

    /**
     * 合并单元格导出excel工具
     *
     * @param response         响应头
     * @param fileName         文件名称
     * @param lsit             需要导出的数据
     * @param data             对应的excel导出类
     * @param mergeColumeIndex 需要合并的下标
     * @param mergeRowIndex    从第几行开始合并
     * @throws IOException
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List lsit, Class data, int[] mergeColumeIndex, int mergeRowIndex) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileNamePath = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNamePath + ".xlsx");
        // 调用合并单元格工具类，此工具类是根据工程名称相同则合并后面数据
        ExcelMergeUtil excelFillCellMergeStrategy = null;
        //是否需要合并
        if (Func.isNotEmpty(mergeColumeIndex) && mergeRowIndex != -1) {
            excelFillCellMergeStrategy = new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex);
        }

        //头部样式
        WriteCellStyle headWriteCellStyle = ExcelMergeUtil.getHeadWriteCellStyle();
        //内容样式
        WriteCellStyle contentWriteCellStyle = ExcelMergeUtil.getContentWriteCellStyle();

        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        EasyExcel.write(response.getOutputStream(), data)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(excelFillCellMergeStrategy)
                //sheet显示的名字
                .autoCloseStream(Boolean.TRUE).sheet(fileName)
                .doWrite(lsit);
    }

    /**
     * 导出动态头excel，适用于浏览器导出
     *
     * @param response
     * @param fileName 文件名
     * @param headList 头集合
     * @param dataList 对应头的列集合
     * @throws IOException
     */
    public static void exportExcelByHeader(HttpServletResponse response, String fileName, List headList, List dataList) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileNamePath = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNamePath + ".xlsx");
        //头部样式
        WriteCellStyle headWriteCellStyle = ExcelMergeUtil.getHeadWriteCellStyle();
        //内容样式
        WriteCellStyle contentWriteCellStyle = ExcelMergeUtil.getContentWriteCellStyle();

        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        EasyExcel.write(response.getOutputStream())
                //自定义头部数据
                .head(headList)
                //头部和内容策略
                .registerWriteHandler(horizontalCellStyleStrategy)
                //自动列宽
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                // .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20)) // 简单的列宽策略，列宽20
                //.registerWriteHandler(new SimpleRowHeightStyleStrategy((short)30,(short)20)) // 简单的行高策略：头行高30，内容行高20
                //sheet显示的名字
                .autoCloseStream(Boolean.TRUE).sheet(fileName)
                //对应的列数据
                .doWrite(dataList);
    }

    /**
     * 合并单元格导出excel工具，存到本地
     *
     * @param fileName         文件名称
     * @param lsit             需要导出的数据
     * @param data             对应的excel导出类
     * @param mergeColumeIndex 需要合并的下标
     * @param mergeRowIndex    从第几行开始合并
     * @throws IOException
     */
    public static String exportExcelLocal(String fileName, List lsit, Class data, int[] mergeColumeIndex, int mergeRowIndex) throws IOException {
        // 调用合并单元格工具类，此工具类是根据工程名称相同则合并后面数据
        ExcelMergeUtil excelFillCellMergeStrategy = null;
        //是否需要合并
        if (Func.isNotEmpty(mergeColumeIndex) && mergeRowIndex != -1) {
            excelFillCellMergeStrategy = new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex);
        }

        //头部样式
        WriteCellStyle headWriteCellStyle = ExcelMergeUtil.getHeadWriteCellStyle();
        //内容样式
        WriteCellStyle contentWriteCellStyle = ExcelMergeUtil.getContentWriteCellStyle();

        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        String filePath = fileName + System.currentTimeMillis() + ".xlsx";

        EasyExcel.write(BladeRemoteConfig.remotePath + "/" + filePath, data)
                .registerWriteHandler(horizontalCellStyleStrategy)
//                .registerWriteHandler(excelFillCellMergeStrategy)
                //自动列宽
//                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20)) // 简单的列宽策略，列宽20
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 15, (short) 15)) // 简单的行高策略：头行高30，内容行高20
                //sheet显示的名字
                .autoCloseStream(Boolean.TRUE).sheet(fileName)
                .doWrite(lsit);
        return BladeRemoteConfig.uploadDomain + "/" + filePath;
    }

    //***************************************excel填充模版用*************************************************

    /**
     * excel填充模版用
     *
     * @param response         响应
     * @param templateFilePath 模版路径
     * @param inputFileName    导出文件名
     * @param List             数据列表
     * @param map              单个对象
     * @param sheetAt          第几个sheet
     * @param row              获取第几行的高度
     * @param mergeColumeIndex 需要合并的下标
     * @param mergeRowIndex    从第几行开始合并
     * @param fillInIndex      从第几行开始使用自定义样式
     * @param endIndex         结束合并的下标
     * @throws Exception
     */
    public static void exportExcelByFillIn(HttpServletResponse response, String templateFilePath, String inputFileName,
                                           List<?> List, Map<String, Object> map, Integer sheetAt, Integer row,
                                           int[] mergeColumeIndex, int mergeRowIndex, int fillInIndex, Integer endIndex) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileNamePath = URLEncoder.encode(inputFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileNamePath + ".xlsx");
        // 调用合并单元格工具类，此工具类是根据工程名称相同则合并后面数据
        ExcelHeBingUtil excelHeBingUtil = null;
        //是否需要合并
        if (Func.isNotEmpty(mergeColumeIndex) && mergeRowIndex != -1) {
            excelHeBingUtil = new ExcelHeBingUtil(mergeRowIndex, mergeColumeIndex, endIndex);
        }
        int rowHeight = getFourthRowHeightFromTemplate(templateFilePath, sheetAt, row);
        InputStream is = getInputStream(templateFilePath);
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(is)
                //从指定行后使设置行高
                .registerWriteHandler(new CustomRowHeightHandler(rowHeight))
                //从指定行开始使用内容样式策略，字体大小，结束行
                .registerWriteHandler(new ExcelFillInStyleHandler(fillInIndex,14,10))
                //合并单元格策略
                .registerWriteHandler(excelHeBingUtil)
                .excelType(ExcelTypeEnum.XLS)
                .build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        // 填充列表数据
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        excelWriter.fill(List, fillConfig, writeSheet);
        // 填充map中的变量
        excelWriter.fill(map, writeSheet);
        excelWriter.finish();

    }

    /**
     * 获取上
     *
     * @param templateFilePath
     * @param sheetAt
     * @param row
     * @return
     * @throws Exception
     */
    public static int getFourthRowHeightFromTemplate(String templateFilePath, Integer sheetAt, Integer row) throws Exception {
        InputStream is = getInputStream(templateFilePath);
        Workbook workbook = WorkbookFactory.create(is);
        Sheet sheet = workbook.getSheetAt(sheetAt); // 第一个工作表
        Row fourthRow = sheet.getRow(row); // 第四行（索引从0开始）
        int rowHeight = fourthRow.getHeight();
        workbook.close();
        return rowHeight;
    }

    /**
     * 获取流
     *
     * @param templateFilePath
     * @return
     * @throws Exception
     */
    public static InputStream getInputStream(String templateFilePath) throws Exception {
        ClassPathResource res = new ClassPathResource(templateFilePath);
        InputStream is = res.getInputStream();
        return is;
    }

}

二、解决合并单元格问题

package org.springblade.modules.api.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springblade.common.config.BladeRemoteConfig;
import org.springblade.core.tool.utils.Func;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

public class ExcelHeBingUtil implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    private Integer endIndex;

    public ExcelHeBingUtil() {
    }

    public ExcelHeBingUtil(int mergeRowIndex, int[] mergeColumnIndex, Integer endIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.endIndex = endIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意：因为我是按照主体名称确定是否需要合并的，所以获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是主体名称所在列的下标，不能大于需要合并列数组的第一个下标
        Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue());
        if (dataBool && bool) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            if (Func.isEmpty(endIndex)) {
                inset(mergeRegions, isMerged, curRowIndex, curColIndex, sheet);
            } else {
                //指定列合并
                if (curColIndex == 0) {
                    inset(mergeRegions, isMerged, curRowIndex, curColIndex, sheet);
                }
                //指定行合并
                if (curColIndex != 0 && curRowIndex < endIndex) {
                    inset(mergeRegions, isMerged, curRowIndex, curColIndex, sheet);
                }
            }
        }
    }

    private void inset(List<CellRangeAddress> mergeRegions, boolean isMerged, Integer curRowIndex, Integer curColIndex, Sheet sheet) {
        for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
            CellRangeAddress cellRangeAddr = mergeRegions.get(i);
            // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
            if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                sheet.removeMergedRegion(i);
                cellRangeAddr.setLastRow(curRowIndex);
                sheet.addMergedRegion(cellRangeAddr);
                isMerged = true;
            }
        }
        // 若上一个单元格未被合并，则新增合并单元
        if (!isMerged) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
            sheet.addMergedRegion(cellRangeAddress);
        }
    }

}

三、解决填充后行高不一致问题

package org.springblade.modules.api.utils;

import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Row;

public class CustomRowHeightHandler implements RowWriteHandler {

    private int rowHeight;

    public CustomRowHeightHandler(int rowHeight) {
        this.rowHeight = rowHeight;
    }

    @Override
    public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {

        row.setHeight((short)rowHeight);
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer integer, Boolean aBoolean) {

    }
}

内容策略

package org.springblade.modules.api.utils;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.springblade.core.tool.utils.Func;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 自定义内容策略 - 优化样式复用，避免超出最大样式数量限制
 */
public class ExcelFillInStyleHandler implements CellWriteHandler {
    private final int startRow; // 起始行（从0开始）
    private final int fontSize; // 字体大小
    private final Integer endRow; // 结束行

    // 缓存样式：每个 Workbook 对应一个样式（避免跨文件冲突）
    private final Map<Workbook, CellStyle> styleCache = new HashMap<>();

    public ExcelFillInStyleHandler(int startRow, int fontSize, Integer endRow) {
        this.startRow = startRow;
        this.fontSize = fontSize;
        this.endRow = endRow;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
        if (isHead || cell.getRowIndex() < startRow || (Func.isNotEmpty(endRow) && cell.getRowIndex() > endRow)) {
            return;
        }

        Workbook workbook = writeSheetHolder.getSheet().getWorkbook();

        // 获取或创建缓存样式
        CellStyle style = styleCache.computeIfAbsent(workbook, k -> {
            CellStyle newStyle = workbook.createCellStyle();
            configStyle(workbook, newStyle); // 配置样式
            return newStyle;
        });

        cell.setCellStyle(style); // 应用复用样式
    }

    // 集中配置样式（避免重复代码）
    private void configStyle(Workbook workbook, CellStyle style) {
        // 文本格式（防止数字科学计数）
        DataFormat dataFormat = workbook.createDataFormat();
        style.setDataFormat(dataFormat.getFormat("@"));

        // 字体设置
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        font.setColor(IndexedColors.BLACK.getIndex());
        style.setFont(font);

        // 对齐方式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true); // 自动换行

        // 边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
    }

    // 其他接口方法保持空实现（按需补充逻辑）
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 Row row, Head head, Integer columnIndex, Integer relativeRowIndex,
                                 Boolean isHead) {}

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                       CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}
}

三、使用方法

只写了如何调用，具体根据需求来

 @Override
    public void exportBlueAlgae(LedgerRefloatDTO dto, HttpServletResponse response) throws Exception {
        List<BlueAlgaeStatementVO> list = blueAlgaeStatement(dto);
        //单个对象的时间
        Map<String, Object> map = new HashMap<>();
        //转为中文
        String date = CommonUtil.changeDateToChine(dto.getBeginDate());
        map.put("date", date);
        //导出文件名
        String inputFileName = "报表" + date + System.currentTimeMillis();
        //第几个sheet
        int sheetAt = 0;
        //获取第几行的高度,解决填充后高度不一致问题
        int row = 3;
        //需要合并的列，没有合并需求可设为null
        int[] mergeColumeIndex = {0, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13};
        //从第3行后开始合并，没有合并需求可设为-1
        int mergeRowIndex = 3;
        //从第几行开始使用内容策略
        int fillIndex = 3;
        //从第几行结束合并，没有合并需求可设为null，没有结束合并的行，也可设为null
        int size = list.stream().filter(x -> x.getType() == 1 && !x.getPlaceName().equals(CommonConstant.XIAO_JI)).collect(Collectors.toList()).size();
        Integer endIndex = fillIndex + size;
        ExcelMergeUtil.exportExcelByFillIn(response, CommonConstant.T_DATA_ANALYSIS_BLUE_ALGAE, inputFileName, list, map, sheetAt, row, mergeColumeIndex, mergeRowIndex, fillIndex, endIndex);

    }