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

2016/04/01 08:58
阅读数 716

1. [代码]特别声明     

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

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

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
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);
         
    }
    /**
     * 保存生成的文件
     * @param 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并设置值
     * @param startRow
     * @param startCell
     * @param endRow
     * @param 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 网络上的开一个高手     

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
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;
    }
 
}
展开阅读全文
打赏
2
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
2
分享
返回顶部
顶部