java exports all tables of the database to excel

The example of this article shares the method of java to export all the tables of a database to excel for your reference. The details are as follows

step 1: how do operate Excel with POI

@Test 
public void createXls() throws Exception{
  // The statement 1 A work sheet 
  HSSFWorkbook wb = new HSSFWorkbook();
  // The statement table 
  HSSFSheet sheet = wb.createSheet(" The first 1 A table ");
  // The statement line 
  HSSFRow row = sheet.createRow(7);
  // The statement column 
  HSSFCell cel = row.createCell(3);
  // Write data 
  cel.setCellValue(" Are you all right ");
  
  FileOutputStream fileOut = new FileOutputStream("d:/a/b.xls");
  wb.write(fileOut);
  fileOut.close();
 }

step 2: exports all the tables for the specified database

Analysis:

            1: how many tables does a certain number database have and what are the table names? � � � DataBaseMetadate. getMetadate (.) getTables (null null, null, new String [] {Table}); File name for  -excel.

2: perform select * operations on each table. - name of each sheet.

3: analysis of table structure, rs.getMetadate (); ResultSetMedated

4: multiple columns, what is the column name? - the field name is the first line of sheet information.

5: get the data in each row after the first row of sheet.

@Test
 public void export() throws Exception{
  // Declare the database to be exported 
  String dbName = "focus";
  // The statement book
  HSSFWorkbook book = new HSSFWorkbook();
  // To obtain Connection, To obtain db The metadata 
  Connection con = DataSourceUtils.getConn();
  // The statement statemen
  Statement st = con.createStatement();
  //st.execute("use "+dbName);
  DatabaseMetaData dmd = con.getMetaData();
  // Gets how many tables the database has 
  ResultSet rs = dmd.getTables(dbName,dbName,null,new String[]{"TABLE"});
  // Get all the table names - that's it 1 a sheet
  List<String> tables = new ArrayList<String>();
  while(rs.next()){
   String tableName = rs.getString("TABLE_NAME");
   tables.add(tableName);
  }
  for(String tableName:tables){
   HSSFSheet sheet = book.createSheet(tableName);
   // The statement sql
   String sql = "select * from "+dbName+"."+tableName;
   // Query data 
   rs = st.executeQuery(sql);
   // The metadata of the result set is analyzed based on the results of the query 
   ResultSetMetaData rsmd = rs.getMetaData();
   // Gets how many rows the query has 
   int cols = rsmd.getColumnCount();
   // Gets all column names 
   // Create the first 1 line 
   HSSFRow row = sheet.createRow(0);
   for(int i=0;i<cols;i++){
    String colName = rsmd.getColumnName(i+1);
    // create 1 A new column 
    HSSFCell cell = row.createCell(i);
    // Write the column name 
    cell.setCellValue(colName);
   }
   // Through the data 
   int index = 1;
   while(rs.next()){
    row = sheet.createRow(index++);
    // The statement column 
    for(int i=0;i<cols;i++){
     String val = rs.getString(i+1);
     // The statement column 
     HSSFCell cel = row.createCell(i);
     // Put the data 
     cel.setCellValue(val);
    }
   }
  }
  con.close();
  book.write(new FileOutputStream("d:/a/"+dbName+".xls"));
 }

The above is the entire content of this article, I hope to help you with your study.