EasyExcel生成多个exel文件并zip压缩

大道至简@EveryDay

已于 2022-11-03 15:19:33 修改

阅读量3.1k

点赞数 3

分类专栏： java工具类文章标签： excel java powerpoint

于 2022-10-21 12:37:07 首次发布

本文链接：https://blog.csdn.net/qq_42511550/article/details/127441989

版权

java工具类专栏收录该内容

17 篇文章 2 订阅

订阅专栏

前言

最近项目上有个关于excel导出的需求，要求如下：
1，表头除了固定的几个，可以动态添加，比如下面表格，范围1，时间，值是固定，其他的表头都是动态的
2，范围1，范围2，范围3是合并列
3，范围2，范围3是联动的
4，自适应宽高，自适应居中，样式设置
5，生成多个excel最后zip压缩

注意：
1，因为产品的需求，合并列范围1，范围2，范围3严格遵守合并的格式，并不是任意合并，所以比较复杂。
2，不支持列与列之间的合并，如果想要支持，可以基于此demo开发是没任何问题的。

在这里插入图片描述

POM

  <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.5.0</version>
        </dependency>


        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>

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


        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.5</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.1</version>
        </dependency>

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.8.0</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.7</version>
        </dependency>

设计

1，数据库获取数据用List<Map<String,Object>>，后面需要转换成List<List>(原因：easyexcel对List不兼容)

代码如下

压缩工具类

public class ZipUtils {

    private static final Charset DEFAULT_CHARSET = Charset.defaultCharset();

    /**
     * 将文件流压缩到目标流中
     *
     * @param out       目标流，压缩完成自动关闭
     * @param fileNames 流数据在压缩文件中的路径或文件名
     * @param ins       要压缩的源，添加完成后自动关闭流
     */
    public static void zip(OutputStream out, List<String> fileNames, List<InputStream> ins) {
        zip(out, fileNames.toArray(new String[0]), ins.toArray(new InputStream[0]));
    }

    /**
     * 将文件流压缩到目标流中
     *
     * @param out       目标流，压缩完成自动关闭
     * @param fileNames 流数据在压缩文件中的路径或文件名
     * @param ins       要压缩的源，添加完成后自动关闭流
     */
    public static void zip(File out, List<String> fileNames, List<InputStream> ins) throws IOException {
        FileOutputStream outputStream = new FileOutputStream(out);
        zip(outputStream, fileNames.toArray(new String[0]), ins.toArray(new InputStream[0]));
        outputStream.flush();
    }

    /**
     * 将文件流压缩到目标流中
     *
     * @param out       目标流，压缩完成自动关闭
     * @param fileNames 流数据在压缩文件中的路径或文件名
     * @param ins       要压缩的源，添加完成后自动关闭流
     */
    public static void zip(OutputStream out, String[] fileNames, InputStream[] ins) {
        ZipOutputStream zipOutputStream = null;
        try {
            zipOutputStream = getZipOutputStream(out, DEFAULT_CHARSET);
            zip(zipOutputStream, fileNames, ins);
        } catch (IOException e) {
//            throw new Exception("压缩包导出失败！", e);
        } finally {
            IOUtils.closeQuietly(zipOutputStream);
        }
    }

    /**
     * 将文件流压缩到目标流中
     *
     * @param zipOutputStream 目标流，压缩完成不关闭
     * @param fileNames       流数据在压缩文件中的路径或文件名
     * @param ins             要压缩的源，添加完成后自动关闭流
     * @throws IOException IO异常
     */
    public static void zip(ZipOutputStream zipOutputStream, String[] fileNames, InputStream[] ins) throws IOException {
        if (ArrayUtils.isEmpty(fileNames) || ArrayUtils.isEmpty(ins)) {
            throw new IllegalArgumentException("文件名不能为空！");
        }
        if (fileNames.length != ins.length) {
            throw new IllegalArgumentException("文件名长度与输入流长度不一致！");
        }
        for (int i = 0; i < fileNames.length; i++) {
            add(ins[i], fileNames[i], zipOutputStream);
        }
    }

