springmvc Excel文件上传

1、前端form表单添加Excel文件，使用ajax提交

<span><a href="javascript:void(0);" class="info_import">批量导入</a> </span>

 //导入按钮,弹出导入框
   $(".info_import").bind("click", function(){
	  $("#importiframe").show();
	  $("#importinfo").show();
   });

 <!-- 导入弹出框 -->
<div class="lock" id="importiframe"></div>
<div class="mt_info" id="importinfo">
	<div class="mt_info_t">
		<span class="fr close" id="importclose"></span>导入Excel文件
	</div>
	<div class="mt_info_c">
	    <div style="height:300px; top:50%; margin:-150px 0 0 0; position:absolute;">
			 <input type="file" id="file" name="file" size="10"/>
	         <input id="uploadbutton" type="button" value="上传"/>
        </div>
	</div>
    <div id="errorInfo" style="margin: 100px 0 0 10px;"></div>
</div>

// 点击上传按钮
$("#uploadbutton").click(function (){
		$("#errorInfo").html("");
		var formData = new FormData();
		var name = $("#file").val();
		if('' == name || name.indexOf("xls") < 0){
			alert("请选择Excel文件进行上传!");
			return;
		}
		formData.append("file",$("#file")[0].files[0]);
		formData.append("name",name);
		$.ajax({ 
			url : "../device_part/importExcel.action",
			type : 'POST', 
			data : formData, 
			// 告诉jQuery不要去处理发送的数据
			processData : false, 
			// 告诉jQuery不要去设置Content-Type请求头
			contentType : false,
			beforeSend:function(){
			 console.log("正在进行，请稍候");
			},
			success : function(responseStr) { 
			if(responseStr.success){
			  alert("导入成功");
			  window.location.reload();
			}else{
			  $("#errorInfo").html("导入失败！原因：" + responseStr.msg);
			}
			}, 
			error : function(responseStr) { 
			console.log("error");
		} 
	  });
   });

2、后端使用反射技术，将Excel文件中的数据映射到一个实体类中

