Springboot+poid读取上传+写入数据库

一、导入依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
            <scope>provided</scope>
        </dependency>

二、文件读写操作
写一个实体类，对应生成表格

import com.alibaba.excel.annotation.ExcelProperty;
@toString
@Getter
@Setter
public class DemoData {
    //excel表格头
    @ExcelProperty("学生编号")
    private Integer sno;
    @ExcelProperty("学生姓名")
    private String sname;
}

写一个测试类生成表格

import com.alibaba.excel.EasyExcel;
import java.util.ArrayList;
import java.util.List;

public class TestEasyExcel {
    public static void main(String[] args) {
        //实现excel写的操作
        //1 设置写入文件夹地址和excel文件名称
        String filename = "E:\\write.csv";
        //2.调用easyexcel实现方法
        EasyExcel.write(filename, DemoData.class).sheet("sheet1").doWrite(getData());
    }

        //创建方法返回list集合
        private static List<DemoData> getData(){
            List<DemoData> list = new ArrayList<>();
            DemoData data = new DemoData();
            for (int i = 0; i < 10 ; i++) {
                data.setSno(i);
                data.setSname("xiaoqiaobian"+i);
                list.add(data);
            }
         return list;
    }
}

image.png

文件读操作
创建实体类

@Setter
@Getter
@ToString
@Table(name="tb_sku")
public class SkuData {
    @ExcelProperty(index = 0)
    private String id;//商品id
    @ExcelProperty(index = 1)
    private String sn;//商品条码
    @ExcelProperty(index = 2)
    private String name;//SKU名称
    @ExcelProperty(index = 3)
    private Integer price;//价格（分）
    @ExcelProperty(index = 4)
    private Integer num;//库存数量
    @ExcelProperty(index = 5)
    private Integer alertNum;//库存预警数量
    @ExcelProperty(index = 6)
    private String image;//商品图片
    @ExcelProperty(index = 7)
    private String images;//商品图片列表
    @ExcelProperty(index = 8)
    private Integer weight;//重量（克）
    @ExcelProperty(index = 9)
    private java.util.Date createTime;//创建时间
    @ExcelProperty(index = 10)
    private java.util.Date updateTime;//更新时间
    @ExcelProperty(index = 11)
    private String spuId;//SPUID
    @ExcelProperty(index = 12)
    private Integer categoryId;//类目ID
    @ExcelProperty(index = 13)
    private String categoryName;//类目名称
    @ExcelProperty(index = 14)
    private String brandName;//品牌名称
    @ExcelProperty(index = 15)
    private String spec;//规格
    @ExcelProperty(index = 16)
    private Integer saleNum;//销量
    @ExcelProperty(index = 17)
    private Integer commentNum;//评论数
    @ExcelProperty(index = 18)
    private String status;//商品状态 1-正常，2-下架，3-删除
}

创建监听类进行excel文件读取

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelCommonException;
import com.alibaba.fastjson.JSON;
import com.xiaoqiaobian.file.pojo.SkuData;
import com.xiaoqiaobian.file.service.SkuDataService;
import com.xiaoqiaobian.file.util.UUIDUtils;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class SkuDataListener extends AnalysisEventListener<SkuData> {

    //因为SkuDataListener不能交给Springboot管理，需要构建无参，有参对象
    public SkuDataService skuDataService;
    public SkuDataListener(SkuDataService skuDataService){
        this.skuDataService=skuDataService;
    }
    public SkuDataListener(){}

    private static final int BATCH_COUNT = 20;
    List<SkuData> list = new ArrayList<SkuData>(BATCH_COUNT);

    @Override
    public void invoke(SkuData skuData, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(skuData));
        list.add(skuData);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            list.clear();
        }
        if(skuData==null){
            throw new ExcelCommonException("文件数据为空");
        }
        //一行一行读取数据库
        //判断第一列id是否为空
        if(skuData.getId()==null) {
            String id = UUIDUtils.getUUID();
            skuData.setId(id);
            skuDataService.save(skuData);
        }
        //判断第二列sn是否为空
        if(skuData.getSn()==null){
            String sn = skuData.getSn();
            skuData.setSn(sn+"");
            skuDataService.save(skuData);
        }
        if(skuData.getStatus()==null){
            String status = skuData.getStatus();
            skuData.setStatus(status+"");
            skuDataService.save(skuData);
        }
        //把实体类存入数据库
        skuDataService.save(skuData);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("所有数据解析完成！");
    }

    private void saveData(){
        log.info("{}条数据，开始存储数据库！", list.size());
        log.info("存储数据库成功！");
    }
}

五、写入数据库
Controller层

@CrossOrigin
@RestController
@RequestMapping("/sku/upload")
public class SkuDataController {

    @Autowired
    private SkuDataService skuDataService;

    //添加sku
    //获取上传的文件
    @PostMapping("/addSkuData")
    public Result saveSkuData(MultipartFile file){
        skuDataService.saveSkuData(file,skuDataService);
        return new Result(true, StatusCode.OK,"excel文件上传成功");
    }
}

Service层

import com.xiaoqiaobian.file.pojo.SkuData;
import org.springframework.web.multipart.MultipartFile;

public interface SkuDataService {    
    //excel解析读取SkuData
    void saveSkuData(MultipartFile file,SkuDataService skuDataService);
    //insert方法 SkuData
    void save(SkuData skuData);
}

ServiceImpl层

@Service
public class SkuDataServiceImpl implements SkuDataService {

    @Autowired
    private SkuDataDao skuDataDao;
    @Override
    public void saveSkuData(MultipartFile file,SkuDataService skuDataService) {

        try {
            InputStream inputStream = file.getInputStream();
            EasyExcel.read(inputStream, SkuData.class, new SkuDataListener(skuDataService)).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    @Override
    public void save(SkuData skuData) {
        skuDataDao.insert(skuData);
    }
}

Dao层

import com.xiaoqiaobian.file.pojo.SkuData;
import com.xiaoqiaobian.file.service.SkuDataService;
import org.apache.ibatis.annotations.Insert;
import org.springframework.web.multipart.MultipartFile;
import tk.mybatis.mapper.common.Mapper;

public interface SkuDataDao extends Mapper<SkuData> {
    //通用Mapper底层实现增删改查
    //void saveSkuData(MultipartFile file, SkuDataService skuDataService);
    //void save(SkuData skuData);
}

//BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
//创建一个工作薄，使用poi的HSSFWorkbook操作
HSSFWorkbook workbook = new HSSFWorkbook();
//2.得到表
HSSFSheet sheet = workbook.getSheetAt(0);
//3.得到行
HSSFRow row = sheet.getRow(0);
//4.得到列
HSSFCell cell = row.getCell(1);

    //取值的时候，一定要注意类型
    cell.getStringCellValue();//字符串
    cell.getDateCellValue();//Data日期
    cell.getBooleanCellValue();
    inputStream.close;