EasyExcel是一款阿里开源的一款非常优秀Excel导入导出工具，使用内存小，处理速度快，使用方便，正好最近项目中有用到多个excel导出下载的功能，便记录下
EasyExcel使用git地址：GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具
1. 首先导出pom依赖
<!--poi工具-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<!--easyexcel工具-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
<!--hutool工具-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>${hutool.version}</version>
</dependency>
<!--lombok工具-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>2. 添加使用的工具类
@Slf4j
public class ExcelBigNumberConvert implements Converter<Long> {
@Override
public Class<Long> supportJavaTypeKey() {
return Long.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Long convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
return Convert.toLong(cellData.getData());
}
@Override
public WriteCellData<Object> convertToExcelData(Long object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
if (ObjectUtil.isNotNull(object)) {
String str = Convert.toStr(object);
if (str.length() > 15) {
return new WriteCellData<>(str);
}
}
WriteCellData<Object> cellData = new WriteCellData<>(new BigDecimal(object));
cellData.setType(CellDataTypeEnum.NUMBER);
return cellData;
}
}@AllArgsConstructor
@Slf4j
public class CellMergeStrategy extends AbstractMergeStrategy {
private List<?> list;
private boolean hasTitle;
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
List<CellRangeAddress> cellList = handle(list, hasTitle);
// judge the list is not null
if (CollectionUtils.isNotEmpty(cellList)) {
// the judge is necessary
if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
for (CellRangeAddress item : cellList) {
sheet.addMergedRegion(item);
}
}
}
}
@SneakyThrows
private static List<CellRangeAddress> handle(List<?> list, boolean hasTitle) {
List<CellRangeAddress> cellList = new ArrayList<>();
if (CollectionUtils.isEmpty(list)) {
return cellList;
}
Class<?> clazz = list.get(0).getClass();
Field[] fields = clazz.getDeclaredFields();
// 有注解的字段
List<Field> mergeFields = new ArrayList<>();
List<Integer> mergeFieldsIndex = new ArrayList<>();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
if (field.isAnnotationPresent(CellMerge.class)) {
CellMerge cm = field.getAnnotation(CellMerge.class);
mergeFields.add(field);
mergeFieldsIndex.add(cm.index() == -1 ? i : cm.index());
}
}
// 行合并开始下标
int rowIndex = hasTitle ? 1 : 0;
Map<Field, RepeatCell> map = new HashMap<>();
// 生成两两合并单元格
for (int i = 0; i < list.size(); i++) {
for (int j = 0; j < mergeFields.size(); j++) {
Field field = mergeFields.get(j);
String name = field.getName();
String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
Method readMethod = clazz.getMethod(methodName);
Object val = readMethod.invoke(list.get(i));
int colNum = mergeFieldsIndex.get(j);
if (!map.containsKey(field)) {
map.put(field, new RepeatCell(val, i));
} else {
RepeatCell repeatCell = map.get(field);
Object cellValue = repeatCell.getValue();
if (cellValue == null || "".equals(cellValue)) {
// 空值跳过不合并
continue;
}
if (cellValue != val) {
if (i - repeatCell.getCurrent() > 1) {
cellList.add(new CellRangeAddress(repeatCell.getCurrent() + rowIndex, i + rowIndex - 1, colNum, colNum));
}
map.put(field, new RepeatCell(val, i));
} else if (i == list.size() - 1) {
if (i > repeatCell.getCurrent()) {
cellList.add(new CellRangeAddress(repeatCell.getCurrent() + rowIndex, i + rowIndex, colNum, colNum));
}
}
}
}
}
return cellList;
}
@Data
@AllArgsConstructor
static class RepeatCell {
private Object value;
private int current;
}
}@Slf4j
@NoArgsConstructor
public class DefaultExcelListener<T> extends AnalysisEventListener<T> implements ExcelListener<T> {
/**
* 是否Validator检验，默认为是
*/
private Boolean isValidate = Boolean.TRUE;
/**
* excel 表头数据
*/
private Map<Integer, String> headMap;
/**
* 导入回执
*/
private ExcelResult<T> excelResult;
public DefaultExcelListener(boolean isValidate) {
this.excelResult = new DefautExcelResult<>();
this.isValidate = isValidate;
}
/**
* 处理异常
*
* @param exception ExcelDataConvertException
* @param context Excel 上下文
*/
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
String errMsg = null;
if (exception instanceof ExcelDataConvertException) {
// 如果是某一个单元格的转换异常 能获取到具体行号
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
Integer rowIndex = excelDataConvertException.getRowIndex();
Integer columnIndex = excelDataConvertException.getColumnIndex();
errMsg = StrUtil.format("第{}行-第{}列-表头{}: 解析异常<br/>",
rowIndex + 1, columnIndex + 1, headMap.get(columnIndex));
if (log.isDebugEnabled()) {
log.error(errMsg);
}
}
if (exception instanceof ConstraintViolationException) {
ConstraintViolationException constraintViolationException = (ConstraintViolationException) exception;
Set<ConstraintViolation<?>> constraintViolations = constraintViolationException.getConstraintViolations();
String constraintViolationsMsg = constraintViolations.stream()
.map(ConstraintViolation::getMessage)
.collect(Collectors.joining(", "));
errMsg = StrUtil.format("第{}行数据校验异常: {}", context.readRowHolder().getRowIndex() + 1, constraintViolationsMsg);
if (log.isDebugEnabled()) {
log.error(errMsg);
}
}
excelResult.getErrorList().add(errMsg);
throw new ExcelAnalysisException(errMsg);
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
this.headMap = headMap;
//log.debug("解析到一条表头数据: {}", JsonUtils.toJsonString(headMap));
}
@Override
public void invoke(T data, AnalysisContext context) {
if (isValidate) {
ValidatorUtils.validate(data);
}
excelResult.getList().add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.debug("所有数据解析完成！");
}
@Override
public ExcelResult<T> getExcelResult() {
return excelResult;
}
}public interface ExcelListener<T> extends ReadListener<T> {
ExcelResult<T> getExcelResult();
}
public interface ExcelResult<T> {
/**
* 对象列表
*/
List<T> getList();
/**
* 错误列表
*/
List<String> getErrorList();
/**
* 导入回执
*/
String getAnalysis();
}@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class FileUtils extends FileUtil {
/**
* 下载文件名重新编码
*
* @param response 响应对象
* @param realFileName 真实文件名
*/
public static void setAttachmentResponseHeader(HttpServletResponse response, String realFileName) throws UnsupportedEncodingException {
String percentEncodedFileName = percentEncode(realFileName);
StringBuilder contentDispositionValue = new StringBuilder();
contentDispositionValue.append("attachment; filename=")
.append(percentEncodedFileName)
.append(";")
.append("filename*=")
.append("utf-8''")
.append(percentEncodedFileName);
response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
response.setHeader("Content-disposition", contentDispositionValue.toString());
response.setHeader("download-filename", percentEncodedFileName);
}
/**
* 百分号编码工具方法
*
* @param s 需要百分号编码的字符串
* @return 百分号编码后的字符串
*/
public static String percentEncode(String s) throws UnsupportedEncodingException {
String encode = URLEncoder.encode(s, StandardCharsets.UTF_8.toString());
return encode.replaceAll("\\+", "%20");
}
}import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.util.IdUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.ruoyi.common.convert.ExcelBigNumberConvert;
import com.ruoyi.common.excel.CellMergeStrategy;
import com.ruoyi.common.excel.DefaultExcelListener;
import com.ruoyi.common.excel.ExcelListener;
import com.ruoyi.common.excel.ExcelResult;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.file.FileUtils;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.Collection;
import java.util.List;
import java.util.Map;
/**
* Excel处理工具类
*/
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class ExcelUtil {
/**
* 同步导入(适用于小数据量)
*
* @param is 输入流
* @return 转换后集合
*/
public static <T> List<T> importExcel(InputStream is, Class<T> clazz) {
return EasyExcel.read(is).head(clazz).autoCloseStream(false).sheet().doReadSync();
}
/**
* 使用校验监听器 异步导入 同步返回
*
* @param is 输入流
* @param clazz 对象类型
* @param isValidate 是否 Validator 检验 默认为是
* @return 转换后集合
*/
public static <T> ExcelResult<T> importExcel(InputStream is, Class<T> clazz, boolean isValidate) {
DefaultExcelListener<T> listener = new DefaultExcelListener<>(isValidate);
EasyExcel.read(is, clazz, listener).sheet().doRead();
return listener.getExcelResult();
}
/**
* 使用自定义监听器 异步导入 自定义返回
*
* @param is 输入流
* @param clazz 对象类型
* @param listener 自定义监听器
* @return 转换后集合
*/
public static <T> ExcelResult<T> importExcel(InputStream is, Class<T> clazz, ExcelListener<T> listener) {
EasyExcel.read(is, clazz, listener).sheet().doRead();
return listener.getExcelResult();
}
/**
* 导出excel
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @param clazz 实体类
* @param response 响应体
*/
public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, HttpServletResponse response) {
exportExcel(list, sheetName, clazz, false, response);
}
/**
* 导出excel
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @param clazz 实体类
* @param merge 是否合并单元格
* @param response 响应体
*/
public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, boolean merge, HttpServletResponse response) {
try {
resetResponse(sheetName, response);
ServletOutputStream os = response.getOutputStream();
ExcelWriterSheetBuilder builder = EasyExcel.write(os, clazz)
.autoCloseStream(false)
// 自动适配
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// 大数值自动转换 防止失真
.registerConverter(new ExcelBigNumberConvert())
.sheet(sheetName);
if (merge) {
// 合并处理器
builder.registerWriteHandler(new CellMergeStrategy(list, true));
}
builder.doWrite(list);
} catch (IOException e) {
throw new RuntimeException("导出Excel异常");
}
}
/**
* 单表多数据模板导出 模板格式为 {.属性}
* {属性}直接填充单个值
* {.属性}填充的为list
*
* @param filename 文件名
* @param templatePath 模板路径 resource 目录下的路径包括模板文件名
* 例如: excel/temp.xlsx
* 重点: 模板文件必须放置到启动类对应的 resource 目录下
* @param data 模板需要的数据
*/
public static void exportTemplate(List<Object> data, String filename, String templatePath, HttpServletResponse response) {
try {
resetResponse(filename, response);
ClassPathResource templateResource = new ClassPathResource(templatePath);
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
.withTemplate(templateResource.getStream())
.autoCloseStream(false)
// 大数值自动转换 防止失真
.registerConverter(new ExcelBigNumberConvert())
.build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
if (CollUtil.isEmpty(data)) {
throw new IllegalArgumentException("数据为空");
}
// 单表多数据导出 模板格式为 {.属性}
for (Object d : data) {
excelWriter.fill(d, writeSheet);
}
excelWriter.finish();
} catch (IOException e) {
throw new RuntimeException("导出Excel异常");
}
}
/**
* 多表多数据模板导出 模板格式为 {key.属性}
* {属性}直接填充单个值
* {.属性}填充的为list
*
* @param filename 文件名
* @param templatePath 模板路径 resource 目录下的路径包括模板文件名
* 例如: excel/temp.xlsx
* 重点: 模板文件必须放置到启动类对应的 resource 目录下
* @param data 模板需要的数据
*/
public static void exportTemplateMultiList(Map<String, Object> data, String filename, String templatePath, HttpServletResponse response) {
try {
resetResponse(filename, response);
ClassPathResource templateResource = new ClassPathResource(templatePath);
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
.withTemplate(templateResource.getStream())
.autoCloseStream(false)
// 大数值自动转换 防止失真
.registerConverter(new ExcelBigNumberConvert())
.build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
if (CollUtil.isEmpty(data)) {
throw new IllegalArgumentException("数据为空");
}
for (Map.Entry<String, Object> map : data.entrySet()) {
// 设置列表后续还有数据
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
if (map.getValue() instanceof Collection) {
// 多表导出必须使用 FillWrapper
excelWriter.fill(new FillWrapper(map.getKey(), (Collection<?>) map.getValue()), fillConfig, writeSheet);
} else {
excelWriter.fill(map.getValue(), writeSheet);
}
}
excelWriter.finish();
} catch (IOException e) {
throw new RuntimeException("导出Excel异常");
}
}
/**
* 单表数据多excel导出打包为zip 模板格式为 {key.属性}
* {属性}直接填充单个值
* {.属性}填充的为list
*
* @param filename 文件名
* @param templatePath 模板路径 resource 目录下的路径包括模板文件名
* 例如: excel/导出使用的模板.xlsx
* 重点: 模板文件必须放置到启动类对应的 resource 目录下
* @param data 模板需要的数据
*/
public static void exportTemplateZip(Map<String, Object> objects, String excelName, String templateNamePath, HttpServletResponse response) throws Exception {
// 这里URLEncoder.encode可以防止中文乱码
String zipFileName = URLEncoder.encode("导出zip包名", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + zipFileName + ".zip");
response.setContentType("application/x-msdownload");
response.setCharacterEncoding("utf-8");
ClassPathResource templateResource = new ClassPathResource(templateNamePath);
//开始存入
try (ZipOutputStream zipOut = new ZipOutputStream(response.getOutputStream())) {
List<PerformanceAppraisal> performanceAppraisals = appraisalMapper.selectList(new LambdaQueryWrapper<PerformanceAppraisal>().eq(PerformanceAppraisal::getGradeStatus, ScoreStatus.FIN.getCode()));
if (CollectionUtil.isEmpty(performanceAppraisals)) {
throw new RuntimeException("暂无打分数据生成！");
}
for (PerformanceAppraisal performanceAppraisal : performanceAppraisals) {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel
.write(outputStream)
.withTemplate(templateResource.getStream())
.build();
zipOut.putNextEntry(new ZipEntry(IdUtil.fastSimpleUUID()+excelName));
//开始写入excel
WriteSheet writeSheet = EasyExcel.writerSheet().build();
if (CollUtil.isEmpty(objects)) {
throw new IllegalArgumentException("数据为空");
}
// 单表多数据导出 模板格式为 {.属性}
for (Object d : objects) {
excelWriter.fill(d, writeSheet);
}
excelWriter.finish();
outputStream.writeTo(zipOut);
zipOut.closeEntry();
}
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
response.getWriter().println("下载文件失败" + e.getMessage());
}
}
/**
* 重置响应体
*/
private static void resetResponse(String sheetName, HttpServletResponse response) throws UnsupportedEncodingException {
String filename = encodingFilename(sheetName);
response.reset();
FileUtils.setAttachmentResponseHeader(response, filename);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
}
/**
* 导出文件名
*/
public static String encodingFilename(String filename) {
return IdUtil.fastSimpleUUID() + "_" + filename + ".xlsx";
}
}3. 添加导出模板，注意一定要添加在resource下面！！
- 单表导出模板
- 多表导出模板
- 单表数据打包模板（和单表的模板是一样的，同理，也可以多表打包导出）
4. 测试excelUtil工具类以及导出结果
import cn.hutool.core.collection.CollUtil;
import com.包名.ExcelUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 测试Excel功能
*
* @author lgd
*/
@Api(value = "测试Excel功能", tags = {"测试Excel功能"})
@RestController
@RequestMapping("/demo/excel")
public class TestExcelController {
/**
* 单列表多数据
*/
@ApiOperation(value = "单列表多数据")
@GetMapping("/exportTemplateOne")
public void exportTemplateOne(HttpServletResponse response) {
Map<String,String> map = new HashMap<>();
map.put("title","单列表多数据");
map.put("test1","数据测试1");
map.put("test2","数据测试2");
map.put("test3","数据测试3");
map.put("test4","数据测试4");
map.put("testTest","666");
List<TestObj> list = new ArrayList<>();
list.add(new TestObj("单列表测试1", "列表测试1", "列表测试2", "列表测试3", "列表测试4"));
list.add(new TestObj("单列表测试2", "列表测试5", "列表测试6", "列表测试7", "列表测试8"));
list.add(new TestObj("单列表测试3", "列表测试9", "列表测试10", "列表测试11", "列表测试12"));
ExcelUtil.exportTemplate(CollUtil.newArrayList(map,list),"单列表.xlsx", "excel/单列表.xlsx", response);
}
/**
* 多列表多数据
*/
@ApiOperation(value = "多列表多数据")
@GetMapping("/exportTemplateMuliti")
public void exportTemplateMuliti(HttpServletResponse response) {
Map<String,String> map = new HashMap<>();
map.put("title1","标题1");
map.put("title2","标题2");
map.put("title3","标题3");
map.put("title4","标题4");
map.put("author","lgd");
List<TestObj1> list1 = new ArrayList<>();
list1.add(new TestObj1("list1测试1", "list1测试2", "list1测试3"));
list1.add(new TestObj1("list1测试4", "list1测试5", "list1测试6"));
list1.add(new TestObj1("list1测试7", "list1测试8", "list1测试9"));
List<TestObj1> list2 = new ArrayList<>();
list2.add(new TestObj1("list2测试1", "list2测试2", "list2测试3"));
list2.add(new TestObj1("list2测试4", "list2测试5", "list2测试6"));
List<TestObj1> list3 = new ArrayList<>();
list3.add(new TestObj1("list3测试1", "list3测试2", "list3测试3"));
List<TestObj1> list4 = new ArrayList<>();
list4.add(new TestObj1("list4测试1", "list4测试2", "list4测试3"));
list4.add(new TestObj1("list4测试4", "list4测试5", "list4测试6"));
list4.add(new TestObj1("list4测试7", "list4测试8", "list4测试9"));
list4.add(new TestObj1("list4测试10", "list4测试11", "list4测试12"));
Map<String,Object> multiListMap = new HashMap<>();
multiListMap.put("map",map);
multiListMap.put("data1",list1);
multiListMap.put("data2",list2);
multiListMap.put("data3",list3);
multiListMap.put("data4",list4);
ExcelUtil.exportTemplateMultiList(multiListMap, "多列表.xlsx", "excel/多列表.xlsx", response);
}
/**
* 单列表多数据
*/
@ApiOperation(value = "测试单表数据打包zip")
@GetMapping("/exportTemplateZip")
public void exportTemplateZip(HttpServletResponse response) {
Map<String,String> map = new HashMap<>();
map.put("title","单列表多数据");
map.put("test1","数据测试1");
map.put("test2","数据测试2");
map.put("test3","数据测试3");
map.put("test4","数据测试4");
map.put("testTest","666");
List<TestObj> list = new ArrayList<>();
list.add(new TestObj("单列表测试1", "列表测试1", "列表测试2", "列表测试3", "列表测试4"));
list.add(new TestObj("单列表测试2", "列表测试5", "列表测试6", "列表测试7", "列表测试8"));
list.add(new TestObj("单列表测试3", "列表测试9", "列表测试10", "列表测试11", "列表测试12"));
ExcelUtil.exportTemplateZip(CollUtil.newArrayList(map,list),"多个单列表打包.xlsx", "excel/导出使用的模板.xlsx", response);
}
@Data
@AllArgsConstructor
static class TestObj1 {
private String test1;
private String test2;
private String test3;
}
@Data
@AllArgsConstructor
static class TestObj {
private String name;
private String list1;
private String list2;
private String list3;
private String list4;
}
}5. 导出功能完毕！有什么不懂的，欢迎留言~
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
