前言
我前面也写过几篇关于easypoi复杂表格导出的文章，什么一对多纵向合并、多级表头、动态表头、多个sheet等，这些我写那几篇文章之前做项目都遇到过，并且都实现出来了。
感兴趣的可以看看：
easypoi多级表头、多个sheet导出，动态导出列、动态更改表头
easypoi一对多，纵向合并单元格，并且根据内容自适应行高
我就以为大部分表格导出都难不倒我了，没想到我碰到的表格还是不够多。
多级表头中，不知道大家有没有碰到过这种表头，如下图：
三级下面有n个四级，一级二级下面分别只有一个二级和三级。亦或是这种：
我一开始寻思这种表头用easypoi的 ExcelExportEntity 实现挺简单的呀，后面我用代码写了才发现这结果完全不是我想的样子啊😂上面那两张图是想要的理想效果，实际上的结果是这样的：
和这样的：
我直接： 
这种情况就是合并单元格只能作用于上一级，而不能跨级。比如对二级来说，四级是它子级的子级，但是它并不能因为子级（三级）有多个子级，所以子级（三级）合并了，它本身也会合并。 用easypoi来导出的话，关于这个问题，我现在没有找到解决方案。
还有一点就是我明明每列都设置了列宽，但是四级3、四级4的列宽没起作用，四级1、四级2设置的列宽倒是起作用了。
发现用 ExcelExportEntity 行不通后，有想过使用模板，但是我项目中实际用的表头比较复杂，我用模板完全不知道要怎么把表头遍历出来。。。项目里用的大概是这种形式的表头：
我不知道在模板里遍历每一项的时候，怎么再遍历每一项里面的list。就是遍历一级时，怎么再把每个一级下面的二级list给遍历出来。我看了easypoi的官方文档，发现有个 模板支持多层循环 的实现，但是偏偏就这一点，它文档上的图片没了。。。
然后我开始找其他解决办法，看过很多关于easypoi复杂表头的文章，都没有找到我这种形式的表格。直到我看某篇文章的评论区时，终于看到个有遇到和我一样问题的人了，并且贴出了参考方案，我按照这个方法，终于做出来我想要的表头了！！解决方案我就是参考的这篇文章：JAVA POI 实现EXCEL 动态表头、动态添加数据（导入导出）、 Tree结构的遍历
解决
一、表头效果
我按照上面那篇文章，终于得到我想要的表头了，效果如图：
设置的列宽也有作用。
再看看用easypoi ExcelExportEntity 导出来的：
我设置的列宽也不起作用。
二、填充数据
表头可以了，接下来就是填充内容数据了，效果如下：
这里就有个问题了，因为所在单位这一列有连续多行是相同内容，所以我想要合并相同内容。
1、纵向合并
于是我在原先的代码基础上，加了纵向合并功能，需要设置哪几列是遇到相同内容要合并的。
private void writeSheetContent(List<ColEntity> headerCellList, List<T> datas, HSSFSheet sheet, int rowIndex,List<Integer> mergeIndex, boolean rowFlag) throws Exception { boolean isMerge = false;// 是否纵向合并单元格 if (mergeIndex != null && !mergeIndex.isEmpty()) isMerge = true; HSSFRow row = null; List<ColEntity> listCol = new ArrayList<>(); rowFlag = false; if (rowFlag) {//暂时没有用 后面扩展用 for (int i = 0, index = rowIndex; i < datas.size(); i++, index++) { row = sheet.createRow(index);//创建行 for (int j = 0; j < headerCellList.size(); j++) { createColl(row, j, headerCellList.get(j).getFieldName(), datas.get(i)); } } } else { getColEntityList(headerCellList, listCol); Map<Integer, Map<Integer, String>> mergeMaps = new HashMap<>();// 需要合并的列：key 列号，value为单元格内容 Map<Integer, String> mergeMap = null;// 需要合并的行：key 行号 value 为单元格内容 for (int i = 0, index = rowIndex; i < datas.size(); i++, index++) { row = sheet.createRow(index);//创建行 for (int j = 0; j < listCol.size(); j++) { ColEntity c = listCol.get(j); //数据列 HSSFCell col = createCol(row, c, datas.get(i)); if (col.toString().length()>0){ // 需要合并 并且 当前单元格所在的列包含在要合并的列中 if (isMerge && mergeIndex.contains(c.getCol())){ if (mergeMaps.get(c.getCol()) != null){ // 如果要合并的列已经有了，则直接去拿该列的数据mergeMap = mergeMaps.get(c.getCol()); }else {mergeMap = new HashMap<>(); } // 当前行号为key，当前单元格内容为value mergeMap.put(index,col.toString()); // 将当前单元格的内容添加到当前行号中 mergeMaps.put(c.getCol(),mergeMap); } } } } if (isMerge) mergedCells(mergeMaps,sheet); }}private void mergedCells(Map<Integer, Map<Integer, String>> mergeMaps,HSSFSheet sheet){ for (Integer colNum : mergeMaps.keySet()) { // 遍历要合并的列，获取每一列的每一行 Map<Integer, String> mergeMap = mergeMaps.get(colNum);// 当前这列每一行的内容：key为行号，value为单元格内容 // 根据mergeMap的value，也就是单元格内容进行分组，每一组都是需要合并在一起的单元格（要合并的区域） Map<String, List<Map.Entry<Integer,String>>>result = mergeMap.entrySet().stream().collect(Collectors.groupingBy(c -> c.getValue())); System.out.println("\n合并的列号："+colNum); System.out.println("合并的区域："+result); for (String key : result.keySet()) { // list为这一组要合并的几个单元格 List<Map.Entry<Integer, String>> list = result.get(key); int start = list.get(0).getKey(); // 开始合并的行号 int end = list.get(list.size()-1).getKey(); // 结束合并的行号 System.out.println("第"+colNum+"列开始合并的行号："+start+"\t第"+colNum+"列结束合并的行号："+"\t"+end+"。"); if (start < end){ // 开始合并的行号必须小于结束合并的行号 sheet.addMergedRegion(new CellRangeAddress(start, end, colNum,colNum)); } } }}写好纵向合并的方法后，我们再来看看导出效果：（行号索引从0开始）
2、多个sheet
实现了纵向合并，项目中又根本不止一个sheet，所以我们还得实现导出多个sheet。
我在原先代码调用导出方法的基础上，再对这个方法传入的参数进行重新封装。原先是表头数据和内容数据都用的list，要实现多个sheet的话，我再用map把每个sheet的表头数据list、内容数据list给封装起来，用sheet名称为key。
public HSSFWorkbook exportWorkbook(List<ColEntity> listTpamsColEntity, List<T> datas) throws Exception { splitDataToSheets(this.title,datas, listTpamsColEntity,null, false); ExcelStyleUtil.setStyleByType(this.workbook,null,0,null); return this.workbook;}public HSSFWorkbook exportWorkbook(Map<String,List<ColEntity>> titles, Map<String,List<T>> datas) throws Exception { for (String sheetName : titles.keySet()) { List<ColEntity> colEntityList = titles.get(sheetName); List<T> tList = datas.get(sheetName); splitDataToSheets(sheetName,tList, colEntityList,null,false); } ExcelStyleUtil.setStyleByType(this.workbook,null,0,null); return this.workbook;}最后导出如图：
三、完整代码
<dependencies> <dependency> <groupId>javax.servletgroupId> <artifactId>servlet-apiartifactId> <version>2.5version> dependency> <dependency> <groupId>cn.afterturngroupId> <artifactId>easypoi-baseartifactId> <version>4.4.0version> dependency>dependencies>1、表头实体类
用于封装表头内容。可以将数据库查出来的动态表头数据用这个实体类封装起来，也可以直接用数据库表映射的实体类
public class TitleEntity { public String id; public String pid; public String content; public String fieldName; public int width; private TitleEntity(){} public TitleEntity(String id, String pid, String content, String fieldName, int width) { this.id = id; this.pid = pid; this.content = content; this.fieldName = fieldName; this.width = width; } // ......省略getter、setter}2、单元格实体类
import java.util.ArrayList;import java.util.List;public class ColEntity { private String content; private String fieldName; private List<ColEntity> cellList = new ArrayList<ColEntity>(); private int totalRow; private int totalCol; private int row; private int col; private int rLen; private int cLen; private boolean hasChildren; private int treeStep; private String id; private String pid; private int width; // ......省略getter、setter}3、构建树形结构工具类
import com.entity.ColEntity;import java.util.ArrayList;import java.util.Collections;import java.util.Iterator;import java.util.List;public class ExcelTreeUtil { public static int getTreeStep(List<ColEntity> list, String id, int step) { if ("".equals(id) || null == id) return step; for (ColEntity cc : list) { if (id.equals(cc.getId())) { int temp = step + 1; return getTreeStep(list, cc.getPid(), temp); } } return step; } public static int getMaxStep(List<ColEntity> list) { List<Integer> nums = new ArrayList<>(); for (ColEntity cc : list) { nums.add(getTreeStep(list, cc.getId(), 0)); } return Collections.max(nums); } public static int getDownChildren(List<ColEntity> list, String did) { int sum = 0; for (ColEntity cc : list) { if (did.equals(cc.getPid())) { sum++; //判断该节点 是否有子节点 if (hasChild(list, cc)) { sum += getDownChildren(list, cc.getId()) - 1; } } } return sum; } public static ColEntity getParentCol(List<ColEntity> list, String did) { for (ColEntity cc : list) { if (did != null && did.equals(cc.getId())) { return cc; } if (did == null && did == cc.getId()) { return cc; } } return new ColEntity() {{ setCol(0); setRow(0); }}; } public static int getBrotherChilNum(List<ColEntity> list, ColEntity ColEntity) { int sum = 0; for (ColEntity cc : list) { if (ColEntity.getId().equals(cc.getId())) { break; } if (!ColEntity.getPid().equals(cc.getPid())) { continue; } int temp = getDownChildren(list, cc.getId()); if (temp == 0 || temp == 1) { sum++; } else { sum += temp; } } return sum; } public static String getStepParentId(List<ColEntity> list, String id, int step) { String f_id = null; for (ColEntity cc : list) { if (id.equals(cc.getId())) { int cstep = getTreeStep(list, cc.getId(), 0); if (step == cstep) { return id; } int fstep = getTreeStep(list, cc.getPid(), 0); if (step == fstep) { f_id = cc.getPid(); break; } else { getStepParentId(list, cc.getPid(), step); } } } return f_id; } public static boolean hasChild(List<ColEntity> list, ColEntity node) { return getChildList(list, node).size() > 0; } public static List<ColEntity> getChildList(List<ColEntity> list, ColEntity node) { List<ColEntity> nodeList = new ArrayList<>(); Iterator<ColEntity> it = list.iterator(); while (it.hasNext()) { ColEntity n = (ColEntity) it.next(); if (n.getPid() != null && n.getPid().equals(node.getId())) { nodeList.add(n); } } return nodeList; } public static List<ColEntity> buildByRecursive(List<ColEntity> treeNodes, String rootID) { List<ColEntity> trees = new ArrayList<>(); boolean flag = false; boolean sflag = false; for (ColEntity treeNode : treeNodes) { if ((rootID == null && rootID == treeNode.getId())) { flag = true; } if (rootID != null && rootID.equals(treeNode.getId())) { flag = true; } if (flag) { trees.add(findChildren(treeNode, treeNodes)); flag = false; } } if (trees.size() <= 0) { for (ColEntity treeNode : treeNodes) { if ((rootID == null && rootID == treeNode.getPid())) { sflag = true; } if (rootID != null && rootID.equals(treeNode.getPid())) { sflag = true; } if (sflag) { trees.add(findChildren(treeNode, treeNodes)); sflag = false; } } } return trees; } public static ColEntity findChildren(ColEntity treeNode, List<ColEntity> treeNodes) { for (ColEntity it : treeNodes) { if (treeNode.getId().equals(it.getPid())) { if (treeNode.getCellList() == null) { treeNode.setCellList(new ArrayList<>()); } treeNode.getCellList().add(findChildren(it, treeNodes)); } } return treeNode; }}4、excel导出样式工具类
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;import org.apache.poi.ss.usermodel.*;import java.util.List;public class ExcelStyleUtil implements IExcelExportStyler { private ExcelStyleUtil(){} private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT"); private static final short FONT_SIZE_TEN = 10; private static final short FONT_SIZE_ELEVEN = 11; private static final short FONT_SIZE_TWELVE = 12; private static final short height = 30; private CellStyle headerStyle; private CellStyle titleStyle; private CellStyle styles; public ExcelStyleUtil(Workbook workbook) { this.init(workbook); } private void init(Workbook workbook) { this.headerStyle = initHeaderStyle(workbook); this.titleStyle = initTitleStyle(workbook,true,FONT_SIZE_ELEVEN); this.styles = initStyles(workbook); } @Override public CellStyle getHeaderStyle(short i) { return headerStyle; } @Override public CellStyle getTitleStyle(short i) { return titleStyle; } @Override public CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) { return null; } @Override public CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) { return styles; } @Override public CellStyle getStyles(Cell cell, int i, ExcelExportEntity entity, Object o, Object o1) { return getStyles(true, entity); } public static CellStyle getStyles(Workbook workbook,int style) { CellStyle cellStyle = null; switch (style){ case 1: cellStyle = initHeaderStyle(workbook); break; case 2: cellStyle = initTitleStyle(workbook,true,FONT_SIZE_ELEVEN); break; case 3: cellStyle = initStyles(workbook); break; default: cellStyle = initStyles(workbook); break; } cellStyle.setDataFormat(STRING_FORMAT); return cellStyle; } private static CellStyle initHeaderStyle(Workbook workbook) { CellStyle style = getBaseCellStyle(workbook); style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true)); return style; } private static CellStyle initTitleStyle(Workbook workbook,boolean isBold,short size) { CellStyle style = getBaseCellStyle(workbook); style.setFont(getFont(workbook, size, isBold)); //背景色 style.setFillForegroundColor(IndexedColors.TAN.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); return style; } private static CellStyle initStyles(Workbook workbook) { CellStyle style = getBaseCellStyle(workbook); style.setFont(getFont(workbook, FONT_SIZE_TEN, false)); style.setDataFormat(STRING_FORMAT); return style; } public static CellStyle getRowBackground(Workbook workbook) { CellStyle style = getBaseCellStyle(workbook); style.setFont(getFont(workbook, FONT_SIZE_TEN,false)); //背景色 style.setFillForegroundColor(IndexedColors.LAVENDER.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setDataFormat(STRING_FORMAT); return style; } private static CellStyle getBaseCellStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); //下边框 style.setBorderBottom(BorderStyle.THIN); //左边框 style.setBorderLeft(BorderStyle.THIN); //上边框 style.setBorderTop(BorderStyle.THIN); //右边框 style.setBorderRight(BorderStyle.THIN); //水平居中 style.setAlignment(HorizontalAlignment.CENTER); //上下居中 style.setVerticalAlignment(VerticalAlignment.CENTER); //设置自动换行 style.setWrapText(true); return style; } private static Font getFont(Workbook workbook, short size, boolean isBold) { Font font = workbook.createFont(); //字体样式 font.setFontName("宋体"); //是否加粗 font.setBold(isBold); //字体大小 font.setFontHeightInPoints(size); return font; } public static void setStyleByType(Workbook workbook,String sheetName,int type,List<Integer> autoRowHeight){ if (sheetName != null && sheetName.length()>0){ Sheet sheet = workbook.getSheet(sheetName); setSheetStyleByType(workbook,sheet,type,autoRowHeight); }else { int sheetNum = workbook.getNumberOfSheets(); for (int i = 0; i < sheetNum; i++) { Sheet sheet = workbook.getSheetAt(i); setSheetStyleByType(workbook,sheet,type,autoRowHeight); } } } public static void setSheetStyleByType(Workbook workbook,Sheet sheet,int type,List<Integer> autoRowHeight){ if (type==1 || type == 3){ // 1和3，设置隔行背景 setRowBackground(workbook,sheet); } int lastRowNum = sheet.getLastRowNum(); for(int j = 0; j <= lastRowNum; j++) { Row row = sheet.getRow(j); row.setHeightInPoints(height); // 默认行高 if (type==2 || type == 3){ // 2和3，设置自适应行高 if (autoRowHeight != null && autoRowHeight.contains(j)){ // 不为空，则只有指定行号的行需要自适应行高 autoRowHeight(row); }else { // 为null，则表示全部行都需要自适应行高 autoRowHeight(row); } } } } public static void setRowBackground(Workbook workbook,Sheet sheet){ CellStyle styles = getRowBackground(workbook); for(int i = 0; i <= sheet.getLastRowNum(); i ++) { if (i%2==0 && i>0){ // 标题用全局的标题样式，就不单独设置样式了，所以排除标题 Row row = sheet.getRow(i); for(int j = 0; j < row.getPhysicalNumberOfCells(); j ++) { Cell cell = row.getCell(j); cell.setCellStyle(styles); } } } } public static void autoRowHeight(Row row){ //根据内容长度设置行高 int enterCnt = 0; for(int j = 0; j < row.getPhysicalNumberOfCells(); j ++) { Cell cell = row.getCell(j); if (cell != null){ int rwsTemp = row.getCell(j).toString().length(); //这里取每一行中的每一列字符长度最大的那一列的字符 if (rwsTemp > enterCnt) { enterCnt = rwsTemp; } } } row.setHeightInPoints(height); // 设置默认行高为35 //如果字符长度大于35，根据内容来设置相应的行高 if (enterCnt>height){ long d = Math.round((double) enterCnt / (double) height)+2; row.setHeightInPoints(enterCnt*d); } }}5、excel poi导出处理工具类
import com.entity.ColEntity;import org.apache.poi.hssf.usermodel.*;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.ss.util.RegionUtil;import java.io.File;import java.io.FileOutputStream;import java.lang.reflect.Field;import java.text.SimpleDateFormat;import java.util.*;import java.util.stream.Collectors;public class ExcelPoiUtil<T> { private HSSFWorkbook workbook; private String title; private CellStyle styleHead; private CellStyle styleBody; private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); public HSSFWorkbook getWorkbook() { return workbook; } public void setWorkbook(HSSFWorkbook workbook) { this.workbook = workbook; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public CellStyle getStyleHead() { return styleHead; } public void setStyleHead(CellStyle styleHead) { this.styleHead = styleHead; } public CellStyle getStyleBody() { return styleBody; } public void setStyleBody(CellStyle styleBody) { this.styleBody = styleBody; } public SimpleDateFormat getSdf() { return sdf; } public void setSdf(SimpleDateFormat sdf) { this.sdf = sdf; } public ExcelPoiUtil() { this.title = "sheet1"; this.workbook = new HSSFWorkbook(); init(); } public ExcelPoiUtil(String title) { this.title = title; this.workbook = new HSSFWorkbook(); init(); } private void init() { this.styleHead = ExcelStyleUtil.getStyles(workbook,2); this.styleBody = ExcelStyleUtil.getStyles(workbook,3); } public HSSFWorkbook exportWorkbook(List<ColEntity> listTpamsColEntity, List<T> datas) throws Exception { splitDataToSheets(this.title,datas, listTpamsColEntity,null, false); ExcelStyleUtil.setStyleByType(this.workbook,null,0,null); return this.workbook; } public HSSFWorkbook exportWorkbook(List<ColEntity> listTpamsColEntity, List<T> datas,List<Integer> mergeIndex) throws Exception { splitDataToSheets(this.title,datas, listTpamsColEntity, mergeIndex,false); ExcelStyleUtil.setStyleByType(this.workbook,null,0,null); return this.workbook; } public HSSFWorkbook exportWorkbook(List<ColEntity> listTpamsColEntity, List<T> datas,int type,List<Integer> autoRowHeight) throws Exception { splitDataToSheets(this.title,datas, listTpamsColEntity,null, false); ExcelStyleUtil.setStyleByType(this.workbook,null,type,autoRowHeight); return this.workbook; } public HSSFWorkbook exportWorkbook(List<ColEntity> listTpamsColEntity, List<T> datas,int type,List<Integer> autoRowHeight,List<Integer> mergeIndex) throws Exception { splitDataToSheets(this.title,datas, listTpamsColEntity,mergeIndex, false); ExcelStyleUtil.setStyleByType(this.workbook,null,type,autoRowHeight); return this.workbook; } public HSSFWorkbook exportWorkbook(Map<String,List<ColEntity>> titles, Map<String,List<T>> datas,int type,List<Integer> autoRowHeight,List<Integer> mergeIndex) throws Exception { for (String sheetName : titles.keySet()) { List<ColEntity> colEntityList = titles.get(sheetName); List<T> tList = datas.get(sheetName); splitDataToSheets(sheetName,tList, colEntityList,mergeIndex,false); } ExcelStyleUtil.setStyleByType(this.workbook,null,type,autoRowHeight); return this.workbook; } public HSSFWorkbook exportWorkbook(Map<String,List<ColEntity>> titles, Map<String,List<T>> datas,Map<String,Integer> types, Map<String,List<Integer>> autoRowHeights,Map<String,List<Integer>> mergeIndexs) throws Exception { for (String sheetName : titles.keySet()) { List<ColEntity> colEntityList = titles.get(sheetName); List<T> tList = datas.get(sheetName); int type = types == null ? 0 : types.get(sheetName) == null ? 0 : types.get(sheetName); List<Integer> autoRowHeight = autoRowHeights == null ? null : autoRowHeights.get(sheetName); List<Integer> mergeIndex = mergeIndexs == null ? null : mergeIndexs.get(sheetName); splitDataToSheets(sheetName,tList, colEntityList,mergeIndex,false); ExcelStyleUtil.setStyleByType(this.workbook,sheetName,type,autoRowHeight); } return this.workbook; } public void save(HSSFWorkbook workbook, String filePath) { File file = new File(filePath); if (!file.getParentFile().exists()) { file.getParentFile().mkdirs(); } FileOutputStream fOut = null; try { fOut = new FileOutputStream(file); workbook.write(fOut); fOut.flush(); } catch (Exception e) { e.printStackTrace(); } try { if (null != fOut) { fOut.close(); } } catch (Exception e1) { } } private void writeSheet(HSSFSheet sheet, List<T> data, List<ColEntity> headerCellList,List<Integer> mergeIndex,boolean rowFlag) throws Exception { sheet = createHead(sheet, headerCellList.get(0).getTotalRow(), headerCellList.get(0).getTotalCol()); createHead(headerCellList, sheet, 0); writeSheetContent(headerCellList, data, sheet, headerCellList.get(0).getTotalRow(),mergeIndex, rowFlag); } private void splitDataToSheets(String sheetName,List<T> data, List<ColEntity> headerCellList,List<Integer> mergeIndex,boolean rowFlag) throws Exception { int dataCount = data.size(); int maxColEntity = 65535; int pieces = dataCount / maxColEntity; for (int i = 1; i <= pieces; i++) { HSSFSheet sheet = this.workbook.createSheet(sheetName + i); List<T> subList = data.subList((i - 1) * maxColEntity, i * maxColEntity); writeSheet(sheet, subList, headerCellList,mergeIndex,rowFlag); } HSSFSheet sheet = this.workbook.createSheet(sheetName); writeSheet(sheet, data.subList(pieces * maxColEntity, dataCount), headerCellList,mergeIndex,rowFlag); } private void writeSheetContent(List<ColEntity> headerCellList, List<T> datas, HSSFSheet sheet, int rowIndex,List<Integer> mergeIndex, boolean rowFlag) throws Exception { boolean isMerge = false;// 是否纵向合并单元格 if (mergeIndex != null && !mergeIndex.isEmpty()) isMerge = true; HSSFRow row = null; List<ColEntity> listCol = new ArrayList<>(); rowFlag = false; if (rowFlag) {//暂时没有用 后面扩展用 for (int i = 0, index = rowIndex; i < datas.size(); i++, index++) { row = sheet.createRow(index);//创建行 for (int j = 0; j < headerCellList.size(); j++) { createColl(row, j, headerCellList.get(j).getFieldName(), datas.get(i)); } } } else { getColEntityList(headerCellList, listCol); Map<Integer, Map<Integer, String>> mergeMaps = new HashMap<>();// 需要合并的列：key 列号，value为单元格内容 Map<Integer, String> mergeMap = null;// 需要合并的行：key 行号 value 为单元格内容 for (int i = 0, index = rowIndex; i < datas.size(); i++, index++) { row = sheet.createRow(index);//创建行 for (int j = 0; j < listCol.size(); j++) { ColEntity c = listCol.get(j); //数据列 HSSFCell col = createCol(row, c, datas.get(i)); if (col.toString().length()>0){ // 需要合并 并且 当前单元格所在的列包含在要合并的列中 if (isMerge && mergeIndex.contains(c.getCol())){if (mergeMaps.get(c.getCol()) != null){ // 如果要合并的列已经有了，则直接去拿该列的数据 mergeMap = mergeMaps.get(c.getCol());}else { mergeMap = new HashMap<>();}// 当前行号为key，当前单元格内容为valuemergeMap.put(index,col.toString()); // 将当前单元格的内容添加到当前行号中mergeMaps.put(c.getCol(),mergeMap); } } } } if (isMerge) mergedCells(mergeMaps,sheet); } } private void mergedCells(Map<Integer, Map<Integer, String>> mergeMaps,HSSFSheet sheet){ for (Integer colNum : mergeMaps.keySet()) { // 遍历要合并的列，获取每一列的每一行 Map<Integer, String> mergeMap = mergeMaps.get(colNum);// 当前这列每一行的内容：key为行号，value为单元格内容 // 根据mergeMap的value，也就是单元格内容进行分组，每一组都是需要合并在一起的单元格（要合并的区域） Map<String, List<Map.Entry<Integer,String>>>result = mergeMap.entrySet().stream().collect(Collectors.groupingBy(c -> c.getValue())); System.out.println("\n合并的列号："+colNum); System.out.println("合并的区域："+result); for (String key : result.keySet()) { // list为这一组要合并的几个单元格 List<Map.Entry<Integer, String>> list = result.get(key); int start = list.get(0).getKey(); // 开始合并的行号 int end = list.get(list.size()-1).getKey(); // 结束合并的行号 System.out.println("第"+colNum+"列开始合并的行号："+start+"\t第"+colNum+"列结束合并的行号："+"\t"+end+"。"); if (start < end){ // 开始合并的行号必须小于结束合并的行号 sheet.addMergedRegion(new CellRangeAddress(start, end, colNum,colNum)); } } } } private void createColl(HSSFRow row, int j, String finame, T t) { HSSFCell cell = row.createCell(j); //创建单元格 cell.setCellStyle(this.styleBody); //设置单元格样式 String text = ""; if (t instanceof List) { List<Map> temp = (List<Map>) t; if (j >= temp.size()) { return; } text = String.valueOf(temp.get(j).get(finame) == null ? "" : temp.get(j).get(finame)); } HSSFRichTextString richString = new HSSFRichTextString(text); cell.setCellValue(richString); } private void getColEntityList(List<ColEntity> list, List<ColEntity> listCol) { for (ColEntity ColEntity : list) { if (ColEntity.getFieldName() != null) { listCol.add(ColEntity); } List<ColEntity> listChildren = ColEntity.getCellList(); if (listChildren.size() > 0) { getColEntityList(listChildren, listCol); } } } public int createRowVal(HSSFRow row, ColEntity tpamsColEntity, T v, int j) throws Exception { //遍历标题 if (tpamsColEntity.getCellList() != null && tpamsColEntity.getCellList().size() > 0) { for (int i = 0; i < tpamsColEntity.getCellList().size(); i++) { createRowVal(row, tpamsColEntity.getCellList().get(i), v, j); } } else { createCol(row, tpamsColEntity, v); } return j; } public HSSFCell createCol(HSSFRow row, ColEntity colEntity, T v) throws Exception { HSSFCell cell = row.createCell(colEntity.getCol()); //创建单元格 cell.setCellStyle(this.styleBody); //设置单元格样式 final Object[] value = {null}; if (v instanceof Map) { Map m = (Map) v; m.forEach((k, val) -> { if (k.equals(colEntity.getFieldName()) && !colEntity.isHasChildren()) { value[0] = val; } }); } else { Class<?> cls = v.getClass();// 拿到该类 Field[] fields = cls.getDeclaredFields();// 获取实体类的所有属性，返回Field数组 for (int i = 0; i < fields.length; i++) { Field f = fields[i]; f.setAccessible(true); // 设置些属性是可以访问的 if (colEntity.getFieldName().equals(f.getName()) && !colEntity.isHasChildren()){ value[0] = f.get(v); } if (value[0] instanceof Date) { value[0] = parseDate((Date) value[0]); } } } if (value[0] != null) { HSSFRichTextString richString = new HSSFRichTextString(value[0].toString()); cell.setCellValue(richString); } return cell; } private String parseDate(Date date) { String dateStr = ""; try { dateStr = this.sdf.format(date); } catch (Exception e) { e.printStackTrace(); } return dateStr; } public HSSFSheet createHead(HSSFSheet sheetCo, int r, int c) { for (int i = 0; i < r; i++) { HSSFRow row = sheetCo.createRow(i); for (int j = 0; j < c; j++) { HSSFCell cell = row.createCell(j); } } return sheetCo; } public void createHead(List<ColEntity> cellList, HSSFSheet sheetCo, int rowIndex) { HSSFRow row = sheetCo.getRow(rowIndex); int len = cellList.size();//当前行 有多少列 for (int i = 0; i < len; i++) {//i是headers的索引，n是Excel的索引 多级表头 ColEntity colEntity = cellList.get(i); //创建这一行的第几列单元格 int r = colEntity.getRow(); int rLen = colEntity.getRLen(); int c = colEntity.getCol(); int cLen = colEntity.getCLen(); int endR = r + rLen; //解决表头导出时多一行问题 if(endR > r){ endR--; } int endC = c + cLen; if (endC > c) { endC--; } HSSFCell cell = row.getCell(c); HSSFRichTextString text = new HSSFRichTextString(colEntity.getContent()); cell.setCellStyle(this.styleHead); //设置表头样式 cell.setCellValue(text); // 合并单元格 CellRangeAddress cra = new CellRangeAddress(r, endR, c, endC); //todo debug if (cra.getNumberOfCells() > 1) { sheetCo.addMergedRegion(cra); } sheetCo.setColumnWidth(c,colEntity.getWidth()*256);// 设置列宽 // 使用RegionUtil类为合并后的单元格添加边框 RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheetCo); // 下边框 RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheetCo); // 左边框 RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheetCo); // 有边框 if (colEntity.isHasChildren()) { rowIndex = r + 1; createHead(colEntity.getCellList(), sheetCo, rowIndex); } } } public List<ColEntity> colEntityTransformer(List<T> list) { List<ColEntity> lc = new ArrayList<>(); if (list.get(0) instanceof Map) { final int[] i = {1}; for (Map<String, String> m : (List<Map<String, String>>) list) { m.forEach((k, val) -> { ColEntity tpamsColEntity = new ColEntity(); tpamsColEntity.setId(String.valueOf(i[0])); tpamsColEntity.setPid("0"); tpamsColEntity.setContent(k); tpamsColEntity.setFieldName(val); tpamsColEntity.setWidth(20); lc.add(tpamsColEntity); i[0]++; }); } } else { int i = 1; for (String s : (List<String>) list) { ColEntity tpamsColEntity = new ColEntity(); tpamsColEntity.setId(String.valueOf(i)); tpamsColEntity.setPid("0"); tpamsColEntity.setContent(s); tpamsColEntity.setFieldName(null); tpamsColEntity.setWidth(20); lc.add(tpamsColEntity); i++; } } setParm(lc, "0");//处理一下 List<ColEntity> s = ExcelTreeUtil.buildByRecursive(lc, "0"); setColNum(lc, s); return s; } public List<ColEntity> colEntityTransformer(List<T> list, Map<String,String> parm,String rootid) throws Exception { List<ColEntity> lc = new ArrayList<>(); if (list.get(0) instanceof Map) { for (Map m : (List<Map>) list) { ColEntity colEntity = new ColEntity(); m.forEach((k, val) -> { if (parm.get("id").equals(k)) { colEntity.setId(String.valueOf(val)); } if (parm.get("pid").equals(k)) { colEntity.setPid((String) val); } if (parm.get("content").equals(k)) { colEntity.setContent((String) val); } if (parm.get("fieldName") != null && parm.get("fieldName").equals(k)) { colEntity.setFieldName((String) val); } if (parm.get("width") != null && parm.get("width").equals(k)) { colEntity.setWidth(Integer.parseInt(val.toString())); } }); lc.add(colEntity); } } else { for (T t : list) { // 反射 ColEntity colEntity = new ColEntity(); Class cls = t.getClass(); Field[] fs = cls.getDeclaredFields(); for (int i = 0; i < fs.length; i++) { Field f = fs[i]; f.setAccessible(true); // 设置些属性是可以访问的 if (parm.get("id").equals(f.getName()) && f.get(t) != null) { colEntity.setId(f.get(t).toString()); } if (parm.get("pid").equals(f.getName()) && f.get(t) != null) { colEntity.setPid(f.get(t).toString()); } if (parm.get("content").equals(f.getName()) && f.get(t) != null) { colEntity.setContent(f.get(t).toString()); } if (f.get(t) != null && parm.get("fieldName") != null && parm.get("fieldName").equals(f.getName())) { colEntity.setFieldName(f.get(t).toString()); } if (parm.get("width").equals(f.getName()) && f.get(t) != null) { colEntity.setWidth(Integer.parseInt(f.get(t).toString())); } } lc.add(colEntity); } } setParm(lc, rootid); // 处理基础参数 List<ColEntity> s = ExcelTreeUtil.buildByRecursive(lc, rootid); // 构建树结构 setColNum(lc, s); return s; } public static void setParm(List<ColEntity> list, String rootid) { int row = 0; //excel第几行 int rLen = 0; //excel 跨多少行 int totalRow = ExcelTreeUtil.getMaxStep(list); int totalCol = ExcelTreeUtil.getDownChildren(list, rootid); for (int i = 0; i < list.size(); i++) { ColEntity poit = list.get(i); int tree_step = ExcelTreeUtil.getTreeStep(list, poit.getPid(), 0);//往上遍历tree poit.setTreeStep(tree_step); poit.setRow(tree_step);//设置第几行 //判断是否有节点 boolean hasCh = ExcelTreeUtil.hasChild(list, poit); poit.setHasChildren(hasCh); if (hasCh) { poit.setRLen(0);//设置跨多少行 } else { if (tree_step < totalRow) { rLen = totalRow - tree_step; } poit.setRLen(rLen); } poit.setTotalRow(totalRow); poit.setTotalCol(totalCol); } } public static void setColNum(List<ColEntity> list, List<ColEntity> treeList) { //int col = pcIndex;//excel第几列 //int cLen ;//xcel跨多少列 List<ColEntity> new_list = new ArrayList<>();//新的遍历list for (int i = 0; i < treeList.size(); i++) { ColEntity poit = treeList.get(i); //String temp_id = ExcelTreeUtil.getStepParentId(list,poit.getId() ,1); int col = ExcelTreeUtil.getParentCol(list, poit.getPid()).getCol(); int brotherCol = ExcelTreeUtil.getBrotherChilNum(list, poit); poit.setCol(col + brotherCol); int cLen = ExcelTreeUtil.getDownChildren(list, poit.getId()); if (cLen <= 1) { cLen = 0; } //else cLen--; poit.setCLen(cLen);//设置跨多少列 if (poit.getCellList().size() > 0) { new_list.addAll(poit.getCellList()); } } if (new_list.size() > 0) { setColNum(list, new_list); } }}6、测试
import com.entity.ColEntity;import com.entity.TitleEntity;import com.google.common.collect.ImmutableMap;import com.util.ExcelPoiUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import java.util.*;public class TestMain { static Map<String,List<ColEntity>> heads = new HashMap<>();// 最终导出的多个sheet的表头 static Map<String,List<Map<String, String>>> datas = new HashMap<>();// 最终导出的多个sheet的内容 static Map<String,Integer> types = new HashMap<>();// 最终导出的每个sheet的样式类型 static Map<String,List<Integer>> autoRowHeights = new HashMap<>();// 最终导出的每个sheet的需要自适应行高的行号 static Map<String,List<Integer>> mergeIndexs = new HashMap<>();// 最终导出的每个sheet的需要纵向合并的单元格列号 public static void main(String[] args) throws Exception { 单级表头(); 多级表头Map(); 多级表头Obj(); 多级表头Obj1(); 多级表头Obj2(); 纵向合并单元格(); // 多个sheet导出 ExcelPoiUtil excelTool = new ExcelPoiUtil(); //HSSFWorkbook workbook = excelTool.exportWorkbook(heads, datas, 0, null, null); // 这里多个sheet都用的同一个样式 HSSFWorkbook workbook = excelTool.exportWorkbook(heads, datas, types, null, mergeIndexs); excelTool.save(workbook,"C:\\Users\\Administrator\\Desktop\\多个sheet.xlsx"); } public static void 单级表头() throws Exception { //单级的表头============================================================== Map<String, String> map = new HashMap<String, String>(); map.put("登录名", "u_login_id"); Map<String, String> map1 = new HashMap<String, String>(); map1.put("用户名", "u_name"); Map<String, String> map2 = new HashMap<String, String>(); map2.put("角色", "u_role"); Map<String, String> map3 = new HashMap<String, String>(); map3.put("部门", "u_dep"); Map<String, String> map4 = new HashMap<String, String>(); map4.put("用户类型", "u_type"); List<Map<String, String>> titleList = new ArrayList<>(); titleList.add(map); titleList.add(map1); titleList.add(map2); titleList.add(map3); titleList.add(map4); //单级的 行内数据 List<Map<String, String>> rowList = new ArrayList<>(); for (int i = 0; i < 7; i++) { Map<String, String> m = new HashMap<String, String>(); m.put("u_login_id", "登录名" + i); m.put("u_name", "张三" + i); m.put("u_role", "角色" + i); m.put("u_dep", "部门" + i); m.put("u_type", "用户类型" + i); rowList.add(m); } ExcelPoiUtil excelTool = new ExcelPoiUtil("单级表头的表格"); List<ColEntity> titleData = excelTool.colEntityTransformer(titleList); HSSFWorkbook workbook = excelTool.exportWorkbook(titleData, rowList,1,null); excelTool.save(workbook,"C:\\Users\\Administrator\\Desktop\\单级表头.xlsx"); } public static void 多级表头Map() throws Exception { List<Map<String,String>> titleList=new ArrayList<>(); Map<String,String> titleMap=new HashMap<String,String>(); titleMap.put("id","11");titleMap.put("pid","0");titleMap.put("content","登录名");titleMap.put("fieldName","u_login_id");titleMap.put("width","20"); Map<String,String> titleMap1=new HashMap<String,String>(); titleMap1.put("id","1");titleMap1.put("pid","0");titleMap1.put("content","姓名");titleMap1.put("fieldName","u_name");titleMap1.put("width","20"); Map<String,String> titleMap2=new HashMap<String,String>(); titleMap2.put("id","2");titleMap2.put("pid","0");titleMap2.put("content","角色加部门");titleMap2.put("fieldName",null);titleMap2.put("width","20"); Map<String,String> titleMap3=new HashMap<String,String>(); titleMap3.put("id","3");titleMap3.put("pid","2");titleMap3.put("content","角色");titleMap3.put("fieldName","u_role");titleMap3.put("width","15"); Map<String,String> titleMap4=new HashMap<String,String>(); titleMap4.put("id","4");titleMap4.put("pid","2");titleMap4.put("content","部门");titleMap4.put("fieldName","u_dep");titleMap4.put("width","15"); Map<String,String> titleMap5=new HashMap<String,String>(); titleMap5.put("id","22");titleMap5.put("pid","0");titleMap5.put("content","角色加部门1");titleMap5.put("fieldName",null);titleMap5.put("width","20"); Map<String,String> titleMap6=new HashMap<String,String>(); titleMap6.put("id","22_1");titleMap6.put("pid","22");titleMap6.put("content","角色1");titleMap6.put("fieldName","u_role");titleMap6.put("width","10"); Map<String,String> titleMap7=new HashMap<String,String>(); titleMap7.put("id","22_2");titleMap7.put("pid","22");titleMap7.put("content","部门1");titleMap7.put("fieldName","u_dep");titleMap7.put("width","10"); titleList.add(titleMap); titleList.add(titleMap1); titleList.add(titleMap2); titleList.add(titleMap3); titleList.add(titleMap4); titleList.add(titleMap5); titleList.add(titleMap6); titleList.add(titleMap7); // 单级的 行内数据 List<Map<String, String>> rowList = new ArrayList<>(); for (int i = 0; i < 7; i++) { Map<String, String> m = new HashMap<String, String>(); m.put("u_login_id", "登录名zx" + i); m.put("u_name", "张三" + i); m.put("u_role", "角色" + i); m.put("u_dep", "部门" + i); m.put("u_type", "用户类型" + i); rowList.add(m); } ExcelPoiUtil excelTool = new ExcelPoiUtil("List); Map<String,String> param = ImmutableMap.<String, String>builder().put("id", "id").put("pid", "pid") .put("content", "content").put("fieldName", "fieldName").put("width", "width").build(); List<ColEntity> titleData = excelTool.colEntityTransformer(titleList,param, "0"); //HSSFWorkbook workbook = excelTool.exportWorkbook(titleData, rowList); //excelTool.save(workbook,"C:\\Users\\Administrator\\Desktop\\多级表头Map.xlsx"); heads.put("List,titleData);// 每个sheet的表头，sheet名称为key datas.put("List,rowList);// 每个sheet的内容，sheet名称为key types.put("List,0);// 每个sheet的样式类型，sheet名称为key } public static void 多级表头Obj() throws Exception { List<TitleEntity> titleList = new ArrayList<>(); titleList.add(new TitleEntity("0", null, "总表", null,20)); titleList.add(new TitleEntity("11", "0", "登录名2", "u_login_id",15)); titleList.add(new TitleEntity("1", "0", "姓名", "u_name",15)); titleList.add(new TitleEntity("2", "0", "角色加部门", null,20)); titleList.add(new TitleEntity("3", "2", "角色", "u_role",20)); titleList.add(new TitleEntity("4", "2", "部门", "u_dep",20)); titleList.add(new TitleEntity("33", "0", "角色加部门1", null,15)); titleList.add(new TitleEntity("33_1", "33", "角色33", "u_role",15)); titleList.add(new TitleEntity("33_2", "33_1", "部门33", "u_dep",15)); titleList.add(new TitleEntity("44", "0", "角色加部门2", null,10)); titleList.add(new TitleEntity("44_1", "44", "角色44", "u_role",10)); titleList.add(new TitleEntity("44_2", "44", "部门44", "u_dep",10)); titleList.add(new TitleEntity("1_1", "1", "姓名1", "u_name",15)); titleList.add(new TitleEntity("44_3", "44_2", "44_2", "u_dep",10)); //单级的 行内数据 List<Map<String, String>> rowList = new ArrayList<>(); for (int i = 0; i < 7; i++) { Map<String, String> m = new HashMap<String, String>(); m.put("u_login_id", "登录名" + i); m.put("u_name", "张三" + i); m.put("u_role", "角色" + i); m.put("u_dep", "部门" + i); m.put("u_type", "用户类型" + i); rowList.add(m); } ExcelPoiUtil excelTool = new ExcelPoiUtil("实体类（entity）多级表头表格"); Map<String,String> param = ImmutableMap.<String, String>builder().put("id", "id").put("pid", "pid") .put("content", "content").put("fieldName", "fieldName").put("width", "width").build(); List<ColEntity> titleData = excelTool.colEntityTransformer(titleList, param, "0"); //HSSFWorkbook workbook = excelTool.exportWorkbook(titleData, rowList); //excelTool.save(workbook,"C:\\Users\\Administrator\\Desktop\\多级表头Obj.xlsx"); heads.put("实体类（entity）多级表头表格",titleData);// 每个sheet的表头，sheet名称为key datas.put("实体类（entity）多级表头表格",rowList);// 每个sheet的内容，sheet名称为key types.put("实体类（entity）多级表头表格",0);// 每个sheet的样式类型，sheet名称为key } public static void 多级表头Obj1() throws Exception { List<TitleEntity> titleList = new ArrayList<>(); titleList.add(new TitleEntity("title", null, "这里是title", null,20)); titleList.add(new TitleEntity("一级1", "title", "一级1", null,20)); titleList.add(new TitleEntity("一级2", "title", "一级2", null,20)); titleList.add(new TitleEntity("二级1", "一级1", "二级1", null,20)); titleList.add(new TitleEntity("二级2", "一级2", "二级2", null,20)); titleList.add(new TitleEntity("三级1", "二级1", "三级1", null,20)); titleList.add(new TitleEntity("三级2", "二级2", "三级2", null,20)); titleList.add(new TitleEntity("四级1", "三级1", "四级1", "fieldName1",20)); titleList.add(new TitleEntity("四级2", "三级1", "四级2", "fieldName2",20)); titleList.add(new TitleEntity("四级3", "三级2", "四级3", "fieldName3",20)); titleList.add(new TitleEntity("四级4", "三级2", "四级4", "fieldName4",20)); //单级的 行内数据 List<Map<String, String>> rowList = new ArrayList<>(); for (int i = 0; i < 5; i++) { Map<String, String> m = new HashMap<String, String>(); m.put("fieldName1", "四级1_" + i); m.put("fieldName2", "四级2_" + i); m.put("fieldName3", "四级3_" + i); m.put("fieldName4", "四级4_" + i); rowList.add(m); } ExcelPoiUtil excelTool = new ExcelPoiUtil("实体类（entity）多级表头表格"); Map<String,String> param = ImmutableMap.<String, String>builder().put("id", "id").put("pid", "pid") .put("content", "content").put("fieldName", "fieldName").put("width", "width").build(); List<ColEntity> titleData = excelTool.colEntityTransformer(titleList, param, "title"); //HSSFWorkbook workbook = excelTool.exportWorkbook(titleData, rowList); //excelTool.save(workbook,"C:\\Users\\Administrator\\Desktop\\多级表头Obj1.xlsx"); heads.put("实体类（entity）多级表头表格1",titleData);// 每个sheet的表头，sheet名称为key datas.put("实体类（entity）多级表头表格1",rowList);// 每个sheet的内容，sheet名称为key types.put("实体类（entity）多级表头表格1",0);// 每个sheet的样式类型，sheet名称为key } public static void 多级表头Obj2() throws Exception { List<TitleEntity> titleList = new ArrayList<>(); titleList.add(new TitleEntity("title", null, "这里是title", null,20)); // 固定的五项表头 titleList.add(new TitleEntity("项目", "title", "项目", null,20)); titleList.add(new TitleEntity("评分规则", "项目", "评分规则", null,20)); titleList.add(new TitleEntity("评分标准", "评分规则", "评分标准", null,20)); titleList.add(new TitleEntity("所在单位", "评分标准", "所在单位", "unit",15)); titleList.add(new TitleEntity("所在部门", "评分标准", "所在部门", "dept",15)); // 动态表头（实际项目需要根据数据库数据添加） int count = 0; for (int i = 0; i < 2; i++) { String xmId = "项目"+(i+1); TitleEntity xm = new TitleEntity(xmId, "title", xmId, null, 20); titleList.add(xm); for (int j = 0; j < 2; j++) { String gzId = "项目"+(i+1)+"-"+"规则"+(j+1); String bzId = "项目"+(i+1)+"-"+"标准"+(j+1); TitleEntity gz = new TitleEntity(gzId, xmId, gzId, null, 20); TitleEntity bz = new TitleEntity(bzId, gzId, String.valueOf(j), null, 20); TitleEntity sl = new TitleEntity(bzId+"_sl"+j, bzId, "数量", "sl"+count, 10); TitleEntity df = new TitleEntity(bzId+"_df"+j, bzId, "得分", "df"+count, 10); titleList.add(gz); titleList.add(bz); titleList.add(sl); titleList.add(df); count++; } } // 填充数据 List<Map<String, String>> rowList = new ArrayList<>(); for (int i = 0; i < 6; i++) { Map<String, String> m = new HashMap<String, String>(); if (i<4){ m.put("unit", "A单位"); m.put("dept", "部门" + i); }else { m.put("unit", "B单位"); m.put("dept", "部门" + i); } for (int j = 0; j < count; j++) { m.put("sl"+j, String.valueOf(j)); m.put("df"+j, String.valueOf(j+i)); } rowList.add(m); } ExcelPoiUtil excelTool = new ExcelPoiUtil("实体类（entity）多级表头表格"); Map<String,String> param = ImmutableMap.<String, String>builder().put("id", "id").put("pid", "pid") .put("content", "content").put("fieldName", "fieldName").put("width", "width").build(); List<ColEntity> titleData = excelTool.colEntityTransformer(titleList, param, "title"); //HSSFWorkbook workbook = excelTool.exportWorkbook(titleData, rowList,Arrays.asList(0)); //excelTool.save(workbook,"C:\\Users\\Administrator\\Desktop\\多级表头Obj2.xlsx"); heads.put("实体类（entity）多级表头表格2",titleData);// 每个sheet的表头，sheet名称为key datas.put("实体类（entity）多级表头表格2",rowList);// 每个sheet的内容，sheet名称为key types.put("实体类（entity）多级表头表格2",0);// 每个sheet的样式类型，sheet名称为key mergeIndexs.put("实体类（entity）多级表头表格2",Arrays.asList(0));// 每个sheet的默认行高，sheet名称为key } public static void 纵向合并单元格() throws Exception { List<Map<String,String>> titleList=new ArrayList<>(); Map<String,String> titleMap=new HashMap<String,String>(); titleMap.put("id","11");titleMap.put("pid","0");titleMap.put("content","登录名");titleMap.put("fieldName","u_login_id");titleMap.put("width","20"); Map<String,String> titleMap1=new HashMap<String,String>(); titleMap1.put("id","1");titleMap1.put("pid","0");titleMap1.put("content","姓名");titleMap1.put("fieldName","u_name");titleMap1.put("width","20"); Map<String,String> titleMap2=new HashMap<String,String>(); titleMap2.put("id","2");titleMap2.put("pid","0");titleMap2.put("content","角色加部门");titleMap2.put("fieldName",null);titleMap2.put("width","20"); Map<String,String> titleMap3=new HashMap<String,String>(); titleMap3.put("id","3");titleMap3.put("pid","2");titleMap3.put("content","角色");titleMap3.put("fieldName","u_role");titleMap3.put("width","15"); Map<String,String> titleMap4=new HashMap<String,String>(); titleMap4.put("id","4");titleMap4.put("pid","2");titleMap4.put("content","部门");titleMap4.put("fieldName","u_dep");titleMap4.put("width","15"); Map<String,String> titleMap5=new HashMap<String,String>(); titleMap5.put("id","22");titleMap5.put("pid","0");titleMap5.put("content","角色加部门1");titleMap5.put("fieldName",null);titleMap5.put("width","20"); Map<String,String> titleMap6=new HashMap<String,String>(); titleMap6.put("id","22_1");titleMap6.put("pid","22");titleMap6.put("content","角色1");titleMap6.put("fieldName","u_role");titleMap6.put("width","10"); Map<String,String> titleMap7=new HashMap<String,String>(); titleMap7.put("id","22_2");titleMap7.put("pid","22");titleMap7.put("content","部门1");titleMap7.put("fieldName","u_dep");titleMap7.put("width","10"); titleList.add(titleMap); titleList.add(titleMap1); titleList.add(titleMap2); titleList.add(titleMap3); titleList.add(titleMap4); titleList.add(titleMap5); titleList.add(titleMap6); titleList.add(titleMap7); // 单级的 行内数据 List<Map<String, String>> rowList = new ArrayList<>(); for (int i = 0; i < 10; i++) { Map<String, String> m = new HashMap<String, String>(); m.put("u_login_id", "登录名zx" + i); if (i<6){ m.put("u_login_id", "登录名zx1"); m.put("u_name", i<3?"张三":"李四"); }else { m.put("u_login_id", "登录名zx2"); m.put("u_name", i<9?"王五":"赵六"); } m.put("u_role", "角色" + i); m.put("u_dep", "部门" + i); m.put("u_type", "用户类型" + i); rowList.add(m); } ExcelPoiUtil excelTool = new ExcelPoiUtil("纵向合并单元格"); Map<String,String> param = ImmutableMap.<String, String>builder().put("id", "id").put("pid", "pid") .put("content", "content").put("fieldName", "fieldName").put("width", "width").build(); List<ColEntity> titleData = excelTool.colEntityTransformer(titleList,param, "0"); //HSSFWorkbook workbook = excelTool.exportWorkbook(titleData, rowList,Arrays.asList(0,1)); //excelTool.save(workbook,"C:\\Users\\Administrator\\Desktop\\纵向合并单元格.xlsx"); heads.put("纵向合并单元格",titleData);// 每个sheet的表头，sheet名称为key datas.put("纵向合并单元格",rowList);// 每个sheet的内容，sheet名称为key types.put("纵向合并单元格",0);// 每个sheet的样式类型，sheet名称为key mergeIndexs.put("纵向合并单元格",Arrays.asList(0,1));// 每个sheet的默认行高，sheet名称为key }}最后
以上，终于是把项目里要的表格给导出来了！😎
实现代码的结构如图：
源码在这
最后感谢 这篇文章 的作者，也感谢这文章评论区提出bug的用户🥰
来源地址：https://blog.csdn.net/weixin_43165220/article/details/129521524
