apache poi 读取Excel资料内容(2003,2007)

2012-11-18

apache poi 读取Excel文件内容(2003,2007)很久没记录了，前一段做了个只读取Excel文件内容的需求，今天整理

apache poi 读取Excel文件内容(2003,2007)

很久没记录了，前一段做了个只读取Excel文件内容的需求，今天整理出来，备份一下。

?项目名称 Test

直接上代码

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%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>        <title>读取的Excel文件内容(2003,2007)</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"><!-- 引入jQuery --><script type="text/javascript" src="/js/jquery.js"></script><script type="text/javascript">//提交Excelfunction importExcel(){    var fileExcel = $("#fileExcel").val();    // 导入文件必选项    if(fileExcel==""||fileExcel==null){    alert("请选择要导入Excel文件！");    return false;    }  // 判断文件类型  if(fileExcel!=""&&fileExcel!=null){  var hz = fileExcel.substr(fileExcel.lastIndexOf(".")+1);  if(hz!="xls"&&hz!="xlsx"){  alert("请按提示导入指定类型文件！");  return false;  }  }  //获得客户端上传的实际路径  $("#filePath").val(fileExcel);    return true;}</script>  </head>    <body><form action="<%=request.getContextPath()%>/Excel.do?method=read" id="excelForm" name="excelForm" method="post" enctype="multipart/form-data" onsubmit="return importExcel();">  <table align="center" border="0">  <tr>  <td>  请选择读取的Excel文件：    <input type="file" id="fileExcel" name="file" title="请选择文件">&nbsp;&nbsp;&nbsp;    <font style="color: red;">注：.xls或.xlsx</font></br>    <input type="hidden" id="filePath" name="filePath" value="">    <input type="submit" value="提 交" title="提 交">    <input type="reset" value="重 置" title="重 置">    </td>    </tr>    </table>    </form>  </body></html>

struts-config.xml

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.3//EN" "http://struts.apache.org/dtds/struts-config_1_3.dtd"><struts-config>  <form-beans>  <form-bean name="excelForm" type="com.test.struts.form.ExcelForm"></form-bean>  </form-beans>  <global-exceptions />  <global-forwards />  <action-mappings>  <action path="/Excel" name="excelForm" type="com.test.struts.action.ExcelReadAction" scope="request" parameter="method"></action>  </action-mappings>  <message-resources parameter="com.test.struts.ApplicationResources" /></struts-config>

web.xml

