将数据库中的数据导入到excel中

        从数据库中读取数据导入到excel中，如果数据量小，的确很简单，直接用POI输出就OK了，可是我这边有＞6W的数据要输出，java运行了直接溢出了，于是前辈给了个取巧的办法，调用java命令并设置vm参数-Xms1024M -Xmx1024M，呵呵，这样很大了，那么下面就是着手运行这个命令就行了，首先把这个导出的方法写在要调用的类的main方法中，然后在我的网站应用的Action中去执行一个命令脚本，windows下用bat，linux下用sh，由于要执行脚本，在linux下要注意就是要把你的应用读写权限打开。那么先从调用的命令开始
    1.设置好参数，就是main中的String[] args参数比如输出excel的路径
                String excelPath = Struts2Utils.getSession().getServletContext().getRealPath("/upload/doc/export.xls");
    2.生成好bat或者是sh文件
                String osName = System.getProperty("os.name");//获取操作系统
                if (osName.toLowerCase().indexOf("windows") != -1) {
                        filePath = Struts2Utils.getSession().getServletContext().getRealPath("/WEB-INF/classes/excelDb.bat");
                } 
                else
               {
                       filePath = Struts2Utils.getSession().getServletContext().getRealPath("/WEB-INF/classes/excelDb.sh");
               }

              String cmdLine ="";
              cmdLine+="cd "+classPath+" \r\n";
              cmdLine+=" java -Xms1024M -Xmx1024M -Djava.ext.dirs="+dirPath+" com.twc.common.utils.command.Command "+excelPath；
              try {
                       FileUtils.writeFile(new File(filePath), cmdLine.getBytes());//生成文件
              } catch (IOException e1) {
                   e1.printStackTrace();
              }
    3.调用这个bat或者是sh文件
      

        Process proc = null;
        try {
                 String result = "";
                 proc = Runtime.getRuntime().exec(filePath);
                 BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(proc.getInputStream()));
                 while ((result = bufferedReader.readLine()) != null)
                 System.out.println(result);

                 if(proc.waitFor() != 0) {
                            logger.error("Can't execute: " + cmdLine);
                 }
                if(error.length() > 0) {
                            logger.warn(error);
                 }
        } catch (Exception e) {
                            logger.error(e.getMessage(), e);
        } finally {
                            proc.destroy();
                            proc = null;
        }

这样Action的使命就完成了，那么接下来就是实现调用类的导出excel方法了(我的调用类是Command类)下面的方法是main中的，我截取了最重要的出来

 1  List < Vector >  result  =   new  ArrayList < Vector > ();
 2              dba.open();
 3               try  {
 4              
 5                  String sql  =  sb.toString();
 6                  String[] arrFields  =  fields.split( " , " );
 7                   int  size  =  arrFields.length;
 8                  dba.query(sql);
 9                   while  (dba.rs  !=   null   &&  dba.rs.next()) {
10              
11                      Vector cell  =   new  Vector();
12                       for ( int  i = 1 ;i < size + 1 ;i ++ )
13                      {
14                          cell.add(dba.rs.getString(i));
15                      }
16                      result.add(cell);
17                  } // 从数据库的到数据存放在result中
18              }  catch  (SQLException e) {
19                  
20              }
21              PoiUtils e   =     new   PoiUtils(excelPath);
22               int  cnt = 1 ;
23               int  size  =  result.size();
24               int  block  =  size / Command.BLOCK;
25               if (block < 1 ) // 判断数据是否超出excel极限（excel的一个sheet最多存放6w多行数据)
26              {
27                   // 直接把所有数据输出到一个sheet中去
28                  e.createSheet( 0 );
29                  e.createRow( 0 );
30                   for ( int  j = 0 ;j < arrField.length;j ++ )
31                  {
32                      
33                          e.setCell(j, arrField[j]);
34                      
35                  }
36                   for ( int  l = 1 ;l < size + 1 ;l ++ )    
37                  {        e.createRow(l);
38                           for ( int  j = 0 ;j < arrField.length;j ++ )
39                          {
40                               if (result.get(l - 1 ).get(j) != null )
41                              {
42                                  e.setCell(j, result.get(l - 1 ).get(j).toString());
43                              }
44                               else
45                              {
46                                  e.setCell(j,  "" );
47                              }
48                          }
49                  }
50              }
51               else
52              {
53                   // 如果超出了范围则把数据分别输出到k个sheet中去
54                   for ( int  k = 0 ;k < block;k ++ )
55                  {
56                       // 第k+1个sheet
57                      e.createSheet(k + 1 );
58                       // 数据字段名
59                      e.createRow( 0 );
60                       for ( int  j = 0 ;j < arrField.length;j ++ )
61                      {
62                          
63                              e.setCell(j, arrField[j]);
64                          
65                      }
66                       int  start  =  k * Command.BLOCK;
67                       int  end  =  Command.BLOCK * (k + 1 );
68                       for ( int  l = start;l < end;l ++ )     // 输出到excel中
69                      {        
70                              e.createRow(cnt);
71                              cnt ++ ;
72                               if (cnt == Command.BLOCK + 1 )cnt  =   1 ;
73                               for ( int  j = 0 ;j < arrField.length;j ++ )
74                              {
75                                   if (result.get(l).get(j) != null )
76                                  {
77                                      e.setCell(j, result.get(l).get(j).toString());
78                                  }
79                                   else
80                                  {
81                                      e.setCell(j,  "" );
82                                  }
83                              }
84                      }
85                  }
86              }
87              
88                try     {
89                      e.exportXLS(); // 输出结果
90                      System.out.println( " Exprot Success! " );
91                  }    catch   (XLSException e1)   {
92                  
93                  }