easyExec 合并单元格

easyExec 合并单元格

添加依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>3.1.1</version>
		</dependency>

自定义注解，用于判断是否需要合并以及合并的主键

import java.lang.annotation.*;

/**
 * 自定义注解，用于判断是否需要合并以及合并的主键
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {
 
    /**
     * 是否需要合并单元格
     */
    boolean needMerge() default false;
 
    /**
     * 是否是主键,即该字段相同的行合并
     */
    boolean isPk() default false;
}

注解实现

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.mstdt.admin.b2c.service.CustomMerge;
import org.apache.commons.lang3.StringUtils;
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.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * 自定义单元格合并策略
 */
public class CustomMergeStrategy implements RowWriteHandler {
    /**
     * 主键下标集合
     */
    private List<Integer> pkColumnIndex = new ArrayList<>();

    /**
     * 需要合并的列的下标集合
     */
    private List<Integer> needMergeColumnIndex = new ArrayList<>();

    private Set<String> orderNoSet = new HashSet<>();

    /**
     * DTO数据类型
     */
    private Class<?> elementType;

    public CustomMergeStrategy(Class<?> elementType) {
        this.elementType = elementType;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 如果是标题,则直接返回
        if (isHead) {
            return;
        }

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 获取标题行
        Row titleRow = sheet.getRow(0);

        if (pkColumnIndex.isEmpty()) {
            this.lazyInit(writeSheetHolder);
        }

        // 判断是否需要和上一行进行合并
        // 不能和标题合并，只能数据行之间合并
        if (row.getRowNum() <= 1) {
            return;
        }
        // 获取上一行数据
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
        boolean margeBol = true;
        for (Integer pkIndex : pkColumnIndex) {
            String lastKey = lastRow.getCell(pkIndex).getCellType() == CellType.STRING ? lastRow.getCell(pkIndex).getStringCellValue() : String.valueOf(lastRow.getCell(pkIndex).getNumericCellValue());
            String currentKey = row.getCell(pkIndex).getCellType() == CellType.STRING ? row.getCell(pkIndex).getStringCellValue() : String.valueOf(row.getCell(pkIndex).getNumericCellValue());

            if (!StringUtils.equalsIgnoreCase(lastKey, currentKey)) {
                if (!orderNoSet.contains(currentKey)) {
                    margeBol = false;
                    break;
                }
            } else {
                orderNoSet.add(currentKey);
            }
        }
        if (margeBol) {
            for (Integer needMerIndex : needMergeColumnIndex) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
                        needMerIndex, needMerIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
                //获取单元格内容
                Cell cell = row.getCell(needMerIndex);
                cell.setCellValue("");

            }
        }

    }

    /**
     * 初始化主键下标和需要合并字段的下标
     */
    private void lazyInit(WriteSheetHolder writeSheetHolder) {

        // 获取当前sheet
        Sheet sheet = writeSheetHolder.getSheet();

        // 获取标题行
        Row titleRow = sheet.getRow(0);
        // 获取DTO的类型
        Class<?> eleType = this.elementType;

        // 获取DTO所有的属性
        Field[] fields = eleType.getDeclaredFields();

        // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
        for (Field theField : fields) {
            // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
            ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
            // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
            if (null == easyExcelAnno) {
                continue;
            }
            // 获取自定义的注解，用于合并单元格
            CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);

            // 没有@CustomMerge注解的默认不合并
            if (null == customMerge) {
                continue;
            }
            int index = easyExcelAnno.index();
            // 判断是否有主键标识
            if (customMerge.isPk()) {
                pkColumnIndex.add(index);
            }

            // 判断是否需要合并
            if (customMerge.needMerge()) {
                needMergeColumnIndex.add(index);
            }
        }

        // 没有指定主键，则异常
        if (pkColumnIndex.isEmpty()) {
            throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
        }

    }
}

controller

 public void exportMiddleOrderRefundWaterExcel(B2cOrderListSearchRequest params, HttpServletResponse response)
            throws Exception {
        List<Demo> list = new ArrayList<>();
        // 方法1: 如果写到同一个sheet
        setExcelRespProp(response, "demo");
        EasyExcel.write(response.getOutputStream())
                .head(Demo.class)
                .registerWriteHandler(new CustomMergeStrategy(Demo.class))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("demo")
                .doWrite(list);
    }
 private void setExcelRespProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }

实体

@Data
public class Demo{
	

    @ApiModelProperty(value = "订单类型（1：平台订单 2：中台订单）,格式化前端使用")
    @ExcelProperty( value = "订单类型",index = 0)
    @CustomMerge(needMerge = true)
    @ColumnWidth(12)
    private String orderSourceStr;

    @ApiModelProperty(value = "平台订单号")
    @ExcelProperty( value = "订单号",index = 1)
    @CustomMerge(needMerge = true,isPk = true)
    @ColumnWidth(18)
    private String orderNo;
    @ExcelProperty( value = "规格",index = 22)
    @ColumnWidth(26)
    private String specification;
}