SpringMVC 导入Excel

1. 添加依赖

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

2. Ajax 实现上传的excel的功能

function upload() {
		var uploadResult = document.getElementById("upload-result");
		uploadResult.style.color = "green";
		uploadResult.innerHTML = "正在上传.....";
		var formData = new FormData($("uploadForm")[0]);
		formData.append('Content-Type', 'multipart/form-data');
		formData.append('image', $('input[type=file]')[0].files[0]);
		$.ajax({
			url : "${pageContext.request.contextPath}/api/v1/file",
			type : 'POST',
			data : formData,
			dataType : 'json',
			contentType : false,
			processData : false,
			cache : false,//防止缓存
			error : function(data) {
				alert("请求失败，网络异常")
				console.log(data);
			},
			success : function(data) {
				console.log(data);
				var code = data.status;
				if (code == 200) {
					uploadResult.style.color = "green";
					uploadResult.innerHTML = "上传成功";
					imgUrl = data.id;
				} else {
					uploadResult.style.color = "red";
					uploadResult.innerHTML = "上传失败";
				}
			}

		});
	}

3. SpringMVC controller 进行文件的处理

 public static List<BaseBean> getExcelDataFromReqeust(HttpServletRequest request,BaseBean baseBean) {
			List<BaseBean> resultList = new ArrayList<>();
			List<Map<String, Object>> result = null;
			CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
					request.getSession().getServletContext());
			// 先判断request中是否包涵multipart类型的数据，
			if (multipartResolver.isMultipart(request)) {
				// 再将request中的数据转化成multipart类型的数据
				MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
				Iterator<String> iter = multiRequest.getFileNames();
				while (iter.hasNext()) {
					MultipartFile file = multiRequest.getFile((String) iter.next());
					if (file != null) {
						try {
							result = ExcelImportUtil.parseExcel(file.getInputStream());
							for (Map<String, Object> map : result) {
								BaseBean newBean = (BaseBean) baseBean.clone();
								transMap2Bean2(map,newBean);
								resultList.add(newBean);
							}
						} catch (IllegalStateException | IOException e) {
							e.printStackTrace();
						}
					}
				}
				return resultList;
			} else {
				return resultList;
			}
		}
	 
		
	    public static void transMap2Bean2(Map<String, Object> map, Object obj) {  
	        if (map == null || obj == null) {  
	            return;  
	        }  
	        try {  
	            BeanUtils.populate(obj, map);  
	        } catch (Exception e) {  
	            System.out.println("transMap2Bean2 Error " + e);  
	        }  
	    }  

3.1 工具类解析excel 数据

 public static List<Map<String, Object>> parseExcel(InputStream fis) {  
	        List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();;  
	        try {  
	            HSSFWorkbook book = new HSSFWorkbook(fis);  
	            HSSFSheet sheet = book.getSheetAt(0);  
	            int firstRow = sheet.getFirstRowNum();  
	            int lastRow = sheet.getLastRowNum();
	            //除去表头和第一行  
	            for(int i = firstRow + 1; i<lastRow+1; i++) {  
	                Map<String, Object> map = new HashMap<String, Object>();  
	                  
	                HSSFRow row = sheet.getRow(i);  
	                int firstCell = row.getFirstCellNum();  
	                int lastCell = row.getLastCellNum();  
	                  
	                for(int j=firstCell; j<lastCell; j++) {  
	                    HSSFCell cellTitle = sheet.getRow(firstRow).getCell(j);  
	                    String key = cellTitle.getStringCellValue();  
	                      
	                    HSSFCell cell = row.getCell(j);  
	                    Object value = null;
	                    if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {  
	                        value = (int)cell.getNumericCellValue();
	                    }  
	                    if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {  
	                        value = cell.getStringCellValue();
	                    } 
	                    map.put(key, value);    
	                }  
	                data.add(map); 
	            }  
	        } catch (IOException e) {  
	            e.printStackTrace();  
	        }  
	        return data;  
	    } 

4. 调用的方法

	@RequestMapping(value = "/excel", method = RequestMethod.POST)
	@ResponseBody
	public Object uploadFile(HttpServletRequest request) {
		List<BaseBean> result = ExcelImportUtil.getExcelDataFromReqeust(request,new VaccineItem());
		for (BaseBean object : result) {
			VaccineItem vaccineItem = (VaccineItem) object;
			System.out.println(vaccineItem.toString());
		}
		return StatusConfig.OK;
	}