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