EasyExcel导出自定义合并单元格文件

亚林瓜子

已于 2022-08-19 10:19:58 修改

阅读量4k

点赞数 4

文章标签： java

于 2022-08-13 15:44:35 首次发布

欢迎流通功德无量

本文链接：https://blog.csdn.net/fxtxz2/article/details/126320082

版权

目标

需要使用阿里的EasyExcel库来导出excel，并要自定义合并单元格。
EasyExcel导出

思路

EasyExcel数据
这里自定义的CellWriteHandler需要将数据进行如下处理：

1.Excel每一行数据必须对应一个对象；
2.每一个对象必须有ID字段。
这里使用EasyExcel的数据数组，必须达到这两个条件。合并单元格的判断逻辑如下：
判断当前行的对象ID与上一行的对象ID相等，且当前单元格对象的字段值与上一行单元格对象的字段值相等。才能够进行单元格合并。

步骤

ExportKbdAreaRuleItemVO.java



import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import io.swagger.annotations.ApiModelProperty;
import lombok.*;

import java.util.Date;

/**
 * KBD区域规则 （hq bu sd） 列表回参对象
 *
 */
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Data
@EqualsAndHashCode
@ColumnWidth(value = 28)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
public class ExportKbdAreaRuleItemVO {

    @ExcelProperty(value = "id", index = 0)
    private String id;

    /** 组织架构 —— BU */
    @ExcelProperty(value = "BU", index = 1)
    private String bu;
    /** 组织架构 —— Region */
    @ApiModelProperty(value = "组织架构二级")
    @ExcelProperty(value = "Region", index = 2)
    private String region;
    /** 组织架构 —— Area */
    @ApiModelProperty(value = "组织架构三级")
    @ExcelProperty(value = "Area", index = 3)
    private String area;
    /** 组织架构 —— Territory */
    @ApiModelProperty(value = "组织架构四级")
    @ExcelProperty(value = "Territory", index = 4)
    private String territory;

    /** 渠道业态name*/
    @ApiModelProperty(value = "渠道业态name")
    @ExcelProperty(value = "渠道业态", index = 5)
    private String channelBusinessLevelName;

    /** 进行中的规则  */
    @ApiModelProperty(value = "进行中的规则")
    @ExcelProperty(value = "进行中规则", index = 6)
    private String doingName;

    /** 规则开始时间 */
    @ApiModelProperty(value = "规则开始时间")
    @ExcelProperty(value = "规则开始时间", index = 7)
    private Date ruleStartTime;

    /** 规则结束时间 */
    @ApiModelProperty(value = "规则结束时间")
    @ExcelProperty(value = "规则结束时间", index = 8)
    private Date ruleEndTime;

    /** 其他规则  */
    @ApiModelProperty(value = "其他规则")
    @ExcelProperty(value = "其他规则", index = 9)
    private String otherName;

    /** 失效规则 */
    @ApiModelProperty(value = "失效规则")
    @ExcelProperty(value = "上一条规则信息", index = 10)
    private String endChannelRuleName;

    /** 失效日期 */
    @ApiModelProperty(value = "失效日期")
    @ExcelProperty(value = "上一条规则失效时间", index = 11)
    private Date endTime;

}

ExcelFillCellMergeStrategy.java

这里就是处理核心类。



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.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

public class ExcelFillCellMergeStrategy implements CellWriteHandler {
    // 需要从第几行开始合并，0表示第1行
    private final int mergeRowIndex;
    // 合并的哪些列，比如为4时，当前行id和上一行id相同则合并前五列
    private final int mergeColumnRegion;

    private final List<Integer> ignoreColumn;

