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