<?xml version="1.0" encoding="UTF-8"?><web-app xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.5" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee   http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"><!-- 编码过滤器 --><filter><filter-name>Set Character Encoding</filter-name><filter-class>com.test.struts.filter.SetCharacterEncodingFilter</filter-class><init-param><param-name>encoding</param-name><param-value>UTF-8</param-value></init-param><init-param><param-name>ignore</param-name><param-value>true</param-value></init-param></filter><filter-mapping><filter-name>Set Character Encoding</filter-name><url-pattern>/*</url-pattern></filter-mapping><!-- struts1配置 -->  <servlet>    <servlet-name>action</servlet-name>    <servlet-class>org.apache.struts.action.ActionServlet</servlet-class>    <init-param>      <param-name>config</param-name>      <param-value>/WEB-INF/struts-config.xml</param-value>    </init-param>    <init-param>      <param-name>debug</param-name>      <param-value>3</param-value>    </init-param>    <init-param>      <param-name>detail</param-name>      <param-value>3</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>  <welcome-file-list>    <welcome-file>index.jsp</welcome-file>  </welcome-file-list>  <!-- 常见异常错误处理 -->  <error-page>  <error-code>404</error-code><location>/common/404.jsp</location>  </error-page>  <error-page>  <error-code>500</error-code>  <location>/common/500.jsp</location>  </error-page>  <error-page>  <error-code>505</error-code>  <location>/common/505.jsp</location>  </error-page></web-app>

Formbean

package com.test.struts.form;import org.apache.struts.action.ActionForm;public class ExcelForm extends ActionForm {private static final long serialVersionUID = -5661095366659304317L;private String filePath;public String getFilePath() {return filePath;}public void setFilePath(String filePath) {this.filePath = filePath;}}

Action

package com.test.struts.action;import java.io.FileInputStream;import java.io.IOException;import javax.servlet.ServletException;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.struts.action.ActionForm;import org.apache.struts.action.ActionForward;import org.apache.struts.action.ActionMapping;import org.apache.struts.actions.DispatchAction;import com.test.struts.form.ExcelForm;public class ExcelReadAction extends DispatchAction {private ExcelForm excelForm;private Workbook workbook;/** * Excel文件内容读取(2003,2007) * @author wxb * @param mapping * @param form * @param request * @param response * @throws ServletException * @throws IOException * @date 2012-04-18 14:29:14 */public ActionForward read(ActionMapping mapping, ActionForm form,HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {excelForm = (ExcelForm) form;/** * 服务端Excel文件验证略过..... *//** * 读取Excel文件内容..... * 注:读取2003版本与读取2007版或更高的版本apache-poi读取文件的方式不同 * Excel中CELL常见类型 * ------------------------------------------------- * |CELL_TYPE_NUMERIC            数值(常见数字、日期等) * |CELL_TYPE_STRING            字符串 * |CELL_TYPE_FORMULA            公式 * |CELL_TYPE_BLANK         空值（cell不为空） * |CELL_TYPE_BOOLEAN            布尔 * |CELL_TYPE_ERROR              错误 * ------------------------------------------------- */try {// 2003版本Excel(.xls)workbook = new HSSFWorkbook(new FileInputStream(excelForm.getFilePath()));} catch (Exception e) {// 2007版本Excel或更高版本(.xlsx)workbook = new XSSFWorkbook(excelForm.getFilePath());}// 循环sheetfor (int k = 0; k < workbook.getNumberOfSheets(); k++) {// sheetSheet sheet = workbook.getSheetAt(k);int rows = sheet.getPhysicalNumberOfRows();// 循环行for (int r = 0; r < rows; r++) {// 定义rowRow row = (Row) sheet.getRow(r);if (row != null) {// 定义cell(列)int cells = row.getPhysicalNumberOfCells();// 循环列for (int c = 0; c < cells; c++) {Cell cell = row.getCell(c);if (cell != null) {String value = null;switch (cell.getCellType()) {case Cell.CELL_TYPE_FORMULA:value = "FORMULA value =" + cell.getCellFormula();break;case Cell.CELL_TYPE_NUMERIC:if (HSSFDateUtil.isCellDateFormatted(cell)) {value = "DATE value = " + cell.getDateCellValue();} else {value = "NUMERIC value = " + cell.getNumericCellValue();}break;case Cell.CELL_TYPE_STRING:value = "STRING value = " + cell.getStringCellValue();break;case Cell.CELL_TYPE_BOOLEAN:value = "BOOLEAN value = " + cell.getBooleanCellValue();break;default:}System.out.println(value);}}}}}return null;}public ExcelForm getExcelForm() {return excelForm;}public void setExcelForm(ExcelForm excelForm) {this.excelForm = excelForm;}}

字符集Filter过滤器

apache-tomcat-6.0目录-----webapps-----examples-----WEB-INF-----classes-----filters

拷贝 SetCharacterEncodingFilter.java

apache官方说明api文档：

http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api

apache功能支持Jar包结构：

http://poi.apache.org/overview.html

apache? poi下载地址：

http://poi.apache.org/download.html

例子中，使用的结构是 struts1.3? +? apache poi3.8??支持的Jar包

struts1.3支持

apache poi 读取Excel资料内容(2003,2007) ?

?apache poi3.8??支持

apache poi 读取Excel资料内容(2003,2007)

?注：

1) 读取Excel2003与Excel2007或更高版本的方式不同

2) 如遇到缺少 apache poi? 相关支持Jar包时，可以去下面的网站去搜索：

http://www.jarfinder.com/?

用于搜索各种Jar包，很好用。

热点排行