    /**
     * 添加文件流到压缩包，添加后关闭流
     *
     * @param in       需要压缩的输入流，使用完后自动关闭
     * @param fileName 压缩的路径
     * @param out      压缩文件存储对象
     * @throws IOException IO异常
     */
    private static void add(InputStream in, String fileName, ZipOutputStream out) throws IOException {
        if (null == in) {
            return;
        }
        try {
            out.putNextEntry(new ZipEntry(fileName));
            IOUtils.copy(in, out);
        } catch (IOException e) {
            throw new IOException(e);
        } finally {
            IOUtils.closeQuietly(in);
            closeEntry(out);
        }
    }

    /**
     * 获得 {@link ZipOutputStream}
     *
     * @param out     压缩文件流
     * @param charset 编码
     * @return {@link ZipOutputStream}
     */
    private static ZipOutputStream getZipOutputStream(OutputStream out, Charset charset) {
        if (out instanceof ZipOutputStream) {
            return (ZipOutputStream) out;
        }
        return new ZipOutputStream(out,  DEFAULT_CHARSET);
    }

    /**
     * 关闭当前Entry，继续下一个Entry
     *
     * @param out ZipOutputStream
     */
    private static void closeEntry(ZipOutputStream out) {
        try {
            out.closeEntry();
        } catch (IOException e) {
            // ignore
        }
    }


}

自适应宽度

mport com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

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

public class CustomCellWriteWeightStrategy extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 254) {
                    columnWidth = 254;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    /**
     * 计算长度
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<WriteCellData<?>>  cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        // 换行符（数据需要提前解析好）
                        int index = cellData.getStringValue().indexOf("\n");
                        return index != -1 ?
                                cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

自适应高度

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.util.Iterator;

public class CustomCellWriteHeightStrategy extends AbstractRowHeightStyleStrategy {
    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 300;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }

        // 默认为 1行高度
        Integer maxHeight = 1;
        while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
            switch (cell.getCellType()) {
                case STRING:
                    if (cell.getStringCellValue().contains("\n")) {
                        int length = cell.getStringCellValue().split("\n").length;
                        maxHeight = Math.max(maxHeight, length);
                    }
                    break;
                default:
                    break;
            }
        }

        row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));

    }
}

自适应居中及字体设置

    //设置样式 去除默认表头样式及设置内容居中
    public static HorizontalCellStyleStrategy getStyleStrategy(){
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //头策略使用默认
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

合并策略

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.yueyang.util.MergeParam;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
import java.util.Map;

public class BizMergeStrategy  extends AbstractMergeStrategy {

    private Map<String, List<MergeParam>> strategyMap;
    private Sheet sheet;

    public BizMergeStrategy(Map<String, List<MergeParam>> strategyMap) {
        this.strategyMap = strategyMap;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        this.sheet = sheet;
        if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
            /**
             * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
             * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
             * 但此时A2,A3已经是合并的单元格了
             */
            for (Map.Entry<String, List<MergeParam>> entry : strategyMap.entrySet()) {
                Integer columnIndex = Integer.valueOf(entry.getKey());
                entry.getValue().forEach(rowRange -> {
                    //添加一个合并请求
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
                            rowRange.getEnd(), columnIndex, columnIndex));
                });
            }
        }
    }
}

对象类

数据库查询的映射类

import lombok.Data;

@Data
public class XxglFieldDefine {

    private String fieldName;

    private String fieldKey;
}

合并基础类

import lombok.Data;

@Data
public class MergeParam {

    private int start;
    private int end;
}

合并列枚举

import lombok.Getter;

@Getter
public enum  MergeColumnEnum {
    /**
     * 通知消息模板
     */
    MERGE_COLUMN1("column1", "范围1"),
    MERGE_COLUMN2("column2", "范围2"),
    MERGE_COLUMN3("column3", "范围3"),
    ;
    private final String mergeColumn;
    private final String desc;

    MergeColumnEnum(String code, String title) {
        this.mergeColumn = code;
        this.desc = title;
    }
}

