使用POI操作Excel文件

hehffyy 2011-01-18

展開全文

針對(duì)使用POI操作Excel文件，可以參考：《POI讀取EXCEL教程》http://julycn./blog/107341

下面為在struts1.x下使用POI讀取上傳的Excel文件中的內(nèi)容。見意：剛接觸POI的讀者可以看看，否則就可以免了。

大概過程如下：在瀏覽器端上傳文件——>使用struts api讀取上傳的文件——>使上傳的文件轉(zhuǎn)換為InputStream——>通過InputSteam得到HSSFWorkbook——>通過HSSFWorkbook得到HSSFSheet ——>通過HSSFSheet 得到HSSFRow——>再通過HSSFRow 得到HSSFCell——>最后得到單元格中的值

為了方便閱讀全部代碼如下,完整代碼見我的資源：http://download.csdn.net/source/765509

//UploadForm.java
import org.apache.struts.action.ActionForm;
import org.apache.struts.upload.FormFile;
public class UploadForm extends ActionForm {
    private FormFile file;
    public FormFile getFile() {
        return file;
    }
    public void setFile(FormFile file) {
        this.file = file;
    }   
}

//UploadAction.java
import java.io.InputStream;
import java.util.ArrayList;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.upload.FormFile;
public class UploadAction extends Action {
    @Override
    public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
        
        UploadForm uploadForm=(UploadForm)form;
        FormFile file=uploadForm.getFile(); 
        //get the InputStream
        InputStream is=file.getInputStream();
        //get the Workbook
        HSSFWorkbook wb=new HSSFWorkbook(is);
        //get the sheet
        HSSFSheet sheet=wb.getSheet("employeelist");
        for(int i=0;i<sheet.getLastRowNum();i++)
        {   
            //get row 
            HSSFRow row = sheet.getRow(i);          
            for(int j=0;j<row.getLastCellNum();j++){
                //get cell
                HSSFCell cell=row.getCell((short)j);    
                /* here  get String type ,if there are other types in your sheet ,
                you can use other Cell's API,such as cell.getBooleanCellValue();
                cell.getDateCellValue();cell.getNumericCellValue();
                */
                System.out.println(i+"/"+j+" : "+cell.getStringCellValue());
            }           
        }       
        return mapping.findForward("success");
    }
    
}

<!--Upload.jsp-->
<%@ page language="java" import="java.util.*" pageEncoding="ISO-8859-1"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>Upload</title>
    
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">    
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <!--
    <link rel="stylesheet" type="text/css" href="styles.css">
    -->
  </head>
  
  <body>
    <br>
    <br>
    <br>
    <br>
    <center>
    <form action="upload.do" method="post" enctype="multipart/form-data">
        Please upload your file : <input type="file" name="file">
        <input type="submit" name="Submit" value="Submit">  
      </form>
    </center>
    <br>
    <br>
    <br>
    <br>
  </body>
</html>

下面為配置文件：web.xml和struts-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" 
    xmlns="http://java./xml/ns/j2ee" 
    xmlns:xsi="http://www./2001/XMLSchema-instance" 
    xsi:schemaLocation="http://java./xml/ns/j2ee 
    http://java./xml/ns/j2ee/web-app_2_4.xsd">
    <servlet>
        <servlet-name>action</servlet-name>
        <servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
        <init-param>
            <param-name>cofig</param-name>
            <param-value>/WEB-INF/struts-config.xml</param-value>
        </init-param>
        <load-on-startup>0</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>action</servlet-name>
        <url-pattern>*.do</url-pattern>
    </servlet-mapping>
</web-app>

