使用EasyExcel生成并导出excel（动态表头+复杂表头）

最新推荐文章于 2025-02-25 17:03:38 发布

城北徐先生

最新推荐文章于 2025-02-25 17:03:38 发布

阅读量1w

点赞数 4

文章标签： excel java

本文链接：https://blog.youkuaiyun.com/qq_42732123/article/details/118710597

版权

本文介绍了如何在Java项目中利用EasyExcel库生成动态表头和复杂表头的Excel文件。首先在POM文件中引入EasyExcel的依赖，接着在service实现类中讲解了实现动态头和复杂头写入的方法，最后展示了导出后的Excel样式，结合动态生成头和复杂表头，提供了一种高效的Excel导出方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、POM文件中引入EasyExcel的依赖

<!--easyExcel工具-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

二、使用EasyExcel复杂头写入和动态头，实时生成头写入的方法

service实现类中业务处理方法

@Override
    public void reportExcelOut(String startDate, String endDate, HttpServletRequest request, HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=HZB.xls");
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            // 这里需要设置不关闭流
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置背景颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            //内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            EasyExcel.write(response.getOutputStream()).autoCloseStream(Boolean.FALSE)
                    .registerWriteHandler(new CustomizeColumnWidth()).registerWriteHandler(horizontalCellStyleStrategy)
                    .head(myriadPeopleHead(startDate,endDate)).sheet("模板")
                        //获取数据填充
                    .doWrite(getMyriadPeopleReportData(startDate, endDate));
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            try {
                response.getWriter().println(JSON.toJSONString(map));
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
    }

    /**
     * 自定义头部的 列的宽度设置 策略. .
     */
    class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {
        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
            // 测试为 COLUMN 宽度定制.
            if (isHead && cell.getRowIndex() == 1) {
                int columnWidth = cell.getStringCellValue().getBytes().length;
                int cellIndex = cell.getColumnIndex();
                switch (cellIndex) {
                    case 0:
                        columnWidth = 80;
                        break;
                    case 1:
                        columnWidth = 20;
                        break;
                    case 2:
                        columnWidth = 20;
                        break;
                    case 3:
                        columnWidth = 20;
                        break;
                    case 4:
                        columnWidth = 20;
                        break;
                    case 5:
                        columnWidth = 20;
                        break;
                    default:
                        break;
                }

                if (columnWidth > 255) {
                    columnWidth = 255;
                }
                writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
            }
        }
    }

    /**
     * 自定义表头
     * @param startDate
     * @param endDate
     * @return
     */
    private List<List<String>> myriadPeopleHead(String startDate, String endDate) {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("统计时间: " + startDate + " 至 " + endDate + " 统计报表 ");
        head0.add("省市区");
        List<String> head1 = new ArrayList<String>();
        head1.add("统计时间: " + startDate + " 至 " + endDate + " 统计报表 ");
        head1.add("批次");
        List<String> head2 = new ArrayList<String>();
        head2.add("统计时间: " + startDate + " 至 " + endDate + " 统计报表 ");
        head2.add("人数");
        List<String> head3 = new ArrayList<String>();
        head3.add("统计时间: " + startDate + " 至 " + endDate + " 统计报表 ");
        head3.add("总人数");
        List<String> head4 = new ArrayList<String>();
        head4.add("统计时间: " + startDate + " 至 " + endDate + " 统计报表 ");
        head4.add("总量");
        List<String> head5 = new ArrayList<String>();
        head5.add("统计时间: " + startDate + " 至 " + endDate + " 统计报表 ");
        head5.add("排序");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        list.add(head3);
        list.add(head4);
        list.add(head5);
        return list;
    }