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

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

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

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

import cn.exrick.xboot.jwaq.entity.contrast.ContrastIndicatorDeptExcel;
import cn.exrick.xboot.jwaq.tool.TitleSheetWriteHandler;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
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;

@RestController
@RequestMapping("/easyExcelController/ContrastIndicatorDeptExcel")
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(), ContrastIndicatorDeptExcel.class)
                    .excelType(ExcelTypeEnum.XLSX).head(ContrastIndicatorDeptExcel.class)
                    .registerWriteHandler(new TitleSheetWriteHandler("我是一个小标题",13))
                    // 标题及样式，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<ContrastIndicatorDeptExcel> data(){
        List<ContrastIndicatorDeptExcel>   list = new ArrayList<>();
        ContrastIndicatorDeptExcel ontrastIndicatorDeptExcel = new ContrastIndicatorDeptExcel();

        ontrastIndicatorDeptExcel.setUnit("1");
        ontrastIndicatorDeptExcel.setIndicatorName("2");
        ontrastIndicatorDeptExcel.setBigType("3");
        ContrastIndicatorDeptExcel ontrastIndicatorDeptExcel1 = new ContrastIndicatorDeptExcel();
        ontrastIndicatorDeptExcel1.setUnit("1");
        ontrastIndicatorDeptExcel1.setIndicatorName("2");
        ontrastIndicatorDeptExcel1.setBigType("3");
        ContrastIndicatorDeptExcel ontrastIndicatorDeptExcel2 = new ContrastIndicatorDeptExcel();
        ontrastIndicatorDeptExcel2.setUnit("1");
        ontrastIndicatorDeptExcel2.setIndicatorName("2");
        ontrastIndicatorDeptExcel2.setBigType("3");
        ContrastIndicatorDeptExcel ontrastIndicatorDeptExcel3 = new ContrastIndicatorDeptExcel();
        ontrastIndicatorDeptExcel3.setUnit("1");
        ontrastIndicatorDeptExcel3.setIndicatorName("2");
        ontrastIndicatorDeptExcel3.setBigType("3");

        list.add(ontrastIndicatorDeptExcel);
        list.add(ontrastIndicatorDeptExcel1);
        list.add(ontrastIndicatorDeptExcel2);
        list.add(ontrastIndicatorDeptExcel3);

        return list;
    }
}

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 cn.exrick.xboot.jwaq.tool;

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));
    }
}