JAVA操作Excel 可配置，动态生成复杂表头复杂的中国式报表表头-白红宇

JAVA操作Excel 可配置，动态生成复杂表头复杂的中国式报表表头

阅读量：6306 次

发布时间：2019-06-22

本文共 30064 字，大约阅读时间需要 100 分钟。

1. [代码]特别声明

             经过了几个月的强度测试现在已经应用到了生产环境 ，现在贴的代码是当初的测试版，并不完善 ，等过点时间吧 完全静下心来 准备把整个开源出来 好与不好先不说，最起码现在几个大系统都在用 
             我再声明下，这个代码是可以动态配置表头的，别再回评论说怎么不先定义 模板 了！受够了，先定义模板用你们给我说么！不装B能死么？ 

2. [代码]持续两周上热门的话贴数据库树表信息不过现在能通过代码获得到建表信息，就俩张表，一张类别表，一张就是核心表树表信息了

1	持续两周上热门的话贴数据库树表信息不过现在能通过代码获得到建表信息，就俩张表，一张类别表，一张就是核心表树表信息了

3. [图片] QQ图片20150717175633.png

4. [代码][Java]代码该代码实现了Excel复杂表头的生成基于sql server 的一张树表你们可以看代码自行建库

             packagecom.jingjiu.util; 
             importjava.io.FileNotFoundException; 
             importjava.io.FileOutputStream; 
             importjava.io.IOException; 
             importjava.io.OutputStream; 
             importjava.sql.Connection; 
             importjava.sql.PreparedStatement; 
             importjava.sql.ResultSet; 
             importjava.sql.SQLException; 
             importjava.util.ArrayList; 
             importjava.util.HashMap; 
             importjava.util.List; 
             importjava.util.Map; 
             importorg.apache.poi.hssf.usermodel.HSSFCell; 
             importorg.apache.poi.hssf.usermodel.HSSFCellStyle; 
             importorg.apache.poi.hssf.usermodel.HSSFRow; 
             importorg.apache.poi.hssf.usermodel.HSSFSheet; 
             importorg.apache.poi.hssf.usermodel.HSSFWorkbook; 
             importorg.apache.poi.ss.util.Region; 
             importorg.aspectj.weaver.patterns.ThisOrTargetAnnotationPointcut; 
             importcom.google.common.collect.Table.Cell; 
             publicclassGenerExcle { 
                 HSSFWorkbook wb =null; 
                 HSSFSheet sheet =null; 
                 HSSFCellStyle style =null; 
                 publicvoidreData(){ 
                     //所有表头集合 
                     List allTableHead =newArrayList(); 
                 } 
                 publicGenerExcle(){ 
                       wb =newHSSFWorkbook();     
                       sheet = wb.createSheet("new   sheet");     
                       style = wb.createCellStyle();// 样式对象     
                       style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直     
                       style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平     
                       //style.setBottomBorderColor(HSSFCellStyle.BORDER_DOTTED); 
                       style.setWrapText(true); 
                 } 
                 /** 
                  * 保存生成的文件 
                  * name 
                  */ 
                 publicvoidsaveAaSpecName(String name){ 
                     OutputStream out; 
                     try{ 
                         out =newFileOutputStream(name); 
                         wb.write(out); 
                         System.out.println(out); 
                     }catch(FileNotFoundException e) { 
                         // TODO Auto-generated catch block 
                         e.printStackTrace(); 
                     }catch(IOException e) { 
                         // TODO Auto-generated catch block 
                         e.printStackTrace(); 
                     } 
                 } 
                 public voidaddRow(intcount){ 
                     for(inti =0; i<count; i++) 
                      sheet.createRow((short) i);   
                 } 
                 publicvoidtestAll(){ 
                     this.addRow(3); 
                     sheet.addMergedRegion(newRegion(0,(short)0,2,(short)0)); 
                     HSSFRow row = sheet.getRow(0); 
                     HSSFCell cell = row.createCell(0); 
                     cell.setCellValue("公司名"); 
                     this.saveAaSpecName("e:/like.xls"); 
                 } 
                 /** 
                  * 合并cell并设置值 
                  * startRow 
                  * startCell 
                  * endRow 
                  * endCell 
                  * @param leve 
                  * @param cellMove 
                  * @param content 
                  */ 
                 public voidmergeCell(intstartRow,intstartCell,intendRow,intendCell,intleve,intcellMove,String content){ 
                     sheet.addMergedRegion(newRegion(startRow,(short)startCell,endRow,(short)endCell)); 
                     HSSFRow row = sheet.getRow(startRow); 
                     HSSFCell cell= row.createCell(cellMove); 
                     cell.setCellValue(content); 
                     cell.setCellStyle(style); 
                 } 
                 publicStringBuffer excleToHtml(){ 
                     ExcelShower es =newExcelShower(); 
                     StringBuffer excleToHtml = es.excleToHtml(this.wb); 
                     returnexcleToHtml; 
                 } 
                   public staticvoidmain(String [] args){ 
                       GenerExcle ge =newGenerExcle(); 
                     // ge.testAll(); 
                       if(true){ 
                         //  return; 
                       } 
                       intreportId =1; 
                       getReportHeaderById(ge, reportId); 
                   } 
                  /** 
                   * 根据报表ID遍历该报表的所有表头 
                   * 并以主表头为原子单位进行处理 
                   * @param ge 
                   * @param reportId 
                   */ 
                 privatestaticvoidgetReportHeaderById(GenerExcle ge,intreportId) { 
                     //拿到煤报表的一级所有一级表头 
                       String sql = 
                               "with tem_header(id,pid,headervalue,leve,custom_col) as(\n"+ 
                                       "\n"+ 
                                       "select id ,pid, headvalue,leve, custom_col from dbo.headerinfo where pid = 0 and report_id = ?\n"+ 
                                       "union all\n"+ 
                                       "select  a.id,a.pid,a.headvalue,a.leve ,a.custom_col from dbo.headerinfo a ,tem_header b\n"+ 
                                       "where a.pid = b.id\n"+ 
                                         "\n"+ 
                                         "and  a.report_id = ?"+ 
                                       "\n"+ 
                                       "\n"+ 
                                       ")\n"+ 
                                       "select * from tem_header where  pid = 0"; 
                       PreparedStatement ps =null; 
                       ResultSet rs  =null; 
                       Connection connection =null; 
                       try{ 
                         connection= DBUtil.getConnection(); 
                         ps = connection.prepareStatement(sql); 
                         ps.setInt(1, reportId); 
                         ps.setInt(2, reportId); 
                         ps.executeQuery(); 
                         rs = ps.getResultSet(); 
                         intmax =0; 
                         while(rs.next()) { 
                             intid = rs.getInt("id"); 
                             intpid = rs.getInt("pid"); 
                             String headvalue = rs.getString("headervalue"); 
                             intleve = rs.getInt("leve"); 
                             System.out.println("ID"+id+"Pid"+pid+"头名"+headvalue); 
                             //先判断最深层有多少层 
                             intmaxLeve = getMaxLeve(reportId,id,connection,ps,rs); 
                             if(maxLeve>max){ 
                                 max = maxLeve; 
                             } 
                         } 
                         //根据最深层添加行 
                         ge.addRow(max); 
                         ps.executeQuery(); 
                         rs = ps.getResultSet(); 
                         intcurrentIndex =0; 
                         intindex =0; 
                         Map<String, Integer> moveInfoMap =newHashMap<String, Integer>(); 
                         Map<String, Integer> leveMoveInfoMap =newHashMap<String, Integer>(); 
                             moveInfoMap.put("currentIndex",0); 
                             moveInfoMap.put("cellMove",0); 
                         while(rs.next()) { 
                             moveInfoMap.put("index",index); 
                             intid = rs.getInt("id"); 
                             intpid = rs.getInt("pid"); 
                             String headvalue = rs.getString("headervalue"); 
                             intleve = rs.getInt("leve"); 
                             intcustom_col = rs.getInt("custom_col"); 
                             if( leveMoveInfoMap.get(leve+"")==null){ 
                                 leveMoveInfoMap.put(leve+"",0); 
                             } 
                             System.out.println("ID"+id+"Pid"+pid+"头名"+headvalue); 
                             generHeader(id, 
                                                  pid, 
                                                  headvalue, 
                                                  custom_col, 
                                                  max, 
                                                  rs, 
                                                  ps, 
                                                  connection, 
                                                  ge, 
                                                  reportId, 
                                                  leve, 
                                                  currentIndex, 
                                                  moveInfoMap, 
                                                  leveMoveInfoMap); 
                             //当前索引自加1 
                             currentIndex++; 
                             index++; 
                         } 
                     //  ge.saveAaSpecName("e:/today.xls"); 
                         ge.excleToHtml(); 
                         DBUtil.closeResources(connection, ps, rs); 
                         System.out.println(connection); 
                     }catch(SQLException e) { 
                         // TODO Auto-generated catch block 
                         e.printStackTrace(); 
                     }finally{ 
                         DBUtil.closeResources(connection, ps, rs); 
                     } 
                 } 
             /** 
              * 判断最深有多少层 
              * @param i 
              * @param id 
              * @param rs 
              * @param ps 
              * @return 
              */ 
                 privatestaticintgetMaxLeve(intreportId,intid,Connection con, PreparedStatement ps, ResultSet rs) { 
                     String sql= 
                             "with tem_header(id,pid,headervalue,leve) as(\n"+ 
                                     "\n"+ 
                                     "select id ,pid, headvalue,leve from dbo.headerinfo where pid = 0 and report_id = ? and id = ?\n"+ 
                                     "union all\n"+ 
                                     "select  a.id,a.pid,a.headvalue,a.leve from dbo.headerinfo a ,tem_header b\n"+ 
                                     "where a.pid = b.id\n"+ 
                                     "\n"+ 
                                     "and  a.report_id = ?"+ 
                                     "\n"+ 
                                     ")\n"+ 
                                     "select count(1) from (select t.leve from tem_header t group by t.leve) mm"; 
                  try{ 
                 ps =    con.prepareStatement(sql); 
                 ps.setObject(1, reportId); 
                 ps.setObject(2, id); 
                 ps.setObject(3,reportId); 
                 rs  = ps.executeQuery(); 
                 if(rs.next()) 
                 returnrs.getInt(1); 
                 return-1; 
                 }catch(SQLException e) { 
                     // TODO Auto-generated catch block 
                     e.printStackTrace(); 
                 } 
                     return0; 
                 } 
                 privatestaticvoidgenerAfterHeader(intid, 
                         intpid, 
                         String headvalue, 
                         intmax, 
                         ResultSet rs, 
                         PreparedStatement ps, 
                         Connection connection, 
                         GenerExcle ge, 
                         intreportId, 
                         intleve, 
                         intcurrentIndex, 
                         Map<String, Integer> moveInfoMap, 
                         Map<String, Integer> leveMoveInfoMap, 
                         Map<String, Map<String,Integer>> repairMovePoint, 
                         intindex){ 
                                         /** 
                                          * 处理多级表头的合并 
                                          */ 
                                 //但到表头的直接后继表头 
                                 rs = getDirAfter(id,pid,reportId,leve,connection,ps,rs); 
                                 //存放直接后继的子头集合 
                                 List<DataModel> listResultSets =newArrayList<DataModel>(); 
                                 booleanflag =false; 
                                 try{ 
                                 //  rs.last(); 
                                 //  int size = rs.getRow(); 
                                     //rs.first(); 
                                     while(rs.next()){ 
                                     int_id= rs.getInt("id"); 
                                     int_pid = rs.getInt("pid"); 
                                     String _headvalue = rs.getString("headervalue"); 
                                     int_leve = rs.getInt("leve"); 
                                     if( leveMoveInfoMap.get(_leve+"")==null){ 
                                         leveMoveInfoMap.put(_leve+"",0); 
                                     } 
                                     Map<String,Integer> infoMovePlus =null; 
                                     if(repairMovePoint.get(_leve+1+"")==null){ 
                                          infoMovePlus =newHashMap<String, Integer>(); 
                                     }else{ 
                                     infoMovePlus =repairMovePoint.get(_leve+1+""); 
                                     } 
                                     infoMovePlus.put(0+"",0); 
                                     infoMovePlus.put(rs.getRow()+"",0); 
                                     repairMovePoint.put(_leve+1+"", infoMovePlus); 
                                     /** 
                                      * 判断该表头有没有直接后继节点 
                                      * 如果有不做向下合并处理 
                                      * 如果没有则合并下面的空格 
                                      */ 
                                     //如果不是最后一行则会发生空出一格的情况这时候需要进行行合并 
                                 int_temDownMovePonit =0; 
                                 int_currentMaxLeve = leveMoveInfoMap.get("currentMaxLeve"); 
                                     if(_leve<_currentMaxLeve&&_leve>1){ 
                                         ResultSet _rs = getDirAfter(_id, _pid, reportId, _leve, connection, ps, rs); 
                                         //如果没有直接后继节点 
                                         if(!_rs.next()){ 
                                             //判断需要向下合并几个位移量 
                                             _temDownMovePonit =3-_leve; 
                                             //让以下每一级的指针右移1位补空位 
                                     /*      if(row==1){ 
                                             for(int i = 0;i<3-_leve;i++){ 
                                             //  leveMoveInfoMap.put(_leve+i+1+"",leveMoveInfoMap.get(_leve+i+1+"")+1); 
                                             } 
                                             ｝*/ 
                                             //如果是连续有值 
                                             if(infoMovePlus.get(rs.getRow()-1+"")!=null&&infoMovePlus.get(rs.getRow()-1+"")>=1){ 
                                         //      infoMovePlus.put(rs.getRow()-1+"",infoMovePlus.get(rs.getRow()-1+"")+1); 
                                             } 
                                             infoMovePlus.put(rs.getRow()+"",infoMovePlus.get(rs.getRow()-1+"")+1); 
                                         } 
                                     } 
                                     intplusPoint =0; 
                                     if(!flag){ 
                                     if(repairMovePoint.get(_leve+"")!=null){ 
                                         Map<String, Integer> map = repairMovePoint.get(_leve+""); 
                                         int_rowCount = rs.getRow(); 
                                         for(inti =1;i<=index;i++) 
                                         if(map.get(i+"")!=null){ 
                                         plusPoint = map.get(i+""); 
                                         } 
                                         flag =true; 
                                     } 
                                     } 
                                     int_sizeMoveCount = leveMoveInfoMap.get(_leve-1+""); 
                                     intsizeMoveCount = leveMoveInfoMap.get(_leve+"")+plusPoint; 
                                 //  int _tem=moveInfoMap.get("cellMove"); 
                                     intstart =_leve-1+(repairMovePoint.get("mainRowCount").get("mainRowCount")-1); 
                                     intmaxCurrentLeveMegreCell = getMaxCurrentLeveMegreCell(_id, reportId, _pid, ps, rs, connection); 
                                 //  ge.mergeCell(_leve-1, sizeMoveCount, _leve-1+_temDownMovePonit, sizeMoveCount+maxCurrentLeveMegreCell-1, _leve, sizeMoveCount, _headvalue); 
                                         ge.mergeCell(start, sizeMoveCount, start+_temDownMovePonit, sizeMoveCount+maxCurrentLeveMegreCell-1, _leve, sizeMoveCount, _headvalue); 
                                     leveMoveInfoMap.put(_leve+"",sizeMoveCount+maxCurrentLeveMegreCell); 
                                     //_temDownMovePonit= 0; 
                                     DataModel dm =newDataModel(); 
                                     dm.setId(_id); 
                                     dm.setPid(_pid); 
                                     dm.setHeadvalue(_headvalue); 
                                     dm.setMax(max); 
                                     dm.setRs(rs);; 
                                     dm.setPs(ps); 
                                     dm.setConnection(connection); 
                                     dm.setGe(ge); 
                                     dm.setReportId(reportId); 
                                     dm.setLeve(_leve); 
                                     dm.setCurrentIndex(currentIndex); 
                                     dm.setMoveInfoMap(leveMoveInfoMap); 
                                     dm.setLeveMoveInfoMap(leveMoveInfoMap); 
                                     dm.setRepairMoveInfo(repairMovePoint); 
                                     listResultSets.add(dm); 
                                 //  getDirAfter(_id, _pid, reportId, maxCurrentLeveMegreCell, connection, ps, rs); 
                                     //break; 
                                 //  System.out.println("leve 2ID"+_id+"Pid"+_pid+"头名"+_headvalue+""+maxCurrentLeveMegreCell); 
                                     } 
                                     /** 
                                      * 判断该表的最终级别有几列 说明该表的合并列为多少 
                                      * 所有单元格的合并最终是看当前表头开始它的最终有多少个子表头 
                                      * 也就是最后一行 
                                      */ 
                                     if(listResultSets!=null&&listResultSets.size()>0){ 
                                         for(inti =0;i<listResultSets.size() ; i ++){ 
                                             DataModel dModel = listResultSets.get(i); 
                                             dModel.setIndex(i); 
                                             generAfterHeader(dModel.getId() , dModel.getPid(), dModel.getHeadvalue(), 
                                                     dModel.getMax(), 
                                                     dModel.getRs(), 
                                                     dModel.getPs(), 
                                                     dModel.getConnection(), 
                                                     dModel.getGe(), 
                                                     dModel.getReportId(), 
                                                     dModel.getLeve(), 
                                                     dModel.getCurrentIndex(), 
                                                     dModel.getMoveInfoMap(), 
                                                     dModel.getLeveMoveInfoMap(), 
                                                     dModel.getRepairMoveInfo(), 
                                                     dModel.getIndex()); 
                                         } 
                                     } 
                                 }catch(SQLException e2) { 
                                     // TODO Auto-generated catch block 
                                     e2.printStackTrace(); 
                                 } 
                 } 
                /** 
                 * 生成表头 
                 * @param id 
                 * @param pid 
                 * @param headvalue 
                 * @param max 
                 * @param max 
                 * @param rs 
                 * @param ps 
                 * @param connection 
                 * @param ge 
                 * @param leve 
                 * @param currentIndex 
                 * @param moveInfoMap 
                 * @param leveMoveInfoMap 
                 */ 
                 privatestaticvoidgenerHeader(intid, 
                                                                    intpid, 
                                                                    String headvalue, 
                                                                    intcustomCol, 
                                                                    intmax, ResultSet rs, 
                                                                    PreparedStatement ps, 
                                                                    Connection connection, 
                                                                    GenerExcle ge, 
                                                                    intreportId, 
                                                                    intleve, 
                                                                    intcurrentIndex, 
                                                                    Map<String, Integer> moveInfoMap, 
                                                                    Map<String, Integer> leveMoveInfoMap) { 
                     //获取当前主表头应该跨几列 
                     intmaxMoveCell = getMaxCurrentLeveMegreCell(id,reportId,pid,ps,rs,connection); 
                     Map<String, Integer> _moveInfoMap =newHashMap<String, Integer>(); 
                     //获得该主表头的层级 
                     intcurrentMaxLeve  = getMaxLeve(reportId, id, connection, ps, rs); 
                     //获取当前主表头应该占几行 
                     intmainRowCount = max - (currentMaxLeve -1); 
                     if(mainRowCount!=max){ 
                         getMax(id, pid, rs, ps, connection, reportId, leve, _moveInfoMap,currentMaxLeve); 
                         if(_moveInfoMap.get("addOtherCell")!=null){ 
                         String _max = _moveInfoMap.get("addOtherCell").toString(); 
                         maxMoveCell +=Integer.parseInt(_max); 
                         } 
                     } 
                     //以主表头层级关系每开一个主表头的位移量想当于下一个主表头的开始量（这是个累加的过程） 
                     maxMoveCell+=customCol; 
                     if(leveMoveInfoMap.get("allColMove")==null){ 
                         leveMoveInfoMap.put("allColMove",maxMoveCell); 
                     }else{ 
                         leveMoveInfoMap.put("allColMove",leveMoveInfoMap.get("allColMove")+maxMoveCell); 
                     } 
                     /* 
                      * 把当前主表头的层级关系带进去 以便控制子表如果下沉合并时的位移量 
                      * */ 
                     leveMoveInfoMap.put("currentMaxLeve", currentMaxLeve); 
                     System.out.println(maxMoveCell); 
                     //初始化每级行的位移量 
                     for(inti =1;i<=max;i++){ 
                         if(leveMoveInfoMap.get(i+"")==null) 
                             leveMoveInfoMap.put(i+"",0); 
                     } 
                     //进行单元格合并 
                     ge.mergeCell(leve-1, 
                                          moveInfoMap.get("cellMove"), 
                                          mainRowCount-1, 
                                          moveInfoMap.get("cellMove")+maxMoveCell-1, 
                                          leveMoveInfoMap.get(leve+""), 
                                          moveInfoMap.get("cellMove"), 
                                          headvalue); 
                     moveInfoMap.put("currentIndex",moveInfoMap.get("currentIndex")+maxMoveCell); 
                     moveInfoMap.put("cellMove",moveInfoMap.get("cellMove")+maxMoveCell); 
                     //如果当前表应该占几行与最大行相同则说明该表头没有子表头 
                     if(mainRowCount == max) { 
                         // 如果该主表头没有子表则所有单元格只位移1个 
                         for(inti =1; i <= max; i++) { 
                             leveMoveInfoMap.put(i +"", leveMoveInfoMap.get(i +"") +1+customCol); 
                         } 
                         return; 
                     } 
                     /** 
                      * 开始处理后继表头的合并工作 
                      */ 
                     Map<String,Map<String,Integer>> repairMovePoint  =newHashMap<String,Map<String,Integer>>(); 
                     Map<String,Integer> _tem =newHashMap<String, Integer>(); 
                     _tem.put("mainRowCount", mainRowCount); 
                     repairMovePoint.put("mainRowCount",_tem); 
                    generAfterHeader(id, 
                                                pid, 
                                                headvalue, 
                                                maxMoveCell, 
                                                rs, 
                                                ps, 
                                                connection, 
                                                ge, 
                                                reportId, 
                                                leve, 
                                                currentIndex, 
                                                moveInfoMap, 
                                                leveMoveInfoMap, 
                                                repairMovePoint,0); 
                     //当一个主表头以级它下面的子表头的填冲合并工作完成后记录最大移动了多少列 
                    //因为下一个主表头的所有数据填冲都会心这个为开始 
                 for(inti=1;i<=max;i++){ 
                         leveMoveInfoMap.put(i +"", leveMoveInfoMap.get("allColMove")); 
                 } 
                 } 
                 /** 
                  * 获取当前层级元素应该合并多少个单元格 
                  * @param id 
                  * @param reportId 
                  * @param pid 
                  * @param ps 
                  * @param rs 
                  * @param connection 
                  * @return 
                  */ 
                 privatestaticintgetMaxCurrentLeveMegreCell(intid,intreportId,intpid, 
                         PreparedStatement ps, ResultSet rs, Connection connection) { 
                 /*  String maxCell = 
                             "with tem_header(id,pid,headervalue,leve) as(\n" + 
                                     "\n" + 
                                     "select id ,pid, headvalue,leve from dbo.headerinfo where pid = ? and report_id = ?  and id =?\n" + 
                                     "union all\n" + 
                                     "select  a.id,a.pid,a.headvalue,a.leve from dbo.headerinfo a ,tem_header b\n" + 
                                     "where a.pid = b.id\n" + 
                                     "\n and a.report_id = ?" + 
                                     "\n" + 
                                     "\n" + 
                                     ")\n" + 
                                     "select max(aa.a) from (select count(1) a from tem_header   group by leve) aa";*/ 
                     String maxCell = 
                                             "with tem_header(id,pid,headervalue,leve) as(\n"+ 
                                             "\n"+ 
                                             "            select id ,pid, headvalue,leve from dbo.headerinfo where pid = ? and report_id = ?  and id =?\n"+ 
                                             "            union all\n"+ 
                                             "            select  a.id,a.pid,a.headvalue,a.leve from dbo.headerinfo a ,tem_header b\n"+ 
                                             "            where a.pid = b.id\n"+ 
                                             "             and a.report_id = ?\n"+ 
                                             "\n"+ 
                                             "\n"+ 
                                             "            )\n"+ 
                                             "\n"+ 
                                             "\n"+ 
                                             "            select top 1 aa.a  from (select count(1) a ,leve le from tem_header   group by leve) aa order by aa.le desc"; 
                     try{ 
                         ps = connection.prepareStatement(maxCell); 
                         ps.setInt(1, pid); 
                         ps.setInt(2,reportId); 
                         ps.setInt(3, id); 
                         ps.setInt(4,reportId); 
                         ps.executeQuery(); 
                         rs = ps.getResultSet(); 
                         if(rs.next()){ 
                             return rs.getInt(1); 
                         } 
                     }catch(SQLException e1) { 
                         // TODO Auto-generated catch block 
                         e1.printStackTrace(); 
                     } 
                     return0; 
                 } 
             /** 
              * 
              * @param id 
              * @param pid 
              * @param leve 
              * @param leve2 
              * @param rs 
              * @param ps 
              * @param connection 
              * @return 
              */ 
             privatestaticResultSet getDirAfter(intid, 
                                                                    intpid, 
                                                                    intreportid, 
                                                                    intleve, 
                                                                    Connection connection, 
                                                                    PreparedStatement ps, 
                                                                    ResultSet rs) { 
                 String sql = 
                         "\n"+ 
                                 "with tem_header(id,pid,headervalue,leve) as(\n"+ 
                                 "\n"+ 
                                 "select id ,pid, headvalue,leve from dbo.headerinfo where pid = ? and report_id = ?\n"+ 
                                 "union all\n"+ 
                                 "select  a.id,a.pid,a.headvalue,a.leve from dbo.headerinfo a ,tem_header b\n"+ 
                                 "where a.pid = b.id\n"+ 
                                 "\n"+ 
                                 "and  a.report_id = ?"+ 
                                 "\n"+ 
                                 "\n"+ 
                                 ")\n"+ 
                                 "select * from tem_header t  where t.leve = ?"; 
                 try{ 
                     ps = connection.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); 
                     ps.setInt(1, id); 
                     ps.setInt(2,reportid); 
                     ps.setInt(4, leve+1); 
                     ps.setInt(3,reportid); 
                     ps.executeQuery(); 
                     returnps.getResultSet(); 
                 }catch(SQLException e) { 
                     // TODO Auto-generated catch block 
                     e.printStackTrace(); 
                 } 
                 returnnull; 
             } 
             privatestaticvoidgetMax(intid, 
                     intpid, 
                     ResultSet rs, 
                     PreparedStatement ps, 
                     Connection connection, 
                     intreportId, 
                     intleve, 
                     Map<String, Integer> moveInfoMap, 
                     intcurrentMaxLeve){ 
                                     /** 
                                      * 处理多级表头的合并 
                                      */ 
                             //但到表头的直接后继表头 
                             rs = getDirAfter(id,pid,reportId,leve,connection,ps,rs); 
                             /*try { 
                                 if(!rs.next()&&leve!=3){ 
                                 if(moveInfoMap.get("addOtherCell")==null){ 
                                     moveInfoMap.put("addOtherCell", 0); 
                                     moveInfoMap.put("addOtherCell", moveInfoMap.get("addOtherCell")+1); 
                                 } 
                                 } 
                             } catch (SQLException e) { 
                                 // TODO Auto-generated catch block 
                                 e.printStackTrace(); 
                             }*/ 
                         //  rs = getDirAfter(id,pid,reportId,leve,connection,ps,rs); 
                             //存放直接后继的子头集合 
                             List<DataModel> listResultSets =newArrayList<DataModel>(); 
                             try{ 
                                 while(rs.next()){ 
                                 int_id= rs.getInt("id"); 
                                 int_pid = rs.getInt("pid"); 
                                 String _headvalue = rs.getString("headervalue"); 
                                 int_leve = rs.getInt("leve"); 
                                 //如果当前层级不是最深层 
                                 //判断该表头有没有直接后继节点 
                                 if(_leve<currentMaxLeve){ 
                                     ResultSet _rs = getDirAfter(_id,_pid,reportId,_leve,connection,ps,rs); 
                                     //如果没有直接后继节点 
                                     if(!_rs.next()){ 
                                         if(moveInfoMap.get("addOtherCell")==null){ 
                                             moveInfoMap.put("addOtherCell",0); 
                                             moveInfoMap.put("addOtherCell", moveInfoMap.get("addOtherCell")+1); 
                                         }else{ 
                                             moveInfoMap.put("addOtherCell", moveInfoMap.get("addOtherCell")+1); 
                                         } 
                                     } 
                                 } 
                                 DataModel dm =newDataModel(); 
                                 dm.setId(_id); 
                                 dm.setPid(_pid); 
                                 dm.setHeadvalue(_headvalue); 
                                 dm.setRs(rs);; 
                                 dm.setPs(ps); 
                                 dm.setConnection(connection); 
                                 dm.setReportId(reportId); 
                                 dm.setLeve(_leve); 
                                 dm.setMoveInfoMap(moveInfoMap); 
                                 listResultSets.add(dm); 
                             //  getDirAfter(_id, _pid, reportId, maxCurrentLeveMegreCell, connection, ps, rs); 
                                 //break; 
                             //  System.out.println("leve 2ID"+_id+"Pid"+_pid+"头名"+_headvalue+""+maxCurrentLeveMegreCell); 
                                 } 
                                 /** 
                                  * 判断该表的最终级别有几列 说明该表的合并列为多少 
                                  * 所有单元格的合并最终是看当前表头开始它的最终有多少个子表头 
                                  * 也就是最后一行 
                                  */ 
                                 if(listResultSets!=null&&listResultSets.size()>0){ 
                                     for(inti =0;i<listResultSets.size() ; i ++){ 
                                         DataModel dModel = listResultSets.get(i); 
                                         getMax(dModel.getId() , dModel.getPid(), 
                                                 dModel.getRs(), 
                                                 dModel.getPs(), 
                                                 dModel.getConnection(), 
                                                 dModel.getReportId(), dModel.getLeve(),dModel.getMoveInfoMap(), 
                                                 currentMaxLeve 
                                                 ); 
                                     } 
                                 } 
                             }catch(SQLException e2) { 
                                 // TODO Auto-generated catch block 
                                 e2.printStackTrace(); 
                             } 
             } 
             } 

