EasyExcel导出工具类(支持模板导出多Sheet导出)

最近写需求发现没有顺手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文章来源地址https://www.toymoban.com/news/detail-578526.html

到了这里，关于EasyExcel导出工具类(支持模板导出多Sheet导出)的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！