说明
POI可以对2003-和2007+版本的Excel文件做导入导出操作,本章只简单介绍对Excel文件的导入操作。
Excel文件的上传处理处理请求,依然使用SpringMvc中的MultipartRequest方式处理。
前端JSP中使用传统form表单提交方式和Juery.form.js插件提供的异步表单请求方式,分别对这两种方式进行介绍。
Juery.form.js插件下载地址http://download.csdn.net/download/liu976180578/10134031
环境
JDK7+ Tomcat7.x + Spring4.1.8
说明:
ImportExcelUtil.Java:Excel解析工具类
UploadExcelControl.java :处理来自页面的请求控制器
InfoVo.java :将Excel转换为对象存储
main.jsp:前端访问页
........
ImportExcelUtil.java(Excel解析工具类)
- package com.poiexcel.util;
- import java.io.IOException;
- import java.io.InputStream;
- import java.text.DecimalFormat;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.List;
- 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;
- public class ImportExcelUtil {
- private final static String excel2003L =".xls"; //2003- 版本的excel
- private final static String excel2007U =".xlsx"; //2007+ 版本的excel
- /**
- * 描述:获取IO流中的数据,组装成List<List<Object>>对象
- * @param in,fileName
- * @return
- * @throws IOException
- */
- public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
- List<List<Object>> list = null;
- //创建Excel工作薄
- Workbook work = this.getWorkbook(in,fileName);
- if(null == work){
- throw new Exception("创建Excel工作薄为空!");
- }
- Sheet sheet = null;
- Row row = null;
- Cell cell = null;
- list = new ArrayList<List<Object>>();
- //遍历Excel中所有的sheet
- for (int i = 0; i < work.getNumberOfSheets(); i++) {
- sheet = work.getSheetAt(i);
- if(sheet==null){continue;}
- //遍历当前sheet中的所有行
- for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j++) {
- row = sheet.getRow(j);
- if(row==null||row.getFirstCellNum()==j){continue;}
- //遍历所有的列
- List<Object> li = new ArrayList<Object>();
- for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
- cell = row.getCell(y);
- li.add(this.getCellValue(cell));
- }
- list.add(li);
- }
- }
- work.close();
- return list;
- }
- /**
- * 描述:根据文件后缀,自适应上传文件的版本
- * @param inStr,fileName
- * @return
- * @throws Exception
- */
- public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
- Workbook wb = null;
- String fileType = fileName.substring(fileName.lastIndexOf("."));
- if(excel2003L.equals(fileType)){
- wb = new HSSFWorkbook(inStr); //2003-
- }else if(excel2007U.equals(fileType)){
- wb = new XSSFWorkbook(inStr); //2007+
- }else{
- throw new Exception("解析的文件格式有误!");
- }
- return wb;
- }
- /**
- * 描述:对表格中数值进行格式化
- * @param cell
- * @return
- */
- public Object getCellValue(Cell cell){
- Object value = null;
- DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
- SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
- DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
- switch (cell.getCellType()) {
- case Cell.CELL_TYPE_STRING:
- value = cell.getRichStringCellValue().getString();
- break;
- case Cell.CELL_TYPE_NUMERIC:
- if("General".equals(cell.getCellStyle().getDataFormatString())){
- value = df.format(cell.getNumericCellValue());
- }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
- value = sdf.format(cell.getDateCellValue());
- }else{
- value = df2.format(cell.getNumericCellValue());
- }
- break;
- case Cell.CELL_TYPE_BOOLEAN:
- value = cell.getBooleanCellValue();
- break;
- case Cell.CELL_TYPE_BLANK:
- value = "";
- break;
- default:
- break;
- }
- return value;
- }
- }
UploadExcelControl.java (spring控制器)
- package com.poiexcel.control;
- import java.io.InputStream;
- import java.io.PrintWriter;
- import java.util.List;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import org.springframework.stereotype.Controller;
- import org.springframework.web.bind.annotation.RequestMapping;
- import org.springframework.web.bind.annotation.RequestMethod;
- import org.springframework.web.bind.annotation.ResponseBody;
- import org.springframework.web.multipart.MultipartFile;
- import org.springframework.web.multipart.MultipartHttpServletRequest;
- import com.poiexcel.util.ImportExcelUtil;
- import com.poiexcel.vo.InfoVo;
- @Controller
- @RequestMapping("/uploadExcel/*")
- public class UploadExcelControl {
- /**
- * 描述:通过传统方式form表单提交方式导入excel文件
- * @param request
- * @throws Exception
- */
- @RequestMapping(value="upload.do",method={RequestMethod.GET,RequestMethod.POST})
- public String uploadExcel(HttpServletRequest request) throws Exception {
- MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
- System.out.println("通过传统方式form表单提交方式导入excel文件!");
- InputStream in =null;
- List<List<Object>> listob = null;
- MultipartFile file = multipartRequest.getFile("upfile");
- if(file.isEmpty()){
- throw new Exception("文件不存在!");
- }
- in = file.getInputStream();
- listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename());
- in.close();
- //该处可调用service相应方法进行数据保存到数据库中,现只对数据输出
- for (int i = 0; i < listob.size(); i++) {
- List<Object> lo = listob.get(i);
- InfoVo vo = new InfoVo();
- vo.setCode(String.valueOf(lo.get(0)));
- vo.setName(String.valueOf(lo.get(1)));
- vo.setDate(String.valueOf(lo.get(2)));
- vo.setMoney(String.valueOf(lo.get(3)));
- System.out.println("打印信息-->机构:"+vo.getCode()+" 名称:"+vo.getName()+" 时间:"+vo.getDate()+" 资产:"+vo.getMoney());
- }
- return "result";
- }
- /**
- * 描述:通过 jquery.form.js 插件提供的ajax方式上传文件
- * @param request
- * @param response
- * @throws Exception
- */
- @ResponseBody
- @RequestMapping(value="ajaxUpload.do",method={RequestMethod.GET,RequestMethod.POST})
- public void ajaxUploadExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {
- MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
- System.out.println("通过 jquery.form.js 提供的ajax方式上传文件!");
- InputStream in =null;
- List<List<Object>> listob = null;
- MultipartFile file = multipartRequest.getFile("upfile");
- if(file.isEmpty()){
- throw new Exception("文件不存在!");
- }
- in = file.getInputStream();
- listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename());
- //该处可调用service相应方法进行数据保存到数据库中,现只对数据输出
- for (int i = 0; i < listob.size(); i++) {
- List<Object> lo = listob.get(i);
- InfoVo vo = new InfoVo();
- vo.setCode(String.valueOf(lo.get(0)));
- vo.setName(String.valueOf(lo.get(1)));
- vo.setDate(String.valueOf(lo.get(2)));
- vo.setMoney(String.valueOf(lo.get(3)));
- System.out.println("打印信息-->机构:"+vo.getCode()+" 名称:"+vo.getName()+" 时间:"+vo.getDate()+" 资产:"+vo.getMoney());
- }
- PrintWriter out = null;
- response.setCharacterEncoding("utf-8"); //防止ajax接受到的中文信息乱码
- out = response.getWriter();
- out.print("文件导入成功!");
- out.flush();
- out.close();
- }
- }
- package com.poiexcel.vo;
- //将Excel每一行数值转换为对象
- public class InfoVo {
- private String code;
- private String name;
- private String date;
- private String money;
- public String getCode() {
- return code;
- }
- public void setCode(String code) {
- this.code = code;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getDate() {
- return date;
- }
- public void setDate(String date) {
- this.date = date;
- }
- public String getMoney() {
- return money;
- }
- public void setMoney(String money) {
- this.money = money;
- }
- }
main.jsp(前端代码)
- <%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
- <%
- String path = request.getContextPath();
- String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
- %>
- <html>
- <head>
- <base href="<%=basePath%>">
- <script type="text/javascript" src="js/jquery-2.1.4.min.js"></script>
- <script type="text/javascript" src="js/jquery.form.js"></script>
- <title>My JSP 'index.jsp' starting page</title>
- <script type="text/javascript">
- //ajax 方式上传文件操作 要引入jquery.form.js,下载地址:http://download.csdn.net/download/liu976180578/10134031
- //要在jquery之后引入
- $(document).ready(function(){
- $('#btn').click(function(){
- if(checkData()){
- $('#form1').ajaxSubmit({
- url:'uploadExcel/ajaxUpload.do',
- dataType: 'text',
- success: resutlMsg,
- error: errorMsg
- });
- function resutlMsg(msg){
- alert(msg);
- $("#upfile").val("");
- }
- function errorMsg(){
- alert("导入excel出错!");
- }
- }
- });
- });
- //JS校验form表单信息
- function checkData(){
- var fileDir = $("#upfile").val();
- var suffix = fileDir.substr(fileDir.lastIndexOf("."));
- if("" == fileDir){
- alert("选择需要导入的Excel文件!");
- return false;
- }
- if(".xls" != suffix && ".xlsx" != suffix ){
- alert("选择Excel格式的文件导入!");
- return false;
- }
- return true;
- }
- </script>
- </head>
- <body>
- <div>1.通过简单的form表单提交方式,进行文件的上</br> 2.通过jquery.form.js插件提供的form表单一步提交功能 </div></br>
- <form method="POST" enctype="multipart/form-data" id="form1" action="uploadExcel/upload.do">
- <table>
- <tr>
- <td>上传文件: </td>
- <td> <input id="upfile" type="file" name="upfile"></td>
- </tr>
- <tr>
- <td><input type="submit" value="提交" onclick="return checkData()"></td>
- <td><input type="button" value="ajax方式提交" id="btn" name="btn" ></td>
- </tr>
- </table>
- </form>
- </body>
- </html>
web.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <web-app version="3.0"
- xmlns="http://java.sun.com/xml/ns/javaee"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
- http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd ">
- <!-- 加载Spring容器监听 -->
- <listener>
- <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
- </listener>
- <!-- 设置Spring容器加载配置文件路径 -->
- <context-param>
- <param-name>contextConfigLocation</param-name>
- <param-value>WEB-INF/application/applicationContext-*.xml</param-value>
- </context-param>
- <!--配置Springmvc核心控制器-->
- <servlet>
- <servlet-name>springmvc</servlet-name>
- <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
- <load-on-startup>1</load-on-startup>
- </servlet>
- <!--为DispatcherServlet建立映射 -->
- <servlet-mapping>
- <servlet-name>springmvc</servlet-name>
- <url-pattern>*.do</url-pattern>
- </servlet-mapping>
- <welcome-file-list>
- <welcome-file>main.jsp</welcome-file>
- </welcome-file-list>
- </web-app>
springmvc-servlet.xml(只做简单配置)
- <?xml version="1.0" encoding="UTF-8"?>
- <beans xmlns="http://www.springframework.org/schema/beans"
- xmlns:context="http://www.springframework.org/schema/context"
- xmlns:mvc="http://www.springframework.org/schema/mvc"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xmlns:tx="http://www.springframework.org/schema/tx"
- xsi:schemaLocation="
- http://www.springframework.org/schema/beans
- http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
- http://www.springframework.org/schema/context
- http://www.springframework.org/schema/context/spring-context-4.1.xsd
- http://www.springframework.org/schema/mvc
- http://www.springframework.org/schema/mvc/spring-mvc-4.1.xsd
- http://www.springframework.org/schema/tx
- http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
- http://www.springframework.org/schema/context
- http://www.springframework.org/schema/beans/spring-beans-4.1.xsd ">
- <!-- 启动注解驱动-->
- <mvc:annotation-driven/>
- <!-- 启动包扫描功能-->
- <context:component-scan base-package="com.poiexcel.*" />
- </beans>
applicationContext-base.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <beans xmlns="http://www.springframework.org/schema/beans"
- xmlns:context="http://www.springframework.org/schema/context"
- xmlns:mvc="http://www.springframework.org/schema/mvc"
- xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xmlns:tx="http://www.springframework.org/schema/tx"
- xsi:schemaLocation="
- http://www.springframework.org/schema/beans
- http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
- http://www.springframework.org/schema/context
- http://www.springframework.org/schema/context/spring-context-4.1.xsd
- http://www.springframework.org/schema/mvc
- http://www.springframework.org/schema/mvc/spring-mvc-4.1.xsd
- http://www.springframework.org/schema/tx
- http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
- http://www.springframework.org/schema/context
- http://www.springframework.org/schema/context/spring-context-4.1.xsd ">
- <!-- 视图解释类 -->
- <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
- <property name="prefix" value="/WEB-INF/jsp/" />
- <property name="suffix" value=".jsp" />
- </bean>
- <!-- SpringMVC上传文件时,需要配置MultipartResolver处理器 -->
- <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
- <property name="defaultEncoding" value="utf-8" />
- <!-- 指定所上传文件的总大小不能超过10485760000B。注意maxUploadSize属性的限制不是针对单个文件,而是所有文件的容量之和 -->
- <property name="maxUploadSize" value="10485760000" />
- <property name="maxInMemorySize" value="40960" />
- </bean>
- </beans>
效果
后台打印信息