【EasyExcel】用自定义合并表格完成 list 属性格子拆分

FeiFei Lee

已于 2023-09-23 11:34:48 修改

阅读量654

点赞数

文章标签： excel java spring boot

于 2023-09-23 11:26:28 首次发布

本文链接：https://blog.csdn.net/weixin_44340643/article/details/133200192

版权

0.前情-需求

针对于这种导出的格式，我们希望把最后的“整改问题1；整改问题2”拆分开来，每个问题一个小格子，像下面一样

但是用 easyexcel 没办法去做到格子拆分，于是，逆其道而行之，可以把这一行分为这样

然后对其前面的格子进行合并，即可得到图2的样子；

1.具体实现的合并代码

		<!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.1</version>
        </dependency>

		<!--poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.1</version>
        </dependency>

//dataList -> 导出的数据
//3500 -> excel列宽
//1 -> 需要细分格子的列数（需要把这个列放在最后）
this.excelDownloadCommon(dataList, 3500, 1, "监督记录汇总表", JdjlhzExportVo.class, response);

//核心代码，下面指定了两个内容控制器，一个控制样式，另一个负责合并表格
public void excelDownloadCommon(List<?> dataList, int width, int exclude, String fileName, Class zlass, HttpServletResponse response) {
        try {
        	//负责样式的控制器
            WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //主题
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();//内容
            //主标题和副标题在excel中分别是是第0和第1行
            List<Integer> columnIndexes = Arrays.asList(0, 1);
            //自定义标题和内容策略(具体定义在下文)
            CellStyleStrategyV2 cellStyleStrategy = new CellStyleStrategyV2(zlass.getDeclaredFields().length, width, columnIndexes, headWriteCellStyle, contentWriteCellStyle);

            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String name = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), zlass)
                    .autoCloseStream(Boolean.TRUE)
                    //负责合并表格
                    .registerWriteHandler(new MultiColumnMergeStrategy(dataList.size(),0,zlass.getDeclaredFields().length-1-exclude))
                    //负责样式
                    .registerWriteHandler(cellStyleStrategy)
                    .sheet(fileName)
                    .doWrite(dataList);
        } catch (Exception e) {
            e.printStackTrace();
            log.info("excel export error : {}", e.getMessage());
        }
    }

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;

/**
 * 自定义合并控制器
 */
public class MultiColumnMergeStrategy extends AbstractMergeStrategy {

    // 合并的列编号，从0开始，指定的index或自己按字段顺序数
    private Integer startCellIndex = 0;
    private Integer endCellIndex = 0;

    // 数据集大小，用于区别结束行位置
    private Integer maxRow = 0;

    // 禁止无参声明
    private MultiColumnMergeStrategy() {
    }


    public MultiColumnMergeStrategy(Integer maxRow, Integer startCellIndex, Integer endCellIndex) {
        this.startCellIndex = startCellIndex;
        this.endCellIndex = endCellIndex;
        this.maxRow = maxRow;
    }

    // 记录上一次合并的信息
    private final List<List<String>> dataList = new ArrayList<>();

    /**
     * 每行每列都会进入，循环注意条件限制
     */
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int currentCellIndex = cell.getColumnIndex();
        int currentRowIndex = cell.getRowIndex();

        // 判断该列是否需要合并
        if (currentCellIndex < startCellIndex || currentCellIndex > endCellIndex) {
            return;
        }

        Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        String currentCellValue = curData.toString();

        List<String> rowList;
        if (dataList.size() > currentRowIndex - 1) {
            rowList = dataList.get(currentRowIndex - 1);
        } else {
            rowList = new ArrayList<>();
            dataList.add(rowList);
        }
        rowList.add(currentCellValue);

        // 结束的位置触发下最后一次没完成的合并
        if (relativeRowIndex == (maxRow - 1) && currentCellIndex == endCellIndex) {
            System.out.println(JSONObject.toJSONString(dataList));
            List<String> tempList = null;
            Integer tempIndex = null;
            for (int i = 0; i < dataList.size(); i++) {
                if (tempList == null) {
                    tempList = dataList.get(i);
                    tempIndex = i;
                    continue;
                }
                List<String> currList = dataList.get(i);
                if (tempList.equals(currList)) {
                    if (i >= dataList.size() - 1) {
                        // 结束的位置触发下最后一次没完成的合并
                        for (int j = 0; j < tempList.size(); j++) {
                            sheet.addMergedRegionUnsafe(new CellRangeAddress(tempIndex + 1, i + 1, startCellIndex + j, startCellIndex + j));
                        }
                    }
                    continue;
                }

                // 当前行数据和上一行数据不同且上面有多行相同数据时触发合并
                if (i - tempIndex > 1) {
                    for (int j = 0; j < tempList.size(); j++) {
                        sheet.addMergedRegionUnsafe(new CellRangeAddress(tempIndex + 1, i, startCellIndex + j, startCellIndex + j));
                    }
                }


                tempIndex = i;
                tempList = currList;


            }

        }
    }
}

