阿里巴巴的easyexcel工具是一种不太占用太多内存的excel读取工具，相比起apache的excel工具来说，easyexcel还是不错的。
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beat1</version>
</dependency>
首先是maven配置。
首先先建立一个ExcelData类继承 BaseRowModel 类
package com.jlau.algsystem.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
/**
* Created by cxr1205628673 on 2020/5/8.
*/
public class XlsMonitorData extends BaseRowModel{
@ExcelProperty(value = "采样日期",index = 1)
private String time;
@ExcelProperty(value = "省名称",index = 2)
private String province;
@ExcelProperty(value = "市名称",index = 3)
private String city;
@ExcelProperty(value = "县名称",index = 4)
private String county;
@ExcelProperty(value = "乡(镇)名称",index = 5)
private String countryside;
@ExcelProperty(value = "村名称",index = 6)
private String village;
@ExcelProperty(value = "农户名称",index = 7)
private String name;
@ExcelProperty(value = "地块名称",index = 8)
private String block;
@ExcelProperty(value = "北纬",index = 9)
private Double latitude;
@ExcelProperty(value = "东经",index = 10)
private Double longitude;
@ExcelProperty(value = "海拔",index = 11)
private Double altitude;
@ExcelProperty(value = "pH",index = 12)
private Double pH;
@ExcelProperty(value = "有机质",index = 13)
private Double som;
@ExcelProperty(value = "碱解氮",index = 14)
private Double ppm;
@ExcelProperty(value = "有效磷",index = 15)
private Double apa;
@ExcelProperty(value = "速效钾",index = 16)
private Double ak;
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getCounty() {
return county;
}
public void setCounty(String county) {
this.county = county;
}
public String getCountryside() {
return countryside;
}
public void setCountryside(String countryside) {
this.countryside = countryside;
}
public String getVillage() {
return village;
}
public void setVillage(String village) {
this.village = village;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getBlock() {
return block;
}
public void setBlock(String block) {
this.block = block;
}
public Double getLatitude() {
return latitude;
}
public void setLatitude(Double latitude) {
this.latitude = latitude;
}
public Double getLongitude() {
return longitude;
}
public void setLongitude(Double longitude) {
this.longitude = longitude;
}
public Double getAltitude() {
return altitude;
}
public void setAltitude(Double altitude) {
this.altitude = altitude;
}
public Double getpH() {
return pH;
}
public void setpH(Double pH) {
this.pH = pH;
}
public Double getSom() {
return som;
}
public void setSom(Double som) {
this.som = som;
}
public Double getPpm() {
return ppm;
}
public void setPpm(Double ppm) {
this.ppm = ppm;
}
public Double getApa() {
return apa;
}
public void setApa(Double apa) {
this.apa = apa;
}
public Double getAk() {
return ak;
}
public void setAk(Double ak) {
this.ak = ak;
}
}
然后建立一个Listener类
package com.jlau.algsystem.utils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.jlau.algsystem.entity.XlsMonitorData;
import com.jlau.algsystem.service.MonitorLocationService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.w3c.dom.stylesheets.LinkStyle;
import java.util.ArrayList;
import java.util.List;
/**
* Created by cxr1205628673 on 2020/5/8.
*/
public class ExcelListener extends AnalysisEventListener<XlsMonitorData>{
List datas = new ArrayList<>();
MonitorLocationService monitorLocationService;
public ExcelListener(MonitorLocationService monitorLocationService) {
this.monitorLocationService = monitorLocationService;
}
public void saveXlsData(List datas){
monitorLocationService.saveXlsData(datas);
}
@Override
public void invoke(XlsMonitorData o, AnalysisContext analysisContext) {
//这里是一行一行的读取内容，我们每到一百行就存一次datas，然后把datas的内容清空
//存储数据要IO操作，做的次数越少越好，不然可能会IO繁忙使得系统慢，最终导致JVM FullGC
//一清理就停几秒
System.out.println(analysisContext.getCurrentSheet());
datas.add(o);
if (datas.size()>100) {
saveXlsData(datas);
datas.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//最后读取完成后的逻辑，因为我们每100行存一次datas,所以最后这里一定会剩余几行，所以
//最后把datas剩余的数据存起来，然后清楚datas
saveXlsData(datas);
datas.clear();
}
public List getDatas() {
return datas;
}
public void setDatas(List datas) {
this.datas = datas;
}
}
这里的invoke和doAfterAllAnalysed需要重写，因为是观察者模式，这两个函数在读取数据时就会被调用。所以这里我们写上存储的逻辑。正如注释说的，每一百行存一次datas中excel数据，然后清理datas。直到整个excel文件读完。
MonitorLocationService的saveXlsData代码如下:
public Result saveXlsData(List<XlsMonitorData> datas){
for (XlsMonitorData data:datas) {
MonitorLocation monitor = new MonitorLocation();
if("".equals(data.getName()) || data.getName() == null){
continue;
}
monitor.setProvince(data.getProvince());
monitor.setCity(data.getCity());
monitor.setCounty(data.getCounty());
monitor.setCountryside(data.getCountryside());
monitor.setVillage(data.getVillage());
monitor.setLatitude(data.getLatitude());
monitor.setLongitude(data.getLongitude());
monitor.setAltitude(data.getAltitude());
monitor.setName(data.getName());
monitor.setBlock(data.getBlock());
monitor.setTime(data.getTime());
Nutrient nutrient = new Nutrient();
nutrient.setpH(data.getpH());
nutrient.setAPA(data.getApa());
nutrient.setPPM(data.getPpm());
nutrient.setSOM(data.getSom());
nutrient.setAK(data.getAk());
monitor.setNutrient(nutrient);
monitorLocationRepository.save(monitor);
}
return new Result(CodeUtil.SUCESS.getCode(),CodeUtil.SUCESS.getMessage(),"success");
}
上传文件的代码：
public Result handleUpload(MultipartFile multipartFile) throws Exception{
String originalFilename = multipartFile.getOriginalFilename();
if (!originalFilename.endsWith(ExcelTypeEnum.XLS.getValue()) && !originalFilename.endsWith(ExcelTypeEnum.XLSX.getValue())) {
throw new RuntimeException("请上传Excel格式文件");
}
InputStream in = null;
try{
in = new BufferedInputStream(multipartFile.getInputStream());
if(in == null){
throw new Exception("文件流错误");
}
//EasyExcelFactory.readBySax(in, new Sheet(1,1,XlsMonitorData.class),listenerl);
ExcelReader excelReader = new ExcelReader(in,null,new ExcelListener(this),false);
excelReader.read(new Sheet(1,1,XlsMonitorData.class));
}catch (Exception ioe){
throw new Exception("请检查excel格式是否符合要求");
}finally {
in.close();
}
return new Result(CodeUtil.SUCESS.getCode(),CodeUtil.SUCESS.getMessage(),"success");
}
把MultipartFile的InputStream取出传入ExcelReader构造函数内，excelReader.read（）函数执行会调用观察者的invoke函数。就会 执行我们的逻辑。
