How do I export a table from a database to Excel?

1. How can I get the information of the database through the meta data?
2. How do I generate Excel tables in Java?
3. Export the tables in the database to build an Excel case

How to get the information of the database through meta-data

Metadata: Data that describes the data

Two ways to use metadata in Java

• DatabaseMetaData information that can be obtained by connecting: Database software, all database names, table names in all databases, describing the metadata of the database
• ResultSetMetaData Get the table structure information: Get the table's column number types and attributes, and describe the metadata for the database tables

The use of DatabaseMetaData learning

@TestDatabaseMetaData information that can be obtained by connecting: Database software, all database names, table names in all databasesPublicvoid Databasemetadata_demo () throws exception{Write your own toolkit to get a database connection Connection con = Connutils4.getconn ();DatabaseMetaData DatabaseMetaData dbmt = Con.getmetadata () by connection;Database software name System.Out.println ("Database software Name:" +dbmt.getdatabaseproductname ());Get all database names ResultSet Rs =dbmt.getcatalogs (); List<string> Tablenames =new arraylist<string> (); while (Rs.next ()) {String tabname=rs.getstring ( "TABLE_CAT"); Tablenames.add (tabname); System. out.println ( "database name:" +tabname);} System. out.println ( "--------------");; //get a database Li man all table names---can specify the type of table rs = dbmt.gettables ( "ake", "ake", null, new string[]{" TABLE ", " VIEW "}); while (Rs.next ()) {System. Out.println ( "table name in database ake:" +rs.getstring ( "tabl" "_name");}}

The use of ResultSetMetaData learning

@Test//ResultSetMetaData Get the table structure information: Get the table column number type and properties public void Resultsetmetadata_demo2 () throws exception{// Write your own toolkit to get a database connection Connection con = ConnUtils4. Getconn (); String sql ="SELECT * FROM Ake.book"; StatementSt = Con. Createstatement (); ResultSet rs =St. executeQuery (SQL); ResultSetMetaData get ResultSetMetaData Rsmt by the return set of the query = RS. GetMetaData (); Gets the number of columns in the table int n =rsmt. getColumnCount (); Type---A column//getcolumntypename:int//The name of a medical//getcolumnname:id//The length of a column//getcolumndisplaysize:one for (int i=1; i<n;i++) {System. Out. println (RSMT. Gettablename (i) +"section of the table" +i+"Column description information"); System. Out. println ("Getcolumndisplaysize:" +rsmt. Getcolumndisplaysize (i)); System. Out. println ("Getcolumnlabel:" +rsmt. Getcolumnlabel (i)); System. Out. println ("getColumnName:" +rsmt. getColumnName (i)); System. Out. println ("Getcolumntype:" +rsmt. Getcolumntype (i)); System. Out. println ("Getcolumntypename:" +rsmt. Getcolumntypename (i)); System.out.println ( " Getprecision: "+rsmt.getprecision (i)) .out.println ( " Getscale: "+rsmt.getscale (i)) .out.println ( " Getschemaname: "+rsmt.getschemaname (i)) .out.println ( " ------------") .close ()

Take out all the contents of the Ake table ~ ~ ~

Take out all the contents of the Ake table ~ ~ ~PublicStaticvoidMain (string[] args) throws exception{Connection con = connutils4.getconn (); System.Out.println (con); DatabaseMetaData DBMT = Con.getmetadata ();Get all the Ake. All table names ResultSet Rs =dbmt.gettables ("Ake","Ake",NullNew string[]{"TABLE","VIEW"}); List<string> Tablenames =New Arraylist<string> ();while (Rs.next ()) {String TableName = rs.getstring ("table_name"); Tablenames.add (tablename); }for (String tablename:tablenames) {System.Out.println (tablename+"Table:");if (Tablename.equals ("img")) {Continue } String sql ="SELECT * from Ake." +tablename; Statement st = Con.createstatement (); ResultSet RS = st.executequery (sql); ResultSetMetaData rsmt = Rs.getmetadata ();//Get the number of columns int colnums = Rsmt.getcolumncount (); For (int i=1;i<=colnums;i++) { //Gets header information String colname = Rsmt.getcolumnname (i); System. out.print (colname+"\ t");} System. out.println (); While (Rs.next ()) {for (int i=1;i<=colnums;i++) { //Gets table information for System. Out.print (rs.getstring (i) +"\ t"); System. out.println ();} } con.close (); }

I put that to the tabular information output

How do I generate Excel tables in Java?

Need a plugin Kit

@Test public void Workbook_demo () throws exception{ //Create a worksheet-equivalent to a database Workbook book = new HSSFW Orkbook (); //A table in the database Sheet Sheet1 =book.createsheet ("Table 1"); //Row row row =sheet1.createrow (4); //Cells cell cell = Row.createcell (3); //Write Data Cell.setcellvalue ("Excel written in Java"); //Save to Silver plate Book.write ( new FileOutputStream ("D:a/a.xls"));}

to export a table in a database to build an Excel case

public staticvoid Main (String[] args) throws Exception {Import all the information in the database into the Excel table ~ Connection con = connutils4.getconn (); DatabaseMetaData DBMT = Con.getmetadata ();To get the names of all the databases through DatabaseMetaData list<String> Database_names =New arraylist<String> (); ResultSet rs =dbmt.getcatalogs ();while (Rs.next ()) {Database_names.add (rs.getstring ("Table_cat")); }DatabaseMetaData get all data table names int m =0;ForString database_name:database_names) {if (! Database_name.equals ("Ake")) {Continue }if (m++>=3) {Break// }A database for an Excel document ~ Workbook book =New Hssfworkbook (); rs = dbmt.gettables (database_name, database_name,NullNewstring[]{"TABLE","VIEW"});Encapsulates all table names list<String> Table_names =New arraylist<String> ();while (Rs.next ()) {Table_names.add (rs.getstring ("table_name")); }ForString table_name:table_names) {If"img". Equals (table_name) | |"Note". Equals (table_name)) {IMG Import error for binary fileContinue }Create a table Sheet Sheet = book.createsheet (table_name); Statement st = Con.createstatement ();String sql ="SELECT * from" +database_name+"." +table_name;SYSTEM.OUT.PRINTLN ("sql:" +sql); rs = st.executequery (SQL);Set header information Row Row1 = Sheet.createrow (0); ResultSetMetaData RSMD = Rs.getmetadata (); int colnum = Rsmd.getcolumncount ();for (int i=1;i<=colnum;i++) {String name = Rsmd.getcolumnname (i); Cell cell = Row1.createcell (i-1); Cell.setcellvalue (name); //System.out.print (name+ "\ t");} //System.out.println (); //Set table information int idx = 1; while (Rs.next ()) {Row row = Sheet.createrow (idx++); for (int i=1;i<=colnum;i++) { String str = rs.getstring (i); //System.out.print (str+ "\ t"); Cell cell = Row.createcell (i-1); Cell.setcellvalue (str);} //System.out.println ();} } book.write ( new FileOutputStream ( "d:a/" +database_name+". xls"));}}

The results of the implementation are successful!

How do I export a table from a database to Excel?