带标题及单元格合并的easyExcel导出表格

easyExcel导出表格（有标题、单元格合并）

如何过一天，就如何过一生。

1、前言

最近工作上遇到了xlsx格式报表的导出，导出的数据存在父子关系，即相当于树形数据，有单元格合并和标题形式的要求，查阅了一些资料，总算是弄出来了，这里另写一个小样简单分享一下关于easyExcel导出具有合并单元格和标题的小结 代码，也算记录一下自己的工作学习。

2、代码

（目录及代码仅为实现，勿纠结格式和规范问题/捂脸）

easyExcel依赖

<dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>easyexcel</artifactId>
          <version>2.2.6</version>
      </dependency>

controller层

package easyexceldemo.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import easyexceldemo.dto.BizMergeStrategy;
import easyexceldemo.dto.RowRangeDto;
import easyexceldemo.dto.TitleSheetWriteHandler;
import easyexceldemo.entity.User;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

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

/**
 * <p>
 * 该类的描述
 * </p>
 *
 * @author
 * @Modified By:
 * @since 2020/11/23 11:31
 */
@RestController
@RequestMapping("/user")
public class EasyExcelController {

    @GetMapping("/excel")
    public void excel(HttpServletResponse response) throws IOException {
        Map<String, List<RowRangeDto>> strategyMap = BizMergeStrategy.addAnnualMerStrategy(data());
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String filename = URLEncoder.encode("用户表测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");

            EasyExcel.write(response.getOutputStream(), User.class)
                    .excelType(ExcelTypeEnum.XLSX).head(User.class)
                    .registerWriteHandler(new TitleSheetWriteHandler("我是一个小标题",2)) // 标题及样式，lastCol为标题第0列到底lastCol列的宽度
                    //设置默认样式及写入头信息开始的行数
                    .relativeHeadRowIndex(1)
                    .registerWriteHandler(new BizMergeStrategy(strategyMap))    // 注册合并策略
                    .registerWriteHandler(BizMergeStrategy.CellStyleStrategy()) // 设置样式
                    .sheet("测试")
                    .doWrite(data());
        }catch (Exception e) {
            e.printStackTrace();
            response.reset();
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/json");
            response.getWriter().println("打印失败");
        }

    }
	
    private List<User> data(){
        List<User> list = new ArrayList<>();
        User user = new User("1","张三","总裁");
        User user1 = new User("2","李四","总经理");
        User user2 = new User("3","李四","技术员");
        User user3 = new User("4","王五","技术员");

        list.add(user);
        list.add(user1);
        list.add(user2);
        list.add(user3);

        return list;
    }
}

实体

package easyexceldemo.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

/**
 * <p>
 * 该类的描述
 * </p>
 *
 * @author
 * @Modified By:
 * @since 2020/11/23 11:28
 */
public class User {
    @ColumnWidth(10)
    @ExcelProperty(value = {"id"}, index = 0)
    private String id;

    @ColumnWidth(20)
    @ExcelProperty(value = {"名字"}, index = 1)
    private String name;

    @ColumnWidth(20)
    @ExcelProperty(value = {"职位"}, index = 2)
    private String postion; //职位

    public User(String id,String name,String postion){
        this.id = id;
        this.name = name;
        this.postion = postion;
    }
    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPostion() {
        return postion;
    }

    public void setPostion(String postion) {
        this.postion = postion;
    }
}

策略及其他

package easyexceldemo.dto;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import easyexceldemo.entity.User;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

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

/**
 * @description: 合并策略和样式
 * @author

 * @param
 * @since 2020/11/20 17:24
 * @Modified By:
 * @return
 */
public class BizMergeStrategy extends AbstractMergeStrategy {

    private Map<String, List<RowRangeDto>> strategyMap;
    private Sheet sheet;

    public BizMergeStrategy(Map<String, List<RowRangeDto>> strategyMap) {
        this.strategyMap = strategyMap;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        this.sheet = sheet;
        //如果没有标题，只有表头的话，这里的 cell.getRowIndex() == 1
        if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {
            /**
             * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
             * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
             * 但此时A2,A3已经是合并的单元格了
             */
            for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
                Integer columnIndex = Integer.valueOf(entry.getKey());
                entry.getValue().forEach(rowRange -> {
                    //添加一个合并请求
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
                            rowRange.getEnd(), columnIndex, columnIndex));
                });
            }
        }
    }

   
    public static Map<String, List<RowRangeDto>> addAnnualMerStrategy(List<User> projectDtoList) {
        Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
        User preUser = null;
        for (int i = 0; i < projectDtoList.size(); i++) {
            User curUser = projectDtoList.get(i);
            //如果名字一样，将名字合并（真正开发中一般不会通过名字这样字段，而是通过一些关联的唯一值，比如父id）
            if (preUser != null) {
                if (curUser.getName() == preUser.getName()){    // 名字相同则合并第一列
//                    BizMergeStrategy.fillStrategyMap(strategyMap, "0", i+1);
                    //如果没有标题，只有表头的话，这里为 BizMergeStrategy.fillStrategyMap(strategyMap, "1", i);
                    BizMergeStrategy.fillStrategyMap(strategyMap, "1", i+1);
                }
            }
            preUser = curUser;
        }
        return strategyMap;
    }
    /**
     * @description: 新增或修改合并策略map
     * @author

     * @param strategyMap
     * @param key
     * @param index
     * @since 2020/11/17 17:32
     * @Modified By:
     * @return
     */
    private static void fillStrategyMap(Map<String, List<RowRangeDto>> strategyMap, String key, int index){
        List<RowRangeDto> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
        boolean flag = false;
        for (RowRangeDto dto : rowRangeDtoList) {
            //分段list中是否有end索引是上一行索引的，如果有，则索引+1
            if (dto.getEnd() == index) {
                dto.setEnd(index + 1);
                flag = true;
            }
        }
        //如果没有，则新增分段
        if (!flag) {
            rowRangeDtoList.add(new RowRangeDto(index, index + 1));
        }
        strategyMap.put(key, rowRangeDtoList);
    }

    /**
     * @description: 表格样式
     * @author

     * @since 2020/11/20 9:40
     * @Modified By:
     * @return
     */
    public static HorizontalCellStyleStrategy CellStyleStrategy(){
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)13);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }
}

package easyexceldemo.dto;

public class RowRangeDto {
    private int start;
    private int end;

    public RowRangeDto(int start,int end){
        this.start = start;
        this.end = end;
    }
    public int getStart() {
        return start;
    }

    public void setStart(int start) {
        this.start = start;
    }

    public int getEnd() {
        return end;
    }

    public void setEnd(int end) {
        this.end = end;
    }
}

package easyexceldemo.dto;
 
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

public class TitleSheetWriteHandler implements SheetWriteHandler {
    private String title;
    private int lastCol;
    public TitleSheetWriteHandler(String title,int lastCol){
        this.title = title;
        this.lastCol = lastCol;
    }
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        //设置标题
        Row row = sheet.createRow(0);
        row.setHeight((short) 800);
        Cell cell = row.createCell(0);
        cell.setCellValue(title);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 400);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, lastCol));
    }
}

总结

这里为该demo的全部代码，使用时新建一个项目，直接复制粘贴过去，运行即可。
借鉴了不少网上的资料，有部分内容本人还尚未搞明白，有不明白可以留言互相探讨。
借鉴连接：
1、这老哥的文档帮了极大忙，感谢！/抱拳
2、这老哥的文档也很详细
3、语雀官网