首页

基于alibaba的easyexcel导出多sheet动态头部及多个列表复杂excel报表示例代码

标签:复杂excel     发布时间:2024-11-12   

一、示例说明

通过alibaba的easyexcel导出如下图效果  - 多sheet页、动态header表头、多列表等  (完整项目下载)

999_2024-11-12_23-47-29副本.jpg

二、代码说明

package com.louis.springboot.demo.controller;@b@@b@import com.alibaba.excel.ExcelWriter;@b@import com.louis.springboot.demo.controller.excel.ExportEnum;@b@import com.louis.springboot.demo.controller.excel.ExcelUtil;@b@import com.louis.springboot.demo.controller.excel.ExportDtoExcel;@b@import com.louis.springboot.demo.controller.excel.style.CommisionExportR0Style;@b@import com.louis.springboot.demo.controller.excel.style.CommisionExportRRStyle;@b@import org.springframework.web.bind.annotation.GetMapping;@b@import org.springframework.web.bind.annotation.RestController;@b@@b@import java.io.IOException;@b@import java.math.BigDecimal;@b@import java.util.*;@b@import java.util.concurrent.ConcurrentHashMap;@b@import java.util.concurrent.ConcurrentMap;@b@@b@@b@@RestController@b@public class CommisionExportController {@b@@b@    @GetMapping("/composeExport")@b@    public void composeExport() throws IOException {@b@@b@        ConcurrentMap<String,List<List<String>>> statisExportHeads = new ConcurrentHashMap<>();@b@        //注入汇总记录的表头字段信息@b@        statisExportHeads.put(ExportEnum.R0.getCode(),@b@                ExcelUtil.getComplexHeader(String.format("（%s）-（%s）","2000-04","XX百事可乐有限公司"),@b@                        "统计数据 标题 人员 处理事件 创建时间 创建人 修改时间 修改人"));@b@        statisExportHeads.put(ExportEnum.R0_01.getCode(),@b@                ExcelUtil.getComplexHeader("AAA",@b@                        "111 222 333 444 555 666 777 888 999 000 1122"));@b@        statisExportHeads.put(ExportEnum.R0_02.getCode(),@b@                ExcelUtil.getComplexHeader("BBB",@b@                        "1111 2222 3333 4444 5555 6666"));@b@        statisExportHeads.put(ExportEnum.R0_06.getCode(),@b@                ExcelUtil.getComplexHeader("CCC",@b@                        "11 22 33 44 55 66"));@b@        statisExportHeads.put(ExportEnum.R0_03.getCode(),@b@                ExcelUtil.getComplexHeader("DDD",@b@                        "100 200 300 400 500 600"));@b@        statisExportHeads.put(ExportEnum.R0_04.getCode(),@b@                ExcelUtil.getComplexHeader("EEE",@b@                        "101 201 301 401 501 601 701"));@b@        statisExportHeads.put(ExportEnum.R0_05.getCode(),@b@                ExcelUtil.getComplexHeader("GGG",@b@                        "121 212 313"));@b@@b@        //挡板数据@b@        List<List<String>> demoStaticExportDatas = new ArrayList<List<String>>(2){{@b@            add(new ArrayList<String>(){{@b@                add("测试狂");@b@                add("测试狂赌2222");@b@                add("测试狂赌3333");@b@                add("测试狂赌4444");@b@                add("测试狂赌5555");@b@                add("测试狂赌6666");@b@                add("测试狂赌7777");@b@                add("测试狂赌8888");@b@            }});@b@            add(new ArrayList<String>());@b@        }};@b@        ConcurrentMap<String,List<List<String>>> statisExportDatas = new ConcurrentHashMap<>();@b@        statisExportDatas.put(ExportEnum.R0.getCode(),demoStaticExportDatas);@b@        statisExportDatas.put(ExportEnum.R0_01.getCode(),demoStaticExportDatas);@b@        statisExportDatas.put(ExportEnum.R0_02.getCode(),demoStaticExportDatas);@b@        statisExportDatas.put(ExportEnum.R0_06.getCode(),demoStaticExportDatas);@b@        statisExportDatas.put(ExportEnum.R0_03.getCode(),demoStaticExportDatas);@b@        statisExportDatas.put(ExportEnum.R0_04.getCode(),demoStaticExportDatas);@b@        statisExportDatas.put(ExportEnum.R0_05.getCode(),demoStaticExportDatas);@b@@b@@b@        //数据@b@        List<ExportDtoExcel> excelList = new ArrayList<>(2);@b@        ExportDtoExcel exportDtoExcel =new ExportDtoExcel();@b@        exportDtoExcel.setCalcDays(1);@b@        exportDtoExcel.setTermNo(1);@b@        exportDtoExcel.setComName("广州市白云区石门xxxxxxxxxxxxxxxxxxxxx四哥汽车配件店");@b@        exportDtoExcel.setComDays("梁13457632538");@b@        exportDtoExcel.setDueDate(new Date());@b@        exportDtoExcel.setRate(new BigDecimal("123.0012"));@b@        exportDtoExcel.setReceivableRent(new BigDecimal("123.0012005"));@b@        exportDtoExcel.setReceivableInterest(new BigDecimal("9876543215.10"));@b@        excelList.add(exportDtoExcel);@b@@b@        ExcelWriter writer = ExcelUtil.createLocExport(String.format("D:/temp/202407/%s-%s.xlsx", "汇总统计-",UUID.randomUUID()));@b@        //汇总sheet@b@        ExcelUtil.writeComplexHeaderSheet(writer, ExportEnum.R0.getSheetName(),statisExportHeads,statisExportDatas,new CommisionExportR0Style());@b@        //sheet2@b@        ExcelUtil.writeSheet(writer, ExportEnum.R2.getSheetName(), excelList, ExportDtoExcel.class,new CommisionExportRRStyle());@b@        ExcelUtil.finishWrite(writer);@b@    }@b@@b@}
package com.louis.springboot.demo.controller.excel;@b@@b@import com.alibaba.excel.EasyExcel;@b@import com.alibaba.excel.ExcelWriter;@b@import com.alibaba.excel.write.builder.ExcelWriterBuilder;@b@import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;@b@import com.alibaba.excel.write.handler.WriteHandler;@b@import com.alibaba.excel.write.metadata.WriteSheet;@b@import com.alibaba.excel.write.metadata.WriteTable;@b@import com.louis.springboot.demo.controller.excel.style.DefaultExportAutoWidthStyle;@b@@b@import java.util.*;@b@@b@import javax.servlet.http.HttpServletResponse;@b@import java.io.File;@b@import java.io.IOException;@b@import java.io.OutputStream;@b@import java.io.UnsupportedEncodingException;@b@import java.net.URLEncoder;@b@@b@@b@public class ExcelUtil {@b@@b@    /**@b@     * 导出XLSX（只有一个sheet）@b@     *@b@     * @param response@b@     * @param fileName    导出的文件名，不需要加.xlsx@b@     * @param sheetName   工作表名@b@     * @param list<T>@b@     * @param entityClass@b@     */@b@    public static <T> void exportXlsx(HttpServletResponse response, String fileName, String sheetName, List<T> list, Class entityClass) {@b@        response.setContentType("application/vnd.ms-excel");@b@        response.setCharacterEncoding("utf-8");@b@        try {@b@            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");@b@        } catch (UnsupportedEncodingException e) {@b@            throw new RuntimeException("导出XLS时发生错误");@b@        }@b@        OutputStream outputStream = null;@b@        try {@b@            outputStream = response.getOutputStream();@b@        } catch (IOException e) {@b@            throw new RuntimeException("导出XLS时发生错误");@b@        }@b@        ExcelWriterBuilder write = EasyExcel.write(outputStream, entityClass);@b@        ExcelWriterSheetBuilder sheet = write.sheet(sheetName);@b@        sheet.doWrite(list);@b@    }@b@@b@    /**@b@     * 创建xlsx导出开始（适用于多个sheet）@b@     * 使用案例：@b@     * ExcelWriter writer = ExcelUtil.createExport(response, "网签合同列表");@b@     * ExcelUtil.writeSheet(writer, "合同列表", list, ContractPageDto.class);@b@     * ExcelUtil.writeSheet(writer, "流水列表", listBills, BusinessBillsPageDto.class);@b@     * ExcelUtil.finishWrite(writer);@b@     *@b@     * @param response@b@     * @param fileName 导出的文件名，不需要加.xlsx@b@     */@b@    public static <T> ExcelWriter createExport(HttpServletResponse response, String fileName) {@b@        response.setContentType("application/vnd.ms-excel");@b@        response.setCharacterEncoding("utf-8");@b@        try {@b@            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");@b@        } catch (UnsupportedEncodingException e) {@b@            throw new RuntimeException("导出XLS时发送错误");@b@        }@b@        OutputStream outputStream = null;@b@        try {@b@            outputStream = response.getOutputStream();@b@        } catch (IOException e) {@b@            throw new RuntimeException("导出XLS发送错误");@b@        }@b@        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();@b@        return excelWriter;@b@    }@b@@b@@b@    public static <T> ExcelWriter createLocExport(String filePath) {@b@        File exFile= new File(filePath);@b@        if(!exFile.getParentFile().exists()){@b@            exFile.getParentFile().mkdirs();@b@        }@b@        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(filePath);@b@@b@        return excelWriterBuilder.build();@b@    }@b@@b@@b@    /**@b@     * 写入简单sheet内容@b@     *@b@     * @param sheetName   工作表名@b@     * @param list<T>@b@     * @param entityClass@b@     */@b@    public static <T> void writeSheet(ExcelWriter excelWriter, String sheetName, List<T> list, Class entityClass) {@b@        writeSheet(excelWriter,sheetName,list,entityClass,null);@b@    }@b@@b@    /**@b@     * 写入简单sheet内容@b@     *@b@     * @param sheetName   工作表名@b@     * @param list<T>@b@     * @param entityClass@b@     */@b@    public static <T> void writeSheet(ExcelWriter excelWriter, String sheetName, List<T> list, Class entityClass, WriteHandler cssHandleStyle) {@b@        WriteSheet sheet = null;@b@        if(Objects.nonNull(cssHandleStyle)){@b@            sheet = EasyExcel.writerSheet(sheetName).head(entityClass).needHead(Boolean.TRUE).registerWriteHandler(cssHandleStyle).build();@b@        }else {@b@            sheet = EasyExcel.writerSheet(sheetName).head(entityClass).needHead(Boolean.TRUE).build();@b@        }@b@        excelWriter.write(list, sheet);@b@    }@b@@b@@b@    public static <T> void writeSheetWithDynamicHeader(ExcelWriter excelWriter, String sheetName, List<T> list,List<List<String>> dynamicHeader) {@b@        WriteSheet sheet = EasyExcel.writerSheet(sheetName).head(dynamicHeader).build();@b@        excelWriter.write(list, sheet);@b@    }@b@@b@    public static <T> void writeSheetWithMutiHeader(ExcelWriter excelWriter, String sheetName, List<T> list,List<List<String>> dynamicHeader) {@b@        WriteSheet sheet = EasyExcel.writerSheet(sheetName).head(dynamicHeader).needHead(Boolean.FALSE).build();@b@        // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要@b@        WriteTable writeTable0 = EasyExcel.writerTable(0).needHead(Boolean.TRUE).build();@b@        WriteTable writeTable1 = EasyExcel.writerTable(1).needHead(Boolean.TRUE).build();@b@        excelWriter.write(list, sheet,writeTable0);@b@        excelWriter.write(list, sheet,writeTable1);@b@    }@b@@b@@b@    public static <T> void writeSheetWithMutiHeader(ExcelWriter excelWriter, String sheetName,Map<Integer,List<List<String>>> headTableMap,Map<Integer,List<T>> dataTableMap) {@b@        WriteSheet sheet = EasyExcel.writerSheet(sheetName).needHead(Boolean.FALSE).build();@b@        // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要@b@        for(int i=0;i<dataTableMap.keySet().size();i++){@b@            WriteTable writeIndexTable= EasyExcel.writerTable(i).head(headTableMap.get(i)).needHead(Boolean.TRUE).build();@b@@b@            excelWriter.write(dataTableMap.get(i),sheet,writeIndexTable);@b@@b@        }@b@    }@b@@b@    /**@b@     * 写入复杂表头sheet内容@b@     * @param excelWriter@b@     * @param sheetName@b@     * @param headTableMap@b@     * @param dataTableMap@b@     * @param <T>@b@     */@b@    public static <T> void writeComplexHeaderSheet(ExcelWriter excelWriter, String sheetName,Map<String,List<List<String>>> headTableMap,Map<String,List<List<String>>> dataTableMap) {@b@        writeComplexHeaderSheet(excelWriter,sheetName,headTableMap,dataTableMap,null);@b@    }@b@@b@    /**@b@     * 写入复杂表头sheet内容@b@     * @param excelWriter@b@     * @param sheetName@b@     * @param headTableMap@b@     * @param dataTableMap@b@     * @param cssHandleStyle@b@     * @param <T>@b@     */@b@    public static <T> void writeComplexHeaderSheet(ExcelWriter excelWriter, String sheetName, Map<String,List<List<String>>> headTableMap, Map<String,List<List<String>>> dataTableMap, WriteHandler cssHandleStyle) {@b@        WriteSheet sheet = null;@b@        if(Objects.nonNull(cssHandleStyle)){@b@            sheet = EasyExcel.writerSheet(sheetName).registerWriteHandler(cssHandleStyle).registerWriteHandler(new DefaultExportAutoWidthStyle()).needHead(Boolean.FALSE).build();@b@        }else {@b@            sheet = EasyExcel.writerSheet(sheetName).needHead(Boolean.FALSE).build();@b@        }@b@        // 这里必须指定需要头，table 会继承sheet的配置，sheet配置了不需要，table 默认也是不需要@b@        int i=0;@b@        for(String rdKey:dataTableMap.keySet()){@b@            WriteTable writeIndexTable= EasyExcel.writerTable(i).head(headTableMap.get(rdKey)).needHead(Boolean.TRUE).build();@b@            excelWriter.write(dataTableMap.get(rdKey),sheet,writeIndexTable);@b@            i++;@b@        }@b@    }@b@@b@@b@@b@    /**@b@     * 获取表单头部配置@b@     * @param tabClassName@b@     * @param tabColumnContext - 需用空格标识@b@     * @return@b@     */@b@    public  static  List<List<String>>  getComplexHeader(String tabClassName,String tabColumnContext){@b@        List<List<String>> list = new ArrayList<>();@b@        Arrays.stream(tabColumnContext.split(" ")).forEach(columnName -> {@b@            List<String> head = new ArrayList<>();@b@            head.add(tabClassName);@b@            head.add(columnName);@b@            list.add(head);@b@        });@b@        return list;@b@    }@b@@b@    /**@b@     * 结束写入sheet并导出@b@     */@b@    public static <T> void finishWrite(ExcelWriter excelWriter) {@b@        excelWriter.finish();@b@    }@b@@b@@b@}
>上一篇: 如何进行用户体验设计User Experience(简称UE）? >下一篇: 分享Apache Portals工程源代码代码规范
  • <<相关内容>>
<<热门下载>>