官网地址:https://easyexcel.opensource.alibaba.com/
github地址:https://github.com/alibaba/easyexcel
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。easyexcel重写了poi对07版Excel的解析,一个3M的excel用POI sax解析依然需要100M左右内存,改用easyexcel可以降低到几M,并且再大的excel也不会出现内存溢出;03版依赖POI的sax模式,在上层做了模型转换的封装,让使用者更加简单方便
一、引入jar包
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
3.1.0之前的版本依赖了cglib,由于cglib版本兼容问题,总是发生各种莫名问题。3.1.0之后移除了cglib依赖,建议升级为3.1.0之后的版本。
二、工具类封装
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
/**
* Excel工具类
*
* @author yangzihe
* @date 2021/12/17
*/
public class ExcelUtils {
/**
* excel内容类型
*/
private static final String CONTENT_TYPE_EXCEL = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
/**
* 编码
*/
private static final String ENCODE = "UTF-8";
/**
* Content-disposition
*/
private static final String CONTENT_DISPOSITION = "Content-disposition";
/**
* excel下载。写到第一个sheet,sheet名:"sheet1"
*
* @param data 数据
* @param response 响应对象
* @param fileName excel文件名,必传
*
* @throws IOException
*/
public static <T> void download(List<T> data, HttpServletResponse response, String fileName) throws IOException {
download(data, response, fileName, "sheet1", 0);
}
/**
* excel下载。写到第一个sheet
*
* @param data 数据
* @param response 响应对象
* @param fileName excel文件名,必传
* @param sheetName sheet名,必传
*
* @throws IOException
*/
public static <T> void download(List<T> data, HttpServletResponse response, String fileName, String sheetName) throws IOException {
download(data, response, fileName, sheetName, 0);
}
/**
* excel下载
*
* @param data 数据
* @param response 响应对象
* @param fileName excel文件名,必传
* @param sheetName sheet名,必传
* @param sheetNo sheet下标,从0开始,必传
*
* @throws IOException
*/
public static <T> void download(List<T> data, HttpServletResponse response, String fileName, String sheetName, Integer sheetNo) throws IOException {
response.setContentType(CONTENT_TYPE_EXCEL);
response.setCharacterEncoding(ENCODE);
// 防止文件名的中文乱码
fileName = URLEncoder.encode(fileName, ENCODE).replaceAll("\\+", "%20");
response.setHeader(CONTENT_DISPOSITION, "attachment;filename*=utf-8''" + fileName + ".xlsx");
write(data, response.getOutputStream(), sheetName, sheetNo);
}
/**
* excel写入输出流
*
* @param data
* @param outputStream
* @param sheetName
* @param sheetNo
*/
public static <T> void write(List<T> data, OutputStream outputStream, String sheetName, Integer sheetNo) {
if (data == null || data.isEmpty()) {
EasyExcel.write(outputStream).sheet(sheetName).sheetNo(sheetNo).doWrite(data);
} else {
EasyExcel.write(outputStream, data.get(0).getClass())
// 自动列宽(不太精确。标题有数字可能导致换行。而且长度也不是刚好和实际长度一致)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.sheet(sheetName)
.sheetNo(sheetNo)
.doWrite(data);
}
}
/**
* excel写入输出流。可指定是否自动关闭流
*/
public static <T> void write(List<T> data, OutputStream outputStream, String sheetName, Integer sheetNo, Boolean autoCloseStream) {
EasyExcel.write(outputStream, data.get(0).getClass())
.autoCloseStream(autoCloseStream)
.sheet(sheetName)
.sheetNo(sheetNo)
.doWrite(data);
}
/**
* excel输入流读取。读取第一个sheet
*/
public static <T> List<T> read(InputStream inputStream, Class<T> clazz) {
return read(inputStream, clazz, 0);
}
/**
* excel输入流读取
*
* @param inputStream 输入流
* @param clazz 类
* @param sheetNo sheet下标
*
* @return
*/
public static <T> List<T> read(InputStream inputStream, Class<T> clazz, Integer sheetNo) {
List<T> resultList = new ArrayList<>();
// 默认一行行的读取excel,创建excel一行一行的回调监听器. PageReadListener会分批处理数据,每次100条
PageReadListener<T> pageReadListener = new PageReadListener<>(resultList::addAll);
EasyExcel.read(inputStream, clazz, pageReadListener).sheet(sheetNo).doRead();
return resultList;
}
// public static <T> List<T> read(String filePathName, Class<T> clazz, Integer sheetNo) {
// List<T> resultList = new ArrayList<>();
// // 默认一行行的读取excel,创建excel一行一行的回调监听器.
// ReadListener<T> readListener = new ReadListener<T>() {
// @Override
// public void invoke(T data, AnalysisContext context) {
// resultList.add(data);
// }
//
// @Override
// public void doAfterAllAnalysed(AnalysisContext context) {
//
// }
// };
// EasyExcel.read(filePathName, clazz, readListener).sheet(sheetNo).doRead();
// return resultList;
// }
/**
* excel本地文件读取。读取第一个sheet
*/
public static <T> List<T> read(String filePathName, Class<T> clazz) {
return read(filePathName, clazz, 0);
}
/**
* excel本地文件读取
*
* <p>
* 读取的回调监听器不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去;
* 文件流会自动关闭;
* </p>
*
* @param filePathName
* @param clazz
* @param sheetNo
*
* @return
*/
public static <T> List<T> read(String filePathName, Class<T> clazz, Integer sheetNo) {
List<T> resultList = new ArrayList<>();
// 默认一行行的读取excel,创建excel一行一行的回调监听器. PageReadListener会分批处理数据,每次100条
PageReadListener<T> pageReadListener = new PageReadListener<>(resultList::addAll);
EasyExcel.read(filePathName, clazz, pageReadListener).sheet(sheetNo).doRead();
return resultList;
}
/**
* excel写入本地文件。写到第一个sheet
*
* @param data 写入的数据
* @param filePathName 文件路径名
* @param sheetName sheet名
*/
public static <T> void write(List<T> data, String filePathName, String sheetName) {
write(data, filePathName, sheetName, 0);
}
/**
* excel写入本地文件
*
* <p>
* 文件流会自动关闭
* </p>
*
* @param data 写入的数据
* @param filePathName 文件路径名
* @param sheetName sheet名
* @param sheetNo sheet下标,从0开始
*/
public static <T> void write(List<T> data, String filePathName, String sheetName, Integer sheetNo) {
if (data == null || data.isEmpty()) {
EasyExcel.write(filePathName).sheet(sheetName).sheetNo(sheetNo).doWrite(data);
} else {
EasyExcel.write(filePathName, data.get(0).getClass())
.sheet(sheetName)
.sheetNo(sheetNo)
.doWrite(data);
}
}
}
三、工具类使用
本地excel文件的读写测试
public class ExcelTest {
@Test
public void testWrite() {
ExcelUtils.write(data(), ExcelUtils.class.getResource("/").getPath() + "yzh.xlsx", "默认sheet名");
}
@Test
public void testRead() {
List<DemoData> read = ExcelUtils.read(ExcelUtils.class.getResource("/").getPath() + "yzh.xlsx", DemoData.class);
read.forEach(demoData -> System.out.println("demoData=" + demoData));
System.out.println("size=" + read.size());
}
private static List<DemoData> data() {
List<DemoData> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56 + i);
list.add(data);
}
return list;
}
}
@Data
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}
web的excel文件上传与下载测试
@RestController
@RequestMapping("/excel")
@Slf4j
public class ExcelController {
/**
* 文件下载
*
* <p>
* 失败了会返回一个有部分数据的Excel.
* </p>
*/
@GetMapping("/download")
public void download(HttpServletResponse response) throws IOException {
ExcelUtils.download(data(), response, "yzh测试");
}
/**
* 文件下载
*
* <p>
* 失败的时候返回json
* </p>
*/
@GetMapping("/downloadFailedUsingJson")
public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// URLEncoder.encode防止中文乱码
String fileName = URLEncoder.encode("web文件测试2", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 这里需要设置不关闭流
ExcelUtils.write(data(), response.getOutputStream(), "sheet模板测试2", 0, Boolean.FALSE);
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
/**
* 文件上传
*/
@PostMapping("/upload")
public String upload(MultipartFile file) throws IOException {
List<DownloadData> downloadDataList = ExcelUtils.read(file.getInputStream(), DownloadData.class);
downloadDataList.forEach(downloadData -> System.out.println("downloadData=" + downloadData));
System.out.println("size=" + downloadDataList.size());
return "success";
}
private List<DownloadData> data() {
List<DownloadData> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
DownloadData data = new DownloadData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56 + i);
list.add(data);
}
return list;
}
}
@Data
public class DownloadData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
}