简介
EasyExcel 是一个基于 Java 的简单、省内存的读写 Excel 的开源项目。在尽可能节约内存的情况下支持读写百M的 Excel。 github地址:github.com/alibaba/eas…
建议先看下官方文档，对 Easy Excel 有个大概的了解。本文旨在通过一个实践案例为具体应用这个库做 Excel 读写抛砖引玉。
前言
- 本实践案例实现了动态头部，多 sheet 页导入导出功能，部分数据持久化，准备导出数据代码已省略。
- 部分代码和业务需求耦合，请自行对照替换。
- 其余未覆盖功能，如有需要请参考官网文档。
- 代码可能存在部分漏洞，尽情谅解，欢迎指正其中的问题。
Controller 层接口
@PostMapping("/importRules")
@ApiOperation(value = "导入", notes = "@author：zy")
public ResponseMessage<ExcelImportVO> importRules(
@ApiParam(value = "导入文件", required = true)
@RequestPart MultipartFile file)
{
return ResponseMessage.success(this.ruleService.importRules(file));
}
@PostMapping("/exportRules")
@ApiOperation(value = "导出", notes = "@author：zy")
public void exportRules(HttpServletResponse response,
@ApiParam(value = "导出质量规则的 ID", required = true)
@RequestBody List<String> ruleIds)
{
this.ruleService.exportRules(response, ruleIds);
}
Service 层逻辑
/**
* 导入
*
* @param file 导入的 Excel 文件
* @return ExcelImportVO
*/
@Override
@Transactional(rollbackFor = Exception.class)
public ExcelImportVO importRules(MultipartFile file) {
final RuleImportListener listener = new RuleImportListener(this);
// 获取文件全名
final String originalName = file.getOriginalFilename();
// 文件名后缀
final String extension = FilenameUtils.getExtension(originalName);
// 格式校验
if (Constants.FILE_EXTENSION_XLSX.equals(extension) || Constants.FILE_EXTENSION_XLS.equals(extension)) {
try (final InputStream inputStream = file.getInputStream()) {
// 读 Excel 文件
ExcelUtil.readExcel(inputStream, listener);
} catch (IOException e) {
final String errorMsg = "解析 Excel 文档出错";
log.error(errorMsg, e);
throw new BusinessException(errorMsg);
}
} else {
throw new BusinessException("导入的文件格式错误");
}
// 统计结果，根据业务场景替换
final ExcelImportVO vo = new ExcelImportVO();
return vo;
}
/**
* 导出 Excel
*
* @param response HttpServletResponse
* @param ruleIds 待导出质量规则的 ID
*/
@Override
@Transactional(rollbackFor = Exception.class)
public void exportRules(HttpServletResponse response, List<String> ruleIds) {
// 准备要导出的数据，根据具体业务场景替换
final List<ExcelExportDTO> exportData = getExportData(ruleIds);
final Date date = new Date();
String fileName = "质量规则-" + DateUtil.format(date, "yyyyMMdd");
log.info("------> 导出质量规则开始");
ExcelUtil.exportExcel(response, exportData, fileName);
log.info("------> 导出质量规则完成");
}
Service 用到的类
RuleImportListener，读 Excel 时调用该类中的方法
package com.carnation.service.rule;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
/**
* Excel 的读取类
*
* @author zy
* @date 2021/4/25
* @since 1.0.0
*/
public class ImportListener extends AnalysisEventListener<Map<Integer, String>> {
List<Map<Integer, String>> list = new LinkedList<>();
private ImportService importService;
public RuleImportListener(RuleService ruleService) {
this.ruleService = ruleService;
}
/**
* 读一行调用一次该方法
*
* @param data 读到的每一行数据
* @param analysisContext AnalysisContext
*/
@Override
public void invoke(Map<Integer, String> data, AnalysisContext analysisContext) {
list.add(data);
}
/**
* 读完一个 sheet 调用
*
* @param analysisContext AnalysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
if (list.size() > 1) {
// 导入模板中的描述信息行不导入
list.remove(0);
// 读完一个 sheet 保存一次数据，根据具体业务需要实现数据持久化即可
this.importService.saveData(list, analysisContext.readSheetHolder().getSheetName());
}
list.clear();
}
}
ExcelUtil，对 Easy Excel 读写的包装工具类
package com.carnation.utils;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.carnation.common.exception.BusinessException;
import com.carnation.domain.dto.rule.ExcelExportDTO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
/**
* @author zy
* @date 2021/4/28
* @since 1.0.0
*/
public class ExcelUtil {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 读 Excel 文件
*
* @param inputStream 数据源
* @param listener {@link com.alibaba.excel.read.listener.ReadListener} 的扩展类
*/
public static void readExcel(InputStream inputStream, ReadListener listener) {
ExcelReader excelReader = EasyExcelFactory.read(inputStream, listener).build();
final List<ReadSheet> sheetList = excelReader.excelExecutor().sheetList();
if (!CollectionUtils.isEmpty(sheetList)) {
sheetList.forEach(readSheet -> {
final String name = readSheet.getSheetName();
log.info("------> 读取 sheet 页【{}】开始", name);
excelReader.read(readSheet);
log.info("------> 读取 sheet 页【{}】结束", name);
});
} else {
final String error = "从 Excel 文档中未读取到 sheet 页，请检查文档内容";
log.error(error);
throw new BusinessException(error);
}
excelReader.finish();
}
/**
* 写入数据到 Excel
*
* @param outputStream OutputStream
* @param exportData 待写入 Excel 的数据
*/
public static void writeExcel(OutputStream outputStream, List<ExcelExportDTO> exportData) {
final ExcelWriter excelWriter = EasyExcelFactory.write(outputStream).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
WriteSheet writeSheet;
if (!CollectionUtils.isEmpty(exportData)) {
for (int i = 0; i < exportData.size(); i++) {
final ExcelExportDTO excelExportDTO = exportData.get(i);
final String sheetName = excelExportDTO.getSheetName();
log.info("------> 开始写入数据到 sheet 页【{}】", sheetName);
// 每次写一个 sheet 都要创建 writeSheet，这里注意必须指定 sheetNo
writeSheet = EasyExcelFactory.writerSheet(i, sheetName)
.head(excelExportDTO.getHead()).build();
excelWriter.write(excelExportDTO.getData(), writeSheet);
log.info("------> 写入数据到 sheet 页【{}】完成", sheetName);
}
}
excelWriter.finish();
}
/**
* 导出 Excel 文件
*
* @param response HttpServletResponse
* @param exportData 导出的数据
* @param name 导出文件的名称
*/
public static void exportExcel(HttpServletResponse response, List<ExcelExportDTO> exportData, String name) {
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里 URLEncoder.encode 可以防止中文乱码
try (ServletOutputStream outputStream = response.getOutputStream()) {
String fileName = URLEncoder.encode(name, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelUtil.writeExcel(outputStream, exportData);
} catch (UnsupportedEncodingException e) {
final String error = "构建下载文件名称出错";
log.error(error, e);
throw new BusinessException(error);
} catch (IOException e) {
final String error = "写入数据到导出 Excel 文件出错";
log.error(error, e);
throw new BusinessException(error);
}
}
private ExcelUtil() {
}
}
ExcelExportDTO，导出 Excel DTO 对象（仅供参考）
package com.carnation.domain.dto.rule;
import java.io.Serializable;
import java.util.List;
/**
* 导出 Excel DTO
*
* @author zy
* @date 2021/4/27
* @since 1.0.0
*/
public class ExcelExportDTO implements Serializable {
private static final long serialVersionUID = -9159070866860531020L;
/**
* sheet 页名称
*/
private String sheetName;
/**
* 待写入每个 sheet 页的内容
*/
private List<List<String>> data;
/**
* sheet 页 title 行内容
*/
private List<List<String>> head;
public String getSheetName() {
return this.sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<List<String>> getData() {
return this.data;
}
public void setData(List<List<String>> data) {
this.data = data;
}
public List<List<String>> getHead() {
return this.head;
}
public void setHead(List<List<String>> head) {
this.head = head;
}
@Override
public String toString() {
return "RuleExportDTO{" +
"sheetName='" + sheetName + '\'' +
", data=" + data +
", head=" + head +
'}';
}
}
