SpringBoot项目中集成EasyExcel实现Excel文件上传至MySQL

2、Controller 类

package com.riemann.springbootdemo.controller;

import com.alibaba.excel.EasyExcel;

import com.alibaba.excel.ExcelReader;

import com.alibaba.excel.read.metadata.ReadSheet;

import com.riemann.springbootdemo.listener.UploadEasyExcelListener;

import com.riemann.springbootdemo.model.ReturnT;

import com.riemann.springbootdemo.model.UploadEasyExcelData;

import com.riemann.springbootdemo.service.UploadEasyExcelService;

import io.swagger.annotations.Api;

import io.swagger.annotations.ApiImplicitParam;

import io.swagger.annotations.ApiOperation;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.http.HttpStatus;

import org.springframework.http.ResponseEntity;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.RestController;

import org.springframework.web.multipart.MultipartFile;

import java.io.Closeable;

import java.io.IOException;

import java.io.InputStream;

/**

• @author riemann

• @date 2019/12/19 22:10

*/

@RestController

public class UploadEasyExcelController {

private static final Logger LOGGER = LoggerFactory.getLogger(UploadEasyExcelController.class);

@Autowired

private UploadEasyExcelService ueeService;

@PostMapping(value = “/uploadEasyExcel”,produces = “application/json;charset=UTF-8”)

public ResponseEntity<ReturnT> UploadEasyExcel(@RequestParam(value = “serviceFile”) MultipartFile serviceFile) {

if (serviceFile == null) {

return new ResponseEntity<>(new ReturnT<>(ReturnT.BAD_REQUEST, “Params can not be null”), HttpStatus.BAD_REQUEST);

}

ExcelReader excelReader = null;

InputStream in = null;

try {

in = serviceFile.getInputStream();

excelReader = EasyExcel.read(in, UploadEasyExcelData.class,

new UploadEasyExcelListener(ueeService)).build();

ReadSheet readSheet = EasyExcel.readSheet(0).build();

excelReader.read(readSheet);

} catch (IOException ex) {

LOGGER.error(“import excel to db fail”, ex);

} finally {

close(in);

// 这里一定别忘记关闭，读的时候会创建临时文件，到时磁盘会崩

if (excelReader != null) {

excelReader.finish();

}

}

return new ResponseEntity<>(new ReturnT<>(“upload easyexcel success”), HttpStatus.OK);

}

private void close(Closeable closeable) {

if (closeable != null) {

try {

closeable.close();

} catch (IOException e) {

LOGGER.error(“Close io stream error”, e);

}

}

}

}

(@RequestParam(value = “serviceFile”) MultipartFile serviceFile)

这里上传 Excel 文件用的是 MultipartFile

3、实体类 UploadEasyExcelData

package com.riemann.springbootdemo.model;

import com.alibaba.excel.annotation.ExcelProperty;

import lombok.Data;

import lombok.Getter;

import lombok.NoArgsConstructor;

import lombok.Setter;

/**

• @author riemann

• @date 2019/12/19 23:08

*/

@NoArgsConstructor

@Data

@Getter

@Setter

public class UploadEasyExcelData {

@ExcelProperty(value = “name”, index = 0)

private String name;

@ExcelProperty(value = “sex”, index = 1)

private String sex;

@ExcelProperty(value = “age”, index = 2)

private Integer age;

@ExcelProperty(value = “address”, index = 3)

private String address;

@ExcelProperty(value = “phone”, index = 4)

private String phone;

}

这里的实体类是是与上传的 Excel 的信息相对应，这里也用了 lombok，lombok能通过注解的方式，在编译时自动为属性生成构造器、getter/setter、equals、hashcode、toString方法。出现的神奇就是在源码中没有getter和setter方法，但是在编译生成的字节码文件中有getter和setter方法。这样就省去了手动重建这些代码的麻烦，使代码看起来更简洁些。

这是它的 maven 依赖

org.projectlombok

lombok

true

4、监听类 UploadEasyExcelListener

EasyExcel.read(in, UploadEasyExcelData.class,

new UploadEasyExcelListener(ueeService)).build();

注意，EasyExcel这里的 read函数，除了传流、实体类还有监听类。

package com.riemann.springbootdemo.listener;

import com.alibaba.excel.context.AnalysisContext;

import com.alibaba.excel.event.AnalysisEventListener;

import com.riemann.springbootdemo.model.UploadEasyExcelData;

import com.riemann.springbootdemo.service.UploadEasyExcelService;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.beans.factory.annotation.Autowired;

import java.util.ArrayList;

import java.util.List;

/**

• 上传Excel的监听类

• @author riemann

• @date 2019/12/19 23:26

*/

public class UploadEasyExcelListener extends AnalysisEventListener {

private static final Logger LOGGER = LoggerFactory.getLogger(UploadEasyExcelListener.class);

// 每隔1000条存储数据库，然后清理list，方便内存回收

private static final int BATCH_COUNT = 1000;

// 存储解析excel条数

private int count = 0;

@Autowired

private UploadEasyExcelService ueeService;

List ueeDatas = new ArrayList<>();

public UploadEasyExcelListener(UploadEasyExcelService ueeService) {

super();

this.ueeService = ueeService;

}

@Override

public void invoke(UploadEasyExcelData ueeData, AnalysisContext analysisContext) {

if (LOGGER.isDebugEnabled()) {

LOGGER.debug(“One record was Parsed, uploadEasyExcelData:{}”, ueeData);

}

count += 1;

ueeDatas.add(ueeData);

if (ueeDatas.size() >= BATCH_COUNT) {

ueeService.saveEasyExcelMappingData(ueeDatas);

ueeDatas.clear();

}

}

@Override

public void doAfterAllAnalysed(AnalysisContext analysisContext) {

ueeService.saveEasyExcelMappingData(ueeDatas);

LOGGER.info(“all excel data parsed, count:{}”, count);

}

}

5、接口、实现类、Dao层

比较简单我就直接放了

UploadEasyExcelService.java

package com.riemann.springbootdemo.service;

import com.riemann.springbootdemo.model.UploadEasyExcelData;

import java.util.List;

/**

• @author riemann

• @date 2019/12/19 23:31

*/

public interface UploadEasyExcelService {

/**

• 通过easy excel上传excel数据至mysql

• @param ueeDatas

*/

void saveEasyExcelMappingData(List ueeDatas);

}

UploadEasyExcelServiceImpl.java

package com.riemann.springbootdemo.service.impl;

import com.riemann.springbootdemo.dao.EasyExcelDao;

import com.riemann.springbootdemo.model.UploadEasyExcelData;

import com.riemann.springbootdemo.service.UploadEasyExcelService;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import java.util.List;

/**

• @author riemann

• @date 2019/12/19 23:40

*/

@Service

public class UploadEasyExcelServiceImpl implements UploadEasyExcelService {

@A
utowired

private EasyExcelDao easyExcelDao;

@Override

public void saveEasyExcelMappingData(List ueeDatas) {

easyExcelDao.saveEasyExcelMappingData(ueeDatas);

}

}

EasyExcelDao.java

package com.riemann.springbootdemo.dao;

import com.riemann.springbootdemo.model.UploadEasyExcelData;

import org.apache.ibatis.annotations.Param;

import java.util.List;

/**