我前面也写过几篇关于easypoi复杂表格导出的文章,什么一对多纵向合并、多级表头、动态表头、多个sheet等,这些我写那几篇文章之前做项目都遇到过,并且都实现出来了。
感兴趣的可以看看:
easypoi多级表头、多个sheet导出,动态导出列、动态更改表头
easypoi一对多,纵向合并单元格,并且根据内容自适应行高
我就以为大部分表格导出都难不倒我了,没想到我碰到的表格还是不够多。
多级表头中,不知道大家有没有碰到过这种表头,如下图:
三级下面有n个四级,一级二级下面分别只有一个二级和三级。亦或是这种:
我一开始寻思这种表头用easypoi的 ExcelExportEntity 实现挺简单的呀,后面我用代码写了才发现这结果完全不是我想的样子啊😂上面那两张图是想要的理想效果,实际上的结果是这样的:
和这样的:
我直接:
这种情况就是合并单元格只能作用于上一级,而不能跨级。比如对二级来说,四级是它子级的子级,但是它并不能因为子级(三级)有多个子级,所以子级(三级)合并了,它本身也会合并。 用easypoi来导出的话,关于这个问题,我现在没有找到解决方案。
还有一点就是我明明每列都设置了列宽,但是四级3、四级4的列宽没起作用,四级1、四级2设置的列宽倒是起作用了。
发现用 ExcelExportEntity 行不通后,有想过使用模板,但是我项目中实际用的表头比较复杂,我用模板完全不知道要怎么把表头遍历出来。。。项目里用的大概是这种形式的表头:
我不知道在模板里遍历每一项的时候,怎么再遍历每一项里面的list。就是遍历一级时,怎么再把每个一级下面的二级list给遍历出来。我看了easypoi的官方文档,发现有个 模板支持多层循环 的实现,但是偏偏就这一点,它文档上的图片没了。。。
然后我开始找其他解决办法,看过很多关于easypoi复杂表头的文章,都没有找到我这种形式的表格。直到我看某篇文章的评论区时,终于看到个有遇到和我一样问题的人了,并且贴出了参考方案,我按照这个方法,终于做出来我想要的表头了!!解决方案我就是参考的这篇文章:JAVA POI 实现EXCEL 动态表头、动态添加数据(导入导出)、 Tree结构的遍历
我按照上面那篇文章,终于得到我想要的表头了,效果如图:
设置的列宽也有作用。
再看看用easypoi ExcelExportEntity 导出来的:
我设置的列宽也不起作用。
表头可以了,接下来就是填充内容数据了,效果如下:
这里就有个问题了,因为所在单位这一列有连续多行是相同内容,所以我想要合并相同内容。
于是我在原先的代码基础上,加了纵向合并功能,需要设置哪几列是遇到相同内容要合并的。
/*** 把数据写入到单元格* @param headerCellList 表头数据* @param datas 行内数据* @param sheet 工作表(excel分页)* @param mergeIndex 需要纵向合并的单元格列号* @throws Exception void*/
private void writeSheetContent(List headerCellList, List datas, HSSFSheet sheet, int rowIndex,List mergeIndex, boolean rowFlag) throws Exception {boolean isMerge = false;// 是否纵向合并单元格if (mergeIndex != null && !mergeIndex.isEmpty()) isMerge = true;HSSFRow row = null;List 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> mergeMaps = new HashMap<>();// 需要合并的列:key 列号,value为单元格内容Map 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);}
}/*** 纵向合并单元格* @param mergeMaps 需要合并的列:key 要合并的列号,value为单元格内容* @param sheet*/
private void mergedCells(Map> mergeMaps,HSSFSheet sheet){for (Integer colNum : mergeMaps.keySet()) { // 遍历要合并的列,获取每一列的每一行Map mergeMap = mergeMaps.get(colNum);// 当前这列每一行的内容:key为行号,value为单元格内容// 根据mergeMap的value,也就是单元格内容进行分组,每一组都是需要合并在一起的单元格(要合并的区域)Map>>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> 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开始)
实现了纵向合并,项目中又根本不止一个sheet,所以我们还得实现导出多个sheet。
我在原先代码调用导出方法的基础上,再对这个方法传入的参数进行重新封装。原先是表头数据和内容数据都用的list,要实现多个sheet的话,我再用map把每个sheet的表头数据list、内容数据list给封装起来,用sheet名称为key。
/*** 返回workbook(单个sheet)* @param listTpamsColEntity 表头数据* @param datas 行内数据*/
public HSSFWorkbook exportWorkbook(List listTpamsColEntity, List datas) throws Exception {splitDataToSheets(this.title,datas, listTpamsColEntity,null, false);ExcelStyleUtil.setStyleByType(this.workbook,null,0,null);return this.workbook;
}/*** 返回workbook(多个sheet),这里全部sheet都用的同一个样式* @param titles 表头数据(key为sheet名称,value为表头数据)* @param datas 行内数据(key为sheet名称,value为行内数据)*/
public HSSFWorkbook exportWorkbook(Map> titles, Map> datas) throws Exception {for (String sheetName : titles.keySet()) {List colEntityList = titles.get(sheetName);List tList = datas.get(sheetName);splitDataToSheets(sheetName,tList, colEntityList,null,false);}ExcelStyleUtil.setStyleByType(this.workbook,null,0,null);return this.workbook;
}
最后导出如图:
javax.servlet servlet-api 2.5 cn.afterturn easypoi-base 4.4.0
用于封装表头内容。可以将数据库查出来的动态表头数据用这个实体类封装起来,也可以直接用数据库表映射的实体类
/*** 表头的实体类: 在具体的项目里,可以是你从数据库里查询出来的数据*/
public class TitleEntity {/*** id*/public String id;/*** 父级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
}
import java.util.ArrayList;
import java.util.List;/*** 单元格*/
public class ColEntity {/*** 单元格内容*/private String content;/*** 字段名称,用户导出表格时反射调用*/private String fieldName;/*** 这个单元格的集合*/private List cellList = new ArrayList();/*** 总行数*/private int totalRow;/*** 总列数*/private int totalCol;/*** excel第几行*/private int row;/*** excel第几列*/private int col;/*** excel 跨多少行*/private int rLen;/*** excel跨多少列*/private int cLen;/*** 是否有子节点*/private boolean hasChildren;/*** 树的级别 从0开始*/private int treeStep;/*** 树的id*/private String id;/*** 树的父级id*/private String pid;/*** 列宽*/private int width;// ......省略getter、setter
}
import com.entity.ColEntity;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;/*** excel poi 处理tree结构的数据 工具类*/
public class ExcelTreeUtil {/*** 传入的id 必须存在list集合里* 获取某节点的深度* @param list* @param id 根节点* @param step 当前节点级别* @return*/public static int getTreeStep(List 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;}/*** 遍历所有数据 获取树最大的深度* @param list* @return*/public static int getMaxStep(List list) {List nums = new ArrayList<>();for (ColEntity cc : list) {nums.add(getTreeStep(list, cc.getId(), 0));}return Collections.max(nums);}/*** 获取最底部子节点的个数 所有叶子节点个数* @param list* @param did* @return*/public static int getDownChildren(List 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;}/*** 获取父节点* @param list 所有的list数据,一条一条* @param did 当前节点id* @return*/public static ColEntity getParentCol(List 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);}};}/*** 获取兄弟节点个数 这个必须是有排序的* @param list 所有的list数据,一条一条* @param ColEntity 当前节点信息* @return*/public static int getBrotherChilNum(List 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;}/*** 根据某节点的第几层的父节点id* @param list 所有的list数据,一条一条* @param id 当前节点id* @param step 第几层(深度 从零开始)* @return*/public static String getStepParentId(List 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;}/*** 判断是否有子节点* @param list 遍历的数据* @param node 某个节点* @return*/public static boolean hasChild(List list, ColEntity node) {return getChildList(list, node).size() > 0;}/*** 得到子节点列表* @param list 遍历的数据* @param node 某个节点* @return*/public static List getChildList(List list, ColEntity node) {List nodeList = new ArrayList<>();Iterator 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;}/*** 使用递归方法建树* @param treeNodes*/public static List buildByRecursive(List treeNodes, String rootID) {List 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;}/*** 递归查找子节点* @param treeNodes* @return*/public static ColEntity findChildren(ColEntity treeNode, List 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;}
}
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;/*** excel导出样式设置工具类* 基础样式、自适应行高、隔行背景色*/
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);}/*** 初始化样式* @param workbook*/private void init(Workbook workbook) {this.headerStyle = initHeaderStyle(workbook);this.titleStyle = initTitleStyle(workbook,true,FONT_SIZE_ELEVEN);this.styles = initStyles(workbook);}@Overridepublic CellStyle getHeaderStyle(short i) {return headerStyle;}@Overridepublic CellStyle getTitleStyle(short i) {return titleStyle;}@Overridepublic CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {return null;}@Overridepublic CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) {return styles;}@Overridepublic CellStyle getStyles(Cell cell, int i, ExcelExportEntity entity, Object o, Object o1) {return getStyles(true, entity);}/*** 获取样式* @param style 1 大标题样式 2 表头样式 3 内容样式*/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;}/*** 字体样式* @param size 字体大小* @param isBold 是否加粗*/private static Font getFont(Workbook workbook, short size, boolean isBold) {Font font = workbook.createFont();//字体样式font.setFontName("宋体");//是否加粗font.setBold(isBold);//字体大小font.setFontHeightInPoints(size);return font;}/*** 根据type设置workbook* @param workbook* @param type 类型(0 默认 1 设置隔行背景 2 自适应行高 3 设置隔行背景同时自适应行高)* @param autoRowHeight 需要自适应行高的行号*/public static void setStyleByType(Workbook workbook,String sheetName,int type,List 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 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);}}
}
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;/*** excel poi 处理工具* 概念-> 表头数据:报表的表头* 行内数据:表头以下的数据* 功能:动态生成单级,多级Excel表头,多个sheet,纵向合并单元格* 备注:tree型结构数据的root节点的id默认为零(0)*/
public class ExcelPoiUtil {/*** excel 对象*/private HSSFWorkbook workbook;/*** 表格标题*/private String title;/*** 表头样式*/private CellStyle styleHead;/*** 主体样式*/private CellStyle styleBody;/*** 日期格式化,默认yyyy-MM-dd HH:mm:ss*/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);}/*** 返回workbook* @param listTpamsColEntity 表头数据* @param datas 行内数据*/public HSSFWorkbook exportWorkbook(List listTpamsColEntity, List datas) throws Exception {splitDataToSheets(this.title,datas, listTpamsColEntity,null, false);ExcelStyleUtil.setStyleByType(this.workbook,null,0,null);return this.workbook;}/*** 返回workbook* @param listTpamsColEntity 表头数据* @param datas 行内数据* @param mergeIndex 需要纵向合并的单元格列号(默认有横向合并)*/public HSSFWorkbook exportWorkbook(List listTpamsColEntity, List datas,List mergeIndex) throws Exception {splitDataToSheets(this.title,datas, listTpamsColEntity, mergeIndex,false);ExcelStyleUtil.setStyleByType(this.workbook,null,0,null);return this.workbook;}/*** 返回workbook* @param listTpamsColEntity 表头数据* @param datas 行内数据* @param type 类型(0 默认 1 设置隔行背景 2 自适应行高 3 设置隔行背景同时自适应行高)* @param autoRowHeight 需要自适应行高的行号*/public HSSFWorkbook exportWorkbook(List listTpamsColEntity, List datas,int type,List autoRowHeight) throws Exception {splitDataToSheets(this.title,datas, listTpamsColEntity,null, false);ExcelStyleUtil.setStyleByType(this.workbook,null,type,autoRowHeight);return this.workbook;}/*** 返回workbook* @param listTpamsColEntity 表头数据* @param datas 行内数据* @param type 类型(0 默认 1 设置隔行背景 2 自适应行高 3 设置隔行背景同时自适应行高)* @param autoRowHeight 需要自适应行高的行号* @param mergeIndex 需要纵向合并的单元格列号(默认有横向合并)*/public HSSFWorkbook exportWorkbook(List listTpamsColEntity, List datas,int type,List autoRowHeight,List mergeIndex) throws Exception {splitDataToSheets(this.title,datas, listTpamsColEntity,mergeIndex, false);ExcelStyleUtil.setStyleByType(this.workbook,null,type,autoRowHeight);return this.workbook;}/*** 返回workbook(多个sheet),这里全部sheet都用的同一个样式* @param titles 表头数据(key为sheet名称,value为表头数据)* @param datas 行内数据(key为sheet名称,value为行内数据)* @param type 样式类型,每个sheet都用这个(0 默认 1 设置隔行背景 2 自适应行高 3 设置隔行背景同时自适应行高)* @param autoRowHeight 需要自适应行高的行号,每个sheet都用这个* @param mergeIndex 需要纵向合并的单元格列号(默认有横向合并),每个sheet都用这个*/public HSSFWorkbook exportWorkbook(Map> titles, Map> datas,int type,List autoRowHeight,List mergeIndex) throws Exception {for (String sheetName : titles.keySet()) {List colEntityList = titles.get(sheetName);List tList = datas.get(sheetName);splitDataToSheets(sheetName,tList, colEntityList,mergeIndex,false);}ExcelStyleUtil.setStyleByType(this.workbook,null,type,autoRowHeight);return this.workbook;}/*** 返回workbook(多个sheet),这里多个sheet都有不同样式* @param titles 表头数据(key为sheet名称,value为表头数据)* @param datas 行内数据(key为sheet名称,value为行内数据)* @param types 每个sheet的类型,key为sheet名称(0 默认 1 设置隔行背景 2 自适应行高 3 设置隔行背景同时自适应行高)* @param autoRowHeights 每个sheet需要自适应行高的行号* @param mergeIndexs 每个sheet需要纵向合并的单元格列号(默认有横向合并)*/public HSSFWorkbook exportWorkbook(Map> titles, Map> datas,Map types,Map> autoRowHeights,Map> mergeIndexs) throws Exception {for (String sheetName : titles.keySet()) {List colEntityList = titles.get(sheetName);List tList = datas.get(sheetName);int type = types == null ? 0 : types.get(sheetName) == null ? 0 : types.get(sheetName);List autoRowHeight = autoRowHeights == null ? null : autoRowHeights.get(sheetName);List mergeIndex = mergeIndexs == null ? null : mergeIndexs.get(sheetName);splitDataToSheets(sheetName,tList, colEntityList,mergeIndex,false);ExcelStyleUtil.setStyleByType(this.workbook,sheetName,type,autoRowHeight);}return this.workbook;}/*** 保存excel到本机指定的路径* @param workbook* @param filePath*/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) {}}/*** 导出Excel,适用于web导出excel* @param sheet excel* @param data 行内数据* @param headerCellList 表头数据* @param mergeIndex 需要纵向合并的单元格列号(默认有横向合并)* @param rowFlag 输出展示数据的结构(表头下面行的数据)*/private void writeSheet(HSSFSheet sheet, List data, List headerCellList,List 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);}/*** 拆分sheet,因为每个sheet不能超过65535,否则会报异常* @param sheetName sheet名称* @param data 行内数据* @param headerCellList 表头数据* @param mergeIndex 需要纵向合并的单元格列号(默认有横向合并)* @param rowFlag 输出展示数据的结构(表头下面行的数据)*/private void splitDataToSheets(String sheetName,List data, List headerCellList,List 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 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);}/*** 把数据写入到单元格* @param headerCellList 表头数据* @param datas 行内数据* @param sheet 工作表(excel分页)* @param mergeIndex 需要纵向合并的单元格列号(默认有横向合并)* @throws Exception void*/private void writeSheetContent(List headerCellList, List datas, HSSFSheet sheet, int rowIndex,List mergeIndex, boolean rowFlag) throws Exception {boolean isMerge = false;// 是否纵向合并单元格if (mergeIndex != null && !mergeIndex.isEmpty()) isMerge = true;HSSFRow row = null;List 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> mergeMaps = new HashMap<>();// 需要合并的列:key 列号,value为单元格内容Map 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);}}/*** 纵向合并单元格* @param mergeMaps 需要合并的列:key 要合并的列号,value为单元格内容* @param sheet*/private void mergedCells(Map> mergeMaps,HSSFSheet sheet){for (Integer colNum : mergeMaps.keySet()) { // 遍历要合并的列,获取每一列的每一行Map mergeMap = mergeMaps.get(colNum);// 当前这列每一行的内容:key为行号,value为单元格内容// 根据mergeMap的value,也就是单元格内容进行分组,每一组都是需要合并在一起的单元格(要合并的区域)Map>>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> 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));}}}}/*** 根据list 来创建单元格 暂时没有用* @param row* @param j* @param finame* @param t*/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
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> heads = new HashMap<>();// 最终导出的多个sheet的表头static Map>> datas = new HashMap<>();// 最终导出的多个sheet的内容static Map types = new HashMap<>();// 最终导出的每个sheet的样式类型static Map> autoRowHeights = new HashMap<>();// 最终导出的每个sheet的需要自适应行高的行号static Map> 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 map = new HashMap();map.put("登录名", "u_login_id");Map map1 = new HashMap();map1.put("用户名", "u_name");Map map2 = new HashMap();map2.put("角色", "u_role");Map map3 = new HashMap();map3.put("部门", "u_dep");Map map4 = new HashMap();map4.put("用户类型", "u_type");List> titleList = new ArrayList<>();titleList.add(map);titleList.add(map1);titleList.add(map2);titleList.add(map3);titleList.add(map4);//单级的 行内数据List> rowList = new ArrayList<>();for (int i = 0; i < 7; i++) {Map m = new HashMap();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 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> titleList=new ArrayList<>();Map titleMap=new HashMap();titleMap.put("id","11");titleMap.put("pid","0");titleMap.put("content","登录名");titleMap.put("fieldName","u_login_id");titleMap.put("width","20");Map titleMap1=new HashMap();titleMap1.put("id","1");titleMap1.put("pid","0");titleMap1.put("content","姓名");titleMap1.put("fieldName","u_name");titleMap1.put("width","20");Map titleMap2=new HashMap();titleMap2.put("id","2");titleMap2.put("pid","0");titleMap2.put("content","角色加部门");titleMap2.put("fieldName",null);titleMap2.put("width","20");Map titleMap3=new HashMap();titleMap3.put("id","3");titleMap3.put("pid","2");titleMap3.put("content","角色");titleMap3.put("fieldName","u_role");titleMap3.put("width","15");Map titleMap4=new HashMap();titleMap4.put("id","4");titleMap4.put("pid","2");titleMap4.put("content","部门");titleMap4.put("fieldName","u_dep");titleMap4.put("width","15");Map titleMap5=new HashMap();titleMap5.put("id","22");titleMap5.put("pid","0");titleMap5.put("content","角色加部门1");titleMap5.put("fieldName",null);titleMap5.put("width","20");Map titleMap6=new HashMap();titleMap6.put("id","22_1");titleMap6.put("pid","22");titleMap6.put("content","角色1");titleMap6.put("fieldName","u_role");titleMap6.put("width","10");Map titleMap7=new HashMap();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> rowList = new ArrayList<>();for (int i = 0; i < 7; i++) {Map m = new HashMap();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 param = ImmutableMap.builder().put("id", "id").put("pid", "pid").put("content", "content").put("fieldName", "fieldName").put("width", "width").build();List 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名称为keydatas.put("List多级表头",rowList);// 每个sheet的内容,sheet名称为keytypes.put("List多级表头",0);// 每个sheet的样式类型,sheet名称为key}public static void 多级表头Obj() throws Exception {List 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> rowList = new ArrayList<>();for (int i = 0; i < 7; i++) {Map m = new HashMap();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 param = ImmutableMap.builder().put("id", "id").put("pid", "pid").put("content", "content").put("fieldName", "fieldName").put("width", "width").build();List 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名称为keydatas.put("实体类(entity)多级表头表格",rowList);// 每个sheet的内容,sheet名称为keytypes.put("实体类(entity)多级表头表格",0);// 每个sheet的样式类型,sheet名称为key}public static void 多级表头Obj1() throws Exception {List 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> rowList = new ArrayList<>();for (int i = 0; i < 5; i++) {Map m = new HashMap();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 param = ImmutableMap.builder().put("id", "id").put("pid", "pid").put("content", "content").put("fieldName", "fieldName").put("width", "width").build();List 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名称为keydatas.put("实体类(entity)多级表头表格1",rowList);// 每个sheet的内容,sheet名称为keytypes.put("实体类(entity)多级表头表格1",0);// 每个sheet的样式类型,sheet名称为key}public static void 多级表头Obj2() throws Exception {List 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> rowList = new ArrayList<>();for (int i = 0; i < 6; i++) {Map m = new HashMap();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 param = ImmutableMap.builder().put("id", "id").put("pid", "pid").put("content", "content").put("fieldName", "fieldName").put("width", "width").build();List 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名称为keydatas.put("实体类(entity)多级表头表格2",rowList);// 每个sheet的内容,sheet名称为keytypes.put("实体类(entity)多级表头表格2",0);// 每个sheet的样式类型,sheet名称为keymergeIndexs.put("实体类(entity)多级表头表格2",Arrays.asList(0));// 每个sheet的默认行高,sheet名称为key}public static void 纵向合并单元格() throws Exception {List> titleList=new ArrayList<>();Map titleMap=new HashMap();titleMap.put("id","11");titleMap.put("pid","0");titleMap.put("content","登录名");titleMap.put("fieldName","u_login_id");titleMap.put("width","20");Map titleMap1=new HashMap();titleMap1.put("id","1");titleMap1.put("pid","0");titleMap1.put("content","姓名");titleMap1.put("fieldName","u_name");titleMap1.put("width","20");Map titleMap2=new HashMap();titleMap2.put("id","2");titleMap2.put("pid","0");titleMap2.put("content","角色加部门");titleMap2.put("fieldName",null);titleMap2.put("width","20");Map titleMap3=new HashMap();titleMap3.put("id","3");titleMap3.put("pid","2");titleMap3.put("content","角色");titleMap3.put("fieldName","u_role");titleMap3.put("width","15");Map titleMap4=new HashMap();titleMap4.put("id","4");titleMap4.put("pid","2");titleMap4.put("content","部门");titleMap4.put("fieldName","u_dep");titleMap4.put("width","15");Map titleMap5=new HashMap();titleMap5.put("id","22");titleMap5.put("pid","0");titleMap5.put("content","角色加部门1");titleMap5.put("fieldName",null);titleMap5.put("width","20");Map titleMap6=new HashMap();titleMap6.put("id","22_1");titleMap6.put("pid","22");titleMap6.put("content","角色1");titleMap6.put("fieldName","u_role");titleMap6.put("width","10");Map titleMap7=new HashMap();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> rowList = new ArrayList<>();for (int i = 0; i < 10; i++) {Map m = new HashMap();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 param = ImmutableMap.builder().put("id", "id").put("pid", "pid").put("content", "content").put("fieldName", "fieldName").put("width", "width").build();List 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名称为keydatas.put("纵向合并单元格",rowList);// 每个sheet的内容,sheet名称为keytypes.put("纵向合并单元格",0);// 每个sheet的样式类型,sheet名称为keymergeIndexs.put("纵向合并单元格",Arrays.asList(0,1));// 每个sheet的默认行高,sheet名称为key}
}
以上,终于是把项目里要的表格给导出来了!😎
实现代码的结构如图:
最后感谢 这篇文章 的作者,也感谢这文章评论区提出bug的用户🥰