import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.List;

/**
 * 样式控制器
 */
public class CellStyleStrategyV2 extends HorizontalCellStyleStrategy {

    private final WriteCellStyle headWriteCellStyle;
    private final WriteCellStyle contentWriteCellStyle;

    /**
     * 操作列
     */
    private final List<Integer> columnIndexes;

    private int widthCount;
    private int width;

    public CellStyleStrategyV2(int widthCount, int width, List<Integer> columnIndexes, WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.widthCount = widthCount;
        this.width = width;
        this.columnIndexes = columnIndexes;
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyle = contentWriteCellStyle;
    }

    //设置头样式
    @Override
    protected void setHeadCellStyle(CellWriteHandlerContext context) {
//        context.getWriteSheetHolder().getSheet().setColumnWidth(context.getRelativeRowIndex(),4000);
        // 获取字体实例
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");
        //垂直居中,水平居中
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        if (columnIndexes.get(0).equals(context.getRelativeRowIndex())) { //主标题
            context.getRow().setHeight((short)500); //设置主标题高度
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            headWriteFont.setFontHeightInPoints((short) 20);
            headWriteFont.setBold(true);
        } else { //副标题
            //headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            headWriteFont.setFontHeightInPoints((short) 12);
            headWriteFont.setBold(false);
        }
        headWriteCellStyle.setWriteFont(headWriteFont);
        if (stopProcessing(context)) {
            return;
        }
        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle());
    }

    //设置填充数据样式
    @Override
    protected void setContentCellStyle(CellWriteHandlerContext context) {

        //TODO 设置列宽度
//        context.getWriteSheetHolder().getSheet().setColumnWidth(context.getRelativeRowIndex(),4000);
        for (int i = 0; i < widthCount; i++) {
            context.getWriteSheetHolder().getSheet().setColumnWidth(i,width);
        }
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 8);
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        //垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        //contentWriteCellStyle.setDataFormatData(new DataFormatData());//设置单元格格式是：文本格式，方式长数字文本科学计数法
        contentWriteCellStyle.setWrapped(true);  //设置自动换行
        contentWriteCellStyle.setShrinkToFit(true); //设置文本收缩至合适


        WriteCellData<?> cellData = context.getFirstCellData();
        WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle());
    }

}

最后给一下 excel 模板实体类

@Data
@ApiModel("监督记录汇总导出vo")
@ContentRowHeight(50)
@HeadRowHeight(50)
public class JdjlhzExportVo {

    @ExcelProperty(value = {"监督记录汇总表","序号"})
    private Integer xh;
    @ExcelProperty(value = {"监督记录汇总表","监督类型"})
    private String jdlx;
    @ExcelProperty(value = {"监督记录汇总表","检查时间"})
    private String jcsj;
    @ExcelProperty(value = {"监督记录汇总表","项目名称"})
    private String gcmc;
    @ExcelProperty(value = {"监督记录汇总表","检查作业点"})
    private String jczyd;
    @ExcelProperty(value = {"监督记录汇总表","检查作业点数"})
    private Integer jczyds;
    @ExcelProperty(value = {"监督记录汇总表","检查人员"})
    private String jcry;
    @ExcelProperty(value = {"监督记录汇总表","检查人次"})
    private Integer jcrc;
    @ExcelProperty(value = {"监督记录汇总表","发出《整改通知书》份数"})
    private Integer zgtzsfs;
    @ExcelProperty(value = {"监督记录汇总表","整改文书编号"})
    private String zgsbh;
    @ExcelProperty(value = {"监督记录汇总表","限期整改问题数"})
    private Integer zgwts;
    @ExcelProperty(value = {"监督记录汇总表","整改期限"})
    private String zgqx;
    @ExcelProperty(value = {"监督记录汇总表","整改情况"})
    private String zgqk;
    @ExcelProperty(value = {"监督记录汇总表","具体整改问题"})
    private String jtzgwt;

    public String getJcsj() {
        if (jcsj != null && jcsj.length() >= 10) {
            return jcsj.substring(0, 10);
        }
        return jcsj;
    }

    public String getZgqx() {
        if (zgqx != null && zgqx.length() >= 10) {
            return zgqx.substring(0, 10);
        }
        return zgqx;
    }

}

2.注意点

本文使用 easyexcel 版本为 3.0.1 ，poi 版本 4.1.1 ，其他版本是否适用改方法未确定；
该方法需要把要细分格子的列放在最后，并指定列数（当然使用者可以看代码来改造）；
关于样式，格子高度在实体类设置，格子宽度要在核心方法参数上设置，其他可以参考文章：https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write#%E5%90%88%E5%B9%B6%E5%8D%95%E5%85%83%E6%A0%BC