利用POI实现复杂表头制作（纯手工）

人生第一篇博文，主要是记录给自己看的。拿来作为以后人生的反思，谢谢。

在这次的工作任务中，突然接触到了数据导出成EXCEL表格的功能。 模板EXCEL为复杂表头，还分为三级，当时看到就已经懵了，因为以前都只做过简单表头的制作。二话不说，在下马上求救度娘，但是网上的资料太多，却不能找到一篇自己称心如意的文章，所以最终决定自己来写一个工具类方便今后的使用。本文章代码仅提供了大致的思路，并没有考虑到执行效率的问题。  JAVA代码如下：

/** * 遍历树创建表头 * 规则： 1、 以二维数组的形式保存树节点          1:表头名称  2：表头索引 3：表头层级   4：父表头索引 *    2、表头存放的顺序必须按照空间顺序从左到右存储   * @param header * @param sheet * @param index 指示表头从第几行开始创建 */private static void createHeader(String[][] header,HSSFSheet sheet,int index,HSSFCellStyle cellStyle){//创建header//获取树深度int deep = getDeep(header);//根据树的深度创建行List<HSSFRow> headerRows = new ArrayList<HSSFRow>();for(int i =0;i<deep;i++){HSSFRow headerRow = sheet.createRow((short) index+i);headerRow.setHeight((short) 400);headerRows.add(headerRow);}List<HeaderNode> headerNodes = arrayToList(header);//获取叶子节点数量，根据此数量创建列int allOverNodeCount = getAllOverNodeCount(header);//创建单元格for(int i=0;i<headerNodes.size();i++){HeaderNode headerNode = headerNodes.get(i);int level = Integer.parseInt(headerNode.getLevel());int overNodeCount = headerNode.getAllOverNodeCount();int frontCount = headerNode.getFrontCount();sheet.setColumnWidth((short)frontCount, (short)7000);HSSFCell headerCell = headerRows.get(level-1).createCell((short)frontCount);headerCell.setCellStyle(cellStyle);            headerCell.setEncoding(HSSFCell.ENCODING_UTF_16);            headerCell.setCellValue(new HSSFRichTextString(headerNode.getHeaderName()));if(headerNode.isoverNode){//为叶子节点//向下合并单元格Region region = new Region();region.setRowFrom(index+level-1);        region.setRowTo(index+deep-1);        region.setColumnFrom((short)frontCount) ;        region.setColumnTo((short)frontCount) ;        sheet.addMergedRegion(region);}else{//为非叶子节点//向右合并单元格Region region = new Region();region.setRowFrom(index+level-1);        region.setRowTo(index+level-1);        region.setColumnFrom((short)frontCount) ;        region.setColumnTo((short)(frontCount + overNodeCount-1)) ;        sheet.addMergedRegion(region);}}}static class HeaderNode{private String index;private String headerName;private String level;private String parentIndex;private int allOverNodeCount;private boolean isoverNode;private int frontCount;public HeaderNode(String headerName,String index,  String level,String parentIndex, int allOverNodeCount, boolean isoverNode,int frontCount) {super();this.index = index;this.headerName = headerName;this.level = level;this.parentIndex = parentIndex;this.allOverNodeCount = allOverNodeCount;this.isoverNode = isoverNode;this.frontCount = frontCount;}public String getIndex() {return index;}public void setIndex(String index) {this.index = index;}public String getHeaderName() {return headerName;}public void setHeaderName(String headerName) {this.headerName = headerName;}public String getLevel() {return level;}public void setLevel(String level) {this.level = level;}public String getParentIndex() {return parentIndex;}public void setParentIndex(String parentIndex) {this.parentIndex = parentIndex;}public int getAllOverNodeCount() {return allOverNodeCount;}public void setAllOverNodeCount(int allOverNodeCount) {this.allOverNodeCount = allOverNodeCount;}public boolean isIsoverNode() {return isoverNode;}public void setIsoverNode(boolean isoverNode) {this.isoverNode = isoverNode;}public int getFrontCount() {return frontCount;}public void setFrontCount(int frontCount) {this.frontCount = frontCount;}}/** * 数组转化list集合 * @param header * @return */private static List<HeaderNode> arrayToList(String[][] header){List<HeaderNode> headerNodes = new ArrayList<HeaderNode>();for(String[] headernode : header){//获取此节点下的所有叶子节点数量int count = getOverNodeCount(headernode,header);int frontCount = getFrontOverNodeCount(headernode,header);headerNodes.add(new HeaderNode(headernode[0], headernode[1], headernode[2], headernode[3],count,isOverNode(headernode,header),frontCount));}return headerNodes;}/** * 获取level层级下的所有表头节点 * @param header * @param level * @return */private static List<HeaderNode> arrayToListByLevel(String[][] header,int level){List<HeaderNode> headerNodes = new ArrayList<HeaderNode>();for(String[] headernode : header){if(headernode[2].equals(level+"")){//获取此节点下的所有叶子节点数量int count = getOverNodeCount(headernode,header);int frontCount = getFrontOverNodeCount(headernode,header);headerNodes.add(new HeaderNode(headernode[0], headernode[1], headernode[2], headernode[3],count,isOverNode(headernode,header),frontCount));}}return headerNodes;}/** * 获取总叶子节点的数量，根据此数量创建表头列数 * @param header * @return */private static int getAllOverNodeCount(String[][] header){int count = 0;//获取表深度int deep = Integer.parseInt(header[header.length-1][2]);for(int i=0;i<header.length;i++){if(isOverNode(header[i], header)){count++;}}return count;}/** * 获取当前节点下的所有叶子节点数量 * @param headerNode * @param header * @return */private static int getOverNodeCount(String[] headerNode,String[][] header){int count = 0;for(int i=0;i<header.length;i++){if(header[i][3].equals(headerNode[1])){if(isOverNode(header[i], header)){count++;}else{count += getOverNodeCount(header[i], header);}}}return count;}/** * 判断是否为叶子节点 * @param headerNode * @param header * @return */private static boolean isOverNode(String[] headerNode,String[][] header){for(int i=0;i<header.length;i++){if(header[i][3].equals(headerNode[1])){return false;}}return true;}/** * 获取当前节点空间顺序之前的所有叶子节点数量 * @param headerNode * @param header * @return */private static int getFrontOverNodeCount(String[] headerNode,String[][] header){int count = 0 ;for(int i=0;i<header.length;i++){if(header[i].equals(headerNode)){break;}if(isOverNode(header[i], header)){count++;}}return count;}private static int getDeep(String[][] header){int deep = 0;for(int i=0;i<header.length;i++){if(Integer.parseInt(header[i][2])>deep){deep = Integer.parseInt(header[i][2]);}}return deep;}

相信大家能够看出，本文大致思路在于将表头信息作为一棵树来存放。由于精力有限，实在无法具体完善。导致本工具类使用有一点最大的约束。即  当我们传入一个header的二维数组（即一颗树）时，必须严格按照代码所制定的规则来传入。  以下给出示例数据：

String[][] header = {{"机构名称","0","1","-1"}, {"卡类型代码","1","1","-1"}, {"卡类型名称","2","1","-1"}, {"期初库存量","3","1","-1"}, {"本期入库情况","4","1","-1"}, {"本期入库小计","5","2","4"}, {"本期入库明细","6","2","4"}, {"印刷入库","7","3","6"}, {"领用入库","8","3","6"}, {"回收入库","9","3","6"}, {"本期出库情况","10","1","-1"}, {"本期出库小计","11","2","10"}, {"本期出库明细","12","2","10"}, {"机构/部门下发出库","13","3","12"}, {"员工下发出库","14","3","12"}, {"回收提交出库","15","3","12"}, {"清理出库","16","3","12"}, {"销毁出库","17","3","12"}, {"其他方式出库","18","3","12"}, {"剩余库存量","19","1","-1"}};

今后开始，在下决定利用博文来记录自己的学习，工作的技术历程。   如果今后的文章有幸能对大家有一些帮助或参考价值，希望大家能够多多交流。