/**
	 * 模板导入
	 * @param req
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "/importExcel")
	@ResponseBody
	public Map<String, Object> importExcel(HttpServletRequest req) throws Exception {
		Map<String, Object> returnMap = new HashMap<>();
		MultipartRequest multiRequest = (MultipartRequest) req;
		AuthorityUser user = (AuthorityUser) req.getSession().getAttribute(PublicConstants.SESSION_USER);
		MultipartFile file = multiRequest.getFile("file");// 获取上传excel文件
		List<DevicePartDto> devicePartDtoList = null;
		String returnMsg = "<br>";
		try {
			devicePartDtoList = ExcelUtils.getData(req,
					0, 
					2,
					new String[] { "deviceCode", "partName", "partNo", "warranty","qty" },//paramValue为参数类型名称
					new boolean[] { true, true, true, true, true},
					new String[] {"deviceCode", "partName", "partNo", "warranty","qty" }, 
					DevicePartDto.class);// 这里就是讲Excel数据映射到实体类中
                        boolean flag1 = true;
			for(DevicePartDto devicepartDto : devicePartDtoList){//保存数据
                            //devicepartDto  数据处理
                        }
			if(!flag1){// 某一条信息出现了错误
				returnMap.put(PublicConstants.SUCCESS_KEY, false);
				returnMap.put(PublicConstants.MESSAGE_KEY, returnMsg);
			}else{// 所有的信息都是正确的
				returnMap.put(PublicConstants.SUCCESS_KEY, true);
			}
		} catch (Exception e) {
			logger.error(e);
			returnMap.put(PublicConstants.SUCCESS_KEY, false);
			returnMsg +=e.getMessage();
			returnMap.put(PublicConstants.MESSAGE_KEY, returnMsg);
			return returnMap;
		}
		return returnMap;
	}

ExcelUtils工具类：

package cn.test.utils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateUtils;
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;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import cn.test.common.utils.DateUtil;

@SuppressWarnings("deprecation")
public class ExcelUtils {

	public final static Map<String, Class<?>[]> SET_PARAMS_MAP;
	static {
		SET_PARAMS_MAP = new HashMap<String, Class<?>[]>();
		SET_PARAMS_MAP.put("java.lang.String", new Class<?>[] { String.class });  
		SET_PARAMS_MAP.put("int", new Class<?>[] { Integer.class });
		SET_PARAMS_MAP.put("java.lang.Integer", new Class<?>[] { Integer.class });
		SET_PARAMS_MAP.put("double", new Class<?>[] { Double.class });
		SET_PARAMS_MAP.put("java.lang.Double", new Class<?>[] { Double.class });
		SET_PARAMS_MAP.put("long", new Class<?>[] { Long.class });
		SET_PARAMS_MAP.put("java.lang.Long", new Class<?>[] { Long.class });
		SET_PARAMS_MAP.put("java.math.BigDecimal", new Class<?>[] { BigDecimal.class });
		SET_PARAMS_MAP.put("char", new Class<?>[] { Character.class });
		SET_PARAMS_MAP.put("java.lang.Character", new Class<?>[] { Character.class });
		SET_PARAMS_MAP.put("boolean", new Class<?>[] { Boolean.class });
		SET_PARAMS_MAP.put("java.lang.Boolean", new Class<?>[] { Boolean.class });
		SET_PARAMS_MAP.put("short", new Class<?>[] { Short.class });
		SET_PARAMS_MAP.put("java.lang.Short", new Class<?>[] { Short.class });
		SET_PARAMS_MAP.put("byte", new Class<?>[] { Byte.class });
		SET_PARAMS_MAP.put("java.lang.Byte", new Class<?>[] { Byte.class });
		SET_PARAMS_MAP.put("date", new Class<?>[] { Date.class });
		SET_PARAMS_MAP.put("java.util.Date", new Class<?>[] { Date.class });
	}

	/**
	 * 返回类属性的类型Map,key为对应的set方法名
	 * 
	 * @param c
	 * @return
	 */
	public static <T> Map<String, String> getFieldType(Class<T> c) {
		Map<String, String> map = new HashMap<String, String>();
		Method[] ms = c.getMethods();
		String setter;
		for (Method m : ms) {
			setter = m.getName();
			if (setter.indexOf("set") == 0) {
				map.put(setter, m.getParameterTypes()[0].getName());
			}
		}
		return map;
	}

	/**
	 * 从导入的Excel获取数据
	 * @param request
	 * @param sheetIdx Excel工作簿序号,从0开始
	 * @param firstLineIdx 首行数据序列号 一般从1开始
	 * @param colNames 列名数组
	 * @param mustArray 对应列是否为必须
	 * @param mainKey 确定是否重复的键
	 * @param c Bean.class
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> getData(HttpServletRequest request, int sheetIdx, int firstLineIdx, String[] colNames,
			boolean[] mustArray, String[] mainKey, Class<T> c) throws Exception {
		List<T> list = new ArrayList<T>();
		try {
			Map<String, String> mainKeyMap = new HashMap<String, String>();//重复key map
			Map<String, String> mainKeyValueMap = new HashMap<String, String>();
			if (mainKey != null) {
				for (String mk : mainKey) {
					mainKeyMap.put(mk, mk);
				}
			}
			int colNum = colNames.length;//总列数
			InputStream in = null;
			MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
			MultipartFile multipartFile = multipartRequest.getFile("file");//上传文件
			Workbook wb = null;
			in = multipartFile.getInputStream();
			wb = new XSSFWorkbook(in);
			Sheet sheet = wb.getSheetAt(sheetIdx);//sheet1

			Map<String, String> fieldTypeMap = getFieldType(c);//实体类set方法 key为方法名 value为

			T t = null;
			String setter;
			String fieldTypeStr;
			String fieldName;
			String data;
			Class<?>[] filedType;
			Object[] params = null;
			Method m;

			//计算后面的空白行数量
			int blankEndCount = 0;
			for (int idx = sheet.getLastRowNum(); idx >= firstLineIdx; idx--) {
				Row row = sheet.getRow(idx);
				if (row == null) {
					blankEndCount++;
				} else {
					boolean isRowAllBlank = true;
					for (int colIdx = 0; colIdx < colNum; colIdx++) {
						data = GetValueTypeForXLSX(row.getCell(colIdx));
						if (!StringUtils.isBlank(data)) {
							isRowAllBlank = false;
						}
					}
					if (isRowAllBlank) {
						blankEndCount++;
					} else {
						break;
					}
				}
			}

			int len = sheet.getLastRowNum() - blankEndCount + 1;//Excel总行数
			StringBuilder sb = new StringBuilder();
			for (int idx = firstLineIdx; idx < len; idx++) {//逐行获取Excel数据
				Row row = sheet.getRow(idx);
				String mainKeyValue = "";
				if (row == null) {
					sb.append("【 ").append(idx + 1).append("行").append(" 】不能为空\\r\\n");
					continue;
				}
				t = c.newInstance();//实例化对象
				for (int colIdx = 0; colIdx < colNum; colIdx++) {//逐列获取Excel数据
					data = GetValueTypeForXLSX(row.getCell(colIdx));//excel值
					fieldName = colNames[colIdx];//excel列名
					if (mainKeyMap.get(fieldName) != null) {
						mainKeyValue += data;
					}
					if (StringUtils.isBlank(fieldName)) {
						continue;
					}
					setter = "set" + String.valueOf(fieldName.charAt(0)).toUpperCase() + fieldName.substring(1);
					fieldTypeStr = fieldTypeMap.get(setter);
					filedType = SET_PARAMS_MAP.get(fieldTypeStr);
					m = c.getMethod(setter, filedType);//获取set方法
					params = new Object[] { data };
					if (mustArray[colIdx] && StringUtils.isBlank(data)) {
						sb.append("【 ").append(idx + 1).append("*").append((char) ('A' + colIdx))
								.append(" 】不能为空\\r\\n");
						continue;
					}
					if (fieldTypeStr.indexOf("int") >= 0 || fieldTypeStr.indexOf("Integer") >= 0) {
						try {
							params = new Object[] { Integer.valueOf(StringUtils.isBlank(data) ? "0" : data) };
						} catch (NumberFormatException e) {
							sb.append("【 ").append(idx + 1).append("*").append((char) ('A' + colIdx))
									.append(" 】必须为数值\\r\\n");
							continue;
						}
					} else if (fieldTypeStr.indexOf("BigDecimal") >= 0) {
						try {
							params = new Object[] { new BigDecimal(StringUtils.isBlank(data) ? "0" : data) };
						} catch (NumberFormatException e) {
							sb.append("【 ").append(idx + 1).append("*").append((char) ('A' + colIdx))
									.append(" 】必须为数值\\r\\n");
							continue;
						}
					} else if (fieldTypeStr.indexOf("double") >= 0 || fieldTypeStr.indexOf("Double") >= 0) {
						try {
							params = new Object[] { Double.valueOf(StringUtils.isBlank(data) ? "0" : data) };
						} catch (NumberFormatException e) {
							sb.append("【 ").append(idx + 1).append("*").append((char) ('A' + colIdx))
									.append(" 】必须为数值\\r\\n");
							continue;
						}
					} else if (fieldTypeStr.indexOf("long") >= 0 || fieldTypeStr.indexOf("Long") >= 0) {
						try {
							params = new Object[] { Long.valueOf(StringUtils.isBlank(data) ? "0" : data) };
						} catch (NumberFormatException e) {
							sb.append("【 ").append(idx + 1).append("*").append((char) ('A' + colIdx))
									.append(" 】必须为数值\\r\\n");
							continue;
						}
					} else if (fieldTypeStr.indexOf("char") >= 0 || fieldTypeStr.indexOf("Character") >= 0) {
						params = new Object[] { data.charAt(0) };
					} else if (fieldTypeStr.indexOf("boolean") >= 0 || fieldTypeStr.indexOf("Boolean") >= 0) {
						params = new Object[] { Boolean.valueOf(data) };
					} else if (fieldTypeStr.indexOf("short") >= 0 || fieldTypeStr.indexOf("Short") >= 0) {
						try {
							params = new Object[] { Short.valueOf(StringUtils.isBlank(data) ? "0" : data) };
						} catch (NumberFormatException e) {
							sb.append("【 ").append(idx + 1).append("*").append((char) ('A' + colIdx))
									.append(" 】必须为数值\\r\\n");
							continue;
						}
					}else if (fieldTypeStr.indexOf("byte") >= 0 || fieldTypeStr.indexOf("Byte") >= 0) {
						try {
							params = new Object[] { Byte.valueOf(StringUtils.isBlank(data) ? "0" : data) };
						} catch (NumberFormatException e) {
							sb.append("【 ").append(idx + 1).append("*").append((char) ('A' + colIdx))
									.append(" 】必须为数值\\r\\n");
							continue;
						}
					}else if (fieldTypeStr.indexOf("date") >= 0 || fieldTypeStr.indexOf("Date") >= 0) {// 日期类型
						try{
							int dayNum = DateUtil.diffDate(new Date(), new SimpleDateFormat("yyyy-MM-dd").parse("1899-12-30"));// 默认计算当前日期到1990-01-01的天数
							int data2 = Integer.valueOf(StringUtils.isBlank(data) ? Integer.toString(dayNum) : data) ;
							Calendar calendar = new GregorianCalendar(1900, 0, -1);
							Date ddd = calendar.getTime();
							params = new Object[] {DateUtils.addDays(ddd, data2)}; // data2是距离1900年1月1日的天数
						}catch (Exception e){
							sb.append("【 ").append(idx + 1).append("*").append((char) ('A' + colIdx))
							.append(" 】日期出错\\r\\n");
							continue;
						}
					}
					m.invoke(t, params);//调用set方法赋值
				}
				if (!StringUtils.isBlank(mainKeyValue)) {
					if (mainKeyValueMap.get(mainKeyValue) != null) {
						sb.append("【 ").append(idx + 1).append("行").append(" 】为重复数据\\r\\n");
						continue;
					} else {
						mainKeyValueMap.put(mainKeyValue, mainKeyValue);
					}
				}
				list.add(t);
			}
			if (sb.length() > 0) {
				throw new Exception("<span style='color:red;'>数据异常</span><br><br><textarea rows='5' cols='40'>"
						+ sb.toString() + "</textarea>");

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage(), e);
		}
		return list;
	}

	/**
	 * 将Double转成String,可能不含有小数和小数点
	 * @param value
	 * @return
	 */
	public static String doubleToString(Double value) {
		String rs = "";
		if (value != null) {
			Long a = value.longValue();
			double b = value.doubleValue() - a;
			if (b == 0) {
				rs = a.toString();
			} else {
				rs = value.toString();
			}
		}
		return rs;
	}

	public static String GetValueTypeForXLSX(Cell cell) {
		if (cell == null)
			return null;
		Object obj;
		cell.setCellType(Cell.CELL_TYPE_STRING);
		obj = cell.getStringCellValue();
		return obj == null ? "" : (obj + "").trim();
	}

}