主要代码

 @Resource
    private DataDbMapper mapper;

    @GetMapping("/test")
    @ApiOperation("/test")
    public List<Map<String, Object>> test() throws Exception {
        List<Map<String, Object>> dataMap = mapper.getMap();

        Map<String, List<MergeParam>> mergeMap = getMergeMap(dataMap);



        List<InputStream> ins = new ArrayList<>();
        // 压缩包内流的文件名
        List<String> paths = new ArrayList<>();
        for (int i = 0; i < 2; i++) {
            // 使用 easyExcel 写到 OutputStream
            String moduleName="moudle_"+System.currentTimeMillis();
            ins.add(writeExcel6(mergeMap, dataMap,moduleName));
            paths.add(moduleName+".xlsx");
        }
        // 保存的 zip 文件名
        File zipFile = new File("E:\\excel\\noModelWrite.zip");


        ZipUtils.zip(zipFile, paths, ins);

        return dataMap;
    }

    /**
     *  自适应合并居中，接list数据 对象固定
     *  1,长度，宽度无变化
     *
     *  数据为  map
     *  head为可变类型
     * @param map
     * @param map1
     */
    public static InputStream writeExcel6(Map<String, List<MergeParam>> map, List<Map<String, Object>> map1,String module) {
        // 使用 easyExcel 写到 OutputStream
        OutputStream out = new ByteArrayOutputStream();
        //这里自定义一个单元格的格式(标黄的行高亮显示)
        ExcelWriter excelWriter = EasyExcel.write(out).excelType(ExcelTypeEnum.XLSX).build();


        List<List<Object>> dataList = new ArrayList<List<Object>>();


        List<XxglFieldDefine> fList = getXxglFieldDefines();

        String[] arry = new String[fList.size()];
        for(int i=0; i<fList.size();i++){
            arry[i] = fList.get(i).getFieldKey();
        }
        //设置导出的数据内容
        for (Map<String, Object> m : map1) {
            List<Object> data = new ArrayList<Object>();
            for (int i = 0; i < arry.length; i++) {
                data.add(m.get(arry[i]));
            }
            dataList.add(data);
        }

        // 写sheet的时候注册相应的自定义合并单元格策略
        WriteSheet writeSheet = EasyExcel.writerSheet(module).head(createHeadByFields(head1()))
                .registerWriteHandler(new BizMergeStrategy( map))
                .registerWriteHandler(new CustomCellWriteWeightStrategy())
                .registerWriteHandler(new CustomCellWriteHeightStrategy())
                .registerWriteHandler(getStyleStrategy())
                .build();
        excelWriter.write(dataList, writeSheet);
        excelWriter.finish();
        return outputStream2InputStream(out);
    }

    /**
     * 输出流转输入流；数据量过大请使用其他方法
     *
     * @param out
     * @return
     */
    private static ByteArrayInputStream outputStream2InputStream(OutputStream out) {
        Objects.requireNonNull(out);
        ByteArrayOutputStream bos;
        bos = (ByteArrayOutputStream) out;
        return new ByteArrayInputStream(bos.toByteArray());
    }


    /**
     * 动态头 列设置
     * @return
     */
    private static  List<String> head1() {
        List<String> head0 = new ArrayList<String>();
        head0.add("范围1" );
        head0.add("范围2" );
        head0.add("范围3" );
        head0.add("时间" );
        head0.add("值" );
        head0.add("排序" );
        head0.add("主键id" );

        return head0;
    }
    //设置样式 去除默认表头样式及设置内容居中
    public static HorizontalCellStyleStrategy getStyleStrategy(){
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //垂直居中,水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //头策略使用默认
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }



    private static List<List<String>> createHeadByFields(List<String> headList) {
        return headList.stream().map(Collections::singletonList).collect(Collectors.toList());
    }

    /**
     * 查询的数据对应的key,记住 顺序和设置动态头的顺序一定要一致
     * @return
     */
    private static List<XxglFieldDefine> getXxglFieldDefines() {
        List<XxglFieldDefine> fList  = new ArrayList<XxglFieldDefine>();
        XxglFieldDefine x = new XxglFieldDefine();
        x.setFieldName("范围1");
        x.setFieldKey("column1");
        fList.add(x);

        XxglFieldDefine x1 = new XxglFieldDefine();
        x1.setFieldName("范围2");
        x1.setFieldKey("column2");
        fList.add(x1);

        XxglFieldDefine x2 = new XxglFieldDefine();
        x2.setFieldName("范围3");
        x2.setFieldKey("column3");
        fList.add(x2);
        XxglFieldDefine x3 = new XxglFieldDefine();
        x3.setFieldName("date");
        x3.setFieldKey("date");
        fList.add(x3);

        XxglFieldDefine x4 = new XxglFieldDefine();
        x4.setFieldName("value");
        x4.setFieldKey("value");
        fList.add(x4);

        XxglFieldDefine x5 = new XxglFieldDefine();
        x5.setFieldName("sort");
        x5.setFieldKey("sort");
        fList.add(x5);

        XxglFieldDefine x6 = new XxglFieldDefine();
        x6.setFieldName("id");
        x6.setFieldKey("id");
        fList.add(x6);
        return fList;
    }

    /**
     * 获取范围1 范围2 范围3 合并数据
     * @param dataMap
     * @return
     */
    private Map<String, List<MergeParam>> getMergeMap(List<Map<String, Object>> dataMap) {
        Map<String, List<MergeParam>> map = new HashMap<>();
        //确定合并的字段有几个

        Map<Object, Integer> column1Map = new LinkedHashMap<>();
        Map<Object, Integer> column2Map = new LinkedHashMap<>();
        Map<Object, Integer> column3Map = new LinkedHashMap<>();
        List<MergeParam> column1List = new ArrayList<>();
        List<MergeParam> column2List = new ArrayList<>();
        List<MergeParam> column3List = new ArrayList<>();
        Boolean firstBool = true;
        int start = 0;
        for (Map<String, Object> stringObjectMap : dataMap) {
            start++;
            if (stringObjectMap.containsKey(MergeColumnEnum.MERGE_COLUMN1.getMergeColumn())) {
                Object column1Obj = stringObjectMap.get(MergeColumnEnum.MERGE_COLUMN1.getMergeColumn());
                if (column1Map.containsKey(column1Obj)) {
                    //说明已经存进去了
                    column1Map.put(column1Obj, column1Map.get(column1Obj) + 1);
                    if (stringObjectMap.containsKey(MergeColumnEnum.MERGE_COLUMN2.getMergeColumn())) {
                        Object column2Obj = stringObjectMap.get(MergeColumnEnum.MERGE_COLUMN2.getMergeColumn());
                        if (column2Map.containsKey(column2Obj)) {
                            //说明已经存进去了
                            column2Map.put(column2Obj, column2Map.get(column2Obj) + 1);
                            if (stringObjectMap.containsKey(MergeColumnEnum.MERGE_COLUMN3.getMergeColumn())) {
                                Object o = stringObjectMap.get(MergeColumnEnum.MERGE_COLUMN3.getMergeColumn());
                                if (column3Map.containsKey(o)) {
                                    //说明已经存进去了
                                    column3Map.put(o, column3Map.get(o) + 1);
                                } else {
                                    column3Map.put(o, 1);
                                }
                            }
                        } else {
                            //说明合并字段等级2的已经结束，需要处理生成合并字段等级3的合并数据
                            column2Map.put(column2Obj, 1);
                            //开始处理
                            column3List.addAll(getMergeParamList(column3Map, start));
                            column3Map.clear();
                            //初始化合并字段等级3
                            if (stringObjectMap.containsKey(MergeColumnEnum.MERGE_COLUMN3.getMergeColumn())) {
                                Object column3Obj = stringObjectMap.get(MergeColumnEnum.MERGE_COLUMN3.getMergeColumn());
                                column3Map.put(column3Obj, 1);
                            }
                        }
                    }

                } else {
                    if (firstBool) {
                        //说明第一次
                        firstBool = false;
                    } else {
                        //说明某一个合并字段等级结束 也是新的合并字段等级开始
                        //开始处理数据
                        //1，处理合并column1Map 的数据
                        column1List.addAll(getMergeParamList(column1Map, start));
                        //2，处理合并column1Map 的数据
                        column2List.addAll(getMergeParamList(column2Map, start));
                        //3，处理合并column1Map 的数据
                        column3List.addAll(getMergeParamList(column3Map, start));
                        //4，清空所有的Map
                        column1Map.clear();
                        column2Map.clear();
                        column3Map.clear();
                    }
                    //初始化map
                    column1Map.put(column1Obj, 1);
                    if (stringObjectMap.containsKey(MergeColumnEnum.MERGE_COLUMN2.getMergeColumn())) {
                        Object column2Obj = stringObjectMap.get(MergeColumnEnum.MERGE_COLUMN2.getMergeColumn());
                        column2Map.put(column2Obj, 1);
                    }
                    if (stringObjectMap.containsKey(MergeColumnEnum.MERGE_COLUMN3.getMergeColumn())) {
                        Object column3Obj = stringObjectMap.get(MergeColumnEnum.MERGE_COLUMN3.getMergeColumn());
                        column3Map.put(column3Obj, 1);
                    }
                }
            }
        }
        //1，处理合并column1Map 的数据
        column1List.addAll(getMergeParamList(column1Map, start + 1));
        //2，处理合并column1Map 的数据
        column2List.addAll(getMergeParamList(column2Map, start + 1));
        //3，处理合并column1Map 的数据
        column3List.addAll(getMergeParamList(column3Map, start + 1));

        map.put("0", column1List);
        map.put("1", column2List);
        map.put("2", column3List);

        return map;

    }

    private List<MergeParam> getMergeParamList(Map<Object, Integer> columnMap, int end) {
        List<MergeParam> mergeParamList = new ArrayList<>();
        if (MapUtil.isEmpty(columnMap)) {
            return mergeParamList;
        }
        int sum = 0;
        Set<Map.Entry<Object, Integer>> en = columnMap.entrySet();
        for (Map.Entry<Object, Integer> entry : en) {
            sum += entry.getValue();
        }
        int start = end - sum;
        for (Map.Entry<Object, Integer> entry : en) {
            if (entry.getValue() > 1) {
                MergeParam mergeParam = new MergeParam();
                mergeParam.setStart(start);
                mergeParam.setEnd(start + entry.getValue() - 1);
                start = start + entry.getValue();
                mergeParamList.add(mergeParam);
            } else {
                start++;
            }
        }
        return mergeParamList;
    }

