友情链接：Spring Data JPA 动态查询 普通查询_tang_sy的博客-CSDN博客

前言   

合并单元格语法： 开始行、结束行、开始列、结束列

对应代码：new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);

合并代码：sxssfSheet.addMergedRegion(cellRangeAddress); 

由此可见：我们只需要知道这样四个参数就行，在工作中，有定制化的合并单元格，已知合并规则，那么在代码中直接写死，还有一种是动态的实现合并单元格。

本文是动态实现合并单元格 。适用于特定场景下合并规则。

1、引入poi依赖

<!--poi-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.0</version>
</dependency>

2、合并单元格两种方案：

1）数据有层级结构：

     比如部门和部门下的员工，一对多，返回数据结构有层级关系

package com.example.demo.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

public class Test03ExcelDemo {

    @SuppressWarnings("unchecked")
    public static HSSFWorkbook warpSingleWorkbook2(String title, List<Map<String, Object>> mapsList, List<String> head) throws Exception {

        String[] str = {"id", "name", "num"};
        String testUsers = "testUsers";
        String[] str2 = {"userId", "userName", "email"};

        if (mapsList == null || mapsList.isEmpty()) {
            throw new NullPointerException("the row list is null");
        }
        // 如果要设置背景色 最好用 XSSFWorkbook
        HSSFWorkbook book = new HSSFWorkbook();
        HSSFSheet sheet = book.createSheet(title);
        sheet.setDefaultColumnWidth(20);
        HSSFCellStyle style = book.createCellStyle();

        // 生成表头
        HSSFRow headRow = sheet.createRow(0);
        for (int i = 0; i < head.size(); i++) {
            HSSFCellStyle headStyle = book.createCellStyle();
            setExcelValue(headRow.createCell(i), head.get(i), headStyle);
        }

        int rowIndex = 1;
        int commonTotalSize = mapsList.get(0).size() - 1;
        List<List<Integer>> mergeParams = new ArrayList<>();

        for (Map<String, Object> map : mapsList) {
            // 记录合并的开始行
            int startRowIndex = rowIndex;
            HSSFRow bodyRow = sheet.createRow(rowIndex++);
            for (int i = 0; i < str.length; i++) {
                setExcelValue(bodyRow.createCell(i), map.get(str[i]), style);
            }
            //组装数据的时候至少又一个，没有数据空串填充一个数据
            List<Map<String, Object>> list = (List<Map<String, Object>>) map.get(testUsers);
            for (int i = 0; i < str2.length; i++) {
                setExcelValue(bodyRow.createCell(str.length + i), null, style);
            }
            for (int i = 1; i < list.size(); i++) {
                HSSFRow bodyRow2 = sheet.createRow(rowIndex++);
                for (int j = 0; j < str2.length; j++) {
                    setExcelValue(bodyRow2.createCell(str.length + j), list.get(i).get(str2[j]), style);
                }
            }
            if (list.size() > 1) {
                // 依次放入  起始行 结束行 起始列 结束列
                for (int i = 0; i < commonTotalSize; i++) {
                    List<Integer> mergeParam = new ArrayList<>(4);
                    mergeParam.add(startRowIndex);
                    mergeParam.add(rowIndex - 1);
                    mergeParam.add(i);
                    mergeParam.add(i);
                    mergeParams.add(mergeParam);
                }
            }
        }

        for (List<Integer> list : mergeParams) {
            sheet.addMergedRegion(new CellRangeAddress(list.get(0), list.get(1), list.get(2), list.get(3)));
        }
        return book;
    }

