easyexcel导出excel文件合并相同单元格数据

1、引入easyexcel依赖

!-- 阿里开源easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>

2、ExcelMergeUtil工具类

package com.sdy.resdir.biz.util;


import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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.math.BigDecimal;
import java.util.List;

import static org.apache.poi.ss.usermodel.CellType.NUMERIC;

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 afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> 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.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
        Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
        Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
        // 将当前单元格数据与上一个单元格数据比较
        Boolean dataBool = preData.equals(curData);
        //此处需要注意:因为我是按照工程名称确定是否需要合并的,所以获取每一行第二列数据和上一行第一列数据进行比较,如果相等合并,getCell里面的值,是工程名称所在列的下标
        BigDecimal d1 = new BigDecimal(cell.getRow().getCell(0).getNumericCellValue());
        BigDecimal d2 = new BigDecimal(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue());
        Boolean bool = d1.compareTo(d2) == 0 ? true:false;
        // 原始的
        // 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);
            }
        }
    }
}









3、导出的excel标题

package com.sdy.resdir.biz.vo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.sdy.common.utils.DateUtil;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.util.Date;

/**
 * Excel模板文件类
 *
 * @author hyh
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class ResItemExcelVo extends BaseRowModel {

    /**
     * 序号
     */
    @ExcelProperty(value = {"资源基本信息", "序号"},index = 0)
    private Integer resNo;

    /**
     * 资源名称
     */
    @ColumnWidth(20)
    // @ExcelProperty(value = "资源名称",index = 1)
    @ExcelProperty(value = {"资源基本信息", "资源名称"}, index = 1)
    private String resName;

    /**
     * 资源类型
     */
    @ColumnWidth(20)
    @ExcelProperty(value = {"资源基本信息", "资源类型"} ,index = 2)
    private String resType;

    /**
     * 资源提供部门
     */
    @ColumnWidth(20)
    @ExcelProperty(value = {"资源基本信息", "资源提供部门"},index = 3)
    private String resSourceDept;

    /**
     * 资源权限
     */
    @ColumnWidth(20)
    @ExcelProperty(value = {"资源基本信息", "资源权限"},index = 4)
    private String resPower;

    /**
     * 资源层级(1.省级 2.市级 3.区级)
     */
    @ColumnWidth(20)
    @ExcelProperty(value = {"资源基本信息", "资源层级"},index = 5)
    private String resLevel;

    /**
     * 归集情况(1.已上线 2.未上线)
     */
    @ColumnWidth(20)
    @ExcelProperty(value = {"资源基本信息", "归集情况"},index = 6)
    private String collectionSituation;

    /**
     * 是否导入省里(0-否,1-是)
     */
    @ColumnWidth(20)
    @ExcelProperty(value = {"资源基本信息", "是否导入省里"},index = 7)
    private String isImport;

    /**
     * 回流标记(0-否,1-是)
     */
    @ColumnWidth(20)
    @ExcelProperty(value = {"资源基本信息", "是否回流"}, index = 8)
    private String backFlow;

    /**
     * 单位名称
     */
    @ColumnWidth(20)
    @ExcelProperty(value = {"资源基本信息", "单位名称"}, index = 9)
    private String companyName;

    /**
     * 数据项
     */
    @ColumnWidth(10)
    @ExcelProperty(value = {"编目信息","数据项"}, index = 10)
    private String chineseName;

    /**
     * 英文名称
     */
    @ColumnWidth(20)
    @ExcelProperty(value = {"编目信息","英文名称"}, index = 11)
    private String englishName;

    /**
     * 字段类型
     */
    @ColumnWidth(15)
    @ExcelProperty(value = {"编目信息","字段类型"}, index = 12)
    private String fieldType;

    /**
     * 字段长度
     */
    @ColumnWidth(15)
    @ExcelProperty(value = {"编目信息","字段长度"}, index = 13)
    private String fieldLength;

    /**
     * 字段精度
     */
    @ColumnWidth(15)
    @ExcelProperty(value = {"编目信息","字段精度"}, index = 14)
    private String fieldAccuracy;

    /**
     * 是否主键
     */
    @ColumnWidth(15)
    @ExcelProperty(value = {"编目信息","是否主键"}, index = 15)
    private String keywords;

    /**
     * 是否字典项(0-否,1-是)
     */
    @ColumnWidth(15)
    @ExcelProperty(value = {"编目信息","是否字典项"}, index = 16)
    private String isDictionary;

    /**
     * 字段描述
     */
    @ColumnWidth(15)
    @ExcelProperty(value = {"编目信息","字段描述"}, index = 17)
    private String fieldDesc;

    /**
     * 是否为空(0-否,1-是)
     */
    @ColumnWidth(15)
    @ExcelProperty(value = {"编目信息","是否为空"}, index = 18)
    private String isEmpty;

    /**
     * 默认值
     */
    @ColumnWidth(15)
    @ExcelProperty(value = {"编目信息","默认值"}, index = 19)
    private String defaultValue;

    /**
     * 共享属性
     */
    @ColumnWidth(18)
    @ExcelProperty(value = {"编目信息","共享属性"}, index = 20)
    private String shareAttribute;

    /**
     * 共享条件
     */
    @ColumnWidth(18)
    @ExcelProperty(value = {"编目信息","共享条件"}, index = 21)
    private String shareCondition;

    /**
     * 开放属性
     */
    @ColumnWidth(18)
    @ExcelProperty(value = {"编目信息","开放属性"}, index = 22)
    private String openAttribute;


}

4、控制层调用

public void downLoadResItem(HttpServletResponse response, String resName, Integer isOnline, Integer resType, Integer resLevel, Integer resPower, Integer resPowerDept, Integer realmId) throws IOException {
        if (StringUtil.isNotBlank(resName)) {
            resName = URLDecoder.decode(resName, "UTF-8");
        }
        List<RdResourceDirExcelDTO> rdResourceDirList = rdResourceDirService.getList(resName, isOnline, resType, resLevel, resPower, resPowerDept, realmId);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("资源列表下载", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        ServletOutputStream output = response.getOutputStream();
        ExcelWriter writer = new ExcelWriter(output, ExcelTypeEnum.XLS, true);
        Sheet sheet = new Sheet(1, 0, ResItemExcelVo.class);
        // sheet.setSheetName("第一页");
        List<ResItemExcelVo> voList = setResItemData(rdResourceDirList);
        // writer.write(voList, sheet);
        //需要合并的列
        int[] mergeColumeIndex = {0,1,2,3,4,5,6,7,8,9};
        // 从第二行后开始合并
        int mergeRowIndex = 2;
        EasyExcel.write(response.getOutputStream(), ResItemExcelVo.class)
                .sheet("第一页")
                // .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
                .registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex))
                .doWrite(voList);
        writer.finish();
        output.flush();
    }

 导出的效果excel ,我是通过序号相同合并

easyexcel导出excel文件合并相同单元格数据

 

上一篇:Java面试题:哪些对象可以作为GC Roots?


下一篇:如何在 K8S 中优雅的使用私有镜像库