• 一个Execl就是一个Workbook
• 一个Sheet就是一张表格
• 一个Workbook可以包含多个Sheet
• 一行为一个Row
• 每一行（Row）的每一列就是一个单元格（Cell）

 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.8</version> <exclusions> <exclusion> <artifactId>commons-codec</artifactId> <groupId>commons-codec</groupId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.8</version> </dependency> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.3</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.5</version> </dependency>

<bean id="multipartResolve" class="org.springframework.web.multipart.support.StandardServletMultipartResolver"> </bean>

 //导入excel用户信息 @ResponseBody @RequestMapping(value = "/importUserInfo.do", method = RequestMethod.POST) public ResultModel importUserInfo(@RequestParam("file") MultipartFile file) throws Exception { if (!file.isEmpty()) { InputStream inputStream = file.getInputStream(); return ResultModel.builder() .data(userService.importUserInfo(inputStream, file.getOriginalFilename())) .code(SUCCESS) .build(); } else return ResultModel.builder() .data("File is empty!") .code(SUCCESS) .build(); }

@Override public Integer importUserInfo(InputStream filePath, String file) { List<User> list = new ArrayList<>(); String[] s; if (!file.endsWith(".xls") && !file.endsWith(".xlsx")) { //文件上传格式出错 return -2; } Workbook workbook = null; try { //获取Workbook实例 workbook = ExcelUtil.getWorkBook(file, filePath); } catch (Exception e) { e.printStackTrace(); } Sheet sheet = workbook.getSheetAt(0);  //去除sheet中的一些无效行，比如值为空但是有格式的空白行 sheet = ExcelUtil.resetSheet(sheet); int lastRowNum = sheet.getLastRowNum(); for (int i = 1; i <= lastRowNum; i++) { Row row = sheet.getRow(i); int cells = sheet.getRow(i).getPhysicalNumberOfCells();  //获取每一行的数值，并将其填入一个字符串的数组 s = ExcelUtil.getExcelRows(row, cells); User user = new User(); for (int j = 0; j < s.length; j++) { if (j == 0 && !s[j].equals("")) user.setInstitute(s[j]); else if (j == 1 && !s[j].equals("")) user.setMajor(s[j]); else if (j == 2 && !s[j].equals("")) user.setClassId(Integer.getInteger(s[j])); else if (j == 3 && !s[j].equals("")) user.setUsername(s[j]); else if (j == 4 && !s[j].equals("")) user.setRealName(s[j]); else if (j == 5 && !s[j].equals("")) user.setBirthday(DateUtil.parseYYYYMMDDDate(s[j])); else if (j == 6 && !s[j].equals("")) user.setQq(s[j]); else if (j == 7 && !s[j].equals("")) user.setTieba(s[j]); else if (j == 8 && !s[j].equals("")) user.setWeibo(s[j]); else if (j == 9 && !s[j].equals("")) user.setBlog(s[j]); user.setPassword("huhugty7tgf6f"); } list.add(user); } return userDao.insertList(list); }

import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellReference;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.InputStream;public class ExcelUtil { //判断文件类型是否满足要求 public static Workbook getWorkBook(String file, InputStream fis)throws Exception{ Workbook workbook = null; if(!file.endsWith(".xls") && !file.endsWith(".xlsx")){ throw new Exception("上传表格的格式错误！"); } if(file.endsWith(".xls")){ workbook = new HSSFWorkbook(fis,true); } if(file.endsWith(".xlsx")){ workbook = new XSSFWorkbook(fis); } return workbook; } //获取每一行的数据，并且返回一个字符串数组 public static String[] getExcelRows(Row row , int columuNum){ String[] s ; StringBuilder builder = new StringBuilder(); for(int x = 0;x<columuNum;x++){ Cell cell = row.getCell(x); builder.append(getStringCellValue(cell)+","); } s = builder.toString().split(","); return s; } //获取单元内的有效数据 public static String getStringCellValue(Cell cell){ StringBuilder sb = new StringBuilder(); switch (cell.getCellType()){ //数字 case Cell.CELL_TYPE_NUMERIC: if(DateUtil.isCellDateFormatted(cell)){ sb.append(cell.getDateCellValue()); }else { //将该数字强制转化为字符串类型获取 cell.setCellType(Cell.CELL_TYPE_STRING); sb.append(cell.getStringCellValue()); } break; //字符串 case Cell.CELL_TYPE_STRING: sb.append(cell.getStringCellValue()); break; //布尔 case Cell.CELL_TYPE_BOOLEAN: sb.append(cell.getBooleanCellValue()); break; //公式 case Cell.CELL_TYPE_FORMULA: sb.append(cell.getCellFormula()); break; //空值 case Cell.CELL_TYPE_BLANK: sb.append(""); break; //故障 case Cell.CELL_TYPE_ERROR: sb.append(""); break; default: sb.append(""); break; } return sb.toString(); } //过滤掉表中的无意义空白行，因为getLastRowNum（）在获取Row行数时，对于表中没有值但却有格式的无意义空白行也将计入 public static Sheet resetSheet(Sheet sheet){ CellReference cellReference = new CellReference("A4"); boolean flag; for(int i=cellReference.getRow();i<=sheet.getLastRowNum();){ Row r = sheet.getRow(i); if(r == null){ sheet.shiftRows(i+1,sheet.getLastRowNum(),-1); continue; } flag = false; for(Cell c : r){ if(c.getCellType()!=Cell.CELL_TYPE_BLANK){ flag = true; break; } } if(flag){ i++; continue; } else{ //如果是空白行，没有数据，但是有格式 if(i == sheet.getLastRowNum()) sheet.removeRow(r); else sheet.shiftRows(i+1,sheet.getLastRowNum(),-1); } } return sheet; }}