导入excel(xls格式, xlsx格式大部分都把类似HSSF替换成XSSF就行了，看api)

悠悠 2022-06-07 05:49 192阅读 0赞

@RequestMapping(value = "/impExcel")
    public String impExcel() {
    	String result = "success";
    	try{
    		String path = "C:/Users/dulinan/Desktop/paopao.xls";
    		File file = new File(path);
    		InputStream is = new FileInputStream(file);
    		HSSFWorkbook wb = new HSSFWorkbook(is);
    		Date createTime = new Date();
    		
    		List<Teacher> list = new ArrayList<Teacher>();
    		/*Map<String, Object> allMap = new HashMap<String, Object>();
    		List<Teacher> duochuList = new ArrayList<Teacher>();*/
    		int count = 0;
    		for (int i = 0; i < 1; i++) {//遍历sheet，这里只有一个
    			Sheet sheet = wb.getSheetAt(i);
    			int subtotal = list.size();
    			if (sheet != null) {
    				int lastRowNum = sheet.getLastRowNum();
    				for (int rowNum = sheet.getFirstRowNum() + 1; rowNum <= lastRowNum; rowNum++) {//遍历每一行
    					HSSFRow row = (HSSFRow) sheet.getRow(rowNum);
    					
    					if (row == null) {
    						continue;
    					}
    					
    					Teacher teacher = new Teacher();
    					String name = getCellValue(row.getCell(0));
    					
    					if (StringUtils.isBlank(name)) {
    						continue;
    					}
    					
    					String testNumber = getCellValue(row.getCell(1));
    					String school = getCellValue(row.getCell(2));
    					String subject = getCellValue(row.getCell(3));
    					String email = getCellValue(row.getCell(4));
    					String schoolarea = getCellValue(row.getCell(5));
    					String classroom = getCellValue(row.getCell(6));
    					String testTime = getCellValue(row.getCell(7));
    					
    					
    					teacher.setOrderNumber(count++);
    					teacher.setId(IdUtils.getId());
    					teacher.setName(name);
    					teacher.setTestNumber(testNumber);
    					teacher.setEmail(email);
    					teacher.setTeacherCode("不显示");
    					teacher.setSchool(school);
    					teacher.setGrade("不显示");
    					teacher.setSubject(subject);
    					teacher.setClasss("不显示");
    					teacher.setSchoolarea(schoolarea);
    					teacher.setClassroom(classroom);
    					teacher.setTestTime(testTime);
    					
    					teacher.setCreateTime(createTime);
    					list.add(teacher);
    					/*
    					if (allMap.containsKey(email)) {
    						duochuList.add(teacher);
    					} else {
    						allMap.put(email, teacher);
    					}*/
    					
    				}
    				System.out.println(sheet.getSheetName() + " 共有 " + (list.size() - subtotal) + " 条数据");
    			}
    		}
    		System.out.println("共有" + list.size() + "条数据");
    		int code = teacherService.insertBatch(list);
    		System.out.println(code);
    		is.close();
    	} catch(Exception e) {
    		result = "fail";
    		e.printStackTrace();
    	}
    	return result;
    }
    
    private String getCellValue(Cell cell) {
    	if (cell != null) {
    		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
    			// 返回布尔类型的值
    			return String.valueOf(cell.getBooleanCellValue());
    		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
    			// 返回数值类型的值
    			return String.valueOf(cell.getNumericCellValue());
    		} else {
    			// 返回字符串类型的值
    			String value = String.valueOf(cell.getStringCellValue());
    			return StringUtils.isNotBlank(value) ? value.replace("\n", "<br />") : "";
    		}
    	} else {
    		return "";
    	}
    	
    }