按钮：
<button class="layui-btn layui-btn-sm" id="upload_importCustomer">导入</button>upload 控件渲染：
upload.render({ elem: '#upload_importCustomer' ,url: ctx+'/customer/customer/importData/' ,accept: 'file' //普通文件 ,data:{importType:1} ,done: function(response){ layer.msg('导入完成，若无数据，请到【导入记录】页面查看,或5秒后刷新'); setTimeout(function () { active.reload(); },3000); } ,error: function(){ layer.msg('导入失败，请到【导入记录】页面查看！'); active.reload(); }});java:
/** * 导入客户 */ @RequestMapping(value = "importData") @ResponseBody public Object importData(@RequestParam("importType") Integer importType,HttpServletRequest request, HttpServletResponse response){ BaseResp resp = new BaseResp(); try { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; MultiValueMap<String, MultipartFile> map = multipartRequest.getMultiFileMap(); StringBuffer sb = new StringBuffer(); ExcelReadUtil excel = null; if (map != null) { Iterator iter = map.keySet().iterator(); while (iter.hasNext()) { String str = (String) iter.next(); List<MultipartFile> fileList = map.get(str); for (MultipartFile mpf : fileList) { excel = new ExcelReadUtil(); excel.importExcel(mpf.getInputStream()); String errMsg = ""; Map<String,Object> checkMap = CustomerImportUtils.checkCustomerImport(excel,errMsg); if ("false".equals(checkMap.get("check"))) { sb.append(mpf.getOriginalFilename()+ "文件,"+checkMap.get("msg")+"未填写"); } else {// List<AccountCustomerImportPo> records = CustomerImportUtils.getAccountCustomerFormExcel(excel); List<CustomerImportRecordPo> records = (List<CustomerImportRecordPo>)checkMap.get("list"); System.out.println("records.size="+records.size()); ImportRecordThread thread = new ImportRecordThread(records, mpf.getOriginalFilename(),importType,getUser()); thread.start(); } } } } if (sb.length() > 0) { resp.setCode(BaseResp.RC_NOT); resp.setSuccess(false); resp.setMsg(sb.toString()); } } catch (Exception e) { e.printStackTrace(); logger.warn("controller Exception: ", e); resp.setCode(BaseResp.RC_NOT); resp.setSuccess(false); resp.setMsg("导入客户失败！请检查文件格式和必填栏目"); } return resp; }重点代码是：
List<MultipartFile> fileList = map.get(str);
获取文件。
处理文件：
excel = new ExcelReadUtil(); excel.importExcel(mpf.getInputStream());ExcelReadUtil.java:
import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.xssf.usermodel.*;import java.io.File;import java.io.FileInputStream;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import java.util.Vector;public class ExcelReadUtil { private XSSFWorkbook workbook;// 工作簿 /** * 读取文件路径字符串。 * <p> * 详细描述。 * </p> * * @param strfile */ public void importExcel(String strfile) { try { // 获取工作薄workbook workbook = new XSSFWorkbook(new FileInputStream(strfile)); } catch (Exception e) { e.printStackTrace(); } } /** * 读取文件。 * <p> * 详细描述。 * </p> * * @param file */ public void importExcel(File file) { try { workbook = new XSSFWorkbook(new FileInputStream(file)); } catch (Exception e) { e.printStackTrace(); } } /** * 读取文件流。 * <p> * 详细描述。 * </p> * * @param filestream */ public void importExcel(InputStream filestream) { try { workbook = new XSSFWorkbook(filestream); } catch (Exception e) { e.printStackTrace(); } } /** * 获取想要第几张工作表第几行的数据importExcel导入。 * <p> * 详细描述。 * </p> * * @param sheetNumber * @return */ @SuppressWarnings("deprecation") public List<Vector<Object>> readSet(int sheetNumber, int rowIndex) { List<Vector<Object>> result = new ArrayList<Vector<Object>>(); try { // 获得指定的sheet XSSFSheet sheet = workbook.getSheetAt(sheetNumber); // 获得sheet总行数 int rowCount = sheet.getLastRowNum(); if (rowCount < 1) { return result; } // HashMap<Integer, Object> map=new HashMap<Integer, Object>(); // 遍历行row for (; rowIndex <= rowCount; rowIndex++) { // 获得行对象 XSSFRow row = sheet.getRow(rowIndex); if (null != row) { // List<Object> rowData = new ArrayList<Object>(); Vector<Object> vector = new Vector<Object>(); // 获得本行中单元格的个数 int cellCount = row.getLastCellNum(); // 遍历列cell for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) { XSSFCell cell = row.getCell(cellIndex); // 获得指定单元格中的数据 Object cellStr = this.getCellString(cell); // map.put(arg0, arg1) vector.add(cellStr); } result.add(vector); } } } catch (Exception e) { System.out.println("readSet error:"+e.getMessage()); result = null; } return result; } /** * 获取想要第几张工作表的数据importExcel导入。 * <p> * 详细描述。 * </p> * * @param sheetNumber * @return */ @SuppressWarnings({"unchecked", "deprecation"}) public List readSet(int sheetNumber) { List<List> result = new ArrayList<List>(); // 获得指定的sheet XSSFSheet sheet = workbook.getSheetAt(sheetNumber); // 获得sheet总行数 int rowCount = sheet.getLastRowNum(); if (rowCount < 1) { return result; } // HashMap<Integer, Object> map=new HashMap<Integer, Object>(); // 遍历行row for (int rowIndex = 0; rowIndex <= rowCount; rowIndex++) { // 获得行对象 XSSFRow row = sheet.getRow(rowIndex); if (null != row) { // List<Object> rowData = new ArrayList<Object>(); Vector<Object> vector = new Vector<Object>(); // 获得本行中单元格的个数 int cellCount = row.getLastCellNum(); // 遍历列cell for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) { XSSFCell cell = row.getCell(cellIndex); // 获得指定单元格中的数据 Object cellStr = this.getCellString(cell); // map.put(arg0, arg1) vector.add(cellStr); } result.add(vector); } } return result; } /** * 从第几张工作表第几行的数据importExcel导入。 * <p> * 详细描述。 * </p> * * @param sheetNumber * @param rowIndex * @return */ @SuppressWarnings({"unchecked", "deprecation"}) public List readRow(int sheetNumber, int rowIndex) { List result = new ArrayList(); // 获得指定的sheet XSSFSheet sheet = workbook.getSheetAt(sheetNumber); // 获得sheet总行数 int rowCount = sheet.getLastRowNum(); if (rowCount < 1) { return result; } // 遍历行row // for (int rowIndex = rows+2; rowIndex <= rowCount; rowIndex++) { // 获得行对象 XSSFRow row = sheet.getRow(rowIndex); if (null != row) { // Vector<Object> vector=new Vector<Object>(); // 获得本行中单元格的个数 int cellCount = row.getLastCellNum(); // 遍历列cell for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) { XSSFCell cell = row.getCell(cellIndex); // 获得指定单元格中的数据 Object cellStr = this.getCellString(cell); // vector.add(cellStr); result.add(cellStr); } } // } return result; } /** * 获取指定工作表的总。 * <p> * 详细描述。 * </p> * * @param sheetNumber * @return */ public int getRowIndex(int sheetNumber) { XSSFSheet sheet = workbook.getSheetAt(sheetNumber); // 获得sheet总行数 int rowCount = sheet.getLastRowNum(); if (rowCount < 1) { return 0; } return rowCount; } /** * 从第几张工作表第几行读到第几行。 * <p> * 详细描述。 * </p> * * @param sheetNumber * @param rows * @param getrows * @return */ @SuppressWarnings({"unchecked", "deprecation"}) public List readCell(int sheetNumber, int rows, int getrows) { List<List> result = new ArrayList<List>(); // 获得指定的sheet XSSFSheet sheet = workbook.getSheetAt(sheetNumber); // 获得sheet总行数 int rowCount = getrows; if (rowCount < 1) { return result; } // HashMap<Integer, Object> map=new HashMap<Integer, Object>(); // 遍历行row for (int rowIndex = rows + 2; rowIndex <= rowCount; rowIndex++) { // 获得行对象 XSSFRow row = sheet.getRow(rowIndex); if (null != row) { // List<Object> rowData = new ArrayList<Object>(); Vector<Object> vector = new Vector<Object>(); // 获得本行中单元格的个数 int cellCount = row.getLastCellNum(); // 遍历列cell for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) { XSSFCell cell = row.getCell(cellIndex); // 获得指定单元格中的数据 Object cellStr = this.getCellString(cell); // map.put(arg0, arg1) vector.add(cellStr); } result.add(vector); } } return result; } /** * 读取第几张工作表的第几列。 * <p> * 详细描述。 * </p> * * @param sheetNumber * @param cells * @return */ @SuppressWarnings("unchecked") public List readColum(int sheetNumber, int cells) { List<List> result = new ArrayList<List>(); // 获得指定的sheet XSSFSheet sheet = workbook.getSheetAt(sheetNumber); // 获得sheet总行数 int rowCount = sheet.getLastRowNum(); if (rowCount < 1) { return result; } // HashMap<Integer, Object> map=new HashMap<Integer, Object>(); // 遍历行row for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++) { // 获得行对象 XSSFRow row = sheet.getRow(rowIndex); if (null != row) { // List<Object> rowData = new ArrayList<Object>(); Vector<Object> vector = new Vector<Object>(); // 获得本行中单元格的个数 XSSFCell cell = row.getCell(cells); Object cellStr = this.getCellString(cell); vector.add(cellStr); result.add(vector); } } return result; } /** * 获取一个cell的数据类型 * * @param cell * @return */ private Object getCellString(XSSFCell cell) { Object result = null; if (cell != null) { // 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5 int cellType = cell.getCellType(); switch (cellType) { case HSSFCell.CELL_TYPE_STRING: result = cell.getRichStringCellValue().getString(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue(); } else result = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: result = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: result = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: result = null; break; case HSSFCell.CELL_TYPE_ERROR: result = null; break; default: break; } } return result; } /** * 删除整行 * * @param sheetNum * @param rowNum */ public void deleteRow(int sheetNum, int rowNum) { XSSFSheet sheet = workbook.getSheetAt(sheetNum); int lastRowNum = sheet.getLastRowNum(); if (rowNum >= 0 && rowNum < lastRowNum) sheet.shiftRows(rowNum + 1, lastRowNum, -1);//将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行 if (rowNum == lastRowNum) { XSSFRow removingRow = sheet.getRow(rowNum); if (removingRow != null) sheet.removeRow(removingRow); } } /** * 在行最后添加一个单元格 * * @param sheetNum * @param rowNum */ public void addCell(int sheetNum, int rowNum, String content) { XSSFSheet sheet = workbook.getSheetAt(sheetNum); XSSFRow row = sheet.getRow(rowNum); XSSFCell cell = row.createCell(row.getLastCellNum()); XSSFCellStyle style = row.getCell(0).getCellStyle(); cell.setCellStyle(style); cell.setCellValue(content); } /** * 新增或者修改特定单元格 * * @param sheetNum * @param rowNum * @param content */ public void addModifyCell(int sheetNum, int rowNum, int columnNum, String content) { XSSFSheet sheet = workbook.getSheetAt(sheetNum); XSSFRow row = sheet.getRow(rowNum); XSSFCell cell = row.getCell(columnNum); if (row.getCell(columnNum) == null) { cell = row.createCell(columnNum); XSSFCellStyle style = row.getCell(0).getCellStyle(); cell.setCellStyle(style); } cell.setCellValue(content); } public XSSFWorkbook getWorkbook() { return workbook; } public void setWorkbook(XSSFWorkbook workbook) { this.workbook = workbook; }}接下来是解析excel数据，转成list:
Map<String,Object> checkMap = CustomerImportUtils.checkCustomerImport(excel,errMsg);CustomerImportUtils.java:
public class CustomerImportUtils { private static ImportException setCheckMap(Map<String,Object> checkMap,int rowIndex,int colIndex){ checkMap.put("check","false"); char c = 'A'; c = (char)(c+colIndex); checkMap.put("msg","【"+(rowIndex+2)+"行"+c+"列】"); return new ImportException(); } public static Map<String,Object> checkCustomerImport(ExcelReadUtil excel,String errMsg){ Map<String,Object> checkMap = new HashMap<String,Object>(); int dataStartRow = 1; int colIndex = 0; // 列序号 List<Vector<Object>> list = excel.readSet(0, dataStartRow); if(list==null){ checkMap.put("check","false"); checkMap.put("msg","表格中存在表达式，请检查！"); } boolean result = true; List<CustomerImportRecordPo> customerImportRecordPoList = new ArrayList<CustomerImportRecordPo>(); if (list != null && list.size() > 0) { for (int i = 0, index = dataStartRow; i < list.size(); i++) { CustomerImportRecordPo customerImportRecordPo = new CustomerImportRecordPo(); try{ Vector<Object> vector = list.get(i); //cjianquan 2019/11/18 这里是页签 if(vector!=null && vector.size()==1 && "客户信息".equals(vector.get(0))){ continue; } try{ if (checkVectorNotNull(vector)) { // 校验行数据不为null int size = 1; if (vector.size() >= size) { // 至少包含两列（至少包含两列必填项） colIndex = 0; String region = cellDataDeal(vector.get(colIndex), ""); // 区域 if (StringUtils.isBlank(region)) { result = false; throw setCheckMap(checkMap,i,colIndex); } customerImportRecordPo.setRegion(region); ++colIndex; String name = cellDataDeal(vector.get(colIndex), ""); // 公司名称 if (StringUtils.isBlank(name)) { result = false; throw setCheckMap(checkMap,i,colIndex); } name = name.replaceAll(" ","").replaceAll("\\(","（").replaceAll("\\)","）"); customerImportRecordPo.setName(name); ++colIndex; String contacts = cellDataDeal(vector.get(colIndex), ""); // 联系人 if (StringUtils.isBlank(contacts)) { result = false; throw setCheckMap(checkMap,i,colIndex); } customerImportRecordPo.setContacts(contacts); ++colIndex; String mobile = cellDataDeal(vector.get(colIndex), ""); // 手机号 if (StringUtils.isBlank(mobile)) { result = false; throw setCheckMap(checkMap,i,colIndex); } customerImportRecordPo.setMobile(mobile); ++colIndex; String issueTime = cellDataDeal(vector.get(colIndex), ""); // 企业注册日期* customerImportRecordPo.setIssueTime(issueTime); ++colIndex; String taxpayerType = cellDataDeal(vector.get(colIndex), ""); // 纳税人类型 customerImportRecordPo.setTaxpayerType(taxpayerType); ++colIndex; String telephone = cellDataDeal(vector.get(colIndex), ""); // 固话号码 customerImportRecordPo.setTelephone(telephone); ++colIndex; String address = cellDataDeal(vector.get(colIndex), ""); // 详细地址 customerImportRecordPo.setAddress(address); ++colIndex; String industry = cellDataDeal(vector.get(colIndex), ""); // 所属行业 customerImportRecordPo.setIndustry(industry); ++colIndex; String email = cellDataDeal(vector.get(colIndex), ""); // 电子邮箱 customerImportRecordPo.setEmail(email); ++colIndex; String website = cellDataDeal(vector.get(colIndex), ""); // 客户网址 customerImportRecordPo.setWebsite(website); ++colIndex; String remark = cellDataDeal(vector.get(colIndex), ""); // 备注描述 customerImportRecordPo.setRemark(remark); ++colIndex; customerImportRecordPoList.add(customerImportRecordPo); } else { result = false; } } }catch (ArrayIndexOutOfBoundsException e){ //cjianquan 2019/11/18 因为很多为空，不算入列长 e.printStackTrace(); customerImportRecordPoList.add(customerImportRecordPo); continue; } }catch (ImportException e){ customerImportRecordPoList.add(customerImportRecordPo); } if(!result){ break; } } } checkMap.put("list",customerImportRecordPoList); return checkMap; } /** * 处理单元格数据，最终转换成字符串类型 * * @param cellData * @return */ private static String cellDataDeal(Object cellData, String type) { String str = ""; if (cellData == null) { } else if (cellData instanceof String) { str = (String) cellData; } else if (cellData instanceof Double) { double tmp = (Double) cellData; if (type.equals("integer")) { long tmp2 = (long) tmp; str = String.valueOf(tmp2); } else if (type.equals("double")) { str = String.valueOf(tmp); } else { long tmp2 = (long) tmp; str = String.valueOf(tmp2); } // long tmp2 = (long) tmp; } else if (cellData instanceof Integer) { int tmp = (Integer) cellData; str = String.valueOf(tmp); } else if (cellData instanceof Float) { float tmp = (Float) cellData; str = String.valueOf(tmp); } else if (cellData instanceof Long) { long tmp = (Long) cellData; str = String.valueOf(tmp); } else if (cellData instanceof Date) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); str = sdf.format(cellData); } else { str = (String) cellData; } return str; } /** * 校验行数据不为null * * @param vector * @return */ private static boolean checkVectorNotNull(Vector<Object> vector) { int j = 0; int size = vector.size(); for (int i = 0; i < size; i++) { if (vector.get(i) != null) { return true; } else { j++; } } if (j == size) { return false; } else { return true; } }ImportException 是自定义异常类：
public class ImportException extends Exception { private static final long serialVersionUID = -6357149550353160810L; private int code; private String msg; public ImportException(){ super(); } public ImportException(int code){ super("ImportException code="+code); } public ImportException(int code, String msg){ super("ImportException code="+code+",msg="+msg); }}