导出数据库数据到Excel

做了一个利用POI导出数据库数据到Excel的例子，并把这个例子与大家一起分享，一起学习，共同进步！
先简单介绍一下poi，poi是apache组织的一个开源项目，最新版为poi-3.0.1,要使用poi需要到apache网站下载poi-bin3.0.1.zip压缩包，并将poi-3.0.1.jar加入到项目的ClassPath中。

 

下面是完成这个实例的步骤：

1、建立好一个web工程：poi；

2、导入所需要的jar包:poi-3.0.1.jar和mysql数据库驱动包；

3、编写一个StudentResultSet类，用于取出数据库中的数据；

Java代码

1. package org.hnylj.poi;   
2.   
3. import java.sql.Connection;   
4. import java.sql.DriverManager;   
5. import java.sql.PreparedStatement;   
6. import java.sql.ResultSet;   
7. import java.sql.SQLException;   
8.   
9. public class StudentResultSet {   
10.     private Connection conn;   
11.     private PreparedStatement pstmt;   
12.     private ResultSet rs;   
13.     private static final String DRIVER = "com.mysql.jdbc.Driver";   
14.     private static final String URL = "jdbc:mysql://localhost:3306/poi";   
15.     private static final String USERNAME = "root";   
16.     private static final String PASSWORD = "123";   
17.        
18.     //查询数据库中的数据   
19.     public ResultSet query() {   
20.         try {   
21.             Class.forName(DRIVER);   
22.             conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);   
23.             pstmt = conn.prepareStatement("select * from student");   
24.             rs = pstmt.executeQuery();   
25.         } catch (ClassNotFoundException e) {   
26.             e.printStackTrace();   
27.         } catch (SQLException e) {   
28.             e.printStackTrace();   
29.         }   
30.         return rs;   
31.     }   
32. }   

package org.hnylj.poi;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentResultSet {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://localhost:3306/poi";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "123";
    
	//查询数据库中的数据
	public ResultSet query() {
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			pstmt = conn.prepareStatement("select * from student");
			rs = pstmt.executeQuery();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
} 

4、编写一个BuildExcelServlet，用于生成Excel；

Java代码

1. package org.hnylj.poi;   
2.   
3. import java.io.FileOutputStream;   
4. import java.io.IOException;   
5. import java.sql.ResultSet;   
6. import java.sql.ResultSetMetaData;   
7.   
8. import javax.servlet.ServletException;   
9. import javax.servlet.http.HttpServlet;   
10. import javax.servlet.http.HttpServletRequest;   
11. import javax.servlet.http.HttpServletResponse;   
12.   
13. import org.apache.poi.hssf.usermodel.HSSFCell;   
14. import org.apache.poi.hssf.usermodel.HSSFRichTextString;   
15. import org.apache.poi.hssf.usermodel.HSSFRow;   
16. import org.apache.poi.hssf.usermodel.HSSFSheet;   
17. import org.apache.poi.hssf.usermodel.HSSFWorkbook;   
18.   
19. public class BuildExcelServlet extends HttpServlet {   
20.   
21.     protected void doGet(HttpServletRequest request,   
22.             HttpServletResponse response) throws ServletException, IOException {   
23.         StudentResultSet stuResultSet = new StudentResultSet();   
24.         ResultSet rs = stuResultSet.query();   
25.         String xlsName = "test.xls";   
26.         String sheetName = "sheetName";   
27.   
28.         HSSFWorkbook workbook = new HSSFWorkbook();   
29.         HSSFSheet sheet = workbook.createSheet();   
30.         workbook.setSheetName(0, sheetName);   
31.         HSSFRow row = sheet.createRow((short) 0);   
32.         HSSFCell cell;   
33.         try {   
34.             ResultSetMetaData md = rs.getMetaData();   
35.             int nColumn = md.getColumnCount();   
36.   
37.             for (int i = 1; i <= nColumn; i++) {   
38.                 cell = row.createCell((short) (i - 1));   
39.                 cell.setCellType(HSSFCell.CELL_TYPE_STRING);   
40.                 cell.setCellValue(new HSSFRichTextString(md.getColumnLabel(i)));   
41.             }   
42.             int iRow = 1;   
43.             while (rs.next()) {   
44.                 row = sheet.createRow((short) iRow);   
45.                 ;   
46.                 for (int j = 1; j <= nColumn; j++) {   
47.                     cell = row.createCell((short) (j - 1));   
48.                     cell.setCellType(HSSFCell.CELL_TYPE_STRING);   
49.                     cell.setCellValue(new HSSFRichTextString(rs.getObject(j)   
50.                             .toString()));   
51.                 }   
52.                 iRow++;   
53.             }   
54.             String filename = request.getRealPath("/") + xlsName;   
55.             request.setAttribute("filename", filename);   
56.             FileOutputStream fOut = new FileOutputStream(filename);   
57.             workbook.write(fOut);   
58.             fOut.flush();   
59.             fOut.close();   
60.             request.getRequestDispatcher("OpenExcelServlet").forward(request,   
61.                     response);   
62.         } catch (Exception e) {   
63.             e.printStackTrace();   
64.         }   
65.     }   
66.   
67.     protected void doPost(HttpServletRequest request,   
68.             HttpServletResponse response) throws ServletException, IOException {   
69.         this.doGet(request, response);   
70.     }   
71. }  

package org.hnylj.poi;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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 BuildExcelServlet extends HttpServlet {

	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		StudentResultSet stuResultSet = new StudentResultSet();
		ResultSet rs = stuResultSet.query();
		String xlsName = "test.xls";
		String sheetName = "sheetName";

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, sheetName);
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell;
		try {
			ResultSetMetaData md = rs.getMetaData();
			int nColumn = md.getColumnCount();

			for (int i = 1; i <= nColumn; i++) {
				cell = row.createCell((short) (i - 1));
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(md.getColumnLabel(i)));
			}
			int iRow = 1;
			while (rs.next()) {
				row = sheet.createRow((short) iRow);
				;
				for (int j = 1; j <= nColumn; j++) {
					cell = row.createCell((short) (j - 1));
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(new HSSFRichTextString(rs.getObject(j)
							.toString()));
				}
				iRow++;
			}
			String filename = request.getRealPath("/") + xlsName;
			request.setAttribute("filename", filename);
			FileOutputStream fOut = new FileOutputStream(filename);
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
			request.getRequestDispatcher("OpenExcelServlet").forward(request,
					response);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		this.doGet(request, response);
	}
}

