Java Imports database data into Excel

How to import General database data into Excel?? This is very confusing, today I found a way to achieve

Need to import a third-party package

Details such as the following:

It contains guidance documents, index.html contains how to read database files to Excel and Excel data into the database.

Mainly used in a package

Copy this package into the project and you will be able to.

Start with a simple version number Helloword

<span style= "White-space:pre" ></span>public void Mkexcel () throws exception{//first set up a good working book in memory Hssfworkbook Book =new Hssfworkbook ();//Build a good workbook hssfsheet sheet = book.createsheet ("Table i");//table sole name and form Hssfrow Row=sheet.createrow (4);// Create the number of rows Hssfcell Cell=row.createcell (4);//Create Columns Cell.setcellvalue ("Big Brother.") Come on!!
")//write data into the cell//in-memory setup is not possible. Required to write to file FileOutputStream out =new fileoutputstream ("A.xls"); Book.write (out);}

：

Using the simple example above, we can find that the file creating the XLS is similar to creating a table table dynamically in a Web page like clicking the Open link

1. Create a working book

2, thin build table

3. Table CCB

4. Row-built Columns

5, set the value in the column

With this simple introduction, we are now starting to import the contents of the database

1, we have to build the workbook from memory

<span style= "FONT-SIZE:18PX; White-space:pre; " ></span><span style= "FONT-SIZE:24PX;" >hssfworkbook book =new Hssfworkbook ();</span>

 

2, when creating the sheet. We need to specify sheet name, all need to use the meta-data to get the table name inside database

Connection con =hibernatefactory2.getcon ();//Database connection succeeded

DatabaseMetaData dmeta=con.getmetadata ();//meta-data

<span style= "FONT-SIZE:24PX;" >resultset rs=dmeta.getcatalogs ();//Get all the databases, but for convenience. We directly specify the database name </span>

3. Gets the table name.

<span style= "White-space:pre" ></span>string dname= "Hncu";//get the table name inside the database, return resultset all in the list resultset Rs=dmeta.gettables (dname, dname, NULL, new string[]{"TABLE"});//Put in list for sheet name list<string > list =new Arraylist<string> (); while (Rs.next ()) {<span style= "white-space:pre;" ></span>list.add (rs.getstring ("table_name"));//Find the table name and put it in list}

 4, start building the sheet form. Using elements to generate a table header at the same time

<span style= "White-space:pre" ><span style= "FONT-SIZE:24PX;" ></span></span><span style= "FONT-SIZE:24PX;" >for (String sheetname:list) {Hssfsheet sheet=book.createsheet (sheetname);//Set form and table sole name String sql= "SELECT * from" + Dname+ "." +sheetname;//with the name of the database. Table name to get statement St =con.createstatement (); ResultSet rs2=st.executequery (SQL); ResultSetMetaData rsmd=rs2.getmetadata (); int col =rsmd.getcolumncount ();//Get how many columns//generate the first row of the form, the table header Hssfrow row0 = Sheet.createrow (0); for (int i=0;i<col;i++) {Hssfcell cell = Row0.createcell (i); Cell.setcellvalue ( Rsmd.getcolumnname (i+1));} </span>

5, create rows and columns, and set the values for the row and column

<span style= "White-space:pre" ></span>int idx=1;//used to generate rows//First part finished//finished part two while (Rs2.next ()) {Hssfrow row = Sheet.createrow (idx++);//The line is created. Start create column for (int i =0; i<col;i++) {Hssfcell cell =row.createcell (i); Cell.setcellvalue (Rs2.getstring (i+1));}}

6, save to file

<span style= "White-space:pre" ></span><span style= "FONT-SIZE:24PX;" >fileoutputstream out =new fileoutputstream ("Db.xls"); <span style= "White-space:pre" ></span>  Book.write (out);//use book to write </span>

code that can be executed directly

public void Test3 () throws exception{/* * To get the data inside the database first. We have to use metadata to get what we want to close. * If executed directly, it is necessary to set up a database, view the generated files in and root folder src sibling *///first from the memory of the data set up Hssfworkbook book =new Hssfworkbook (); Connection con =hibernatefactory2.getcon ();//Database Connection DatabaseMetaData dmeta=con.getmetadata ();//element according to//resultset rs= Dmeta.getcatalogs ();//Get all the database string dname= "Hncu";//get the table name inside the database, return resultset all in the list resultset rs=dmeta.gettables ( Dname, dname, NULL, new string[]{"TABLE"});//Put in list for sheet name list<string > list =new arraylist<string> () ; while (Rs.next ()) {List.add (rs.getstring ("table_name"));//Find the table name and put it in List}for (String sheetname:list) {Hssfsheet Sheet=book.createsheet (sheetname);//Set form and table sole name string sql= "select * from" +dname+ "." +sheetname;//with the name of the database. Table name to get statement St =con.createstatement (); ResultSet rs2=st.executequery (SQL); ResultSetMetaData rsmd=rs2.getmetadata (); int col =rsmd.getcolumncount ();//Get how many columns//generate the first line of the form hssfrow row0 = Sheet.createrow (0); for (int i=0;i<col;i++) {Hssfcell cell = Row0.createcell (i); Cell.setcellvalue (RSMD.getcolumnname (i+1));} int idx=1;//First part finished//finished the second part while (Rs2.next ()) {Hssfrow row =sheet.createrow (idx++);//A row was created. Start create column for (int i =0; i<col;i++) {Hssfcell cell =row.createcell (i); Cell.setcellvalue (Rs2.getstring (i+1));}} FileOutputStream out =new fileoutputstream ("Db.xls"), Book.write (out);

That's how you import all the data into Excel.

：

Data in the database

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity /center ">

Data for the exported Excel

Can be completely exported, but at the time of the experiment. A database type of BLOB type cannot be exported because the data for each cell in Excel does not support that large.

What is the problem, we put together to discuss together. Thank you

Java Imports database data into Excel