实战演练，如何用Easyexcel合并单元格，设置字体、边框样式，并在尾行加入备注

一、前言

项目又提需求导出复杂样式表格，还要根据不同区划导出不同数据，又琢磨学习了下easyexcel和poi，实现效果如下：

1、多级表头合并

2、表头底纹+表格边框

3、合并相同数据单元格

4、尾行加入备注

二、效果图

直接上图

三、依赖

注意文中还需引入Lombok注解

Easyexcel

      <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
      </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

四、具体实现

1、测试类说明

注解说明：

@ContentRowHeight(25) //内容行高

@HeadRowHeight(20)//表头行高

@ColumnWidth(15)列宽度

@ExcelProperty(value = {"用户信息","行政区划","市级"}, index = 1)表头信息：

value中 相同表头会自动合并，index要按照自己需求设置好，否则字段顺序会乱

示例中用户信息是一级表头，行政区划是二级表头，市级、县市区是三级

代码

package com.hua.bo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;

import java.io.Serializable;

/**
 * @Author su
 * @Date 2023-09-15 14:47
 * @Description
 **/
@Data
@ContentRowHeight(25) //内容行高
@HeadRowHeight(20)//表头行高
public class ReportUserInfoBO implements Serializable {
    @ColumnWidth(5)//宽度
    @ExcelProperty(value = {"用户信息","序号"}, index = 0)
    private String id;

    @ColumnWidth(10)
    @ExcelProperty(value = {"用户信息","行政区划","市级"}, index = 1)
    private String sjmc;

    @ColumnWidth(15)
    @ExcelProperty(value = {"用户信息","行政区划","县（市、区）"}, index = 2)
    private String qxmc;

    @ColumnWidth(15)
    @ExcelProperty(value = {"用户信息","用户名称"}, index = 3)
    private String userName;

    @ColumnWidth(15)
    @ExcelProperty(value = {"用户信息","下单总数"}, index = 4)
    private String  sumNum;
    
}

2、控制层入口

说明：这块代码主要是造数据并在失败异常时处理异常转化为json格式方便前端展现。为序号复制根据自己需求来即可。

@GetMapping("/excelTest")
    public void excel(HttpServletRequest req, HttpServletResponse res,@RequestParam("xzqhdm") String xzqhdm) throws Exception {
        try {
            log.info("开始导出");
            res.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            res.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("test", "UTF-8").replaceAll("\\+", "%20");
            res.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            List<ReportUserInfoBO> userList = new ArrayList<>();
            ReportUserInfoBO bo;
            //造数据
            for (int i = 1; i < 30; i++) {
                bo = new ReportUserInfoBO();
                if (i < 10) {
                    bo.setQxmc("桥西");
                    bo.setSjmc("石家庄");
                } else {
                    bo.setQxmc("桥东");
                    bo.setSjmc("秦皇岛");
                }
                bo.setSumNum(String.valueOf(i * (i + 9)));
                bo.setUserName("测试" + i);
                userList.add(bo);
            }
            //模仿数据库取出数据没有序号赋值
            for (int i = 0; i < userList.size(); i++) {
                userList.get(i).setId(String.valueOf(i + 1));
            }
            excelUtil.ReportExcel(userList, res);
            log.info("导出成功");
        } catch (Exception e) {
            log.error(e.getMessage());
            res.reset();
            res.setContentType("application/json");
            res.setCharacterEncoding("utf-8");
            String origin = req.getHeader("Origin");
            res.setHeader("Access-Control-Allow-Origin", origin);
            res.getWriter().println(JSON.toJSONString(SysResult.fail(e.getMessage())));
        }finally {
            res.getOutputStream().close();

        }

3、导出工具类ExcelUtil

说明：

写入数据需要嵌套List所以准备了beizhu这个list，在MergeLastWriteHandler中会使用WriteCellStyle是风格样式字体、底纹、边框等在这里设置

mergeColumeIndex是合并市级字段所需参数

mergeRowIndex 是合并市级字段所需参数

 public void ReportExcel(List<ReportUserInfoBO> listVo, HttpServletResponse response) throws IOException {
        //末尾备注
        List<List<String>> beizhu = new ArrayList<>();
        List<String> list = new ArrayList<>();
        //末尾添加说明 合并最后一行单元格时赋值在 MergeLastWriteHandler
        list.add("说明");
        beizhu.add(list);
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 标题字体大小
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(contentWriteFont);
        //标题黄色底纹
        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());

        //边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置自动换行，前提内容中需要加「\n」才有效
        contentWriteCellStyle.setWrapped(true);
        //初始化样式
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        //合并单元格所需参数
        //合并坐标
        int[] mergeColumeIndex = {1, 1};
        //从第二行后开始合并
        int mergeRowIndex = 1;
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ReportUserInfoBO.class).autoCloseStream(Boolean.FALSE)
                //风格样式
                .registerWriteHandler(horizontalCellStyleStrategy)
                //合并说明并赋值
                .registerWriteHandler(new MergeLastWriteHandler())
                //自动合并市级单元格 注意两个入参是出事
                .registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex))
                .build()) {
            WriteSheet sheet = EasyExcel.writerSheet("测试").build();
            excelWriter.write(listVo, sheet);
            excelWriter.write(beizhu, sheet);
        }
    }

4、合并工具类

1、某个字段合并

借鉴前辈的代码，注意不同版本可能cell的某些方法不一致

mergeColumnIndex是你要合并单元格的范围

mergeRowIndex从第几行开始合并

package com.hua.Util;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
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.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;



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 afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> 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.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellType()== CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意，获取每一行第二列数据和上一行第一列数据进行比较，如果相等合并，getCell里面的值，是名称所在列的下标
        Boolean bool = cell.getRow().getCell(1).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(1).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);
            }
        }
    }
}

2、合并文末说明

在excelutil中会在末尾插入一个为名为beizhu的list，本方法或根据beizhu的值判断是否合并单元格

package com.hua.Util;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

public class MergeLastWriteHandler extends AbstractMergeStrategy {


    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        String strValue = cell.getStringCellValue();
        if (StringUtils.equals(strValue,"说明")){
            //如果最后一行是说明则重新赋值
            cell.setCellValue("说明:该表格仅用于测试学习哦！来看一看最终效果吧");
            //获取表格最后一行
            int lastRowNum = sheet.getLastRowNum();
            //合并单元格
            CellRangeAddress region = new CellRangeAddress(lastRowNum,lastRowNum,0,4);
            sheet.addMergedRegion(region);
        }
    }
}

五、测试

测试工具发送保存请求

保存

最终结果