All tables of Java Export database to Excel_java

The example of this article for everyone to share the Java database of a table all exported to excel in the method, for your reference, the specific contents are as follows

The first step: How to use POI to manipulate Excel

@Test public 
void Createxls () throws exception{
  //Declare a work thin
  hssfworkbook wb = new Hssfworkbook ();
  Declaration Table
  Hssfsheet sheet = wb.createsheet ("first table");
  Declaration Line
  Hssfrow row = Sheet.createrow (7);
  Declaration column
  Hssfcell cel = Row.createcell (3);
  Write Data
  cel.setcellvalue ("Good for You Too");
  
  FileOutputStream fileout = new FileOutputStream ("D:/a/b.xls");
  Wb.write (fileout);
  Fileout.close ();
 }

Step Two: export all tables for the specified database

Analysis:

1: How many tables are there in a database, and what is the table name?  ―――databasemetadate.getmetadate (). Gettables (Null,null,null,new string[]{table}); -The file name of Excel.

2: SELECT * operation for each table. -The name of each sheet.

3: Analysis table structure, rs.getmetadate (); resultsetmedated

4: Multiple columns, what is the name of the column. -The field name is the first line of information in the sheet.

5: Get the data for each row-after sheet the first row.

@Test public void Export () throws exception{//DECLARE database String dbname = "Focus" that needs to be exported;
  DECLARE book Hssfworkbook = new Hssfworkbook ();
  Gets the Connection, obtains the db metadata Connection con = datasourceutils.getconn ();
  Statement statemen Statement st = Con.createstatement ();
  St.execute ("use" +dbname);
  DatabaseMetaData DMD = Con.getmetadata ();
  Gets how many tables ResultSet rs = dmd.gettables (dbname,dbname,null,new string[]{"Table"}) in the database;
  Get all table names-is 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); DECLARE SQL String sql = "SELECT * from" +dbname+ "."
   +tablename;
   Query Data rs = st.executequery (SQL);
   According to the result of the query, the metadata of the result set is analyzed resultsetmetadata RSMD = Rs.getmetadata ();
   Gets how many rows of this query are int cols = Rsmd.getcolumncount ();
   Get all column names//create first row Hssfrow row = sheet.createrow (0); for (int i=0; i<cols;i++) {String colname = Rsmd.getcolumnname (i+1);
    Create a new column Hssfcell cell = Row.createcell (i);
   Writes the column name Cell.setcellvalue (colname);
   }//Traversal data int index = 1;
    while (Rs.next ()) {row = Sheet.createrow (index++);
     declare column for (int i=0;i<cols;i++) {String val = rs.getstring (i+1);
     Declaration column Hssfcell cel = Row.createcell (i);
    Put 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 learn.