前言
上篇写了数据导入，本文补充一下EasyExcel 批量导出
包括常规excel和复杂excel
一、EasyExcel 导出封装
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.base.Charsets;
import com.gsafety.bg.gsdss.common.contants.CommonConstants;
import com.gsafety.bg.gsdss.common.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.atomic.AtomicReference;
/**
* AbstractExcelView
*/
@Slf4j
public abstract class AbstractExcelView<T> {
private String excelName;
private ExcelTypeEnum excelType;
private HttpServletResponse response;
public AbstractExcelView(HttpServletResponse response, String excelName, ExcelTypeEnum excelType) {
this.excelName = excelName;
this.excelType = excelType;
this.response = response;
}
protected OutputStream prepareResponse(HttpServletResponse response) throws Exception {
response.setContentType(CommonConstants.CONTENT_TYPE_EXCEL);
response.setCharacterEncoding(Charsets.UTF_8.name());
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(excelName, Charsets.UTF_8.name()) + excelType.getValue());
return response.getOutputStream();
}
public void exportExcel() {
ExcelWriter excelWriter = null;
try {
List<Sheet<T>> sheets = this.sheets();
if (CollectionUtils.isEmpty(sheets)) {
return;
}
excelWriter = EasyExcel.write(this.prepareResponse(response)).excelType(excelType).build();
for (Sheet s : sheets) {
ExcelWriterSheetBuilder sb = EasyExcel.writerSheet(s.getIndex(), s.getName()).head(s.getClz());
if (CollectionUtils.isNotEmpty(s.getHandlers())) {
List<WriteHandler> l = s.getHandlers();
l.forEach(h -> sb.registerWriteHandler(h));
}
if (CollectionUtils.isNotEmpty(this.sheetDynamicHead(s.getIndex()))){
sb.head(this.sheetDynamicHead(s.getIndex()));
}
excelWriter.write(this.sheetData(s.getIndex()), sb.build());
}
} catch (Exception e) {
log.error(" 导出失败! ", e);
throw new BusinessException(" 导出失败! ");
} finally {
if (Objects.nonNull(excelWriter)) {
excelWriter.finish();
}
}
}
public String asyncExport(ThreadPoolTaskExecutor executor) {
AtomicReference<String> result = new AtomicReference<>(StringUtils.EMPTY);
ExcelWriter excelWriter = null;
try {
List<Sheet<T>> sheets = this.sheets();
if (CollectionUtils.isEmpty(sheets)) {
return result.get();
}
excelWriter = EasyExcel.write(this.prepareResponse(response)).excelType(excelType).build();
WriteSheet[] writeSheets = sheets.stream().map(sheet -> {
ExcelWriterSheetBuilder sb = EasyExcel.writerSheet(sheet.getIndex(), sheet.getName())
.head(sheet.getClz());
if (CollectionUtils.isNotEmpty(sheet.getHandlers())) {
List<WriteHandler> l = sheet.getHandlers();
l.forEach(h -> sb.registerWriteHandler(h));
}
if (CollectionUtils.isNotEmpty(this.sheetDynamicHead(sheet.getIndex()))){
sb.head(this.sheetDynamicHead(sheet.getIndex()));
}
return sb.build();
}).toArray(WriteSheet[]::new);
ExcelWriter finalExcelWriter = excelWriter;
CompletableFuture[] cfs = sheets.stream()
.map(sheet -> CompletableFuture.runAsync(
() -> {
log.info(" 当前sheet {} {} ", sheet.getIndex(), sheet.getName());
log.info(" 当前sheet {} ", writeSheets[sheet.getIndex()]);
finalExcelWriter.write(this.sheetData(sheet.getIndex()), writeSheets[sheet.getIndex()]);
}, executor)
.exceptionally((e) -> {
result.set(sheet.getName() + " 导出失败 ");
log.error(sheet.getName() + " 导出失败 ", e);
return null;
}
)
).toArray(CompletableFuture[]::new);
CompletableFuture.allOf(cfs).join();
} catch (Exception e) {
log.error(" 导出失败! ", e);
throw new BusinessException(" 导出失败! ");
} finally {
if (Objects.nonNull(excelWriter)) {
excelWriter.finish();
}
}
return result.get();
}
protected abstract List<Sheet<T>> sheets();
protected abstract List<T> sheetData(int sheetIndex);
protected List<List<String>> sheetDynamicHead(int sheetIndex){
return null;
}
}
二、食用步骤
1.自定义excel样式
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
/**
* @description excel样式策略类
*/
public class ExcelCustomStyleStrategy {
public static HorizontalCellStyleStrategy getSimpleStyle(){
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 背景色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
// 字体策略
WriteFont headWriteFont = new WriteFont();
headWriteFont.setBold(false);
headWriteFont.setFontName("微软雅黑");
headWriteFont.setFontHeightInPoints((short) 12);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景色
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色。头默认了 FillPatternType所以可以不指定。
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//背景设置白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
// 字体策略
WriteFont contentWriteFont = new WriteFont();
//contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//设置 自动换行
contentWriteCellStyle.setWrapped(false);
//设置 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
//设置边框样式
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
}
2.导出数据
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.google.common.collect.Lists;
import com.gsafety.bg.gsdss.common.excel.out.AbstractExcelView;
import com.gsafety.bg.gsdss.common.excel.out.Sheet;
import com.gsafety.bg.sv.controller.excel.strategy.ExcelCustomStyleStrategy;
import com.gsafety.bg.sv.model.dto.resp.AccidentEduExcelResp;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
public class AccidentEduExcelView extends AbstractExcelView<AccidentEduExcelResp> {
//导出数据list对象 对象属性用@ExcelProperty("事故名称")即可生成表头
private final List<AccidentEduExcelResp> data;
public AccidentEduExcelView(HttpServletResponse response, List<AccidentEduExcelResp> data, String excelName) {
super(response, excelName, ExcelTypeEnum.XLSX);
this.data = data;
}
//定义excel样式、sheet名称等
@Override
protected List<Sheet<AccidentEduExcelResp>> sheets() {
return Lists.newArrayList(Sheet.<AccidentEduExcelResp>builder().name("事故警示教育")
.handlers(Lists.newArrayList(ExcelCustomStyleStrategy.getSimpleStyle(), new SimpleColumnWidthStyleStrategy(30))).clz(AccidentEduExcelResp.class).build());
}
//指定sheet页
@Override
protected List<AccidentEduExcelResp> sheetData(int sheetIndex) {
return data;
}
}
@ApiOperation(value = "列表导出")
@PostMapping("/v1/list/export")
public void downLoadList(@RequestBody AccidentEduQO req, HttpServletResponse response) {
//查询导出数据list 对象
List<AccidentEduExcelResp> data = service.list(req);
new AccidentEduExcelView(response,data,"事故警示教育").exportExcel();
}
三、复杂excel导出
3.1. 自定义复杂表头
通过重写sheetDynamicHead方法自定义复杂表头@ExcelProperty(order = 0)注解指定excel列即可填充到指定单元格
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.google.common.collect.Lists;
import com.gsafety.bg.gsdss.common.excel.out.AbstractExcelView;
import com.gsafety.bg.gsdss.common.excel.out.Sheet;
import com.gsafety.bg.sv.controller.excel.strategy.ExcelCustomStyleStrategy;
import com.gsafety.bg.sv.model.dto.resp.AccidentEduSLResp;
import com.gsafety.bg.sv.model.dto.resp.AccidentEduStatisticResp;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
public class AccidentEduSExcelView extends AbstractExcelView<AccidentEduSLResp> {
private AccidentEduStatisticResp data;
private Integer total;
public AccidentEduSExcelView(HttpServletResponse response, AccidentEduStatisticResp data, String excelName) {
super(response, excelName, ExcelTypeEnum.XLSX);
this.data = data;
this.total = data.getTotal();
}
@Override
protected List<Sheet<AccidentEduSLResp>> sheets() {
return Lists.newArrayList(Sheet.<AccidentEduSLResp>builder().name("警示教育信息")
.handlers(Lists.newArrayList(ExcelCustomStyleStrategy.getSimpleStyle(),new SimpleColumnWidthStyleStrategy(30))).clz(AccidentEduSLResp.class).build());
}
@Override
protected List<AccidentEduSLResp> sheetData(int sheetIndex) {
return data.getList();
}
//这里是动态表头，需要读取数据库计算数据数量做表头，所以中间走了一层，如果不是动态数据可直接返回sheetDynamicHead(Integer total)方法的内容
@Override
protected List<List<String>> sheetDynamicHead(int sheetIndex) {
return sheetDynamicHead(this.total);
}
private List<List<String>> sheetDynamicHead(Integer total){
List<List<String>> head = Lists.newArrayList();
head.add(new ArrayList() {{
add(" ");
add(" ");
add(" ");
}});
head.add(new ArrayList() {{
add("参与执法持证人员数");
add("参与执法持证人员数");
add("参与执法持证人员数");
}});
head.add(new ArrayList() {{
add("执法检查次数(次)");
add("合计");
add("合计");
}});
head.add(new ArrayList() {{
add("执法检查次数(次)");
add("检查");
add("检查");
}});
head.add(new ArrayList() {{
add("执法检查次数(次)");
add("复查");
add("复查");
}});
return head;
}
}
2. 多sheet
protected List sheets() 方法内返回多个sheet
protected List sheetData(int sheetIndex)方法内指定sheet填充的数据文章来源：https://www.toymoban.com/news/detail-485808.html
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.google.common.collect.Lists;
import com.gsafety.bg.emis.base.utils.ExcelCustomStyleStrategy;
import com.gsafety.bg.emis.euip.model.resp.EuipExcelListResp;
import com.gsafety.bg.emis.fpln.model.dto.resp.UavInfoResp;
import com.gsafety.bg.gsdss.common.excel.out.AbstractExcelView;
import com.gsafety.bg.gsdss.common.excel.out.Sheet;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
public class EuipExcelView extends AbstractExcelView {
private final List<EuipExcelListResp> euip;
private final List<UavInfoResp> uva;
public EuipExcelView(HttpServletResponse response, List<EuipExcelListResp> euip, List<UavInfoResp> uav, String excelName) {
super(response, excelName, ExcelTypeEnum.XLSX);
this.euip = euip;
this.uva = uav;
}
@Override
protected List<Sheet> sheets() {
return Lists.newArrayList(Sheet.<EuipExcelListResp>builder().name("通讯装备信息")
.index(0)
.handlers(Lists.newArrayList(ExcelCustomStyleStrategy.getSimpleStyle(), new SimpleColumnWidthStyleStrategy(30))).clz(EuipExcelListResp.class).build(),
Sheet.<UavInfoResp>builder().name("无人机信息").index(1)
.handlers(Lists.newArrayList(ExcelCustomStyleStrategy.getSimpleStyle(), new SimpleColumnWidthStyleStrategy(30))).clz(UavInfoResp.class).build());
}
@Override
protected List sheetData(int sheetIndex) {
if (0 == sheetIndex) {
return euip;
} else {
return uva;
}
}
}
文章来源地址https://www.toymoban.com/news/detail-485808.html
到了这里，关于EasyExcel 批量导出的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
