关闭

SpringMvc+POI 处理Excel的导入操作

标签: spring mvcpoiexcelJquery.form
20356人阅读 评论(24) 收藏 举报
分类:

说明

  POI可以对2003-和2007+版本的Excel文件做导入导出操作,本章只简单介绍对Excel文件的导入操作。

       Excel文件的上传处理处理请求,依然使用SpringMvc中的MultipartRequest方式处理。

       前端JSP中使用传统form表单提交方式和Juery.form.js插件提供的异步表单请求方式,分别对这两种方式进行介绍。


环境

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();
	}


}




InfoVo.java(保存Excel数据对应的对象)

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 方式上传文件操作
			 $(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>          


效果


后台打印信息




10
1

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:80136次
    • 积分:1029
    • 等级:
    • 排名:千里之外
    • 原创:32篇
    • 转载:21篇
    • 译文:0篇
    • 评论:30条
    最新评论