5. [代码]这个是复杂表头Excle转Html带格式很赞 power by 网络上的开一个高手

             packagecom.jingjiu.util; 
             importjava.io.FileInputStream; 
             importjava.io.FileNotFoundException; 
             importjava.io.IOException; 
             importjava.math.BigDecimal; 
             importjava.text.DecimalFormat; 
             importjava.text.SimpleDateFormat; 
             importjava.util.Date; 
             importjava.util.Map; 
             importorg.apache.poi.hssf.usermodel.HSSFCell; 
             importorg.apache.poi.hssf.usermodel.HSSFCellStyle; 
             importorg.apache.poi.hssf.usermodel.HSSFDateUtil; 
             importorg.apache.poi.hssf.usermodel.HSSFFont; 
             importorg.apache.poi.hssf.usermodel.HSSFPalette; 
             importorg.apache.poi.hssf.usermodel.HSSFRow; 
             importorg.apache.poi.hssf.usermodel.HSSFSheet; 
             importorg.apache.poi.hssf.usermodel.HSSFWorkbook; 
             importorg.apache.poi.hssf.util.HSSFColor; 
             importorg.apache.poi.hssf.util.Region; 
             importorg.apache.poi.ss.usermodel.CellStyle; 
             importorg.apache.poi.ss.util.CellRangeAddress; 
             /** 
              * @author like 
              **/ 
             publicclassExcelShower { 
                 /** 
                  * SID. 
                  */ 
                 privatestaticfinallongserialVersionUID = -8344971443770122206L; 
                 /** 
                  * 读取 Excel 显示页面. 
                  * 
                  * @param properties 
                  * @return 
                  * @throws Exception 
                  */ 
                 publicstaticvoidmain(String[] args) { 
                     ExcelShower els =newExcelShower(); 
                     try{ 
                         els.read(null); 
                     }catch(Exception e) { 
                         // TODO Auto-generated catch block 
                         e.printStackTrace(); 
                     } 
                 } 
                 publicStringBuffer read(Map properties)throwsException { 
                     HSSFSheet sheet =null; 
                     StringBuffer lsb =newStringBuffer(); 
                     // String excelFileName = SojaProperties.getSojaRoot() + 
                     // "/page/readExcel/sale.xls"; 
                     String excelFileName ="e:/today.xls"; 
                     HSSFWorkbook workbook =newHSSFWorkbook(newFileInputStream( 
                             excelFileName));// 获整个Excel 
                     lsb = excleToHtml(workbook); 
                     returnlsb; 
                 } 
                 publicStringBuffer excleToHtml(HSSFWorkbook workbook) { 
                     HSSFSheet sheet; 
                     StringBuffer lsb =newStringBuffer(); 
                     try{ 
                         for(intsheetIndex =0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) { 
                             sheet = workbook.getSheetAt(sheetIndex);// 获所有的sheet 
                             String sheetName = workbook.getSheetName(sheetIndex);// sheetName 
                             if(workbook.getSheetAt(sheetIndex) !=null) { 
                                 sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet 
                                 if(sheet !=null) { 
                                     intfirstRowNum = sheet.getFirstRowNum();// 第一行 
                                     intlastRowNum = sheet.getLastRowNum();// 最后一行 
                                     // 构造Table 
                                     lsb.append("<table width=\"100%\" style=\"border:1px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;border-collapse:collapse;\">"); 
                                     for(introwNum = firstRowNum; rowNum <= lastRowNum; rowNum++) { 
                                         if(sheet.getRow(rowNum) !=null) {// 如果行不为空， 
                                             HSSFRow row = sheet.getRow(rowNum); 
                                             shortfirstCellNum = row.getFirstCellNum();// 该行的第一个单元格 
                                             shortlastCellNum = row.getLastCellNum();// 该行的最后一个单元格 
                                             intheight = (int) (row.getHeight() /15.625);// 行的高度 
                                             lsb.append("<tr height=\"" 
                                                     + height 
                                                     +"\" style=\"border:1px solid #000;border-width:0 1px 1px 0;margin:2px 0 2px 0;\">"); 
                                             for(shortcellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) {// 循环该行的每一个单元格 
                                                 HSSFCell cell = row.getCell(cellNum); 
                                                 if(cell !=null) { 
                                                     if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { 
                                                         continue; 
                                                     }else{ 
                                                         StringBuffer tdStyle =newStringBuffer( 
                                                                 "<td style=\"border:1px solid #000; border-width:0 1px 1px 0;margin:2px 0 2px 0; "); 
                                                         HSSFCellStyle cellStyle = cell 
                                                                 .getCellStyle(); 
                                                         HSSFPalette palette = workbook 
                                                                 .getCustomPalette();// 类HSSFPalette用于求颜色的国际标准形式 
                                                         HSSFColor hColor = palette 
                                                                 .getColor(cellStyle 
                                                                         .getFillForegroundColor()); 
                                                         HSSFColor hColor2 = palette 
                                                                 .getColor(cellStyle 
                                                                         .getFont(workbook) 
                                                                         .getColor()); 
                                                         String bgColor = convertToStardColor(hColor);// 背景颜色 
                                                         shortboldWeight = cellStyle 
                                                                 .getFont(workbook) 
                                                                 .getBoldweight();// 字体粗细 
                                                         shortfontHeight = (short) (cellStyle 
                                                                 .getFont(workbook) 
                                                                 .getFontHeight() /2);// 字体大小 
                                                         String fontColor = convertToStardColor(hColor2);// 字体颜色 
                                                         if(bgColor !=null 
                                                                 && !"".equals(bgColor 
                                                                         .trim())) { 
                                                             tdStyle.append(" background-color:" 
                                                                     + bgColor +"; "); 
                                                         } 
                                                         if(fontColor !=null 
                                                                 && !"".equals(fontColor 
                                                                         .trim())) { 
                                                             tdStyle.append(" color:" 
                                                                     + fontColor +"; "); 
                                                         } 
                                                         tdStyle.append(" font-weight:" 
                                                                 + boldWeight +"; "); 
                                                         tdStyle.append(" font-size: " 
                                                                 + fontHeight +"%;"); 
                                                         lsb.append(tdStyle +"\""); 
                                                         intwidth = (int) (sheet 
                                                                 .getColumnWidth(cellNum) /35.7);// 
                                                         intcellReginCol = getMergerCellRegionCol( 
                                                                 sheet, rowNum, cellNum);// 合并的列（solspan） 
                                                         intcellReginRow = getMergerCellRegionRow( 
                                                                 sheet, rowNum, cellNum);// 合并的行（rowspan） 
                                                         String align = convertAlignToHtml(cellStyle 
                                                                 .getAlignment());// 
                                                         String vAlign = convertVerticalAlignToHtml(cellStyle 
                                                                 .getVerticalAlignment()); 
                                                         lsb.append(" align=\""+ align 
                                                                 +"\" valign=\""+ vAlign 
                                                                 +"\" width=\""+ width 
                                                                 +"\" "); 
                                                         lsb.append(" colspan=\"" 
                                                                 + cellReginCol 
                                                                 +"\" rowspan=\"" 
                                                                 + cellReginRow +"\""); 
                                                         lsb.append(">"+ getCellValue(cell) 
                                                                 +"</td>"); 
                                                     } 
                                                 } 
                                             } 
                                             lsb.append("</tr>"); 
                                         } 
                                     } 
                                 } 
                             } 
                         } 
                     }catch(FileNotFoundException e) { 
                     }catch(IOException e) { 
                     } 
                     lsb.append("</table>"); 
                     returnlsb; 
                 } 
                 /** 
                  * 取得单元格的值 
                  * 
                  * @param cell 
                  * @return 
                  * @throws IOException 
                  */ 
                 privatestaticObject getCellValue(HSSFCell cell)throwsIOException { 
                     Object value =""; 
                     if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { 
                         value = cell.getRichStringCellValue().toString(); 
                     }elseif(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { 
                         if(HSSFDateUtil.isCellDateFormatted(cell)) { 
                             Date date = cell.getDateCellValue(); 
                             SimpleDateFormat sdf =newSimpleDateFormat("yyyy-MM-dd"); 
                             value = sdf.format(date); 
                         }else{ 
                             doublevalue_temp = (double) cell.getNumericCellValue(); 
                             BigDecimal bd =newBigDecimal(value_temp); 
                             BigDecimal bd1 = bd.setScale(3, bd.ROUND_HALF_UP); 
                             value = bd1.doubleValue(); 
                             /* 
                              * DecimalFormat format = new DecimalFormat("#0.###"); value = 
                              * format.format(cell.getNumericCellValue()); 
                              */ 
                         } 
                     } 
                     if(cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { 
                         value =""; 
                     } 
                     returnvalue; 
                 } 
                 /** 
                  * 判断单元格在不在合并单元格范围内，如果是，获取其合并的列数。 
                  * 
                  * @param sheet 
                  *            工作表 
                  * @param cellRow 
                  *            被判断的单元格的行号 
                  * @param cellCol 
                  *            被判断的单元格的列号 
                  * @return 
                  * @throws IOException 
                  */ 
                 privatestaticintgetMergerCellRegionCol(HSSFSheet sheet,intcellRow, 
                         intcellCol)throwsIOException { 
                     intretVal =0; 
                     intsheetMergerCount = sheet.getNumMergedRegions(); 
                     for(inti =0; i < sheetMergerCount; i++) { 
                         CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i); 
                         intfirstRow = cra.getFirstRow();// 合并单元格CELL起始行 
                         intfirstCol = cra.getFirstColumn();// 合并单元格CELL起始列 
                         intlastRow = cra.getLastRow();// 合并单元格CELL结束行 
                         intlastCol = cra.getLastColumn();// 合并单元格CELL结束列 
                         if(cellRow >= firstRow && cellRow <= lastRow) {// 判断该单元格是否是在合并单元格中 
                             if(cellCol >= firstCol && cellCol <= lastCol) { 
                                 retVal = lastCol - firstCol +1;// 得到合并的列数 
                                 break; 
                             } 
                         } 
                     } 
                     returnretVal; 
                 } 
                 /** 
                  * 判断单元格是否是合并的单格，如果是，获取其合并的行数。 
                  * 
                  * @param sheet 
                  *            表单 
                  * @param cellRow 
                  *            被判断的单元格的行号 
                  * @param cellCol 
                  *            被判断的单元格的列号 
                  * @return 
                  * @throws IOException 
                  */ 
                 privatestaticintgetMergerCellRegionRow(HSSFSheet sheet,intcellRow, 
                         intcellCol)throwsIOException { 
                     intretVal =0; 
                     intsheetMergerCount = sheet.getNumMergedRegions(); 
                     for(inti =0; i < sheetMergerCount; i++) { 
                         CellRangeAddress cra = (CellRangeAddress) sheet.getMergedRegion(i); 
                         intfirstRow = cra.getFirstRow();// 合并单元格CELL起始行 
                         intfirstCol = cra.getFirstColumn();// 合并单元格CELL起始列 
                         intlastRow = cra.getLastRow();// 合并单元格CELL结束行 
                         intlastCol = cra.getLastColumn();// 合并单元格CELL结束列 
                         if(cellRow >= firstRow && cellRow <= lastRow) {// 判断该单元格是否是在合并单元格中 
                             if(cellCol >= firstCol && cellCol <= lastCol) { 
                                 retVal = lastRow - firstRow +1;// 得到合并的行数 
                                 break; 
                             } 
                         } 
                     } 
                     returnretVal; 
                 } 
                 /** 
                  * 单元格背景色转换 
                  * 
                  * @param hc 
                  * @return 
                  */ 
                 privateString convertToStardColor(HSSFColor hc) { 
                     StringBuffer sb =newStringBuffer(""); 
                     if(hc !=null) { 
                         inta = HSSFColor.AUTOMATIC.index; 
                         intb = hc.getIndex(); 
                         if(a == b) { 
                             returnnull; 
                         } 
                         sb.append("#"); 
                         for(inti =0; i < hc.getTriplet().length; i++) { 
                             String str; 
                             String str_tmp = Integer.toHexString(hc.getTriplet()[i]); 
                             if(str_tmp !=null&& str_tmp.length() <2) { 
                                 str ="0"+ str_tmp; 
                             }else{ 
                                 str = str_tmp; 
                             } 
                             sb.append(str); 
                         } 
                     } 
                     returnsb.toString(); 
                 } 
                 /** 
                  * 单元格小平对齐 
                  * 
                  * @param alignment 
                  * @return 
                  */ 
                 privateString convertAlignToHtml(shortalignment) { 
                     String align ="left"; 
                     switch(alignment) { 
                     caseHSSFCellStyle.ALIGN_LEFT: 
                         align ="left"; 
                         break; 
                     caseHSSFCellStyle.ALIGN_CENTER: 
                         align ="center"; 
                         break; 
                     caseHSSFCellStyle.ALIGN_RIGHT: 
                         align ="right"; 
                         break; 
                     default: 
                         break; 
                     } 
                     returnalign; 
                 } 
                 /** 
                  * 单元格垂直对齐 
                  * 
                  * @param verticalAlignment 
                  * @return 
                  */ 
                 privateString convertVerticalAlignToHtml(shortverticalAlignment) { 
                     String valign ="middle"; 
                     switch(verticalAlignment) { 
                     caseHSSFCellStyle.VERTICAL_BOTTOM: 
                         valign ="bottom"; 
                         break; 
                     caseHSSFCellStyle.VERTICAL_CENTER: 
                         valign ="center"; 
                         break; 
                     caseHSSFCellStyle.VERTICAL_TOP: 
                         valign ="top"; 
                         break; 
                     default: 
                         break; 
                     } 
                     returnvalign; 
                 } 
             } 