    public ExcelFillCellMergeStrategy(int mergeRowIndex, int mergeColumnRegion, List<Integer> ignoreColumn) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnRegion = mergeColumnRegion;
        this.ignoreColumn = ignoreColumn;
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        CellWriteHandler.super.afterCellCreate(writeSheetHolder, writeTableHolder, cell, head, relativeRowIndex, isHead);
        // 隐藏id列
        writeSheetHolder.getSheet().setColumnHidden(0, true);
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        CellWriteHandler.super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead);
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (!ignoreColumn.contains(curColIndex) && curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnRegion; i++) {
                if (curColIndex <= mergeColumnRegion) {
                    mergeWithPreviousRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }

    /**
     * 当前单元格向上合并：当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列
     *
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPreviousRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        // 当前行的第一个Cell
        Cell curFirstCell = cell.getSheet().getRow(curRowIndex).getCell(0);
        Object curFirstData = curFirstCell.getCellType() == CellType.STRING ? curFirstCell.getStringCellValue() : curFirstCell.getNumericCellValue();
        // 上一行的第一个Cell
        Cell preFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
        Object preFirstData = preFirstCell.getCellType() == CellType.STRING ? preFirstCell.getStringCellValue() : preFirstCell.getNumericCellValue();

        // 当前cell
        Object data = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        // 上面的Cell
        Cell upCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object upData = upCell.getCellType() == CellType.STRING ? upCell.getStringCellValue() : upCell.getNumericCellValue();

        // 当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列 且上一行值相同
        if (curFirstData.equals(preFirstData) && data.equals(upData)) {
            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);
            }
        }
    }
}

使用片段

@Override
    public void kbdAreaRuleListToExcel(HttpServletResponse response, List<KbdAreaRuleListVO> kbdAreaRuleListVOList) {
        try {
            List<ExportKbdAreaRuleItemVO> dataList = new ArrayList<>();
            int size = kbdAreaRuleListVOList.size();
            for (int i = 0; i < size; i++) {
                KbdAreaRuleListVO data = kbdAreaRuleListVOList.get(i);
                List<KbdAreaRuleListVO.BasicDTO> doingList = data.getDoingList();
                int doingListSize;
                if (CollectionUtils.isEmpty(doingList)){
                    doingListSize = 0;
                } else {
                    doingListSize = doingList.size();
                }
                List<KbdAreaRuleListVO.BasicDTO> otherList = data.getOtherList();
                List<KbdAreaRuleListVO.BasicTime> timeList = data.getTimeList();
                int timeListSize;
                if (CollectionUtils.isEmpty(timeList)){
                    timeListSize = 0;
                } else {
                    timeListSize = timeList.size();
                }
                int sizeMax = Math.max(doingListSize, timeListSize);
                if (sizeMax > 0){
                    // 这里需要重复填充对象
                    for (int j = 0; j < sizeMax; j++) {
                        ExportKbdAreaRuleItemVO exportKbdAreaRuleItemVO = ExportKbdAreaRuleItemVO.builder()
                                .id(String.valueOf(i))
                                .bu(data.getOrgLevel1Name())
                                .region(data.getOrgLevel2Name())
                                .area(data.getOrgLevel3Name())
                                .territory(data.getOrgLevel4Name())
                                .channelBusinessLevelName(data.getChannelBusinessLevelName())
                                .endTime(data.getEndTime())
                                .endChannelRuleName(data.getEndChannelRuleName())
                                .otherName(CollectionUtils.isEmpty(otherList) ? "" : otherList.stream().map(KbdAreaRuleListVO.BasicDTO::getName).collect(Collectors.joining(",")))
                                .build();
                        if (j < doingListSize){
                            KbdAreaRuleListVO.BasicDTO doing = doingList.get(j);
                            exportKbdAreaRuleItemVO.setDoingName(doing.getName());
                        }
                        if (j < timeListSize){
                            KbdAreaRuleListVO.BasicTime time = timeList.get(j);
                            exportKbdAreaRuleItemVO.setRuleStartTime(time.getStartTime());
                            exportKbdAreaRuleItemVO.setRuleEndTime(time.getEnDTime());
                        }
                        dataList.add(exportKbdAreaRuleItemVO);
                    }
                } else {
                    ExportKbdAreaRuleItemVO exportKbdAreaRuleItemVO = ExportKbdAreaRuleItemVO.builder()
                            .id(String.valueOf(i))
                            .bu(data.getOrgLevel1Name())
                            .region(data.getOrgLevel2Name())
                            .area(data.getOrgLevel3Name())
                            .territory(data.getOrgLevel4Name())
                            .channelBusinessLevelName(data.getChannelBusinessLevelName())
                            .endTime(data.getEndTime())
                            .endChannelRuleName(data.getEndChannelRuleName())
                            .otherName(CollectionUtils.isEmpty(otherList) ? "" : otherList.stream().map(KbdAreaRuleListVO.BasicDTO::getName).collect(Collectors.joining(",")))
                            .build();
                    dataList.add(exportKbdAreaRuleItemVO);
                }
            }
            // 上面就是数据的前处理
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        	response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("数据.xlsx", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
            // 这里是设置这些列 不用合并的列
            List<Integer> ignoreColumn = new ArrayList<>();
            ignoreColumn.add(6);
            ignoreColumn.add(7);
            ignoreColumn.add(8);
            ignoreColumn.add(10);
            ignoreColumn.add(11);
            // 这里需要设置不关闭流
            EasyExcelFactory.write(response.getOutputStream(), ExportKbdAreaRuleItemVO.class).autoCloseStream(Boolean.TRUE).sheet("数据")
                    .registerWriteHandler(new ExcelFillCellMergeStrategy(1, 11, ignoreColumn))
                    .doWrite(dataList);
        } catch (Exception e) {
            // 重置response
            log.error("exportList导出异常:{}", e);
           
        }
    }