使用easyExcel导出

参考博文

https://www.yuque.com/easyexcel/doc/fill

https://blog.csdn.net/qq_41514643/article/details/106993760

需要导入依赖的 上面两篇文章都有提交过

直接上才艺

1、常规导出（不合并表头的）

   public void exportExcel(HttpServletRequest request, HttpServletResponse response, Map<String, Object> param) {
        List<Map<String, Object>> list = 需要导出的数据
        String fileName = param.get("fileName").toString();
        //需要将查询出来的数据重新进行转换的集合
        List<List<Object>> dataList = Lists.newArrayList();
        //装配表头名称
        List<String> headList = Lists.newArrayList("表头名称1", "表头名称2",...);
        for (int i = 0; i < list.size(); i++) {
            Map m = list.get(i);
            List<Object> data = Lists.newArrayList(map.get("xxx"),map.get("xxx),...);
            dataList.add(data);
        }
        ExportExcel.exportExcel(request, response, fileName, headList, dataList);
    }

被调用的工具类

public class ExportExcel {
    /**
     * 导出excel
     * @param request  请求
     * @param response 响应
     * @param fileName excel文件名
     * @param headList 列头
     * @param dataList 数据
     */
    public static void exportExcel(HttpServletRequest request, HttpServletResponse response, String fileName, List<String> headList, List<List<Object>> dataList) {
        ServletOutputStream sos = null;
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + FileUtils.encodeFileName(fileName, request));
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            sos = response.getOutputStream();
            ExcelUtil.exportExcel(sos, headList, dataList);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sos != null) {
                try {
                    sos.flush();
                    sos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

2、合并表头（使用list装配表头，目前只能合并二级表头）  使用list合并多级的欢迎指教

   public void exportExcel(HttpServletRequest request, HttpServletResponse response, Map<String, Object> param) {
        List<Map<String, Object>> list = 需要导出的数据
        String fileName = param.get("fileName").toString();
        //需要将查询出来的数据重新进行转换的集合
        List<List<Object>> dataList = Lists.newArrayList();
        for (int i = 0; i < list.size(); i++) {
            Map m = list.get(i);
            List<Object> data = Lists.newArrayList(map.get("xxx"),map.get("xxx),...);
            dataList.add(data);
        }
        ExportExcel.exportExcelHbbt(request, response, fileName, Tztjhead(), dataList);
    }


    /**
     * 装配表头
     */
    private List<List<String>> Tztjhead() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> headArea = new ArrayList<String>();
        headArea.add("废物类别");
        List<String> headQys = new ArrayList<String>();
        headQys.add("废物名称");
        list.add(headArea);
        list.add(headQys);
        //装配二级子表头
        Map<String, List<String>> map = new LinkedHashMap<>();
        map.put("库存量（吨）", Lists.newArrayList("上月底", "下月底"));
        map.put("产生量（吨）", Lists.newArrayList("当月", "当年累计"));
        map.put("利用处置总量（吨）", Lists.newArrayList("当月", "当年累计"));
        map.put("自行利用处置量（吨）", Lists.newArrayList("当月", "当年累计"));
        map.put("委外利用处置量（吨）", Lists.newArrayList("当月", "当年累计"));
        map.forEach((k, v) -> {
            v.forEach(e -> {
                List<String> head = new ArrayList<>();
                head.add(k);
                head.add(e);
                list.add(head);
            });
        });
        List<String> headn = new ArrayList<String>();
        headn.add("月底库存量（吨）");
        list.add(headn);
        return list;
    }

被调用的工具类（工具类中有用到  CustomCellWriteHandler，BtSheetWriteHandler两个自定义的类，文末会给出）

public class ExportExcel {
    /**
     * 导出excel 使用List集合合并表头 表头格式List<List<String>>
     * @param request  请求
     * @param response 响应
     * @param fileName excel文件名
     * @param headList 列头
     * @param dataList 数据
     */
    public static void exportExcelHbbt(HttpServletRequest request, HttpServletResponse response, String fileName, List<List<String>> headList, List<List<Object>> dataList) {
        ServletOutputStream sos = null;
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + FileUtils.encodeFileName(fileName, request));
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            sos = response.getOutputStream();
            //LongestMatchColumnWidthStyleStrategy  自动计算列宽（效果不怎么好）
            //CustomCellWriteHandler  自动计算表头列宽（这个感觉比 LongestMatchColumnWidthStyleStrategy 效果好点）
            ExcelWriterBuilder write = EasyExcel.write(sos).head(headList).registerWriteHandler(new CustomCellWriteHandler());
            //设置标题
            write.registerWriteHandler(new BtSheetWriteHandler(fileName, 15)).useDefaultStyle(true).relativeHeadRowIndex(1);
            sos.flush();
            write.head(headList).sheet(fileName).doWrite(dataList);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sos != null) {
                try {
                    sos.flush();
                    sos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

效果图

 

3、使用实体导出  由于想合并3级表头 使用list没研究出来，就使用数量实体+注解的方法

实体：

@ExcelProperty=====导出时的表头名称

@ColumnWidth(30)====表头需要的宽度

@ExcelIgnore========排除该属性（如果不使用该注解，会将属性名作为表头）

更过注解可以参考

https://tech.souyunku.com/?p=26513

public class TChartsWfJyqkybExcel {

    @ColumnWidth(30)
    @ExcelProperty("经营单位")
    private String dwmc;

    //实际经营规模（吨）-接收-当月
    @ExcelProperty({"实际经营规模（吨）","接收","当月"})
    private BigDecimal jygmJsDy;

    //实际经营规模（吨）-接收-当年累计
    @ExcelProperty({"实际经营规模（吨）","接收","当年累计"})
    private BigDecimal jygmJsDn;

    //实际经营规模（吨）-利用处置-当月
    @ExcelProperty({"实际经营规模（吨）","利用处置","当月"})
    private BigDecimal jygmLyczDy;

    //实际经营规模（吨）-利用处置-当年累计
    @ExcelProperty({"实际经营规模（吨）","利用处置","当年累计"})
    private BigDecimal jygmLyczDn;

    //实际经营规模（吨）-二次转移-当月
    @ExcelProperty({"实际经营规模（吨）","二次转移","当月"})
    private BigDecimal jygmEcDy;

    //实际经营规模（吨）-二次转移-当年
    @ExcelProperty({"实际经营规模（吨）","二次转移","当年"})
    private BigDecimal jygmEcDn;

    //库存（吨）
    @ExcelProperty({"实际经营规模（吨）", "库存（吨）"})
    private BigDecimal kcl;

    //次生危废情况（吨）-产生-当月
    @ExcelProperty({"次生危废情况（吨）","产生","当月"})
    private BigDecimal csfwCsDy;

    //次生危废情况（吨）-产生-当年累计
    @ExcelProperty({"次生危废情况（吨）","产生","当年累计"})
    private BigDecimal csfwCsDn;

    //次生危废情况（吨）-利用处置-当月
    @ExcelProperty({"次生危废情况（吨）","利用处置","当月"})
    private BigDecimal csdwLyczDy;

    //次生危废情况（吨）-利用处置-当年累计
    @ExcelProperty({"次生危废情况（吨）","利用处置","当年累计"})
    private BigDecimal csdwLyczDn;

    @ExcelIgnore
    private String test;

}

service

    public void exportExcel(HttpServletRequest request, HttpServletResponse response, Map<String, Object> param) {
        List<Map<String, Object>> list = jdbcQueryManager.queryForMap("getWfjyqkybtjList", param);
        String fileName = param.get("fileName").toString();
        List<List<Object>> dataList = Lists.newArrayList();
        for (int i = 0; i < list.size(); i++) {
            Map m = list.get(i);
            List<Object> data = Lists.newArrayList(m.get("xxx"), m.get("xxx"),...);
            dataList.add(data);
        }
        ExportExcel.exportExcelEntity(request, response, fileName, TChartsWfJyqkybExcel.class, dataList);
    }

被调用的工具类

public class ExportExcel
    /**
     * 导出excel 根据实体注解导出
     * @param request  请求
     * @param response 响应
     * @param fileName excel文件名
     * @param head     列头（实体类使用了@ExcelProperty注解）
     * @param dataList 数据
     */
    public static void exportExcelEntity(HttpServletRequest request, HttpServletResponse response, String fileName, Class head, List<List<Object>> dataList) {
        ServletOutputStream sos = null;
        try {
            response.setHeader("Content-Disposition", "attachment;filename=" + FileUtils.encodeFileName(fileName, request));
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            sos = response.getOutputStream();
            //LongestMatchColumnWidthStyleStrategy  自动计算列宽（效果不怎么好）
            //CustomCellWriteHandler  自动计算表头列宽（这个感觉比 LongestMatchColumnWidthStyleStrategy 效果好点）
            ExcelWriterBuilder write = EasyExcel.write(sos, head).registerWriteHandler(new CustomCellWriteHandler());
            //设置标题
            write.registerWriteHandler(new BtSheetWriteHandler(fileName, 15)).useDefaultStyle(true).relativeHeadRowIndex(1);
            sos.flush();
            write.sheet(fileName).doWrite(dataList);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (sos != null) {
                try {
                    sos.flush();
                    sos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

效果图

 

上文提到的两个自定义类

自适应宽度（CustomCellWriteHandler）

public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead && cell.getRowIndex() != 0) {
            int columnWidth = cell.getStringCellValue().getBytes().length;
            if (columnWidth > MAX_COLUMN_WIDTH) {
                columnWidth = MAX_COLUMN_WIDTH;
            } else {
                columnWidth = columnWidth + 3;
            }
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }

}

设置标题（BtSheetWriteHandler）

public class BtSheetWriteHandler implements SheetWriteHandler {

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * 标题
     */
    private static String title;
    /**
     * 需要合并到第几列
     */
    private static int lastCol;

    public BtSheetWriteHandler(String title, int lastCol) {
        BtSheetWriteHandler.setTitle(title);
        BtSheetWriteHandler.setLastCol(lastCol);
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        //设置标题
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 800);
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue(BtSheetWriteHandler.getTitle());
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short) 400);
        cellStyle.setFont(font);
        cell1.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, BtSheetWriteHandler.getLastCol()));
    }

    public static String getTitle() {
        return title;
    }

    public static void setTitle(String title) {
        BtSheetWriteHandler.title = title;
    }

    public static int getLastCol() {
        return lastCol;
    }

    public static void setLastCol(int lastCol) {
        BtSheetWriteHandler.lastCol = lastCol;
    }

}