【EasyExcel】EasyExcel列表填充数据时指定单元格合并

最新推荐文章于 2024-08-26 17:26:55 发布

粗心女子写代码

最新推荐文章于 2024-08-26 17:26:55 发布

阅读量1.9k

点赞数

分类专栏： EasyExcel 文章标签： java

原文链接：https://blog.csdn.net/Aeve_imp/article/details/104910080

版权

EasyExcel 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

EasyExcel列表填充数据时指定单元格合并

package com.susing.security.handler;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.susing.common.utils.RowRangeDto;
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;
import java.util.Map;

/**
 * easyexcel 拦截器， 拦截设置相同的类别合并单元格
 * @Author 
 * @Date 2021/11/30 22:31
 * @description
 */
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(org.apache.poi.ss.usermodel.Sheet sheet, Cell cell, Head head, Integer integer) {
        this.sheet = sheet;
        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));
                });
            }
        }
    }
}

ExcelUtil 方法

package com.susing.common.utils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author 
 * @Date 2021/11/30 22:57
 * @description
 */
public class ExcelUtil {
    /**
     * 添加合并策略
     */
    public static Map<String, List<RowRangeDto>> addMerStrategy(List<Map<String, Object>>  excelDtoList) {
        Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
        Map<String, Object> preExcelDto = null;
        for (int i = 0; i < excelDtoList.size(); i++) {
            Map<String, Object> currDto = excelDtoList.get(i);
            if (preExcelDto != null) {
                //从第二行开始判断是否需要合并
                if (currDto.get("categoryName").equals(preExcelDto.get("categoryName"))) {
                    //如果类别一样，则可合并单位格为一列
                    fillStrategyMap(strategyMap, "0", i+1);
                }
            }
            preExcelDto = currDto;
        }
        return strategyMap;
    }

    /**
     * 新增或修改合并策略
     **/
    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);
    }
}

package com.susing.common.utils;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/**
 * @Author dfp
 * @Date 2021/11/30 22:57
 * @description
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class RowRangeDto {
    private int start;
    private int end;
}

引用

 Map<String, List<RowRangeDto>> strategyMap = ExcelUtil.addMerStrategy(list);
      WriteSheet writeSheet = EasyExcel.writerSheet("统计表")
              .registerWriteHandler(new BizMergeStrategy(strategyMap)).build();