依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
<scope>compile</scope>
</dependency>
导入导出类
@Data
public class Export implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = "value")
@ApiModelProperty(value = "value")
private String value;
@ExcelProperty(value = "id")
@ApiModelProperty(value = "id")
private String id;
}
监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* excel表格读取监视器
*/
public class ExcelListener extends AnalysisEventListener {
//可以通过实例获取该值
private List<Object> datas = new ArrayList<Object>();
public void invoke(Object o, AnalysisContext analysisContext) {
datas.add(o);
doSomething(o);
}
private void doSomething(Object object) {
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
导入
@GetMapping("/import")
public void import(MultipartFile file) {
imports(file);
}
public void import(MultipartFile file) {
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
ExcelListener listener = new ExcelListener();
ExcelReader excelReader = EasyExcel.read(inputStream, listener).build();
// 第一个sheet读取类型
ReadSheet sheet0 = EasyExcel.readSheet(0).head(Export.class).build();
// 第二个sheet读取类型
ReadSheet sheet1 = EasyExcel.readSheet(1).head(Export.class).build();
// 开始读取第一个sheet数据
excelReader.read(sheet0);
List<Object> sheetData0 = listener.getDatas();
log.info("sheetData0:{}", JSONObject.toJSONString(sheetData0));
// 清空上次读取数据
listener.getDatas().clear();
// 开始读取第二个sheet数据
excelReader.read(sheet1);
List<Object> sheetData1 = listener.getDatas();
log.info("sheetData1:{}", JSONObject.toJSONString(sheetData1));
} catch (Exception e) {
log.error("导入异常:{}", e.getMessage());
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
导出
@GetMapping("/export")
public void export(HttpServletResponse response, @RequestParam("ids") String ids) {
export(response, ids);
}
public void export(HttpServletResponse response, String ids) {
List<String> idList = Arrays.asList(ids.split(","));
List<Export> datas = service.queryByIds(ids);
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
ExcelWriter writer = EasyExcel.write(outputStream).build();
WriteSheet sheet0 = EasyExcel.writerSheet(0, "第一页").head(Export.class).build();
writer.write(datas, sheet0);
WriteSheet sheet1 = EasyExcel.writerSheet(1, "第二页").head(Export.class).build();
writer.write(datas, sheet1);
String fileName = URLEncoder.encode("导出", String.valueOf(StandardCharsets.UTF_8));
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(String.valueOf(StandardCharsets.UTF_8));
writer.finish();
outputStream.flush();
} catch (Exception e) {
log.error("导出异常:{}", e.getMessage());
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
参考自:Easyexcel导入导出多个sheet_easyexcel 多sheet_宋忠瑾的博客-CSDN博客