导出数据库中所有数据到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");
加强：有的数据库中不允许Result嵌套，所以需要把数据暂存到List中进行加强，提高兼容性
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 List<String> dbnameList=new ArrayList<String>();//数据库名 7 while (dnset.next()) {//遍历所有数据库 8 String dbName = dnset.getString("TABLE_CAT");//获取所有数据库名称 9 dbnameList.add(dbName); 10 } 11 12 for(String dbName:dbnameList) 13 { 14 List<String> tanameList=new ArrayList<String>();//数据库中表名 15 ResultSet tSet = dbmd.getTables(dbName, dbName, null,new String[] { "TABLE" }); 16 while (tSet.next()) {//遍历数据库中所有表 17 String tName = tSet.getString("TABLE_NAME"); 18 tanameList.add(tName); 19 } 20 21 stmt.execute("use " + dbName);// 22 for(String tName:tanameList) 23 { 24 String sql = "select * from " + tName; 25 Sheet sheet = wb.createSheet(tName);//为表创建一个sheet 26 Row row = sheet.createRow(0);// 27 ResultSet rSet = stmt.executeQuery(sql); 28 ResultSetMetaData rsmd = rSet.getMetaData(); 29 int count = rsmd.getColumnCount(); 30 List<String> list = new ArrayList<String>(); 31 for (int i = 0; i < count; i++) {//获取表头并保存到cell中 32 String name = rsmd.getColumnName(i + 1); 33 row.createCell(i).setCellValue(name); 34 list.add(name); 35 } 36 int i = 0; 37 while (rSet.next()) {//讲查询数据保存到cell中 38 i++; 39 int j = 0; 40 Row row2 = sheet.createRow(i); 41 for (String s : list) { 42 String value = rSet.getString(s); 43 Cell cell = row2.createCell(j); 44 cell.setCellValue(value); 45 j++; 46 } 47 } 48 FileOutputStream out = new FileOutputStream("d:/a.xls");//写入workbook 49 wb.write(out); 50 out.close(); 51 } 52 } 53 System.out.println("Success");
