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

 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");

导出数据库中所有数据到Excle中文章来源地址https://www.yii666.com/article/764272.html文章地址https://www.yii666.com/article/764272.html网址:yii666.com文章来源地址:https://www.yii666.com/article/764272.html

导出数据库中所有数据到Excle中网址:yii666.com<

加强:有的数据库中不允许Result嵌套,所以需要把数据暂存到List中进行加强,提高兼容性

 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");

版权声明:本文内容来源于网络,版权归原作者所有,此博客不拥有其著作权,亦不承担相应法律责任。文本页已经标记具体来源原文地址,请点击原文查看来源网址,站内文章以及资源内容站长不承诺其正确性,如侵犯了您的权益,请联系站长如有侵权请联系站长,将立刻删除

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信图片_20190322181744_03.jpg

微信扫一扫打赏

请作者喝杯咖啡吧~

支付宝扫一扫领取红包,优惠每天领

二维码1

zhifubaohongbao.png

二维码2

zhifubaohongbao2.png