导出数据库数据到Excel

于 2009-03-08 00:05:00 发布
阅读量55 收藏
点赞数
文章标签： Excel Servlet Java SQL MySQL
本文介绍了一个使用Apache POI从数据库导出数据到Excel文件的示例。该示例包括创建Web工程、导入必要的jar包、编写获取数据库数据的类、生成Excel文件的Servlet以及打开Excel文件的Servlet。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

做了一个利用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. packageorg.hnylj.poi;
  3. importjava.sql.Connection;
  4. importjava.sql.DriverManager;
  5. importjava.sql.PreparedStatement;
  6. importjava.sql.ResultSet;
  7. importjava.sql.SQLException;
  9. publicclassStudentResultSet{
  10. privateConnectionconn;
  11. privatePreparedStatementpstmt;
  12. privateResultSetrs;
  13. privatestaticfinalStringDRIVER="com.mysql.jdbc.Driver";
  14. privatestaticfinalStringURL="jdbc:mysql://localhost:3306/poi";
  15. privatestaticfinalStringUSERNAME="root";
  16. privatestaticfinalStringPASSWORD="123";
  18. //查询数据库中的数据
  19. publicResultSetquery(){
  20. try{
  21. Class.forName(DRIVER);
  22. conn=DriverManager.getConnection(URL,USERNAME,PASSWORD);
  23. pstmt=conn.prepareStatement("select*fromstudent");
  24. rs=pstmt.executeQuery();
  25. }catch(ClassNotFoundExceptione){
  26. e.printStackTrace();
  27. }catch(SQLExceptione){
  28. e.printStackTrace();
  29. }
  30. returnrs;
  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. packageorg.hnylj.poi;
  3. importjava.io.FileOutputStream;
  4. importjava.io.IOException;
  5. importjava.sql.ResultSet;
  6. importjava.sql.ResultSetMetaData;
  8. importjavax.servlet.ServletException;
  9. importjavax.servlet.http.HttpServlet;
  10. importjavax.servlet.http.HttpServletRequest;
  11. importjavax.servlet.http.HttpServletResponse;
  13. importorg.apache.poi.hssf.usermodel.HSSFCell;
  14. importorg.apache.poi.hssf.usermodel.HSSFRichTextString;
  15. importorg.apache.poi.hssf.usermodel.HSSFRow;
  16. importorg.apache.poi.hssf.usermodel.HSSFSheet;
  17. importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
  19. publicclassBuildExcelServletextendsHttpServlet{
  21. protectedvoiddoGet(HttpServletRequestrequest,
  22. HttpServletResponseresponse)throwsServletException,IOException{
  23. StudentResultSetstuResultSet=newStudentResultSet();
  24. ResultSetrs=stuResultSet.query();
  25. StringxlsName="test.xls";
  26. StringsheetName="sheetName";
  28. HSSFWorkbookworkbook=newHSSFWorkbook();
  29. HSSFSheetsheet=workbook.createSheet();
  30. workbook.setSheetName(0,sheetName);
  31. HSSFRowrow=sheet.createRow((short)0);
  32. HSSFCellcell;
  33. try{
  34. ResultSetMetaDatamd=rs.getMetaData();
  35. intnColumn=md.getColumnCount();
  37. for(inti=1;i<=nColumn;i++){
  38. cell=row.createCell((short)(i-1));
  39. cell.setCellType(HSSFCell.CELL_TYPE_STRING);
  40. cell.setCellValue(newHSSFRichTextString(md.getColumnLabel(i)));
  41. }
  42. intiRow=1;
  43. while(rs.next()){
  44. row=sheet.createRow((short)iRow);
  45. ;
  46. for(intj=1;j<=nColumn;j++){
  47. cell=row.createCell((short)(j-1));
  48. cell.setCellType(HSSFCell.CELL_TYPE_STRING);
  49. cell.setCellValue(newHSSFRichTextString(rs.getObject(j)
  50. .toString()));
  51. }
  52. iRow++;
  53. }
  54. Stringfilename=request.getRealPath("/")+xlsName;
  55. request.setAttribute("filename",filename);
  56. FileOutputStreamfOut=newFileOutputStream(filename);
  57. workbook.write(fOut);
  58. fOut.flush();
  59. fOut.close();
  60. request.getRequestDispatcher("OpenExcelServlet").forward(request,
  61. response);
  62. }catch(Exceptione){
  63. e.printStackTrace();
  64. }
  65. }
  67. protectedvoiddoPost(HttpServletRequestrequest,
  68. HttpServletResponseresponse)throwsServletException,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. packageorg.hnylj.poi;
  3. importjava.io.BufferedInputStream;
  4. importjava.io.FileInputStream;
  5. importjava.io.IOException;
  6. importjava.io.PrintWriter;
  8. importjavax.servlet.ServletException;
  9. importjavax.servlet.http.HttpServlet;
  10. importjavax.servlet.http.HttpServletRequest;
  11. importjavax.servlet.http.HttpServletResponse;
  13. publicclassOpenExcelServletextendsHttpServlet{
  15. publicvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)
  16. throwsServletException,IOException{
  18. response.setContentType("application/vnd.ms-excel");
  19. PrintWriterout=response.getWriter();
  20. StringfilePath=(String)request.getAttribute("filename");
  21. BufferedInputStreambis=null;
  22. try{
  23. bis=newBufferedInputStream(newFileInputStream(filePath));
  24. intbytesRead;
  25. while((bytesRead=bis.read())!=-1){
  26. out.write(bytesRead);
  27. out.flush();
  28. }
  29. }catch(IOExceptione){
  30. e.printStackTrace();
  31. }finally{
  32. if(bis!=null)
  33. bis.close();
  34. if(out!=null)
  35. out.close();
  36. }
  37. }
  39. publicvoiddoPost(HttpServletRequestrequest,HttpServletResponseresponse)
  40. throwsServletException,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>
  10. <servlet-mapping>
  11. <servlet-name>OpenExcelServlet</servlet-name>
  12. <url-pattern>/OpenExcelServlet</url-pattern>
  13. </servlet-mapping>
  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，运行程序

iteye_4965
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值