easyexcel 动态合并相同的单元格

最新推荐文章于 2024-03-16 17:52:23 发布

打小就聪明丨

最新推荐文章于 2024-03-16 17:52:23 发布

阅读量327

点赞数 1

文章标签： java excel

本文链接：https://blog.csdn.net/qq_40592590/article/details/132739754

版权

先贴个图看是不是你需要的结果:
在这里插入图片描述

1.先引入pom依赖

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

2.建一个合并单元格的工具类

package cn.com.htwins.larkfast.system.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.util.List;

/**
 * 合并单元格工具类
 */
public class ExcelMergeUtil implements CellWriteHandler {
	//需要合并的列
    private int[] mergeColumnIndex;
    //合并的起始行
    private int mergeRowIndex;
    //合并依据列
    private int mergeBaseIndex;

    public ExcelMergeUtil() {
    }

    public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex, int mergeBaseIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
        this.mergeBaseIndex = mergeBaseIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @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, mergeBaseIndex);
                    break;
                }
            }
        }
    }


    /**
     * 当前单元格向上合并
     *
     * @param writeSheetHolder sheet
     * @param cell             当前单元格
     * @param curRowIndex      当前行
     * @param curColIndex      当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int mergeBaseIndex) {
        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);
        // 判断合并依据的列是否相同
        Boolean bool =  cell.getRow().getCell(mergeBaseIndex).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(mergeBaseIndex).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.service实际调用代码示例

		//文件名
		DateFormat format = new SimpleDateFormat("yyMMdd");
        String exportFileName = "经销商账户授信额度" + format.format(new Date());
        String encodedFileName = new String(exportFileName.getBytes(), "ISO8859-1") + ".xlsx";
        response.setContentType("application/octet-stream;charset=ISO8859-1");
        response.setHeader(
                "Content-Disposition",
                "attachment;filename="
                        + encodedFileName);
        response.setHeader("Pragma", "No-cache");
        response.setHeader("Cache-Control", "No-cache");
        response.setDateHeader("Expires", 0);
        response.setCharacterEncoding("UTF-8");

        //需要合并的列
        int[] mergeColumnIndex = {0, 1, 2};
        // 从第二行后开始合并
        int mergeRowIndex = 1;
        // 合并依据的列
        int mergeBaseIndex = 0;
        //DistributorCreditExcelVO 就是你要导出的实体类
        EasyExcel.write(response.getOutputStream(), DistributorCreditExcelVO.class)
                .sheet("经销商账户授信额度")
                .registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumnIndex, mergeBaseIndex))
                //需要写的数据
                .doWrite(list);

4.实体类样例

package cn.com.htwins.larkfast.system.vo;

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

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

@Data
public class DistributorCreditExcelVO implements Serializable {

    /**
     * id
     */
    @ExcelIgnore
    private Long id;

    /**
     * 经销商账户id
     */
    @ExcelIgnore
    private Long daId;

    /**
     * 经销商id
     */
    @ExcelIgnore
    private Long distributorId;

    /**
     * 授信时间限制模式：0 不限时间 1限制时间
     */
    @ExcelIgnore
    private Integer creditIstimelimit;

    /**
     * 经销商编码
     */
    @ColumnWidth(20)
    @ExcelProperty("经销商编码")
    private String distributorNo;

    /**
     * 经销商名称
     */
    @ColumnWidth(30)
    @ExcelProperty("经销商名称")
    private String distributorName;

    /**
     * 总授信额度
     */
    @ColumnWidth(20)
    @ExcelProperty("授信总额")
    private BigDecimal totalLineOfCredit;

    /**
     * 授信额度
     */
    @ColumnWidth(20)
    @ExcelProperty("授信额度")
    private BigDecimal lineOfCredit;

    /**
     * 授信开始时间
     */
    @ColumnWidth(30)
    @ExcelProperty("授信开始时间")
    private Date creditStarttime;

    /**
     * 授信结束时间
     */
    @ColumnWidth(30)
    @ExcelProperty("授信结束时间")
    private Date creditEndtime;

    /**
     * 当前状态
     */
    @ColumnWidth(10)
    @ExcelProperty("当前状态")
    private String status;

    /**
     * 创建人ID
     */
    @ExcelIgnore
    private Long createUser;

    /**
     * 创建人姓名
     */
    @ColumnWidth(15)
    @ExcelProperty("创建人姓名")
    private String createUserName;

    /**
     * 备注
     */
    @ColumnWidth(50)
    @ExcelProperty("备注")
    private String remark;

}

附:easyExcel常用注解

注解	说明
@ColumnWidth(20)	控制行的宽度，用在class上代表所有单元格的宽度，用在属性上代表当前列的宽度
@ContentRowHeight(20)	控制内容的高度，用在class上代表所有单元格的宽度，用在属性上代表当前列的宽度
@HeadRowHeight(20)	控制标题的高度
@ExcelIgnore	导出时忽略该属性，默认是导出全部的属性，无论属性上写没写@ExcelProperty
@ContentStyle	设置导出时的单元格格式，可用在class上也可用在属性上
@ContentFontStyle	设置导出时的文本格式
@ExcelProperty	标识导出或导入时，该属性为有效列
@DateTimeFormat	时间类型转为字符串格式。值可为：yyyy-MM-dd HH:mm:ss