最新版easyExcel工具类
最新版的easyexcel2.0升级后原有的导入导出书写方法就已经过时了，本文记录了最新版的EasyExcel工具类的整合与使用。
最新版书写比原来要简单不少，性能也有所提高，推荐换到最新版。
引入最新的依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.4.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
主要用到了easyexcel、hutool工具类和lombok插件
导入导出的实体类
package org.geekboy.bean;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
/**
* @ClassName ExportModel
* @Description 导出模型
* @Author zhangwei
* @Version 1.0.0
* @Date 2020/4/1 20:55
*/
@ContentRowHeight(20)
@HeadRowHeight(25)
@ColumnWidth(25)
@Data
public class ExportModel {
@ExcelProperty(value = "姓名" ,index = 0)
private String name;
@ExcelProperty(value = "性别" ,index = 1)
private String sex;
@ExcelProperty(value = "年龄" ,index = 2)
private Integer age;
}
package org.geekboy.bean;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* @ClassName ImportModel
* @Description 导入模型
* @Author zhangwei
* @Version 1.0.0
* @Date 2020/4/1 20:54
*/
@Data
public class ImportModel {
@ExcelProperty(index = 0)
private String date;
@ExcelProperty(index = 1)
private String author;
@ExcelProperty(index = 2)
private String book;
}
导入导出工具类
package org.geekboy.common;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* @Author zhang wei
* @Description 监听类
* @Date 2020-08-13
*/
public class ExcelListener extends AnalysisEventListener {
/**
* 可以通过实例获取该值
*/
private List<Object> dataList = new ArrayList<>();
@Override
public void invoke(Object object, AnalysisContext context) {
//数据存储到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 org.geekboy.common;
import cn.hutool.core.convert.Convert;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @Author zhang wei
* @Description Excel读写工具类
* @Date 2020-08-13
*/
public class ExcelUtil {
/**
* 读取Excel（多个sheet可以用同一个实体类解析）
* @param excelInputStream
* @param fileName
* @param clazz
* @param <T>
* @return
*/
public static <T> List<T> readExcel(InputStream excelInputStream, String fileName,Class<T> clazz) {
ExcelListener excelListener = new ExcelListener();
ExcelReader excelReader = getReader(excelInputStream, fileName,clazz, excelListener);
if (excelReader == null) {
return new ArrayList<>();
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet : readSheetList) {
excelReader.read(readSheet);
}
excelReader.finish();
return Convert.toList(clazz, excelListener.getDataList());
}
/**
* 导出Excel(一个sheet)
*
* @param response HttpServletResponse
* @param list 数据list
* @param fileName 导出的文件名
* @param sheetName 导入文件的sheet名
* @param clazz 实体类
*/
public static <T> void writeExcel(HttpServletResponse response, List<T> list, String fileName, String sheetName, Class<T> clazz) {
OutputStream outputStream = getOutputStream(response, fileName);
ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build();
WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
excelWriter.write(list, writeSheet);
excelWriter.finish();
}
/**
* 导出时生成OutputStream
*/
private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
//创建本地文件
String filePath = fileName + ".xlsx";
File file = new File(filePath);
try {
if (!file.exists() || file.isDirectory()) {
file.createNewFile();
}
fileName = new String(filePath.getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
return response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 返回ExcelReader
*
* @param excel 文件
* @param clazz 实体类
* @param excelListener
*/
private static <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;
}
}
测试类
package org.geekboy.test;
import org.geekboy.bean.ExportModel;
import org.geekboy.bean.ImportModel;
import org.geekboy.common.ExcelUtil;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
@Controller
public class ExcelTestController {
@PostMapping(value = "/import")
public List<ImportModel> read(MultipartFile excel) throws IOException {
return ExcelUtil.readExcel(excel.getInputStream(), excel.getOriginalFilename(), ImportModel.class);
}
@GetMapping(value = "/export")
public void writeExcel(HttpServletResponse response) {
List<ExportModel> list = getList();
String fileName = "Excel导出测试";
String sheetName = "sheet1";
ExcelUtil.writeExcel(response, list, fileName, sheetName, ExportModel.class);
}
private List<ExportModel> getList() {
List<ExportModel> modelList = new ArrayList<>();
ExportModel firstModel = new ExportModel();
firstModel.setName("李明");
firstModel.setSex("男");
firstModel.setAge(20);
modelList.add(firstModel);
ExportModel secondModel = new ExportModel();
secondModel.setName("珍妮");
secondModel.setSex("女");
secondModel.setAge(19);
modelList.add(secondModel);
return modelList;
}
}
镜像地址
https://blog.codage.info/post/easyexcel-code.html
