数据库多张表导出到excel

 数据库多张表导出到excel

public static void export() throws Exception{

  //声明需要导出的数据库

  String dbName = "hdcloude";

  //声明book

  HSSFWorkbook book = new HSSFWorkbook();

  //获取Connection,获取db的元数据

  //Connection con = DataSourceUtils.getConn();

  Connection con =DriverManager.getConnection("jdbc:MySQL://127.0.0.1:3306", "root", "root");

  //声明statemen

  Statement st = con.createStatement();

  //st.execute("use "+dbName); 

  DatabaseMetaData dmd = con.getMetaData();

  //获取数据库有多少表

  ResultSet rs = dmd.getTables(dbName,dbName,null,new String[]{"TABLE"});

  //获取所有表名　－　就是一个sheet

  List<String> tables = new ArrayList<String>();

  while(rs.next()){

  String tableName = rs.getString("TABLE_NAME");

  System.out.println(tableName);

  tables.add(tableName);

  }

  for(String tableName:tables){

  HSSFSheet sheet = book.createSheet(tableName);

  //声明sql

  String sql = "select * from "+dbName+"."+tableName;

  //查询数据

  rs = st.executeQuery(sql);

  //根据查询的结果，分析结果集的元数据

  ResultSetMetaData rsmd = rs.getMetaData();

  //获取这个查询有多少行

  int cols = rsmd.getColumnCount();

  //获取所有列名

  //创建第一行

  HSSFRow row = sheet.createRow(0);

  for(int i=0;i<cols;i++){

    String colName = rsmd.getColumnName(i+1);

    //创建一个新的列

    HSSFCell cell = row.createCell(i);

    //写入列名

    cell.setCellValue(colName);

  }

  //遍历数据

  int index = 1;

  while(rs.next()){

      row = sheet.createRow(index++);

    //声明列

    for(int i=0;i<cols;i++){

    String val = rs.getString(i+1);

    //声明列

    HSSFCell cel = row.createCell(i);

    //放数据

    cel.setCellValue(val);

    }

  }

  }

  con.close();

  book.write(new FileOutputStream("\\"+dbName+".xls"));

}