<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE struts-config PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 1.3//EN"
"http://struts./dtds/struts-config_1_3.dtd">
<struts-config>
<form-beans>
<form-bean name="uploadForm" type="UploadForm"></form-bean>
</form-beans>
<action-mappings>
<action path="/upload" name="uploadForm" type="UploadAction">
<forward name="success" path="/upload.jsp"></forward>
</action>
</action-mappings>
</struts-config>
針對(duì)使用POI操作Excel文件，可以參考：《POI讀取EXCEL教程》http://julycn./blog/107341
下面為在struts1.x下使用POI生成Excel文件并下載。
大概過程如下：創(chuàng)建HSSFWorkbook——在剛創(chuàng)建的HSSFWorkbook下創(chuàng)建sheet——在剛創(chuàng)建的sheet下創(chuàng)建row——在剛創(chuàng)建的row下創(chuàng)建Cell——在剛創(chuàng)建的Cell中填入數(shù)據(jù)——把創(chuàng)建的HSSFWorkbook加入到OutputStream中。
在實(shí)際的開發(fā)中，經(jīng)常會(huì)碰到以Excel形式導(dǎo)出數(shù)據(jù)庫中的數(shù)據(jù)，完成此功能，可以在此程序的基礎(chǔ)上把寫入Cell中的數(shù)據(jù)改為寫入數(shù)據(jù)庫中的數(shù)據(jù)即可。
在此程序中使用了struts中的action做為控制器，所以相應(yīng)的視圖JSP文件內(nèi)容為空即可。
為了方便閱讀，全部代碼如下，完整工程可到我的資源里下載：http://download.csdn.net/source/765876
1. //DownloadAction.java
2. import java.io.OutputStream;
3. import javax.servlet.http.HttpServletRequest;
4. import javax.servlet.http.HttpServletResponse;
5. import org.apache.poi.hssf.usermodel.HSSFCell;
6. import org.apache.poi.hssf.usermodel.HSSFRow;
7. import org.apache.poi.hssf.usermodel.HSSFSheet;
8. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
9. import org.apache.struts.action.Action;
10. import org.apache.struts.action.ActionForm;
11. import org.apache.struts.action.ActionForward;
12. import org.apache.struts.action.ActionMapping;
13. public class DownloadAction extends Action {
14. @Override
15. public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
16. //create HSSFWorkbook
17. HSSFWorkbook workbook = new HSSFWorkbook();
18. //create HSSFSheet named user
19. HSSFSheet sheet = workbook.createSheet("user");
20. for(int i=0;i<5;i++){
21. //create row
22. HSSFRow row = sheet.createRow(i);
23. for(int j=0;j<3;j++){
24. //create cell
25. HSSFCell cell = row.createCell((short)(j));
26. //filled cell
27. cell.setCellValue(i+"/"+j);
28. }
29. }
30. //set response content type
31. response.setContentType("application/vnd.ms-excel");
32. //set the name of download file
33. response.addHeader("Content-Disposition","attachment;filename=user.xls");
34. //get response stream
35. OutputStream out=response.getOutputStream();
36. //add workbook to OutpustStream
37. workbook.write(out);
38. out.close();
39. return mapping.findForward("success");
40. }
42. }
1. 
下面的為配置文件：web.xml and struts-config.xml
1. <?xml version="1.0" encoding="UTF-8"?>
  <web-app version="2.4"
      xmlns="http://java./xml/ns/j2ee"
      xmlns:xsi="http://www./2001/XMLSchema-instance"
      xsi:schemaLocation="http://java./xml/ns/j2ee
      http://java./xml/ns/j2ee/web-app_2_4.xsd">
      <servlet>
          <servlet-name>action</servlet-name>
          <servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
          <init-param>
              <param-name>cofig</param-name>
              <param-value>/WEB-INF/struts-config.xml</param-value>
          </init-param>
          <load-on-startup>0</load-on-startup>
      </servlet>
      <servlet-mapping>
          <servlet-name>action</servlet-name>
          <url-pattern>*.do</url-pattern>
      </servlet-mapping>
  </web-app>
  <?xml version=
  "1.0" encoding="ISO-8859-1" ?>
2. <!DOCTYPE struts-config PUBLIC
3. "-//Apache Software Foundation//DTD Struts Configuration 1.3//EN"
4. "http://struts./dtds/struts-config_1_3.dtd">
5. <struts-config>
6. <form-beans>
7. </form-beans>
8. <action-mappings>
9. <action path="/download" type="DownloadAction">
10. 
11. <forward name="success" path="/download.jsp"></forward>
12. </action>
13. </action-mappings>
14. </struts-config>

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自： hehffyy > 《java》

舉報(bào)/認(rèn)領(lǐng)