测试数据

用的pg数据库
Mapper

public interface DataDbMapper {

     List<Map<String,Object>> getMap();
}

xml

<mapper namespace="com.yueyang.demo1.mapper.DataDbMapper">

    <select id="getMap" resultType="java.util.Map">
       select *
        from t_module_test
        order by sort asc
    </select>
</mapper>

数据

CREATE TABLE "public"."t_module_test" (
  "id" serial4,
  "column1" varchar(50) ,
  "column2" varchar(50) ,
  "column3" varchar(50) ,
  "date" varchar(50) ,
  "value" varchar(50) ,
  "sort" int8

)
;
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (1, '范围1', '范围1-1', '范围1-2', '今天', '12.11', 1);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (3, '范围1', '范围1-1', '范围1-2-1', '今天', '12.11', 3);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (4, '范围1', '范围1-1', '范围1-2-1', '今天', '12.11', 4);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (5, '范围1', '范围1-2', '范围1-2-2', '今天', '12.11', 5);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (6, '范围1', '范围1-2', '范围1-2-2', '今天', '12.11', 6);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (7, '范围1', '范围1-2', '范围1-2-2', '今天', '12.11', 7);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (8, '范围1', '范围1-2', '范围1-2-2', '今天', '12.11', 8);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (25, '范围2', '范围2-1', '范围2-2', '今天', '12.11', 9);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (26, '范围2', '范围2-1', '范围2-2-1', '今天', '12.11', 10);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (27, '范围2', '范围2-1', '范围2-2-1', '今天', '12.11', 11);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (28, '范围2', '范围2-1', '范围2-2-1', '今天', '12.11', 12);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (29, '范围2', '范围2-2', '范围2-2-2', '今天', '12.11', 13);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (30, '范围2', '范围2-2', '范围2-2-2', '今天', '12.11', 14);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (31, '范围2', '范围2-2', '范围2-2-2', '今天', '12.11', 15);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (32, '范围2', '范围2-2', '范围2-2-2', '今天', '12.11', 16);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (33, '范围3', '范围3-1', '范围3-2', '今天', '12.11', 17);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (34, '范围3', '范围3-1', '范围3-2-1', '今天', '12.11', 18);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (35, '范围3', '范围3-1', '范围3-2-1', '今天', '12.11', 19);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (36, '范围3', '范围3-1', '范围3-2-1', '今天', '12.11', 20);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (37, '范围3', '范围3-2', '范围3-2-2', '今天', '12.11', 21);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (38, '范围3', '范围3-2', '范围3-2-2', '今天', '12.11', 22);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (39, '范围3', '范围3-2', '范围3-2-2', '今天', '12.11', 23);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (40, '范围3', '范围3-2', '范围3-2-2', '今天', '12.11', 24);
INSERT INTO "public"."t_module_test"("id", "column1", "column2", "column3", "date", "value", "sort") VALUES (2, '范围1', '范围1-1', '范围1-2', '今天', '12.11', 2);