导出数据库中所有数据到Excle中

 1 Workbook wb = new HSSFWorkbook();//创建工作簿
 2         Connection conn = DataSourceUtils.getDataSource().getConnection();//获取数据库连接
 3         Statement stmt = conn.createStatement();
 4         DatabaseMetaData dbmd = conn.getMetaData();//获取结果集conn的所有信息
 5         ResultSet dnset = dbmd.getCatalogs();//获取数据库目录
 6         while (dnset.next()) {//遍历所有数据库
 7             String dbName = dnset.getString("TABLE_CAT");//获取所有数据库名称
 8             {
 9                 ResultSet tSet = dbmd.getTables(dbName, dbName, null,new String[] { "TABLE" });
10                 while (tSet.next()) {//遍历数据库中所有表
11                     String tName = tSet.getString("TABLE_NAME");
12                     stmt.execute("use " + dbName);//
13                     String sql = "select * from " + tName;
14                     Sheet sheet = wb.createSheet(tName);//为表创建一个sheet
15                     Row row = sheet.createRow(0);//
16                     ResultSet rSet = stmt.executeQuery(sql);
17                     ResultSetMetaData rsmd = rSet.getMetaData();
18                     int count = rsmd.getColumnCount();
19                     List<String> list = new ArrayList<String>();
20                     for (int i = 0; i < count; i++) {//获取表头并保存到cell中
21                         String name = rsmd.getColumnName(i + 1);
22                         row.createCell(i).setCellValue(name);
23                         list.add(name);
24                     }
25                     int i = 0;
26                     while (rSet.next()) {//讲查询数据保存到cell中
27                         i++;
28                         int j = 0;
29                         Row row2 = sheet.createRow(i);
30                         for (String s : list) {
31                             String value = rSet.getString(s);
32                             Cell cell = row2.createCell(j);
33                             cell.setCellValue(value);
34                             j++;
35                         }
36                     }
37                     FileOutputStream out = new FileOutputStream("d:/a.xls");//写入workbook
38                     wb.write(out);
39                     out.close();
40                 }
41             }
42         }
43         
44         System.out.println("Success");