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