This example shares with you how to export all tables of a database into Excel for your reference. The specific content is as follows
Step 1: How to operate Excel with POI
@Test public void createXls() throws Exception{ //Declare a workbook HSSFWorkbook wb = new HSSFWorkbook(); //Declare table HSSFSheet sheet = wb.createSheet("first table"); //Declare row HSSFRow row = sheet.createRow(7); //Declare column HSSFCell cel = row.createCell(3); //Write data cel.setCellValue("You are also good"); FileOutputStream fileOut = new FileOutputStream("d:/a/b.xls"); wb.write(fileOut); fileOut.close(); }Step 2: Export all tables of the specified database
analyze:
1: How many tables does a certain number database have and what is the table name? DataBaseMetadate.getMetadate().getTables(null,null,null,new String[]{Table}); - The file name of excel.
2: Perform a select * operation on each table. - The name of each sheet.
3: Analyze the table structure, rs.getMetadate(); ResultSetMedated
4: Multiple columns, what are the column names? - The field name is the first row information of the sheet.
5: Get the data of each row and put it on the first row of the sheet.
@Test public void export() throws Exception{ //Declare the database that needs to be exported String dbName = "focus"; //Declare book HSSFWorkbook book = new HSSFWorkbook(); //Get Connection, get db metadata Connection con = DataSourceUtils.getConn(); //Declare statement 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 - it 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); //Analyze the metadata of the result set based on the query ResultSetMetaData rsmd = rs.getMetaData(); //Get how many rows there are in this query int cols = rsmd.getColumnCount(); //Get 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 column name cell.setCellValue(colName); } //Transfer 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); //Declare column HSSFCell cel = row.createCell(i); //Post data cel.setCellValue(val); } } } con.close(); book.write(new FileOutputStream("d:/a/"+dbName+".xls")); }The above is all about this article, I hope it will be helpful to everyone's learning.