做了一個利用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類,用于取出數據庫中的數據;
- 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 Connectionconn;
- private PreparedStatementpstmt;
- private ResultSetrs;
- private static final StringDRIVER= "com.mysql.jdbc.Driver" ;
- private static final StringURL= "jdbc:mysql://localhost:3306/poi" ;
- private static final StringUSERNAME= "root" ;
- private static final StringPASSWORD= "123" ;
- //查詢數據庫中的數據
- public ResultSetquery(){
- try {
- Class.forName(DRIVER);
- conn=DriverManager.getConnection(URL,USERNAME,PASSWORD);
- pstmt=conn.prepareStatement( "select*fromstudent" );
- rs=pstmt.executeQuery();
- } catch (ClassNotFoundExceptione){
- e.printStackTrace();
- } catch (SQLExceptione){
- e.printStackTrace();
- }
- return rs;
- }
- }
4、編寫一個BuildExcelServlet,用于生成Excel;
- 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(HttpServletRequestrequest,
- HttpServletResponseresponse) throws ServletException,IOException{
- StudentResultSetstuResultSet= new StudentResultSet();
- ResultSetrs=stuResultSet.query();
- StringxlsName= "test.xls" ;
- StringsheetName= "sheetName" ;
- HSSFWorkbookworkbook= new HSSFWorkbook();
- HSSFSheetsheet=workbook.createSheet();
- workbook.setSheetName( 0 ,sheetName);
- HSSFRowrow=sheet.createRow(( short ) 0 );
- HSSFCellcell;
- try {
- ResultSetMetaDatamd=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++;
- }
- Stringfilename=request.getRealPath( "/" )+xlsName;
- request.setAttribute( "filename" ,filename);
- FileOutputStreamfOut= new FileOutputStream(filename);
- workbook.write(fOut);
- fOut.flush();
- fOut.close();
- request.getRequestDispatcher( "OpenExcelServlet" ).forward(request,
- response);
- } catch (Exceptione){
- e.printStackTrace();
- }
- }
- protected void doPost(HttpServletRequestrequest,
- HttpServletResponseresponse) throws ServletException,IOException{
- this .doGet(request,response);
- }
- }
5、編寫一個OpenExcelServlet,用于把生成的Excel在頁面上打開;
- 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(HttpServletRequestrequest,HttpServletResponseresponse)
- throws ServletException,IOException{
- response.setContentType( "application/vnd.ms-excel" );
- PrintWriterout=response.getWriter();
- StringfilePath=(String)request.getAttribute( "filename" );
- BufferedInputStreambis= null ;
- try {
- bis= new BufferedInputStream( new FileInputStream(filePath));
- int bytesRead;
- while ((bytesRead=bis.read())!=- 1 ){
- out.write(bytesRead);
- out.flush();
- }
- } catch (IOExceptione){
- e.printStackTrace();
- } finally {
- if (bis!= null )
- bis.close();
- if (out!= null )
- out.close();
- }
- }
- public void doPost(HttpServletRequestrequest,HttpServletResponseresponse)
- throws ServletException,IOException{
- this .doGet(request,response);
- }
- }
6、在web.xml里配置一下servlet的映射;
- < 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,運行程序
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
