SpringBoot上传xlsx并批量导入

最新推荐文章于 2024-05-09 20:55:58 发布

树欲静而风不止

最新推荐文章于 2024-05-09 20:55:58 发布

阅读量332

点赞数

分类专栏： SpringBoot 文章标签： Spring导入excel easyexcel

本文链接：https://blog.csdn.net/zjy660358/article/details/126305567

版权

SpringBoot 专栏收录该内容

40 篇文章 0 订阅

订阅专栏

maven

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

bean

public class Point extends Model<Point> {
    @Schema(description = "唯一主键")
    @TableId(type = IdType.ASSIGN_UUID)
    @Null(groups = ValidGroup.Crud.Create.class,message = "插入时ID为空")
    @NotNull(groups = ValidGroup.Crud.Update.class, message = "编辑时ID不能为空")
    private String pointId;

    @Schema(description = "点位名称")
    @NotNull
    @ExcelProperty("*点位名称")
    private String name;
    @Schema(description = "位号")
    @NotNull
    @ExcelProperty("*位号")
    private String bitCode;
    @Schema(description = "所属车间")
    @NotNull
    private Integer wid;
    @Schema(description = "工艺段ID")
    private Integer processId;
    @Schema(description = "0 否 1 是")
    @NotNull
    @ExcelProperty("*是否关键点位（0否  1是）")
    private Integer keyPoint;
    @Schema(description = "类型 1 开关量  2模拟量")
    @NotNull
    @ExcelProperty("*点位类型（1开关量  2模拟量）")
    private Integer type;
    @Schema(description = "类别 - 温度、压力、流量")
    @ExcelProperty("*类别（开关量的话，不必填）")
    private String category;
    @Schema(description = "单位")
    @ExcelProperty("*单位（开关量的话，不必填）")
    private String unit;
    @Schema(description = "IOT序列")
    @NotNull
    @ExcelProperty("*Iot序列")
    private String iotStr;


}

@ExcelProperty 对应列名

listener

@Slf4j
@Component
public class PointBatchImportListener extends AnalysisEventListener<Point> {


    @Autowired
    PointService pointService;

    private Integer wid;

    List<Point> list = new ArrayList<>(10);

    @Override
    public void invoke(Point point, AnalysisContext analysisContext) {
        point.setWid(wid);
        list.add(point);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        pointService.saveBatch(list);
        log.info("插入完成：共"+ list.size()+"条");
    }

    public void setWid(Integer wid) {
        this.wid = wid;
    }
}

controller

@Operation(summary = "批量导入")
    @PostMapping("pointBatchImport")
    public R pointBatchImport(Integer wid, MultipartFile file){
        String OriginalFilename = file.getOriginalFilename();
        String suffixName = OriginalFilename.substring(OriginalFilename.lastIndexOf("."));
        if(!SUFFIX_FILE_NAME.equals(suffixName)){
            return failed("文件格式不符合");
        }
        pointBatchImportListener.setWid(wid);
        try {
            EasyExcel.read(file.getInputStream(),Point.class,pointBatchImportListener).sheet().doRead();
        } catch (IOException e) {
            log.error(e.getMessage(),e);
            return failed("上传失败");
        }
        return success("success");
    }