5、编写一个OpenExcelServlet，用于把生成的Excel在页面上打开；

Java代码

1. package org.hnylj.poi;   
2.   
3. import java.io.BufferedInputStream;   
4. import java.io.FileInputStream;   
5. import java.io.IOException;   
6. import java.io.PrintWriter;   
7.   
8. import javax.servlet.ServletException;   
9. import javax.servlet.http.HttpServlet;   
10. import javax.servlet.http.HttpServletRequest;   
11. import javax.servlet.http.HttpServletResponse;   
12.   
13. public class OpenExcelServlet extends HttpServlet {   
14.   
15.     public void doGet(HttpServletRequest request, HttpServletResponse response)   
16.             throws ServletException, IOException {   
17.   
18.         response.setContentType("application/vnd.ms-excel");   
19.         PrintWriter out = response.getWriter();   
20.         String filePath = (String) request.getAttribute("filename");   
21.         BufferedInputStream bis = null;   
22.         try {   
23.             bis = new BufferedInputStream(new FileInputStream(filePath));   
24.             int bytesRead;   
25.             while ((bytesRead = bis.read()) != -1) {   
26.                 out.write(bytesRead);   
27.                 out.flush();   
28.             }   
29.         } catch (IOException e) {   
30.             e.printStackTrace();   
31.         } finally {   
32.             if (bis != null)   
33.                 bis.close();   
34.             if (out != null)   
35.                 out.close();   
36.         }   
37.     }   
38.   
39.     public void doPost(HttpServletRequest request, HttpServletResponse response)   
40.             throws ServletException, IOException {   
41.         this.doGet(request, response);   
42.     }   
43. }  

package org.hnylj.poi;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class OpenExcelServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("application/vnd.ms-excel");
		PrintWriter out = response.getWriter();
		String filePath = (String) request.getAttribute("filename");
		BufferedInputStream bis = null;
		try {
			bis = new BufferedInputStream(new FileInputStream(filePath));
			int bytesRead;
			while ((bytesRead = bis.read()) != -1) {
				out.write(bytesRead);
				out.flush();
			}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (bis != null)
				bis.close();
			if (out != null)
				out.close();
		}
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		this.doGet(request, response);
	}
}

6、在web.xml里配置一下servlet的映射；

Xml代码

1.        <servlet>  
2.     <servlet-name>BuildExcelServlet</servlet-name>  
3.     <servlet-class>org.hnylj.poi.BuildExcelServlet</servlet-class>  
4. </servlet>  
5. <servlet>  
6.     <servlet-name>OpenExcelServlet</servlet-name>  
7.     <servlet-class>org.hnylj.poi.OpenExcelServlet</servlet-class>  
8. </servlet>  
9.   
10. <servlet-mapping>  
11.     <servlet-name>OpenExcelServlet</servlet-name>  
12.     <url-pattern>/OpenExcelServlet</url-pattern>  
13. </servlet-mapping>  
14.   
15. <servlet-mapping>  
16.     <servlet-name>BuildExcelServlet</servlet-name>  
17.     <url-pattern>/BuildExcelServlet</url-pattern>  
18. </servlet-mapping>  

        <servlet>
		<servlet-name>BuildExcelServlet</servlet-name>
		<servlet-class>org.hnylj.poi.BuildExcelServlet</servlet-class>
	</servlet>
	<servlet>
		<servlet-name>OpenExcelServlet</servlet-name>
		<servlet-class>org.hnylj.poi.OpenExcelServlet</servlet-class>
	</servlet>

	<servlet-mapping>
		<servlet-name>OpenExcelServlet</servlet-name>
		<url-pattern>/OpenExcelServlet</url-pattern>
	</servlet-mapping>

	<servlet-mapping>
		<servlet-name>BuildExcelServlet</servlet-name>
		<url-pattern>/BuildExcelServlet</url-pattern>
	</servlet-mapping>

7、启动tomcat，运行程序