SpringMvc+POI上传Excel

1、首先需要导入3个jar包：

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.9</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.9</version>
		</dependency>

		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.4</version>
		</dependency>

2、导入ImportExcelUtil工具类：里面支持传统的form和ajax请求：

package tf56.skynetAdmin.utils;

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()+1; j < sheet.getLastRowNum()+1; j++) {  
	                row = sheet.getRow(j);  
	                if(row==null){continue;}  
	              //  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;  
	    }  
	      
}

3、编写controller类：

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.json.JSONException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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;

@Controller  
@RequestMapping("/uploadExcel/*") 
public class UploadExcelControl {

	
	
	
private static final Logger logger = LoggerFactory.getLogger(MyportalController.class);
	
	@Resource
	private MyportalService  myportalService;
	
	@Resource
	private MyportalApi myportalApi;
	
	@Resource
	private SystemUserService systemUserService;
	/**
	 * 描述：通过传统方式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相应方法进行数据保存到数据库中，现只对数据输出
		return "result";
	}

	/**
	 * 描述：通过 jquery.form.js 插件提供的ajax方式上传文件
	 * 
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@ResponseBody
	@RequestMapping(value = "ajaxUpload.do", method = { RequestMethod.GET, RequestMethod.POST })
	public String ajaxUploadExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		StringBuffer resultMsgSB = new StringBuffer();
		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());
		List<Map<String, Object>> listUserMap = new ArrayList<>();
		// 该处可调用service相应方法进行数据保存到数据库中，现只对数据输
		
		return ResultUtil.returnSuccessResult(response, "处理成功", "", resultMsgSB.toString());
	}
}

4、编写简单的前端页面代码

<html>  
  <head>
  	<meta charset="utf8">  
    <script type="text/javascript" src="js/jquery.min.js"></script>  
    <script type="text/javascript" src="js/jquery.form.min.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){  
                        	
                           //document.write(msg.data);
                           var data = JSON.parse(msg.replace(/\\n/g,'</br>'))
                           $('body').append('<span>' + data.data + '</span>');
                            $("#upfile").val("");  
                        }  
                        function errorMsg(err){   
                            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>  --> 
  <h1>上传excel</h1>
    <form method="POST"  enctype="multipart/form-data" id="form1" action="uploadExcel/upload.do">  
        <div>  
            <div>上传Excel表格: </div></br>  
            <div><input id="upfile" type="file" name="upfile"></div>   
 
            <!-- <td><input type="submit" value="提交" οnclick="return checkData()"></td>   -->
            <div><input type="button" value="ajax方式提交" id="btn" name="btn" ></div>    
        </div>    
    </form>  
      
  </body>  
</html>  

5、最后在Spring-Serviet.xml中加入如下配置来限制上传文件的大小：

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