Database multiple tables exported to Excel

Database multiple tables exported to Excel

public static void Export () throws exception{

Declaring a database that needs to be exported

String dbName = "Hdcloude";

Declaring book

Hssfworkbook book = new Hssfworkbook ();

Get connection, get the metadata for DB

Connection con = datasourceutils.getconn ();

Connection con =drivermanager.getconnection ("jdbc:mysql://127.0.0.1:3306", "root", "root");

Statement Statemen

Statement st = Con.createstatement ();

St.execute ("use" +dbname);

DatabaseMetaData DMD = Con.getmetadata ();

Get how many tables the database has

ResultSet rs = dmd.gettables (dbname,dbname,null,new string[]{"TABLE"});

Get all table names-just a 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);

Declaring SQL

String sql = "SELECT * from" +dbname+ "." +tablename;

Querying data

rs = st.executequery (SQL);

Analyze the metadata of the result set based on the results of the query

ResultSetMetaData RSMD = Rs.getmetadata ();

Get how many rows this query has

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

To create a new column

Hssfcell cell = Row.createcell (i);

Write column Name

Cell.setcellvalue (colname);

}

Traversing data

int index = 1;

while (Rs.next ()) {

row = Sheet.createrow (index++);

declaring columns

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

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

declaring columns

Hssfcell cel = Row.createcell (i);

Put data

Cel.setcellvalue (Val);

}

}

}

Con.close ();

Book.write (new FileOutputStream ("\ \" +dbname+ ". xls"));

}

Database multiple tables exported to Excel