java Excel文件上传 解析入库

    @ApiOperation("上传文件")
    @RequestMapping(value = "/uploadFile", method = RequestMethod.POST)
    public BusinessResult uploadFile(@RequestParam("file") MultipartFile file) {
        return productCommentService.uploadFile(file);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public BusinessResult uploadFile(MultipartFile file) {
        if (file.isEmpty()) throw new BusinessException("上传失败，请选择文件");
        LoginUserVO user = getSysUser();
        if (null == user) return BusinessResult.fail("无法获取用户信息");
        String userId = user.getId();
        String userName = user.getName();
        Date date = new Date();
        String fileName = file.getOriginalFilename();
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (!fileType.equals(FILE_SUFFIXS[0]) && !fileType.equals(FILE_SUFFIXS[1])) {
            throw new BusinessException("只允许上传xlsx或xls文件");
        }
        try {
            ExcelListen excelListen = new ExcelListen();
            EasyExcel.read(file.getInputStream(), ProductReview.class, excelListen).doReadAll();
            List<ProductReview> data = excelListen.getData();
            if (CollectionUtils.isEmpty(data)) return BusinessResult.fail("文件数据为空");
            data.stream().forEach(item -> {
                item.setCreateTime(date);
                item.setCreator(userName);
                item.setCreatorId(userId);
            });
            Set<String> commentIds = data.stream().filter(item -> StringUtils.isNotBlank(item.getCommentId())).map(ProductReview::getCommentId).collect(Collectors.toSet());
            if (CollectionUtils.isEmpty(commentIds)) return BusinessResult.fail("评论id为空");
            List<String> list = new ArrayList<>();
            list.addAll(commentIds);
            productReviewDao.deleteBatch(list);
            List<List<ProductReview>> lists = Lists.partition(data, 500);
            lists.forEach(productReviews -> {
                productReviewDao.batchAdd(productReviews);
            });
        } catch (Exception e) {
            logger.error("Excel文件解析失败，异常：" + e.getMessage(), e);
            throw new BusinessException("Excel文件解析失败，异常：" + e.getMessage(), e);
        }
        return BusinessResult.success();
    }

util类

public class ExcelListen extends AnalysisEventListener<ProductReview> {

    private List<ProductReview> list = new ArrayList<>();

    @Override
    public void invoke(ProductReview data, AnalysisContext context) {
        list.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }

    public List<ProductReview> getData() {
        return list;
    }
}

实体类

@Data
public class ProductReview implements Serializable {
    private Long id;
    @ExcelProperty(value = "产品ID")
    private String productId;
    @ExcelProperty(value = "评论ID")
    private String commentId;
    @ExcelProperty(value = "尺码")
    private String size;
    @ExcelProperty(value = "颜色")
    private String color;
    @ExcelProperty(value = "评论内容")
    private String commentContent;
    @ExcelProperty(value = "评论时间")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date commentTime;
    @ExcelProperty(value = "附图")
    private String imageUrls;
    private String creatorId;
    private String creator;
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;
}

maven依赖

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

文件常量名

private static final String[] FILE_SUFFIXS = {".xls", ".xlsx"};