依赖
<!--EasyExcel-->
<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>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
一、监听器
package com.xiyue.publicbase;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
public class ExcelListener extends AnalysisEventListener {
/**
* 可以通过实例获取该值
*/
private List<Object> dataList = new ArrayList<>();
/**
* 逐行读取excel内容
* */
@Override
public void invoke(Object object, AnalysisContext analysisContext) {
//数据存储到list,供批量处理,或后续自己业务逻辑处理。
dataList.add(object);
// handleBusinessLogic();
/*
如数据过大,可以进行定量分批处理
if(dataList.size()>=200){
handleBusinessLogic();
dataList.clear();
}
*/
}
/**
* 读取完成之后执行
* */
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//非必要语句,查看导入的数据
System.out.println("导入的数据条数为: " + dataList.size());
}
//根据业务自行实现该方法,例如将解析好的dataList存储到数据库中
private void handleBusinessLogic() {
}
public List<Object> getDataList() {
return dataList;
}
public void setDataList(List<Object> dataList) {
this.dataList = dataList;
}
}
二、工具类
package com.xiyue.publicbase;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
public class ExcelUtil {
private ExcelUtil(){}
/**
* 读取excel文件,返回list集合
* @param excelInputStream
* @param fileName
* @param clazz
* @param <T>
* @return
*/
public static <T> ReJson readExcel(InputStream excelInputStream, String fileName, Class<T> clazz) {
ExcelListener excelListener = new ExcelListener();
ExcelUtil excelUtil = new ExcelUtil();
ExcelReader excelReader = excelUtil.getReader(excelInputStream, fileName,clazz, excelListener);
if (excelReader == null) {
return ReJson.error();
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet : readSheetList) {
excelReader.read(readSheet);
}
excelReader.finish();
return ReJson.ok().data("dataList",excelListener.getDataList());
}
/**
*
* @param inputStream
* @param filename
* @param clazz
* @param excelListener
* @param <T>
* @return
*/
public <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener excelListener) {
try {
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
return null;
}
ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();
inputStream.close();
return excelReader;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 把数据写入excel文件中,响应浏览器下载
* @param response
* @param list
* @param fileName
* @param sheetName
* @param clazz
* @param <T>
*/
public static <T> void writeExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz) {
ExcelUtil excelUtil = new ExcelUtil();
excelUtil.setResponseHeader(response,fileName);
ServletOutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
EasyExcel.write(outputStream,clazz).sheet(sheetName).doWrite(list);
}
/**
* 发送响应流方法
* @param response
* @param fileName
*/
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(),"ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename="+ fileName +".xlsx"); //要保存的文件名
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
三、实体类
package com.xiyue.admin.entity.Excel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class ImportModel {
@ExcelProperty(value = "学生编号",index = 0)
private Integer sno;
@ExcelProperty(value = "学生姓名",index = 1)
private String sname;
}
四、测试
//测试
@PostMapping("testRead")
public void testRead(MultipartFile excel) throws IOException {
ReJson reJson = ExcelUtil.readExcel(excel.getInputStream(), excel.getOriginalFilename(), ImportModel.class);
System.out.println(reJson);
}
@GetMapping(value = "/testWrite")
public void testWrite(HttpServletResponse response) {
List<ImportModel> list = getList();
String fileName = "Excel123456";
String sheetName = "sheet1";
ExcelUtil.writeExcel(response, list, fileName, sheetName, ImportModel.class);
}
public List<ImportModel> getList(){
ArrayList<ImportModel> excels = new ArrayList<>();
for (int i = 0; i < 10; i++) {
ImportModel excel = new ImportModel();
excel.setSno(i);
excel.setSname("yy"+i);
excels.add(excel);
}
return excels;
}