最菜的Java导入excel

poi Maven

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.14</version> </dependency> 

基础代码

package com.peas.mdp.module.util; import com.peas.mdp.dto.Teacher; import org.apache.poi.ss.usermodel.*; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; public class ExcelUtil { public static List<?> getBankListByExcel(InputStream in) throws Exception { List<Teacher> list = new ArrayList<>(); //创建Excel工作薄 Workbook work = getWorkbook(in); if (null == work) { throw new Exception("创建Excel工作薄为空！"); } Sheet sheet = null; Row row = null; Cell cell = null; //遍历Excel中所有的sheet for (int i = 0; i < work.getNumberOfSheets(); i++) { sheet = work.getSheetAt(i); if (sheet == null) { continue; } //遍历当前sheet中的所有行 for (int j =1; j <= sheet.getLastRowNum(); j++) { row = sheet.getRow(j); if (row == null ) { continue; } Teacher teacher = new Teacher(); //把每个单元格的值付给对象的对应属性 if (row.getCell(0)!=null){ teacher.setAccount(String.valueOf(getCellValue(row.getCell(0)))); } if (row.getCell(1)!=null){ teacher.setName(String.valueOf(getCellValue(row.getCell(1)))); } if (row.getCell(2)!=null){ teacher.setSex(Integer.valueOf((String) getCellValue(row.getCell(2)))); } if (row.getCell(3)!=null){ teacher.setAge(Integer.valueOf((String) getCellValue(row.getCell(3)))); } if (row.getCell(4)!=null){ teacher.setEducation(String.valueOf(getCellValue(row.getCell(4)))); } if (row.getCell(5)!=null){ teacher.setJobTitle(String.valueOf(getCellValue(row.getCell(5)))); } if (row.getCell(6)!=null){ teacher.setPosition(String.valueOf(getCellValue(row.getCell(6)))); } if (row.getCell(7) != null) { teacher.setRole(String.valueOf(getCellValue(row.getCell(7)))); } if (row.getCell(8) != null) { teacher.setPhone(String.valueOf(getCellValue(row.getCell(8)))); } if (row.getCell(9) != null) { teacher.setEmail(String.valueOf(getCellValue(row.getCell(9)))); } if (row.getCell(10) != null) { teacher.setKeyWords(String.valueOf(getCellValue(row.getCell(10)))); } if (row.getCell(11) != null) { teacher.setIntroduction(String.valueOf(getCellValue(row.getCell(11)))); } if (row.getCell(12) != null) { teacher.setIdCard(String.valueOf(getCellValue(row.getCell(12)))); } if (row.getCell(13) != null) { teacher.setType(Integer.valueOf((String)getCellValue(row.getCell(13)))); } //遍历所有的列(把每一行的内容存放到对象中) list.add(teacher); } } return list; } /** * * @param inStr * @return * @throws Exception */ public static Workbook getWorkbook(InputStream inStr) throws Exception { Workbook wb = null; wb = WorkbookFactory.create(inStr); return wb; } /** * 描述：对表格中数值进行格式化 * * @param cell * @return */ public static Object getCellValue(Cell cell) { Object value = null; DecimalFormat df = new DecimalFormat("0"); //格式化number String字符 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化 DecimalFormat df2 = new DecimalFormat("0"); //格式化数字 switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if ("General".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) { value = sdf.format(cell.getDateCellValue()); } else { value = df2.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK: value = ""; break; default: break; } return value; } } 

Control

@ApiOperation("excel导入") @GET @Path("excelFile") public Map<String, String> readExcel(@QueryParam("path") File path) throws Exception { HashMap<String, String> map = new HashMap<>(); //读取excel中的内容 InputStream inputStream = new FileInputStream(path); List<Teacher> teachers = (List<Teacher>) ExcelUtil.getBankListByExcel(inputStream); for (Teacher teacher : teachers) { teacherService.saveOrUpdate(teacher); } return map; } /** * 读取excel更新数据 * * @param upfile * @param * @param * @throws Exception */ /* @RequestMapping(value = "/readExcel") @Transactional public Map<String, String> readExcel(MultipartFile upfile) throws Exception { HashMap<String, String> map = new HashMap<>(); InputStream in = upfile.getInputStream(); //读取excel中的内容 List<LineDownOrder> lineDownOrders = ExcelUtils.getBankListByExcel(in); String s = orderService.updateByExcel(lineDownOrders); map.put("status", s); return map; } */