最近写需求发现没有顺手excel导出工具类, 于是自己造了个小轮子, 链式一"."到底, 代码既注释
特点:
支持多sheet
支持从模板导出
支持分页拿数据, 避免数据量过大一次拿出导致内存溢出
数据类型支持业务实体类或Map, 无需easyExcel的注解, 低侵入
工具类使用演示
基于数据导出excel演示
@GetMapping("/exportDemo")
public R exportDemo(Map<String, Object> p, HttpServletResponse res) {
ExportChainUtil.export("用户信息", res) // 准备导出, 参数: excel文件名、HttpServletResponse res
.sheet("Map测试", Lists.newArrayList( // 导出Map数据
ImmutableMap.of("t1", 1, "t2", 2),
ImmutableMap.of("t1", 11, "t2", 22)
))
.column("字段1", i -> i.get("t1")) // 定义excel列
.column("字段2", i -> i.get("t2"))
.sheet("测试sheet1", Lists.newArrayList(
User.builder().name("张三").address("北京").build(),
User.builder().name("李四").address("山东").build()
)) // 导出excel第二个sheet, 参数: sheet的名称、数据, 数据可以是List<实体类>, 可以是List<Map<String, Object>>
.column("用户名", User::getName) // 参数: 第一列的名称、数据
.column("地址", User::getAddress) // 参数: 第二列的名称、数据
.sheet((pageNum, pageSize) -> bService.page(new Page<B>(pageNum, pageSize, false)).getRecords()) // 导出excel第三个sheet, 参数: 分页获取数据function
.column("主键", B::getId)
.column("代号", B::getCode)
.finish(); // 写入数据, 关闭excel文件流, 最后必须调用, 不然excel没数据
return R.ok();
}导出的excel
基于模板导出演示
@GetMapping("/exportByTemplate")
public R exportByTemplate(Map<String, Object> p, HttpServletResponse res) {
ExportChainUtil.exportByTemplate("模板Demo Excel", "lib/money_template.xlsx", res)
.sheet(0)
.column((pageNum, pageSize) -> userService.page(new Page<TUser>(pageNum, pageSize, false)).getRecords())
.column("time", new Date())
.sheet(1)
.column(Lists.newArrayList(
ImmutableMap.of("money", "aaa", "test1", "sss"),
ImmutableMap.of("money", "aaa", "test1", "sss")
))
.column((pageNum, pageSize) -> userService.page(new Page<TUser>(pageNum, pageSize, false)).getRecords())
.finish();
return R.ok();
}模板文件
模板路径
导出效果
导出用的数据
使用
maven依赖
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.4</version>
</dependency>
导出工具类ExportChainUtil
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.handler.SheetWriteHandler;
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.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.BiFunction;
import java.util.function.Function;
/**
* easyExcel xlsx导出工具 (链式) (easyExcel基于3.1.4)
* 支持多sheet
* 支持从模板导出
* 支持分页拿数据, 避免数据量过大一次拿出导致内存溢出
* 数据类型支持业务实体类或Map, 无需easyExcel的注解, 低侵入
* 如果你完善了该轮子, 很荣幸收到您的更新: 1622321813@qq.com
*/
public class ExportChainUtil {
/*
Demo:
@GetMapping("/exportDemo")
public R exportDemo(Map<String, Object> p, HttpServletResponse res) {
ExportChainUtil.export("用户信息", res) // 准备导出, 参数: excel文件名、HttpServletResponse res
.sheet("Map测试", Lists.newArrayList(
ImmutableMap.of("t1", 1, "t2", 2),
ImmutableMap.of("t1", 11, "t2", 22)
))
.column("字段1", i -> i.get("t1"))
.column("字段2", i -> i.get("t2"))
.sheet("测试sheet1", Lists.newArrayList(
User.builder().name("张三").address("北京").build(),
User.builder().name("李四").address("山东").build()
)) // 声明开始写入excel第一个sheet, 参数: sheet的名称、数据, 数据可以是List<实体类>, 可以是List<Map<String, Object>>
.column("用户名", User::getName) // 参数: 第一列的名称、数据
.column("地址", User::getAddress) // 参数: 第二列的名称、数据
.sheet((pageNum, pageSize) -> bService.page(new Page<B>(pageNum, pageSize, false)).getRecords()) // 声明开始写入excel第二个sheet, 参数: 分页获取数据function
.column("主键", B::getId)
.column("代号", B::getCode)
.finish(); // 写入数据, 关闭excel文件流, 最后必须调用, 不然excel没数据
return R.ok();
}
模板导出:
@GetMapping("/exportByTemplate")
public R exportByTemplate(Map<String, Object> p, HttpServletResponse res) {
ExportChainUtil.exportByTemplate("模板Demo Excel", "lib/money_template.xlsx", res)
.sheet(0)
.column((pageNum, pageSize) -> userService.page(new Page<TUser>(pageNum, pageSize, false)).getRecords())
.column("time", new Date())
.sheet(1)
.column(Lists.newArrayList(
ImmutableMap.of("money", "aaa", "test1", "sss"),
ImmutableMap.of("money", "aaa", "test1", "sss")
))
.column((pageNum, pageSize) -> userService.page(new Page<TUser>(pageNum, pageSize, false)).getRecords())
.finish();
return R.ok();
}
*/
/**
* 导出excel
* @param fileName excel文件名
*/
public static CommonSheet export(String fileName, HttpServletResponse res) {
try {
setHead(fileName, res);
return new CommonSheet(EasyExcel.write(res.getOutputStream()).registerWriteHandler(new FreezeAndFilterHandler()).build());
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 基于模板导出excel
* @param fileName excel文件名
* @param templatePath 模板路径, 文件xlsx格式 (excel模板内在每列数据开头处填入{.key}, 表示该字段从此处开始写入excel, 例: 用户名: {.name}, 表头自己在模板excel内搞好)
*/
public static TemplateSheet exportByTemplate(String fileName, String templatePath, HttpServletResponse res) {
try {
setHead(fileName, res);
return new TemplateSheet(EasyExcel.write(res.getOutputStream()).withTemplate(templatePath).build());
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 无模板导出excel实现类
*/
public static class CommonSheet {
private int sheetDefaultNo;
private ExcelWriter excelWriter;
private CommonSheetDetail currentSheet;
private Set<Integer> complateSheetSet = new HashSet<>();
private Set<String> sheetNameSet = new HashSet<>();
public CommonSheet(ExcelWriter excelWriter) {
this.excelWriter = excelWriter;
}
public ExcelWriter getExcelWriter() {
return excelWriter;
}
public <T>CommonSheetDetail<T> sheet(List<T> data) {
return sheet(null, data);
}
public <T>CommonSheetDetail<T> sheet(String sheetName, List<T> data) {
int sheetNo = incrementAndGetSheetDefaultNo();
if(sheetName == null) {
sheetName = "sheet"+sheetNo;
}
check(sheetNo, sheetName, "如不是一次传入所有数据, 请调用另一个sheet方法, 那个方法支持数据分批次写入");
CommonSheetDetail commonSheetDetail = new CommonSheetDetail(data, sheetNo, EasyExcel.writerSheet(sheetNo, sheetName).build(), this);
updateSheet(commonSheetDetail);
return commonSheetDetail;
}
public <T>CommonSheetDetail<T> sheet(BiFunction<Integer, Integer, List<T>> pageNumPageSizeDataFunction) {
return sheet(pageNumPageSizeDataFunction, null, null);
}
public <T>CommonSheetDetail<T> sheet(BiFunction<Integer, Integer, List<T>> pageNumPageSizeDataFunction, Integer startPageNum, Integer pageSize) {
return sheet(null, pageNumPageSizeDataFunction, startPageNum, pageSize);
}
public <T>CommonSheetDetail<T> sheet(String sheetName, BiFunction<Integer, Integer, List<T>> pageNumPageSizeDataFunction) {
return sheet(sheetName, pageNumPageSizeDataFunction, null, null);
}
public <T>CommonSheetDetail<T> sheet(String sheetName, BiFunction<Integer, Integer, List<T>> pageNumPageSizeDataFunction, Integer startPageNum, Integer pageSize) {
int sheetNo = incrementAndGetSheetDefaultNo();
if(sheetName == null) {
sheetName = "sheet"+sheetNo;
}
check(sheetNo, sheetName, "同一个sheetNo勿多次调用, 自己封装一个方法包含每一列数据, 然后将该方法传到入参pageNumPageSizeDataFunction里");
CommonSheetDetail commonSheetDetail = new CommonSheetDetail(pageNumPageSizeDataFunction, startPageNum, pageSize, sheetNo, EasyExcel.writerSheet(sheetNo, sheetName).build(), this);
updateSheet(commonSheetDetail);
return commonSheetDetail;
}
/**
* set完毕, 写入数据, 关闭流
*/
public void finish() {
updateSheet(null);
excelWriter.finish();
}
/**
* 校验sheetNo是否已经创建过
* @param sheetNo
* @param errMsg
*/
private void check(int sheetNo, String sheetName, String errMsg) {
if(!this.complateSheetSet.add(sheetNo)) {
throw new RuntimeException(errMsg);
}
if(!sheetNameSet.add(sheetName)) {
throw new RuntimeException("sheetName\""+sheetName+"\"重复");
}
}
/**
* 把上一个sheet数据写入excel (如果有上一个sheet的话)
*/
private void updateSheet(CommonSheetDetail commonSheetDetail) {
if(this.currentSheet == null) {
this.currentSheet = commonSheetDetail;
}else if(!this.currentSheet.isWriteExcelCompleted) {
this.currentSheet.writeExcel();
}
this.currentSheet = commonSheetDetail;
}
private int incrementAndGetSheetDefaultNo() {
return this.sheetDefaultNo += 1;
}
}
public static class CommonSheetDetail<T> {
private boolean isWriteExcelCompleted = false;
private CommonSheet commonSheet;
private int sheetNo;
private WriteSheet writeSheet;
private int defaultPageNum = 1, defaultPageSize = 2000;
private BiFunction<Integer, Integer, List<T>> getDatasourceFunction;
private List<T> datasourceList;
private List<String> keyList = new ArrayList<>();
private List<Function> valueFunctionList = new ArrayList<>();
public CommonSheetDetail(List<T> data, int sheetNo, WriteSheet writeSheet, CommonSheet commonSheet) {
this.datasourceList = data;
this.sheetNo = sheetNo;
this.writeSheet = writeSheet;
this.commonSheet = commonSheet;
}
public CommonSheetDetail(BiFunction<Integer, Integer, List<T>> pageNumPageSizeDataFunction, Integer startPageNum, Integer pageSize, int sheetNo, WriteSheet writeSheet, CommonSheet commonSheet) {
this.commonSheet = commonSheet;
this.getDatasourceFunction = pageNumPageSizeDataFunction;
this.sheetNo = sheetNo;
this.writeSheet = writeSheet;
Optional.ofNullable(startPageNum).ifPresent(i -> defaultPageNum = i);
Optional.ofNullable(pageSize).ifPresent(i -> defaultPageSize = i);
}
/**
* 创建sheet, 完后继续调用column方法设置列
* @param data 数据
*/
public <T>CommonSheetDetail<T> sheet(List<T> data) {
return commonSheet.sheet(data);
}
public <T>CommonSheetDetail<T> sheet(String sheetName, List<T> data) {
return commonSheet.sheet(sheetName, data);
}
/**
* 创建sheet, 完后继续调用column方法设置列
* @param pageNumPageSizeDataFunction 分页数据获取function Demo: .sheet((pageNum, pageSize) -> bService.page(new Page<B>(pageNum, pageSize, false)).getRecords())
*/
public <T>CommonSheetDetail<T> sheet(BiFunction<Integer, Integer, List<T>> pageNumPageSizeDataFunction) {
return commonSheet.sheet(pageNumPageSizeDataFunction);
}
/**
* @param startPageNum 默认为1
*/
public <T>CommonSheetDetail<T> sheet(BiFunction<Integer, Integer, List<T>> pageNumPageSizeDataFunction, Integer startPageNum, Integer pageSize) {
return commonSheet.sheet(pageNumPageSizeDataFunction, startPageNum, pageSize);
}
public <T>CommonSheetDetail<T> sheet(String sheetName, BiFunction<Integer, Integer, List<T>> pageNumPageSizeDataFunction) {
return commonSheet.sheet(sheetName, pageNumPageSizeDataFunction);
}
public <T>CommonSheetDetail<T> sheet(String sheetName, BiFunction<Integer, Integer, List<T>> pageNumPageSizeDataFunction, Integer startPageNum, Integer pageSize) {
return commonSheet.sheet(sheetName, pageNumPageSizeDataFunction, startPageNum, pageSize);
}
/**
* 写入列
* @param columnName 列名
* @param getData 该列的数据获取Function, 数据基于sheet方法传入的List
* @return
*/
public CommonSheetDetail<T> column(String columnName, Function<T, ?> getData) {
return columnIf(true, columnName, getData);
}
/**
* @param isAdd 该字段为true才会生效
*/
public CommonSheetDetail<T> columnIf(Boolean isAdd, String columnName, Function<T, ?> getData) {
checkIsSheetCreate();
if(isAdd != null && isAdd) {
this.keyList.add(columnName);
this.valueFunctionList.add(getData);
}
return this;
}
public void finish() {
commonSheet.finish();
}
private void checkIsSheetCreate() {
if(writeSheet == null) {
throw new RuntimeException("先调用sheet方法再来set");
}
}
private void writeExcel() {
if(this.isWriteExcelCompleted) {
return;
}
this.isWriteExcelCompleted = true;
// 最终写入数据
List<List<?>> sheetList = new LinkedList();
// 写表头
sheetList.add(keyList);
// 写数据 (不分批次)
if(this.datasourceList != null) {
// 拿到所有数据获取function
for(T data : datasourceList) {
LinkedList<Object> lineList = new LinkedList<>();
valueFunctionList.forEach(getDataFunction -> {
lineList.add(getDataFunction.apply(data));
});
sheetList.add(lineList);
}
}else if(getDatasourceFunction != null) {
// getDatasourceFunction 分页拿数据写入
int pageNum = this.defaultPageNum;
List<T> applyList = getDatasourceFunction.apply(pageNum, defaultPageSize);
while(applyList != null && applyList.size() > 0) {
for(T data : applyList) {
LinkedList<Object> lineList = new LinkedList<>();
valueFunctionList.forEach(getDataFunction -> {
lineList.add(getDataFunction.apply(data));
});
sheetList.add(lineList);
}
applyList = getDatasourceFunction.apply(pageNum += 1, defaultPageSize);
}
}
// sheet数据组织完毕, 写入excel
commonSheet.getExcelWriter().write(sheetList, writeSheet);
// 清空引用
this.datasourceList = null;
this.keyList = null;
this.valueFunctionList = null;
}
}
/**
* 根据模板导出实现类
* todo idea: 可以在后面追加新的空白sheet, 有空再写, 就当复习~
*/
public static class TemplateSheet {
private FillConfig fillConfig;
private ExcelWriter excelWriter;
private WriteSheet writeSheet;
private int defaultPageNum = 1, defaultPageSize = 2000;
public TemplateSheet(ExcelWriter excelWriter) {
this.excelWriter = excelWriter;
}
/**
* 创建sheet, 不支持设置sheet名, 自己在模板里设置吧
* @param sheetNo 编号, 第一个sheet为0 !!!!!!!
* @return
*/
public TemplateSheet sheet(int sheetNo) {
this.writeSheet = EasyExcel.writerSheet(sheetNo).build();
return this;
}
/**
* 写入一列数据 (可以追加该key)
* @param key 对应模板内的{.key}
* @param value 数据
* @return
*/
public TemplateSheet column(String key, List<Object> value) {
checkIsSheetCreate();
excelWriter.fill(new FillWrapper(swapFillFormat(key, value)), writeSheet);
return this;
}
/**
* 写入一列数据 (可以追加该key)
* @param key 对应模板内的{.key}
* @param value 数据
* @return
*/
public TemplateSheet column(String key, Object...value) {
checkIsSheetCreate();
excelWriter.fill(new FillWrapper(swapFillFormat(key, Lists.newArrayList(value))), writeSheet);
return this;
}
/**
* 写入多字段
* @param data 数据, 泛型T为实体类 or Map, key对应模版内的{.key}
* @return
*/
public <T>TemplateSheet column(List<T> data) {
return setList(data);
}
/**
* 分页写入多字段
* @param dataFunction 数据, 泛型T为实体类 or Map, key对应模版内的{.key}
* @return
*/
public <T>TemplateSheet column(BiFunction<Integer, Integer, List<T>> dataFunction) {
return column(dataFunction, null, null);
}
public <T>TemplateSheet column(BiFunction<Integer, Integer, List<T>> dataFunction, Integer pageNum, Integer pageSize) {
pageNum = Optional.ofNullable(pageNum).orElse(defaultPageNum);
pageSize = Optional.ofNullable(pageSize).orElse(defaultPageSize);
List<T> dataList = dataFunction.apply(pageNum, pageSize);
while(dataList != null && dataList.size() > 0) {
setList(dataList);
dataList = dataFunction.apply(pageNum += 1, pageSize);
}
return this;
}
/**
* 横向写入数据 (可以追加该key)
* @param key 对应模板内的{.key}
* @param data 数据, 格式: ["123",123, new Date(), new BigDecimal() ....]
* @return
*/
public TemplateSheet setLine(String key, List<Object> data) {
checkIsSheetCreate();
if(fillConfig == null) {
this.fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
}
List<Map<String, Object>> list = Lists.newArrayList();
for(Object o : data) {
list.add(ImmutableMap.of(key, o));
}
excelWriter.fill(new FillWrapper(swapFillFormat(key, data)), fillConfig, writeSheet);
return this;
}
/**
* set完毕, 写入数据, 关闭流, 不调用此方法excel内不会有数据
*/
public void finish() {
excelWriter.finish();
}
// 写入excel多字段
private TemplateSheet setList(List data) {
checkIsSheetCreate();
excelWriter.fill(new FillWrapper(data), writeSheet);
return this;
}
// 检测是否选择了sheet
private void checkIsSheetCreate() {
if(writeSheet == null) {
throw new RuntimeException("setColumn方法调用错误, 请修改代码, 先调用sheet方法再来使用该方法");
}
}
// 入参格式转为easyExcel写入格式
private List<Map<String, Object>> swapFillFormat(String fieldName, List<Object> data) {
List<Map<String, Object>> list = Lists.newArrayList();
for(Object o : data) {
list.add(ImmutableMap.of(fieldName, o));
}
return list;
}
}
/**
* 设置response头信息
* @param fileName
* @param res
* @throws UnsupportedEncodingException
*/
private static void setHead(String fileName, HttpServletResponse res) throws UnsupportedEncodingException {
// res.setContentType("application/xlsx");
// 设置编码格式
// res.setCharacterEncoding("UTF-8");
//设置文件名
//设置响应头
fileName = Optional.ofNullable(fileName).orElseGet(() -> "export_"+ new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()));
res.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + ".xlsx");
}
public static class FreezeAndFilterHandler implements SheetWriteHandler {
/**
* 四个参数分别代表:
* cellNum:表示要冻结的列数；
* rowNum:表示要冻结的行数；
* firstCellNum:表示被固定列右边第一列的列号；
* firstRollNum :表示被固定行下边第一列的行号;
*
* 举例：
* CreateFreezePane(0,1,0,1):冻结第一行,冻结行下侧第一行的左边框显示“2”
* CreateFreezePane(1,0,1,0):冻结第一列，冻结列右侧的第一列为B列
* CreateFreezePane(2,0,5,0):冻结左侧两列，冻结列右侧的第一列为F列
*/
public int colSplit = 0, rowSplit = 1, leftmostColumn = 0, topRow = 1;
public String autoFilterRange = "1:1";
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
sheet.setAutoFilter(CellRangeAddress.valueOf(autoFilterRange));
}
}
}
over
323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
