springboot3使用Excel导入数据库数据

时间：2024-10-16 16:37:32

一、导入依赖

         <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.3.0</version>
        </dependency>

二、实体类

package com.guide.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

/**
 * @author cxy
 * @create 2024-10-11 09:41:43
 */

@Data
@TableName("workpiece")
public class Workpiece {
    @TableId(value = "id", type = IdType.AUTO)
    @Schema(description = "id")
    private Integer id;

    @TableField(value = "vehicleModel")
    @Schema(description = "车型")
    private String vehicleModel;

    @TableField(value = "twoNum")
    @Schema(description = "轴二起始Num")
    private Integer twoNum;

    @TableField(value = "threeNum")
    @Schema(description = "轴三起始Num")
    private Integer threeNum;

    @TableField(value = "imgUrl")
    @Schema(description = "图片路径")
    private String imgUrl;
}

三、上传的模板

四、controller层 

package com.guide.controller;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.guide.entity.Workpiece;
import com.guide.result.Result;
import com.guide.service.WorkpieceService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author cxy
 * @create 2024-10-12 10:12:02
 */

@Tag(name = "excel")
@RequestMapping("/excel")
@RestController
@CrossOrigin(origins = "*")
@Slf4j
@RequiredArgsConstructor
public class ExcelController {
    private final WorkpieceService service;

    @PostMapping("/upLoad")
    @Operation(summary = "上传Excel")
    public Result upLoad(MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        List<List<Object>> list = reader.read(1);
        ArrayList<Workpiece> users = CollUtil.newArrayList();
        for (List<Object> row : list) {
            Workpiece user = new Workpiece();
            //excel表格结构数据要对应
            user.setVehicleModel(row.get(0).toString());
            user.setTwoNum(Integer.valueOf(row.get(1).toString()));
            user.setThreeNum(Integer.valueOf(row.get(2).toString()));
            user.setImgUrl(row.get(3).toString());
            users.add(user);
            //已经把excel数据赋值到users上 此时就可以插入数据库
            service.save(user);
        }
        return Result.success(true);
    }

}

相关文章