easyexcel合并单元格

引用

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

代码

import lombok.Data;

import java.util.Date;

@Data
public class Info {

    private String ango;

    private String name;

    private Date time;

    private Integer age;


    private Boolean deal;
}

import com.alibaba.excel.write.handler.RowWriteHandler;

public interface MergeCell extends RowWriteHandler {

    void finish();
}

import com.alibaba.excel.write.handler.WorkbookWriteHandler;
import com.alibaba.excel.write.handler.context.WorkbookWriteHandlerContext;
import lombok.extern.slf4j.Slf4j;

import java.util.Arrays;
import java.util.Collection;

@Slf4j
public class WorkbookHandlerExtend implements WorkbookWriteHandler {
    private Collection<MergeCell> mergeCellList;

    public WorkbookHandlerExtend(Collection<MergeCell> mergeCellList) {
        this.mergeCellList = mergeCellList;
    }

    public WorkbookHandlerExtend(MergeCell... mergeCellLists) {
        this.mergeCellList = Arrays.asList(mergeCellLists);
    }

    @Override
    public void afterWorkbookDispose(WorkbookWriteHandlerContext context) {
        if(mergeCellList!=null &&mergeCellList.size()!=0) {
            log.info("请开始你的表演");
            mergeCellList.forEach(MergeCell::finish);
            log.info("噢 这啊！ ");
        }
    }
}

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.LocaleUtil;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Slf4j
public class RowHandlerExtend implements MergeCell {

    private Boolean isRun =false;

    private Boolean init = true;

    private Map<String,Integer> indexMap=new HashMap<>(8);

    private Map<String,List> mergeMap=new HashMap<>(8);

    private RowWriteHandlerContext rowWriteHandlerContext;

    //   条件字段   合并字段
    private Map<String,List<String>> mergeFileMap=new HashMap<>(8);

    public RowHandlerExtend(Map<String, String> mergeFileMap) {
        if (mergeFileMap!=null){
            this.mergeFileMap = mergeFileMap.entrySet().stream().collect(Collectors.toMap(n->n.getKey(),n->
                    {
                        if(n.getValue() == null){
                            log.info("合并列不能为空");
                            throw new Exception("合并列不能为空")
                        }
                        return Stream.of(n.getValue().split(",")).collect(Collectors.toList());
                    }
            ));
            if(mergeFileMap!=null && mergeFileMap.size()!=0){
                isRun=true;
                List<String> list = this.mergeFileMap.values().stream().flatMap(n -> n.stream()).collect(Collectors.toList());
                List<String> list1 = list.stream().distinct().collect(Collectors.toList());
                if(list.size() != list1.size()){
                    log.info("同一列只允许操作一次");
                  throw new Exception("同一列只允许操作一次")
                }
            }
        }
    }

    @Override
    public void afterRowDispose(RowWriteHandlerContext context) {
        if(!context.getHead() & isRun & mergeFileMap.size() > 0){
            rowWriteHandlerContext=context;
            init();
            startMerge();
        }
    }

    private void init() {
        if(init){
            List<String> fieldNames = Lists.newArrayList(rowWriteHandlerContext.getWriteSheetHolder().includeColumnFieldNames().iterator());
            Boolean orderByIncludeColumn = rowWriteHandlerContext.getWriteSheetHolder().getOrderByIncludeColumn();
            if(fieldNames == null | !orderByIncludeColumn){
                Map<Integer, Head> headMap = rowWriteHandlerContext.getWriteSheetHolder().getExcelWriteHeadProperty().getHeadMap();
                if(headMap == null || headMap.size()==0){
                    fieldNames=new ArrayList<>();
                }else {
                    fieldNames = headMap.values().stream().map(n->n.getFieldName()).collect(Collectors.toList());
                }
            }
            List<String> finalFieldNames = fieldNames;
            mergeFileMap = mergeFileMap.entrySet().stream().collect(Collectors.toMap(n -> n.getKey()
                            , n -> n.getValue().stream().filter(y ->contains(finalFieldNames,y)).collect(Collectors.toList())))
                    .entrySet().stream().filter(n ->n.getValue().size() > 0 && contains(finalFieldNames,n.getKey()))
                    .collect(Collectors.toMap(n -> n.getKey(), n -> n.getValue()));
            init=false;
        }
    }

