“easyExcel”导入的代码实现 - ToB企服应用市场:ToB评测及商务社交产业平台

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>

复制代码

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
/**
* 读取数据监听器.
* 监听excel解析到的数据
* @author: xiehj
* Date: 2023-06-07
*/
@Slf4j
public class CommonExcelListener<T> extends AnalysisEventListener<T> {
/**存放解析到的数据，给了一个初始容量，为了避免list的频繁扩容带来的性能问题.*/
private final List<T> list = new ArrayList<>(1000);
/**解析每一行都会执行该方法.*/
@Override
public void invoke(final T data, final AnalysisContext analysisContext) {
this.list.add(data);
}
/**解析完成.*/
@Override
public void doAfterAllAnalysed(final AnalysisContext analysisContext) {
}
/**解析表头.*/
@Override
public void invokeHeadMap(final Map<Integer, String> headMap, final AnalysisContext context) {
headMap.entrySet().removeIf(h -> Objects.isNull(h.getValue()) || "".equals(h.getValue()));
}
public List<T> getList() {
return this.list;
}
}

复制代码

public String importFile(final InputStream inputStream) {
// 如果集合为空，则不用处理。
if (Objects.isNull(inputStream)) {
throw new ExtBusinessException("导入文件时，文件流异常。");
}
final CommonExcelListener<ArticleImportVo> listener = new CommonExcelListener<>();
EasyExcel.read(inputStream, ArticleImportVo.class, listener)
.headRowNumber(1)
.autoTrim(true)
.sheet(0)
.doRead();
// list即为文件流解析出的数据实体类。
final List<ArticleImportVo> list = listener.getList();
}

复制代码

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.alibaba.excel.EasyExcel;
import com.mocha.framework.commons.log.annotation.AuditLog;
import com.mocha.framework.commons.log.annotation.Language;
import com.mocha.sn.business.itbussiness.service.IExportCaseService;
import com.mocha.sn.business.itbussiness.vo.ArticleImportVo;
import lombok.extern.slf4j.Slf4j;
/**
* 导出数据控制器.
* Author：xiehj
* Date：2023-06-12
*/
@RestController
@RequestMapping("/v1/api/it/pc/business/export")
@Slf4j
public class ExportCaseController {
private static final String EXCEL_TYPE = ".xlsx";
@Resource
private IExportCaseService exportCaseService;
/**
* 导出文件。
* @param response 前端响应。
*/
@GetMapping("/file")
@AuditLog(operation = @Language(cn = "下载附件"))
public void downloadFile(final HttpServletResponse response) {
final List<ArticleImportVo> articleVos = exportCaseService.exportFile();
final String excelName = "导出数据";
final String fileName = new String(excelName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.addHeader("content-Disposition", "attachment;filename=" + fileName + EXCEL_TYPE);
try {
EasyExcel.write(response.getOutputStream(), ArticleImportVo.class)
.autoCloseStream(Boolean.TRUE).sheet(excelName).doWrite(articleVos);
} catch (final IOException e) {
log.error("导出文件失败。", e);
}
}
}

复制代码