Workbook wb = new HSSFWorkbook();//创建工作簿
Connection conn = DataSourceUtils.getDataSource().getConnection();//获取数据库连接
Statement stmt = conn.createStatement();
DatabaseMetaData dbmd = conn.getMetaData();//获取结果集conn的所有信息
ResultSet dnset = dbmd.getCatalogs();//获取数据库目录
while (dnset.next()) {//遍历所有数据库
String dbName = dnset.getString("TABLE_CAT");//获取所有数据库名称
{
ResultSet tSet = dbmd.getTables(dbName, dbName, null,new String[] { "TABLE" });
while (tSet.next()) {//遍历数据库中所有表
String tName = tSet.getString("TABLE_NAME");
stmt.execute("use " + dbName);//
String sql = "select * from " + tName;
Sheet sheet = wb.createSheet(tName);//为表创建一个sheet
Row row = sheet.createRow();//
ResultSet rSet = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rSet.getMetaData();
int count = rsmd.getColumnCount();
List<String> list = new ArrayList<String>();
for (int i = ; i < count; i++) {//获取表头并保存到cell中
String name = rsmd.getColumnName(i + );
row.createCell(i).setCellValue(name);
list.add(name);
}
int i = ;
while (rSet.next()) {//讲查询数据保存到cell中
i++;
int j = ;
Row row2 = sheet.createRow(i);
for (String s : list) {
String value = rSet.getString(s);
Cell cell = row2.createCell(j);
cell.setCellValue(value);
j++;
}
}
FileOutputStream out = new FileOutputStream("d:/a.xls");//写入workbook
wb.write(out);
out.close();
}
}
} System.out.println("Success");
网址:yii666.com文章来源地址:https://www.yii666.com/article/764272.html
加强：有的数据库中不允许Result嵌套，所以需要把数据暂存到List中进行加强，提高兼容性文章地址https://www.yii666.com/article/764272.html
Workbook wb = new HSSFWorkbook();//创建工作簿
Connection conn = DataSourceUtils.getDataSource().getConnection();//获取数据库连接
Statement stmt = conn.createStatement();
DatabaseMetaData dbmd = conn.getMetaData();//获取结果集conn的所有信息
ResultSet dnset = dbmd.getCatalogs();//获取数据库目录
List<String> dbnameList=new ArrayList<String>();//数据库名
while (dnset.next()) {//遍历所有数据库
String dbName = dnset.getString("TABLE_CAT");//获取所有数据库名称
dbnameList.add(dbName);
} for(String dbName:dbnameList)
{
List<String> tanameList=new ArrayList<String>();//数据库中表名
ResultSet tSet = dbmd.getTables(dbName, dbName, null,new String[] { "TABLE" });
while (tSet.next()) {//遍历数据库中所有表
String tName = tSet.getString("TABLE_NAME");
tanameList.add(tName);
} stmt.execute("use " + dbName);//
for(String tName:tanameList)
{
String sql = "select * from " + tName;
Sheet sheet = wb.createSheet(tName);//为表创建一个sheet
Row row = sheet.createRow(0);//
ResultSet rSet = stmt.executeQuery(sql);
ResultSetMetaData rsmd = rSet.getMetaData();
int count = rsmd.getColumnCount();
List<String> list = new ArrayList<String>();
for (int i = 0; i < count; i++) {//获取表头并保存到cell中
String name = rsmd.getColumnName(i + 1);
row.createCell(i).setCellValue(name);
list.add(name);
}
int i = 0;
while (rSet.next()) {//讲查询数据保存到cell中
i++;
int j = 0;
Row row2 = sheet.createRow(i);
for (String s : list) {
String value = rSet.getString(s);
Cell cell = row2.createCell(j);
cell.setCellValue(value);
j++;
}
}
FileOutputStream out = new FileOutputStream("d:/a.xls");//写入workbook
wb.write(out);
out.close();
}
}
System.out.println("Success");