使用EasyExcel的Excel表导入

1.controller

@PostMapping("/import")
public ResponseData<Boolean> importData(@RequestParam("file") MultipartFile file) throws IOException {
    List<MaterialExcelData> tmpList = EasyExcel.read(file.getInputStream())
            // 设置与Excel表映射的类
            .head(MaterialExcelData.class)
            // 设置sheet,默认读取第一个
            .sheet()
            // 设置标题所在行数
            .headRowNumber(1)
            // 异步读取
            .doReadSync();
    //插入数据库
    for (int i = 0; i < tmpList.size(); i++) {
        BaseMaterial baseMaterial = new BaseMaterial();
        baseMaterial.setCode(tmpList.get(i).getCode());
        baseMaterial.setName(tmpList.get(i).getName());
        baseMaterial.setType(tmpList.get(i).getType());
        baseMaterial.setUnit(tmpList.get(i).getUnit());
        baseMaterial.setVolume(tmpList.get(i).getVolume());
        baseMaterial.setWeight(tmpList.get(i).getWeight());
        baseMaterial.setStorageRoom(tmpList.get(i).getStorageRoom());
        baseMaterialMapper.insert(baseMaterial);
    }
    return ResponseData.success();
}
2.设置与Excel表映射的类

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class MaterialExcelData {
    //设置excel表头名称
    @ExcelProperty("物料编码")
    private String code;

    @ExcelProperty("物料名称")
    private String name;

    @ExcelProperty("规格型号")
    private String type;

    @ExcelProperty("单位")
    private String unit;

    @ExcelProperty("库房")
    private String storageRoom;

    @ExcelProperty("单位体积")
    private String volume;

    @ExcelProperty("单位重量")
    private String weight;
}
3.实体类

@Getter
@Setter
@TableName("base_material")
@ApiModel(value = "BaseMaterial对象", description = "")
public class BaseMaterial implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty("编码")
    private String code;

    @ApiModelProperty("名称")
    private String name;

    @ApiModelProperty("单位体积（立方米）")
    private String volume;

    @ApiModelProperty("单位重量（公斤）")
    private String weight;

    @ApiModelProperty("规格型号")
    private String type;

    @ApiModelProperty("单位")
    private String unit;

    @ApiModelProperty("所属库房")
    private String storageRoom;

    @ApiModelProperty("创建人编码")
    private String createUser;

    @ApiModelProperty("创建时间")
    private String createTime;

    @ApiModelProperty("修改人编码")
    private String updateUser;

    @ApiModelProperty("修改时间")
    private String updateTime;


}
5.mapper接口层

public interface BaseMaterialMapper extends BaseMapper<BaseMaterial> {
  
}
6.pom.xml

<!-- easyexcel-->
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.1</version>
</dependency>
<!--easyexcel  2.1.1 对应 poi  3.17 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>