学生照片批量导入功能总结——poi导入或者easyExcel导入

效果图

poi写法—传压缩包

压缩包里面包含了Excel文件和学生照片文件

    @Override
    @Transactional
    public CommonResult<Integer> importStudentInfoFromExcel(MultipartFile multipartFile) throws IOException, ApiException {

        System.out.println("开始解析excel....");
//        long start = System.currentTimeMillis();
        //当前登录用户

        File saveZipPath = new File(studentPicturePath);
        try {
            if (!saveZipPath.exists()) {
                saveZipPath.mkdirs();
            }
            String fileName = UUID.randomUUID().toString().replace("-", "");
            String originalFilename = multipartFile.getOriginalFilename();
            String suffix = originalFilename.substring(originalFilename.lastIndexOf("."));


            File saveZip = new File(studentPicturePath + fileName + suffix);
            multipartFile.transferTo(saveZip);
            File unzip = ZipUtil.unzip(saveZip, Charset.forName("GBK"));//解压压缩包
            saveZip.delete();
            File absoluteFile = unzip.getAbsoluteFile();//解压后的文件夹
            for (File listFile : absoluteFile.listFiles()) {//遍历解压后的文件夹中的所有文件
                String name = listFile.getName();
                //判断是不是excel文件（因为压缩包里还有照片文件）
                if (name.substring(name.lastIndexOf(".")).equals(".xlsx")) {

                    FileInputStream fis = new FileInputStream(listFile);
                    Workbook workbook = new XSSFWorkbook(fis);
                    Sheet sheet = workbook.getSheetAt(0);
                    sheet.removeRow(sheet.getRow(0));//删除第一行
                    for (Row row : sheet) {
                        Cell cell = row.getCell(0);//第一列的数据
                        cell.setCellType(CellType.STRING);
                        Integer id = Integer.valueOf(String.valueOf(cell));//学生id
                        StudentInfo studentInfo = studentInfoDao.selectById(id);
                        if (studentInfo != null) {
                            Cell cell1 = row.getCell(2);
                            studentInfo.setStudentPicture(fileName+"/"+cell1.toString());//学生的图片地址存起来
                            studentInfoDao.update(studentInfo);
                        }


                    }
                    listFile.delete();

                }

            }

            return CommonResult.success();
        } catch (Exception e) {
            e.printStackTrace();
            return CommonResult.failed();
        }
    }

easyexcel写法—不传压缩包

以下这种写法没用压缩包，而是用的一个Excel文件，图片也是插入到了Excel表中的

@Data
public class StudentPictureInfo {
	//学号
	@ExcelProperty("学号")
	@ApiModelProperty(value = "学号",required = true)
	private String studentId;

	//名字
	@ExcelProperty("姓名")
	@ApiModelProperty(value = "名字",required = true)
	private String name;

	// 学生照片
	@ExcelProperty("学生照片")
	@ApiModelProperty(value = "学生照片")
	private String studentPicture;

	@ExcelIgnore
	private String picture;

	@ExcelIgnore
	private PictureData pictureData;
}

 public CommonResult<Integer> importStudentPictureExcel(MultipartFile multipartFile) throws IOException {
        List<StudentPictureInfo> readList = new ArrayList<>();
        try {
            EasyExcel.read(multipartFile.getInputStream(), StudentPictureInfo.class, new SyncReadListener() {
                //EasyExcel在读取excel表格时，每读取到一行，就会调用一次这个方法
                @Override
                public void invoke(Object object, AnalysisContext context) {
                    //读取到的数据被存入bean
                    StudentPictureInfo studentInfo = (StudentPictureInfo) object;
                    readList.add(studentInfo);
                }
            }).doReadAll();
        } catch (ExcelAnalysisException e) {
            return CommonResult.failed(e.getMessage());
        }

        ExcelReader reader = ExcelUtil.getReader(multipartFile.getInputStream());
        //key是图片位置，row_cell的格式；value是图片数据
        Map<String, XSSFPictureData> picMap = getPictures((XSSFSheet) reader.getSheet());
        //这里只关心行数，把数据装到Bean里面去，也可用map在循环中取获取
        picMap.forEach((k, v) -> {
            String[] split = k.split(StrUtil.DASHED);
            Integer index = Integer.valueOf(split[0]);
            readList.get(index - 1).setPictureData(v);
        });

        readList.stream().parallel().forEach(i -> {
            if (null == i.getPictureData()) {
                return;
            }
            String mimeType = i.getPictureData().getMimeType();
            String[] mimeTypes = mimeType.split(StrUtil.SLASH);

            String fileName = UUID.randomUUID() + "." + mimeTypes[1];
            //把学生的照片文件存起来
            File file = FileUtil.writeBytes(i.getPictureData().getData(), filePath + "/" + DateUtil.today() + "/" + fileName);
            //把照片相对路径保存到学生记录中
            i.setStudentPicture(DateUtil.today() + "/" + fileName);
            // 入库
            System.out.println(file.getAbsolutePath());
        });

        int i = studentInfoDao.batchUpdateStudentPicture(readList);

        if (i > 0) {
            return CommonResult.success();
        }

        return CommonResult.failed();
    }