    /**
     * 设置Excel浮点数可做金额等数据统计
     *
     * @param cell  单元格类
     * @param value 传入的值
     */
    public static void setExcelValue(HSSFCell cell, Object value, HSSFCellStyle style) {
        // 写数据
        if (value == null) {
            cell.setCellValue("");
        } else {
            if (value instanceof Integer || value instanceof Long) {
                cell.setCellValue(Long.parseLong(value.toString()));
            } else if (value instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) value).setScale(1, RoundingMode.HALF_UP).doubleValue());
            } else {
                cell.setCellValue(value.toString());
            }
            cell.setCellStyle(style);
        }
    }

    public static void main(String[] args) {
        FileOutputStream fileOut = null;
        try {
            List<String> head = Arrays.asList("部门ID", "部门", "renshu", "人员ID", "姓名", "邮箱");
            List<Map<String, Object>> depts = getData();
            HSSFWorkbook wb = warpSingleWorkbook2("测试", depts, head);
            File file = new File("/Users/tangshanyuan/test/new2.xls");
            fileOut = new FileOutputStream(file);
            wb.write(fileOut);
            System.out.println("----Excle文件已生成------");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fileOut != null) {
                try {
                    fileOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 模拟查询获取数据
     *
     * @return
     */
    private static List<Map<String, Object>> getData() {
        List<Map<String, Object>> depts = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Map<String, Object> deptMap = new HashMap<>();
            deptMap.put("id", i + "主键");
            if (i > 0) {
                deptMap.put("name", i + "部门");
            } else {
                deptMap.put("name", null);
            }
            deptMap.put("num", i + "");

            List<Map<String, String>> testUserList = new ArrayList<>();
            for (int j = 0; j < new Random().nextInt(10) + 1; j++) {
                Map<String, String> testUser = new HashMap<>();
                testUser.put("userId", j + "");
                testUser.put("userName", j + "姓名");
                testUser.put("email", j + "544416131");
                testUserList.add(testUser);
            }
            deptMap.put("testUsers", testUserList);
            depts.add(deptMap);
        }
        return depts;
    }

}

示例：明细第一行没有值，是因为代码写死null，注意对应取值

2）数据没有层级结构

      返回是一条条数据，但是数据与数据之间不规则，存在相同的则需要合并，比如以商品为度去查询订单

package com.example.demo.excel;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

public class Test02 {

    /**
     * @param field
     * @param title      标题集合 tilte的长度应该与list中的model的属性个数一致
     * @param listList   内容集合
     * @param mergeIndex 合并单元格的列
     */
    @SneakyThrows
    public static String createExcel(String[] field, String[] title, List<List<Map<String, String>>> listList, int[] mergeIndex) {
        long startTime = System.currentTimeMillis();
        if (title.length == 0) {
            return null;
        }
        // 初始化excel模板
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = null;

        int n = 0;
        //循环sheet页 实例化sheet对象并且设置sheet名称，book对象
        try {
            sheet = workbook.createSheet();
            workbook.setSheetName(n, "sheet1");
            workbook.setSelectedTab(0);
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 数据总数
        int size = listList.stream().mapToInt(List::size).sum();
        assert sheet != null;
        Row row0 = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            Cell cell_1 = row0.createCell(i);
            cell_1.setCellValue(title[i]);
        }

        List<PoiModel> poiModels = Lists.newArrayList();
        for (List<Map<String, String>> list : listList) {
            for (Map<String, String> map : list) {
                int index = sheet.getLastRowNum() + 1;
                Row row = sheet.createRow(index);
                for (int i = 0; i < title.length; i++) {
                    String titleField = field[i];
                    String old = null;
                    if (index > 1) {
                        old = poiModels.get(i) == null ? null : poiModels.get(i).getContent();
                    }
                    for (int k : mergeIndex) {
                        if (index == 1) {
                            PoiModel poiModel = PoiModel.builder().oldContent(map.get(titleField)).content(map.get(titleField)).rowIndex(1).cellIndex(i).build();
                            poiModels.add(poiModel);
                            break;
                        }
                        PoiModel poiModel = poiModels.get(i);
                        String content = map.get(titleField);
                        // 当前行的当前列与上一行的当前列的内容不一致时，则把当前行以上的合并
                        if (i > 0 && k == i) {
                            // 如果不需要考虑当前行与上一行内容相同，但是它们的前一列内容不一样则不合并的情况，把或条件删除
                            if (!poiModel.getContent().equals(content) || poiModel.getContent().equals(content) && !poiModels.get(i - 1).getOldContent().equals(map.get(field[i - 1]))) {
                                get(poiModel, content, index, i, sheet);
                            }
                        }
                        // 处理第一列的情况
                        if (k == i && i == 0 && !poiModel.getContent().equals(content)) {
                            get(poiModel, content, index, i, sheet);
                        }
                        // 最后一行没有后续的行与之比较，所有当到最后一行时则直接合并对应列的相同内容
                        if (k == i && index == size && poiModels.get(i).getRowIndex() != index) {
                            CellRangeAddress cra = new CellRangeAddress(poiModels.get(i).getRowIndex(), index, poiModels.get(i).getCellIndex(), poiModels.get(i).getCellIndex());
                            sheet.addMergedRegion(cra);
                        }
                    }
                    Cell cell = row.createCell(i);
                    cell.setCellValue(map.get(titleField));
                    poiModels.get(i).setOldContent(old);
                }
            }
        }

        File file = new File("/Users/tangshanyuan/test/demo.xls");
        FileOutputStream fout = new FileOutputStream(file);
        workbook.write(fout);
        fout.close();

        long endTime = System.currentTimeMillis();    //获取结束时间
        System.out.println("程序运行时间：" + (endTime - startTime) + "ms");    //输出程序运行时间
        return file.getAbsolutePath();
    }

    /**
     * 合并单元格
     *
     * @param poiModel
     * @param content
     * @param index
     * @param i
     * @param sheet
     */
    private static void get(PoiModel poiModel, String content, int index, int i, Sheet sheet) {
        if (poiModel.getRowIndex() != index - 1) {
            CellRangeAddress cra = new CellRangeAddress(poiModel.getRowIndex(), index - 1, poiModel.getCellIndex(), poiModel.getCellIndex());
            //在sheet里增加合并单元格
            sheet.addMergedRegion(cra);
        }
        /*重新记录该列的内容为当前内容，行标记改为当前行标记，列标记则为当前列*/
        poiModel.setContent(content);
        poiModel.setRowIndex(index);
        poiModel.setCellIndex(i);
    }

    public static void main(String[] args) throws IOException {
        // 此处标题的数组则对应excel的标题
        String[] title = {"id", "标题", "描述", "负责人", "开始时间", "名字", "年龄", "性别", "班级"};
        String[] field = {"id", "title", "dec", "manager", "beginTime", "name", "age", "sex", "clazz"};
        List<Map<String, String>> list = Lists.newArrayList();
        // 这边是制造一些数据，注意每个list中map的key要和标题数组中的元素一致
        for (int i = 0; i < 100; i++) {
            HashMap<String, String> map = Maps.newHashMap();
            if (i > 40) {
                if (i < 45) {
                    map.put("id", "333");
                    map.put("title", "美女");
                } else if (i > 50 && i < 55) {
                    map.put("id", "444");
                    map.put("title", "美男");
                } else {
                    map.put("id", "444");
                    map.put("title", "少男");
                }
            } else if (i > 25) {
                map.put("id", "222");
                map.put("title", "少女");
            } else if (i == 5 || i == 8) {
                map.put("id", "222");
                map.put("title", "少年");
            } else {
                map.put("id", "222");
                map.put("title", "青年");
            }
            map.put("dec", "都是有用的人");
            map.put("manager", "管理员");
            map.put("beginTime", "2017-02-27 11:20:26");
            map.put("name", "tsy");
            map.put("age", "28");
            map.put("sex", "男");
            if (i > 80) {
                if (i < 82) {
                    map.put("clazz", "er版");
                } else {
                    map.put("clazz", "");
                }
            } else {
                map.put("clazz", "一版");
            }
            list.add(map);
        }

        Map<String, List<Map<String, String>>> map = Maps.newHashMap();
        map.put("测试合并数据", list);

        // 模拟大数据量情况下，任务中心可分页查询接口，分批返回数据
        List<List<Map<String, String>>> groups = pageByNum(list, 5);
        // 此处数组为需要合并的列，可能有的需求是只需要某些列里面相同内容合并
        System.out.println(createExcel(field, title, groups, new int[]{0, 1, 2, 8}));
    }

    public static <T> List<List<T>> pageByNum(List<T> list, int pageSize) {
        return IntStream.range(0, list.size()).boxed().filter(t -> t % pageSize == 0).map(t -> list.stream().skip(t).limit(pageSize).collect(Collectors.toList())).collect(Collectors.toList());
    }

}


package com.example.demo.excel;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Builder
@NoArgsConstructor
@AllArgsConstructor
@Data
public class PoiModel {
 
    private String content;
 
    private String oldContent;

    private String primaryKey;

    private int rowIndex;
 
    private int cellIndex;

}

3、友情提醒

    1）SXSSFSheet源码：大数据合并导出，可采用addMergedRegionUnsafe，不必要校验，大大提高效率

public int addMergedRegion(CellRangeAddress region) {
    return this._sh.addMergedRegion(region);
}
public int addMergedRegionUnsafe(CellRangeAddress region) {
    return this._sh.addMergedRegionUnsafe(region);
}