引用pom内容:
<properties>
<easyexcel.version>2.2.6</easyexcel.version>
</properties>
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
</dependency>
</dependencies>
1.监听
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.google.common.collect.Lists;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.List;
/**
* @Author FangYN
* @Date 2020/8/7 17:21
* @Description 表格监听处理
**/
public class ExcelListener extends AnalysisEventListener {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
private List<Object> data = Lists.newArrayList();
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(o));
//数据存储到list,
data.add(o);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (data.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
data.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 入库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", data.size());
//这个方法自己实现 能完成保存数据入库即可
LOGGER.info("存储数据库成功!");
}
public List<Object> getData() {
return data;
}
public void setData(List<Object> data) {
this.data = data;
}
}
2.实现工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* @Author FangYN
* @Date 2020/8/11 13:55
* @Description 表格工具类实现
**/
public class ExcelUtil {
private static Logger log = LoggerFactory.getLogger(WorkBookUtil.class);
/**
* @param inStr 文件流
* @param cls 类数组
* @return 构建各个sheet对象返回
*/
public static Map<Integer, Object> repeatedRead(InputStream inStr, List<Class> cls) {
Map<Integer, Object> map = new HashMap<>();
ExcelReader excelReader = null;
ExcelListener excelListener = new ExcelListener();
excelReader = EasyExcel.read(inStr, cls.get(0), excelListener).build();// 获取部分sheet信息
for (int i = 0; i < cls.size(); i++) map.put(i, buildList(cls.get(i), i, excelListener, excelReader));
// 这里一定别忘记关闭,读的时候会创建临时文件,到时磁盘会崩
if (excelReader != null)
excelReader.finish();
return map;
}
private static <T> List<T> buildList(Class<T> cls, int sheetNo, ExcelListener excelListener, ExcelReader excelReader) {
ReadSheet readSheet = EasyExcel.readSheet(sheetNo).head(cls).build();//获取指定sheet对象
excelReader.read(readSheet);//读取数据
List<T> resultList = excelListener.getData().stream().map(dto -> {
T vo = null;
try {
vo = (T) cls.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
SpringUtil.copyPropertiesIgnoreNull(dto, vo);
return vo;
}).collect(Collectors.toList());
excelListener.getData().clear();//清空list数据
return resultList;
}
}
3.实体类举例
@Data
public class StudentInfoDTO implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = {"班级"})
private String className;
@ExcelProperty(value = {"姓名"})
private String studentName;
@ExcelProperty(value = {"学籍号"})
private String studentCode;
@ExcelProperty(value = {"性别"}, converter = SexConverter.class)
private String sex;
}
@Data
public class StudentInfoDTO2 implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = {"班级"})
private String className;
@ExcelProperty(value = {"姓名"})
private String studentName;
@ExcelProperty(value = {"学籍号"})
private String studentCode;
@ExcelProperty(value = {"性别"}, converter = SexConverter.class)
private String sex;
@ExcelProperty(value = {"生日"})
private String birthday;
}
4.调用实现
@RestController
@RequestMapping("/dock")
public class ApiController {
private static Logger log = LoggerFactory.getLogger(ApiController.class);
@PostMapping
public void save(@RequestParam("file") MultipartFile file) throws IOException {
Map<Integer, Object> map;
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
List<Class> cls = new ArrayList<>();
cls.add(StudentInfoDTO.class);// 根据实际需求定义实体类
cls.add(StudentInfoDTO2.class);
map = ExcelUtil.repeatedRead(inputStream, cls);
if (inputStream != null)
inputStream.close();
// TODO 具体需求具体逻辑实现
} catch (IOException ex) {
ex.printStackTrace();
System.out.println("数据上传解析失败");
return;
} finally {
if (inputStream != null)
inputStream.close();
}
}
}