一、导入依赖
<!-- 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);}}
