将数据库数据导到excel(xls)中

import java.io.FileOutputStream; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import cn.hncu.proxy.ConnsUtil; public class DatabaseToExcelUtil { public static void main(String[] args) { try { Connection con = ConnsUtil.getConnection(); DatabaseMetaData dmd = con.getMetaData(); ResultSet rs = dmd.getCatalogs(); List<String>dbNames = new ArrayList<String>(); while(rs.next()){ dbNames.add(rs.getString(1)); } con.close(); writeToExcel(dbNames); } catch (Exception e) { e.printStackTrace(); } } private static void writeToExcel(List<String> dbNames) throws Exception { Connection con = ConnsUtil.getConnection(); for(String dbName:dbNames){ con.createStatement().execute("use "+dbName); ResultSet rs = con.getMetaData().getTables(dbName, dbName, null,new String[]{"TABLE"}); List<String>tbNames = new ArrayList<String>(); while(rs.next()){ tbNames.add(rs.getString("TABLE_NAME")); } if(tbNames.size()==0) continue; HSSFWorkbook book = new HSSFWorkbook(); for(String tbName:tbNames){ Sheet sheet = book.createSheet(tbName); String sql = "select * from "+dbName+"."+tbName; ResultSet res = con.createStatement().executeQuery(sql); ResultSetMetaData rsmd = res.getMetaData(); int cols = rsmd.getColumnCount(); Row row = sheet.createRow(0); for(int i=0;i<cols;i++){ row.createCell(i).setCellValue(rsmd.getCatalogName(i+1)); } int index = 1; while(res.next()){ Row row2 = sheet.createRow(index++); for(int i=0;i<cols;i++){ row2.createCell(i).setCellValue(res.getString(i+1)); } } } FileOutputStream fos = new FileOutputStream(dbName+".xls"); book.write(fos); } } }