How to import data from databases to Excel using poi in Java

Java uses poi to import data in the database to Excel

Effect:

Import the poi package to the path of the project before use. Note that you only need to import the poi package. After downloading the package, there are three jar packages.

Core code:

Connect to the database: DBConnection. java

Copy codeThe Code is as follows: package org. xg. db;
Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. PreparedStatement;
Import java. SQL. ResultSet;
Import java. SQL. SQLException;
Public class DBConnection {
Private final String DBUrl = "jdbc: mysql: // localhost: 3306/notebook ";
Private final String DBDriver = "com. mysql. jdbc. Driver ";
Private final String username = "root ";
Private final String password = "riskfitfeng ";
Private Connection con;
Public DBConnection ()
{
Try {
Class. forName (DBDriver );
Con = DriverManager. getConnection (DBUrl, username, password );
} Catch (Exception e ){
// TODO Auto-generated catch block
E. printStackTrace ();
}
}
Public Connection getDB ()
{
Return con;
}
Public void closeDb (ResultSet rs, PreparedStatement ps)
{
If (rs! = Null)
{
Try {
Rs. close ();
} Catch (SQLException e ){
// TODO Auto-generated catch block

E. printStackTrace ();
}
}
If (ps! = Null)
{
Try {
Ps. close ();
} Catch (SQLException e ){
// TODO Auto-generated catch block
E. printStackTrace ();
}
}
}
}

Import excel class: MySql2Excel. java

Package org. xg. db;
Import java. io. FileOutputStream;
Import java. io. OutputStream;
Import java. SQL. Connection;
Import java. SQL. ResultSet;
Import org. apache. poi. hssf. usermodel. HSSFCell;
Import org. apache. poi. hssf. usermodel. HSSFRichTextString;
Import org. apache. poi. hssf. usermodel. HSSFRow;
Import org. apache. poi. hssf. usermodel. HSSFSheet;
Import org. apache. poi. hssf. usermodel. HSSFWorkbook;
Public class MySql2Excel {
Public MySql2Excel () throws Exception
{
Connection con = null;
DBConnection db = new DBConnection ();
Con = db. getDB ();
String SQL = "select * from students ";
ResultSet rs = con.createstatement(cmd.exe cuteQuery (SQL );
// Obtain the total number of Columns
Int CountColumnNum = rs. getMetaData (). getColumnCount ();
Int I = 1;
// Create an Excel document
HSSFWorkbook wb = new HSSFWorkbook ();
// Sheet corresponds to a work page
HSSFSheet sheet = wb. createSheet ("data in student table ");
HSSFRow firstrow = sheet. createRow (0); // start with a row whose subscript is 0
HSSFCell [] firstcell = new HSSFCell [CountColumnNum];
String [] names = new String [CountColumnNum];
Names [0] = "ID ";
Names [1] = "student ID ";
Names [2] = "name ";
Names [3] = "gender ";
Names [4] = "class ";
For (int j = 0; j <CountColumnNum; j ++ ){
Firstcell [j] = firstrow. createCell (short) j );
Firstcell [j]. setCellValue (new HSSFRichTextString (names [j]);
}
While (rs. next ())
{
// Create a row of the workbook
HSSFRow row = sheet. createRow (I); // start with a row whose subscript is 1
For (int j = 0; j <CountColumnNum; j ++)
{
// Loop in a row
HSSFCell cell = row. createCell (short) j );
// Set the sequence set of the table to support Chinese Characters
/// Determine the data type in the database first
// Add the values in the result set to the workbook.
Cell. setCellValue (new HSSFRichTextString (rs. getString (j + 1 )));
}
I ++;
}
// Create a file output stream and prepare the output workbook
OutputStream out = new FileOutputStream ("E: \ person.xls ");
Wb. write (out );
Out. close ();
System. out. println ("database exported successfully ");
Rs. close ();
Con. close ();
}
Public static void main (String [] args)
{
Try {
@ SuppressWarnings ("unused ")
MySql2Excel excel = new MySql2Excel ();
} Catch (Exception e ){
// TODO Auto-generated catch block
E. printStackTrace ();
}
}
}

For example, you can call it in front-end jsp as follows:

<A href = "ReportServlet" onclick = "return confirm ('Are you sure you want to export the data to E? '); "> Export data to Excel </a>

Write the above Code in the background servlet. Note that response. sendRedirect ("") is required to return the front-end