使用EasyExcel做自定义表头的excel文件导出

如题所示

项目中需要做表格导出功能，且表头为复杂的动态表头，决定采用EasyExcel来进行操作

demo使用到的依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.10</version></dependency><dependency><groupId>javax.servlet</groupId><artifactId>javax.servlet-api</artifactId><version>3.0.1</version><scope>provided</scope></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>compile</scope></dependency><dependency><groupId>org.assertj</groupId><artifactId>assertj-core</artifactId><version>RELEASE</version><scope>compile</scope></dependency>

具体代码展示如下：

packageexcelExport;importcom.alibaba.excel.EasyExcelFactory;importcom.alibaba.excel.ExcelWriter;importcom.alibaba.excel.write.metadata.WriteSheet;importcom.alibaba.excel.write.metadata.WriteTable;importorg.assertj.core.util.Lists;importorg.junit.Test;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.OutputStream;importjava.util.List;/**
 * @author: 骑猪撞地球QAQ
 * @date: 2022/3/18 15:44
 * @content:
 */publicclassExcelImport{@Testpublicvoidtest()throwsIOException{// 文件输出位置OutputStream out=newFileOutputStream("d://测试导出.xlsx");ExcelWriter writer=EasyExcelFactory.write(out).build();// 动态添加表头WriteSheet sheet1=newWriteSheet();
        sheet1.setSheetName("测试导出sheet1");
        sheet1.setSheetNo(0);// 创建一个表格，用于 Sheet 中使用WriteTable table=newWriteTable();
        table.setTableNo(1);
        table.setHead(head());// 写数据
        writer.write(contentData(), sheet1, table);
        writer.finish();
        out.close();}/**
     * 生成动态表头，表头数据可以当做参数传入
     *
     * @return 表头list
     */privatestaticList<List<String>>head(){List<List<String>> headTitles=Lists.newArrayList();String warZone="表头1", base="表头2", personal="表头3", total="合计", invoiceAmount="子项1", invoiceQuantity="子项2", subtotal="小计";//第一列，1/2/3行
        headTitles.add(Lists.newArrayList(warZone));
        headTitles.add(Lists.newArrayList(base));
        headTitles.add(Lists.newArrayList(personal));
        headTitles.add(Lists.newArrayList(total, invoiceAmount, invoiceAmount));
        headTitles.add(Lists.newArrayList(total, invoiceQuantity, invoiceQuantity));// 可动态获取List<String> channelList=Lists.newArrayList("动态渠道1","动态渠道2");// 可动态获取List<String> orderDetailed=Lists.newArrayList(subtotal,"order1","order2","order3");
        channelList.forEach(channel->{
            orderDetailed.forEach(title->{
                headTitles.add(Lists.newArrayList(channel, title, invoiceAmount, invoiceAmount));
                headTitles.add(Lists.newArrayList(channel, title, invoiceQuantity, invoiceQuantity));});});return headTitles;}/**
     * 导入数据封装，需要导出数据进行传参
     *
     * @return 导出数据集合
     */privatestaticList<List<Object>>contentData(){List<List<Object>> contentList=Lists.newArrayList();// 这里一个List<Object>代表一行数据，需要映射成每行数据填充，横向填充（把实体数据的字段设置成一个List<Object>）
        contentList.add(Lists.newArrayList("测试","测试A","测试B","100",999,999,666.66,"200","测试","测试","测试","测试","测试","测试","测试","测试","测试","测试","测试","测试","测试"));
        contentList.add(Lists.newArrayList("测试","测试A1","测试B1","2002",888,888,888.88,"测试","测试","测试","测试","测试","测试","测试","测试","测试","测试","测试","测试","测试","测试"));return contentList;}}

生成表格如下所示：

poi

EasyExcel

后记：

EasyExcel是阿里出的一套基于POI的快速、简单避免OOM的java处理Excel工具，避免OOM的主要原因是EasyExcel在写入的时候是逐行进行，而非全部进行。所以在导出效率上是不如POI的，数据量较小且对效率要求较高，建议使用POI进行导出，但数据量大、对效率要求不高的时候建议使用EasyExcel，简单易上手。 注：太初级的东西大家可以看B站狂神的课，地址如下：讲的还是比较好的，而且都是干货，废话比较少。 https://www.bilibili.com/video/BV1Ua4y1x7BK?spm_id_from=333.337.search-card.all.click