SpringMvc+POI 处理Excel的导入操作

说明

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+"/";
%>

1.通过简单的form表单提交方式，进行文件的上 2.通过jquery.form.js插件提供的form表单一步提交功能

上传文件:

web.xml

<?xml version="1.0" encoding="UTF-8"?>

  <!-- 加载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>  

springmvc-servlet.xml（只做简单配置）

<?xml version="1.0" encoding="UTF-8"?>

 <!-- 启动注解驱动-->  
 <mvc:annotation-driven/>  
 <!-- 启动包扫描功能-->  
 <context:component-scan base-package="com.poiexcel.*" />  

applicationContext-base.xml

<?xml version="1.0" encoding="UTF-8"?>

 <!-- 视图解释类 -->  
 <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>

效果

后台打印信息