Java uses POI to import data from a database into Excel solution _java

Java uses POI to import data from a database into Excel

Effect:

Use the first POI package into the project path, note that only need to import POI package can be downloaded after three jar package

Core code:

Connection database: Dbconnection.java

Copy Code code 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 (). executequery (SQL);
Get 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 of line with subscript 0
hssfcell[] FirstCell = new Hssfcell[countcolumnnum];
string[] names = new String[countcolumnnum];
Names[0] = "ID";
NAMES[1] = "School Number";
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 for a spreadsheet
Hssfrow row = Sheet.createrow (i); Start of line with subscript 1
for (int j=0;j<countcolumnnum;j++)
{
Loop inside a line
Hssfcell cell = Row.createcell ((short) j);
Set the code set of the table so that support Chinese
Determine the data type in the database first
Put the values in the result set into a spreadsheet
Cell.setcellvalue (New Hssfrichtextstring (Rs.getstring (j+1)));
}
i++;
}
Create file output stream, prepare output spreadsheet
OutputStream out = new FileOutputStream ("E:\\person.xls");
Wb.write (out);
Out.close ();
SYSTEM.OUT.PRINTLN ("Database export succeeded");
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 this in the front-end JSP:

<a href= "Reportservlet" onclick= "return confirm (' Confirm data export to e:/? ');" > Export Data to Excel</a>

The background servlet writes the above code, note that the last need Response.sendredirect ("") back to the front