Export all tables in a database to excel (43) and excel43

Export all tables in a database to excel (43) and excel43

How to Operate excel. -- POI-apache. JXL -.

Step 1: How to Use POI to Operate Excel

@ Test public void createXls () throws Exception {// declare a work thin HSSFWorkbook wb = new HSSFWorkbook (); // declare the table HSSFSheet sheet = wb. createSheet ("first table"); // declare the row HSSFRow row = sheet. createRow (7); // declare the column HSSFCell cel = row. createCell (3); // write data to cel. setCellValue (""); FileOutputStream fileOut = new FileOutputStream ("d:/a/B .xls"); wb. write (fileOut); fileOut. close ();}

Step 2: export all tables of the specified database

Analysis:

1: How many tables are there in a database and what is the table name? -- DataBaseMetadate. getMetadate (). getTables (null, new String [] {Table});-excel file name.

2: select * for each table. -Name of each sheet.

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

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

5: Get the data of each row-put it after the first row of sheet.

@ Test public void export () throws Exception {// declare the database to be exported String dbName = "focus"; // declare book HSSFWorkbook book = new HSSFWorkbook (); // obtain the Connection and obtain the metadata of the database. Connection con = ceceutils. getConn (); // declare statemen Statement st = con. createStatement (); // st.exe cute ("use" + dbName); DatabaseMetaData dmd = con. getMetaData (); // obtain the number of tables in the database. getTables (dbName, dbName, null, new String [] {"TABLE "}); // obtain the names of all tables-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.exe cuteQuery (SQL); // analyze the result set metadata ResultSetMetaData rsmd = rs. getMetaData (); // obtain the number of rows in this query. int cols = rsmd. getColumnCount (); // obtain all column names // create the 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); // write the cell column name. setCellValue (colName);} // int index = 1 for data traversal; while (rs. next () {row = sheet. createRow (index ++); // declare the column for (int I = 0; I <cols; I ++) {String val = rs. getString (I + 1); // declare the HSSFCell cel = row. createCell (I); // store data cel. setCellValue (val) ;}} con. close (); book. write (new FileOutputStream ("d:/a/" + dbName + ". xls "));}