文档章节

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

问题达人
 问题达人
发布于 2016/04/01 08:58
字数 4787
阅读 273
收藏 0

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;
    }
 
}

本文转载自:http://www.oschina.net/code/snippet_1424099_49530

问题达人
粉丝 14
博文 94
码字总数 87078
作品 0
昌平
程序员
私信 提问
Java报表比较之中国式报表(复杂报表)篇

Java报表工具,首先可以分成两大类:纯Java报表工具,和支持Java的报表工具。 支持Java的报表工具 我们所说的”支持”Java的报表工具.其实就是非Java的报表工具,但是可以在Java程序中调用....

敏捷商业智能
2012/08/01
685
2
请教有经验的同仁推荐合适的JAVA报表工具

公司的支付系统后台一直用birt报表生成统计报表,近日感觉birt对内存的利用很不好,速度很慢,且容易耗尽内存;所以看了下其他工具,网上有很多人推荐jasperReorts+ireport开发,可是发现ire...

honganan
2015/01/06
1K
6
UReport 2.1.5 发布,中式高性能报表引擎

中式高性能报表引擎 UReport 2.1.5 正式版已发布,更新内容: 修复设计器中斜表头手动调整坐标错位的问题 修复设计器二维码或条码无法定义表达式的BUG 修复在条件属性中设置列宽为0无法导出P...

youseries
2017/10/19
1K
0
Java解析excel工具easyexcel助你快速简单避免OOM[图]

Java解析excel工具easyexcel助你快速简单避免OOM[图] Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程...

原创小博客
2018/07/19
459
1
(JAVA)easyui datagrid 怎么动态获取到表头的名称?最好是在后台获取

网站是JAVA EE+ EASYUI 形式的,我现在动态或者静态方式创建了datagrid, 我想动态获取到表头的名称(最好是后台,因为我要做EXCEL导出datagrid的功能),下面是表格。 r然后给大家看一个我导...

KMSFan
2014/08/23
6.5K
1

没有更多内容

加载失败,请刷新页面

加载更多

作为一个(IT)程序员!聊天没有话题?试试这十二种技巧

首先呢?我是一名程序员,经常性和同事没话题。 因为每天都会有自己的任务要做,程序员对于其他行业来说;是相对来说比较忙的。你会经常看到程序员在发呆、调试密密麻麻代码、红色报错发呆;...

小英子wep
今天
14
0
【SpringBoot】产生背景及简介

一、SpringBoot介绍 Spring Boot 是由 Pivotal 团队提供的全新框架,其设计目的是用来简化新 Spring 应用的初始搭建以及开发过程,该框架使用了特定的方式来进行配置,从而使开发人员不再需要...

zw965
今天
5
0
简述并发编程分为三个核心问题:分工、同步、互斥。

总的来说,并发编程可以总结为三个核心问题:分工、同步、互斥。 所谓分工指的是如何高效地拆解任务并分配给线程,而同步指的是线程之间如何协作,互斥则是保证同一时刻只允许一个线程访问共...

dust8080
今天
6
0
OSChina 周四乱弹 —— 当你简历注水但还是找到了工作

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @花间小酌 :#今日歌曲推荐# 分享成龙的单曲《男儿当自强》。 《男儿当自强》- 成龙 手机党少年们想听歌,请使劲儿戳(这里) @hxg2016 :刚在...

小小编辑
今天
3.3K
22
靠写代码赚钱的一些门路

作者 @mezod 译者 @josephchang10 如今,通过自己的代码去赚钱变得越来越简单,不过对很多人来说依然还是很难,因为他们不知道有哪些门路。 今天给大家分享一个精彩的 GitHub 库,这个库整理...

高级农民工
昨天
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部