1、引入Maven以来
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
注意:POI的版本必须为3.17
2、编写模板类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author zyq
* @Description ExcelProperty为表头名称
* @createTime 2021年01月22日 19:52:00
*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class ExcelDataVO {
@ExcelProperty(value = "编号", index = 0)
private Integer id;
@ExcelProperty(value = "姓名", index = 1)
private String name;
}
3、EasyExcel工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* @author zyq
* @Description
* @createTime 2021年01月22日 20:04:00
*/
public class ExcelUtils {
/**
* 导出Excel
* @param response
* @param list 数据
* @param fileName 文件名称
* @param sheetName 表名
* @param clazz 指定导出模板类的类型
*/
public static void writeExcel(HttpServletResponse response, List<?> list,
String fileName, String sheetName, Class<?> clazz){
EasyExcel.write(getOutputStream(fileName, response), clazz)
.excelType(ExcelTypeEnum.XLSX)
.sheet(sheetName)
.doWrite(list);
}
/**
* 读取Excel中的数据
* @param file
* @param clazz
* @param listener
*/
public static void readExcel(MultipartFile file, Class<?> clazz, AnalysisEventListener<?> listener) {
ExcelReader excelReader = null;
try {
excelReader = EasyExcel.read(getInputStream(file), clazz, listener).build();
ReadSheet readSheet = EasyExcel.readSheet(0).build();
excelReader.read(readSheet);
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
excelReader.finish();
}
}
}
/**
* 导出文件时为Writer生成OutputStream
* @param fileName
* @param response
* @return
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new ApiException("导出excel表格失败!");
}
}
/**
* 导入文件时为Reader生成InputStream
* @param file
* @return
*/
private static InputStream getInputStream(MultipartFile file) {
try {
return file.getInputStream();
} catch (IOException e) {
throw new ApiException("导入excel表格数据失败!");
}
}
}
5、导入Excel时需要监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.guli.service_edu.entity.EduTeacher;
import com.guli.service_edu.mapper.EduTeacherMapper;
import com.guli.service_edu.vo.ExcelDataVO;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/**
* @author zyq
* @Description
* ExcelDataListener 不能被spring管理,
* 要每次读取excel都要new,然后里面用到spring可以构造方法传进
* 每次创建Listener的时候需要把spring管理的类传进来
* @createTime 2021年01月23日 14:23:00
*/
@Slf4j
public class ExcelDataListener extends AnalysisEventListener<ExcelDataVO> {
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
List<ExcelDataVO> list = new ArrayList<>();
/**
* 每次创建Listener的时候需要把spring管理的类传进来
*/
private final EduTeacherMapper teacherMapper;
public ExcelDataListener(EduTeacherMapper eduTeacherMapper){
this.teacherMapper = eduTeacherMapper;
}
/**
* 每一条数据解析都会来调用
* @param data
* @param context
*/
@Override
public void invoke(ExcelDataVO data, AnalysisContext context) {
log.info("【解析Excel数据】");
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库, 这里批量插入需要写一个服务层进行批量插入,
* 避免在循环体中调用持久层的insert方法
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", list.size());
for (ExcelDataVO vo : list) {
EduTeacher eduTeacher = new EduTeacher();
eduTeacher.setAvatar(vo.getId() + vo.getName());
eduTeacher.setName(vo.getName());
eduTeacher.setLevel(vo.getId());
eduTeacher.setSort(vo.getId());
eduTeacher.setIntro(vo.getName());
teacherMapper.insert(eduTeacher);
}
log.info("存储数据库成功!");
}
}
4、控制层调用
@GetMapping("/excel")
public void excel(HttpServletResponse response){
List<ExcelDataVO> list = new ArrayList<>();
for (int i = 1; i < 10; i++) {
list.add(ExcelDataVO.builder()
.id(i)
.name("🐖👁掏" + i)
.build());
}
try {
ExcelUtils.writeExcel(response, list, "fileName", "sheetName", ExcelDataVO.class);
}catch (Exception e) {
throw new ApiException("EXCEL DOWNLOAD ERROR!");
}
}
@ApiOperation("导入数据")
@PostMapping("/excel")
public void addExcel(MultipartFile file){
try {
ExcelUtils.readExcel(file, ExcelDataVO.class, new ExcelDataListener(eduTeacherMapper));
}catch (Exception e) {
throw new ApiException("EXCEL UPLOAD ERROR!");
}
}
注意:不需要对response.getOutputStream()进行flush和close,见参考文档
HttpServletResponse输入输出流是否需要flush close
EasyExcel官方文档地址 EasyExcel