首页 > 代码库 > 导出数据库中所有数据到Excle中

导出数据库中所有数据到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);//为表创建一个sheet15                     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 =http://www.mamicode.com/ 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");//写入workbook38                     wb.write(out);39                     out.close();40                 }41             }42         }43         44         System.out.println("Success");

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