友情链接：Spring Data JPA 动态查询 普通查询

友情链接：利用POI实现动态复杂多级表头

前言

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

对应代码：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）数据有层级结构：

比如部门和部门下的员工、订单信息和订单明细，典型一对多，返回数据结构有层级关系，目前只考虑两层结构。

表头文案label、表头列对应的属性字段field、列的宽度witdh、列是否有需要脱敏等等可以配置为json到数据库，作为一个job任务，可以适用大部分导出，任务中心就很好的复用导出逻辑。

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");}// 如果要设置背景色 最好用 XSSFWorkbookHSSFWorkbook 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 mon.collect.Lists;import mon.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 合并单元格的列*/@SneakyThrowspublic 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", "-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@Datapublic 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);}

2）合并先判断单元格是否需要合并，可以合并再合并

CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowIndex, rowIndex - 1, i, i);if (cellRangeAddress.getNumberOfCells() > 1) {sxssfSheet.addMergedRegionUnsafe(cellRangeAddress);}

4、后记

1、如果有帮助到你，不胜荣幸。

2、如果有问题，可以直接私信我，互相学习。

3、文中难免会存在错误的地方，希望能提出来，帮助我解决它。