1.工具类部分
package net.lesscoding.utils;
import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;
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.ArrayList;
import java.util.List;
@Slf4j
public class EasyExcelUtil<T> {
private static final int MAX_SIZE = 900;
public <T> void downloadExcel(HttpServletResponse response, Class cls, String fileName, List<T> data) throws IOException {
if (CollUtil.isEmpty(data)) {
data = new ArrayList<>();
}
try (OutputStream os = response.getOutputStream()) {
setResponseHeader(response, fileName);
EasyExcel.write(os, cls)
.sheet("导入模板")
.doWrite(data);
} catch (IOException e) {
log.error("下载导入模板异常{}", e);
throw new IOException("下载导入模板异常");
}
}
public static void setResponseHeader(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("Connection", "close");
response.setHeader("Content-Type", "application/octet-stream");
}
public int parseExcelAndDoInsertBatch(MultipartFile file, Class cls, ServiceImpl impl) throws IOException {
List<T> resultList = new ArrayList<>();
try (InputStream inputStream = file.getInputStream()) {
EasyExcel.read(inputStream, cls, new ReadListener<T>() {
@Override
public void invoke(T t, AnalysisContext analysisContext) {
resultList.add(t);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}).sheet().doRead();
if (CollUtil.isNotEmpty(resultList)) {
List<List<T>> partition = Lists.partition(resultList, MAX_SIZE);
partition.forEach(impl::saveBatch);
}
return resultList.size();
}
}
public List<T> parseExcel(MultipartFile file, Class cls) throws IOException {
try (InputStream inputStream = file.getInputStream()) {
List<T> resultList = new ArrayList<>();
EasyExcel.read(inputStream, cls, new ReadListener<T>() {
@Override
public void invoke(T t, AnalysisContext analysisContext) {
resultList.add(t);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("解析数据完成-{}\n", resultList);
}
}).sheet().doRead();
return resultList;
}
}
public List<T> parseExcel(InputStream inputStream, Class cls) throws IOException {
List<T> resultList = new ArrayList<>();
EasyExcel.read(inputStream, cls, new ReadListener<T>() {
@Override
public void invoke(T t, AnalysisContext analysisContext) {
resultList.add(t);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("解析数据完成-{}\n", resultList);
}
}).sheet().doRead();
return resultList;
}
}
2. 使用
@Override
public void downloadTemplate(HttpServletResponse response) throws IOException {
new EasyExcelUtil<InterviewQuestion>()
.downloadExcel(response,
InterviewQuestion.class,
"面试问题表导入模板.xlsx",
null);
}
@Override
public Result import4Excel(MultipartFile file) throws IOException {
int insertBatch = new EasyExcelUtil<InterviewQuestion>()
.parseExcelAndDoInsertBatch(file, InterviewQuestion.class, this);
return ResultFactory.buildByResult(insertBatch);
}
@Override
public void export2Excel(InterviewQuestion interviewQuestion, HttpServletResponse response) throws IOException {
new EasyExcelUtil<InterviewQuestion>()
.downloadExcel(response,
InterviewQuestion.class,
"面试问题表.xlsx",
questionMapper.getPageByLike(interviewQuestion));
}