    private Boolean contains(List<String> finalFieldNames,String key){
        int i = finalFieldNames.indexOf(key);
        if(i!=-1){
            indexMap.put(key,i);
            return true;
        }else {
            return false;
        }
    }


    private void startMerge(){
        Integer rowIndex = rowWriteHandlerContext.getRowIndex();
        //值求出来
        for (String key : mergeFileMap.keySet()) {
            List list = mergeMap.get(key);
            String dateCellValue = toCellString(rowWriteHandlerContext.getRow().getCell(indexMap.get(key)));
            if(list == null || list.size()==0){
                List mergeList=new ArrayList();
                mergeList.add(rowIndex);
                mergeList.add(dateCellValue);
                mergeMap.put(key,mergeList);
            }else {
                if(!list.get(1).toString().equals(dateCellValue)) {
                    Integer startRow = Integer.valueOf(list.get(0).toString());
                    if(rowIndex - startRow > 0){
                        merge(mergeFileMap.get(key),startRow,rowIndex-1);
                    };
                    list.clear();
                    list.add(rowIndex);
                    list.add(dateCellValue);
                }
            }
        }


    }


    @Override
    public void finish() {
        if(isRun & mergeMap.size() > 0){
            Integer rowIndex = rowWriteHandlerContext.getRowIndex();
            mergeMap.forEach((k,v)->{
                Integer startRow = Integer.valueOf(v.get(0).toString());
                if(rowIndex-startRow > 0){
                    merge(mergeFileMap.get(k),startRow,rowIndex);
                }
            });
        }
    }

    public String toCellString(Cell cell) {
        switch(cell.getCellType()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss", LocaleUtil.getUserLocale());
                    sdf.setTimeZone(LocaleUtil.getUserTimeZone());
                    return sdf.format(cell.getDateCellValue());
                }

                return cell.getNumericCellValue() + "";
            case STRING:
                return cell.getRichStringCellValue().toString();
            case BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case ERROR:
                return ErrorEval.getText(cell.getErrorCellValue());
            case _NONE:
            default:
                return "Unknown Cell Type: " + cell.getCellType();
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
        }
    }


    private void merge(List<String> mergeList, int startRow,int endRow) {
        long l = System.currentTimeMillis();

        for (String s : mergeList) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, endRow,
                    indexMap.get(s), indexMap.get(s));
            rowWriteHandlerContext.getWriteSheetHolder().getSheet()
                    .addMergedRegionUnsafe(cellRangeAddress);
        }
        long l1 = System.currentTimeMillis();
        log.info("耗时："+(l1-l)+" ms");
    }
}

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CacheLocationEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Slf4j
public class TestDome {


    public static void main(String[] args) {
        File file=new File("C:\\Users\\你的电脑用户名\\Desktop\\合并单元格.xlsx");


        List<Info> list=new ArrayList<>();
        int t=0;
        Date date = new Date();
        for (int i = 0; i < 10000; i++) {
            if(i%10==0){
                t++;
            }
            Info info=new Info();
            info.setAge(i);
            info.setAngo("ango-"+i);
            info.setDeal(i%2==1?true:false);
            info.setTime(date);
            info.setName("名称："+t);
            list.add(info);
        }
        long l = System.currentTimeMillis();
        List<List<String>> headList=new ArrayList<>();
        for (int i = 1; i < 5; i++) {
            headList.add(Stream.of("列头-"+i,"二列-"+i).collect(Collectors.toList()));
        }

        List<String> strings=Stream.of("name","age","deal","ango","time").collect(Collectors.toList());
        Map<String,String> map=new HashMap<>();
        map.put("name","name,age,deal,ango");
        RowHandlerExtend writeHandler = new RowHandlerExtend(map);

        ExcelWriter excelWriter = EasyExcel.write(file,Info.class)
                .head(headList)
                .includeColumnFieldNames(strings)
                .orderByIncludeColumn(true)
                .filedCacheLocation(CacheLocationEnum.MEMORY)
                .registerWriteHandler(new WorkbookHandlerExtend(writeHandler))
                .registerWriteHandler(writeHandler)
                .build();

        WriteSheet writeSheet = EasyExcel.writerSheet("模板")

                .build();
        excelWriter.write(list, writeSheet);
        excelWriter.finish();
        long l1 = System.currentTimeMillis();
        log.info("-----------+ "+ (l1-l) +" ms+-------------");
    }

}