JAVA 使用EasyExcel动态导出以及导出多个Sheet

废话不多说，直接上代码

添加pom.xml依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

 具体导出代码

List<List<String>> heads = new ArrayList<>();		//表头信息

List<String> head1 = new ArrayList<>();
head1 .add("银行名称1");
head1 .add("期间新增项目");
head1 .add("期间新增担保总额");
head1 .add("期间新增解保项目数");
head1 .add("期间新增解保金额");
heads.add(head1);

List<String> head2 = new ArrayList<>();
head2  .add("银行名称2");
head2 .add("期间新增项目");
head2 .add("期间新增担保总额");
head2 .add("期间新增解保项目数");
head2 .add("期间新增解保金额");
heads.add(head2);

List<Map<String, Object>> list = new ArrayList<>();   //内容信息
Map<String,Object> test1 = new LinkedHashMap<>();//手动添加测试数据（可根据需要从数据库查询）
test1 .put("t1", 1);
test1 .put("t2", 2);
test1 .put("t3", 3);
test1 .put("t4", 4);
test1 .put("t5", 5);
list.add(test1);

Map<String,Object> test2 = new LinkedHashMap<>();
test2 .put("t1", 1);
test2 .put("t2", 2);
test2 .put("t3", 3);
test2 .put("t4", 4);
test2 .put("t5", 5);
list.add(test2);

Collection<Object> values;
List<List<Object>> resultData = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
    values = list.get(i).values();
    for (Object value : values) {
        objects.add(value.toString());
    }
    resultData.add(objects);
}

// 导出excel
try {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmm");
    String date = sdf.format(new Date());
    String fileName = URLEncoder.encode("导出EXCEl_" + date + ".xlsx", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName);
    response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
    //添加表格样式
    ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
            .registerWriteHandler(new StyleHandler())
            .registerWriteHandler(new LongestCellWidthHandler())
            .registerWriteHandler(new LongestCellRowHandler())
            .build();
    //循环输出sheet和表头以及表格内容
    for (int i = 0; i < resultData.size(); i++) {
        WriteSheet sheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(heads.get(i)).build();
        excelWriter.write(resultData.get(i), sheet);
    }
    excelWriter.finish();
} catch (Exception e) {
    log.error("导出失败" + e.getMessage(), e);
} finally {
    try {
        response.getOutputStream().close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

注：response 是从请求接口带过来 HttpServletResponse response

表格样式类：

StyleHandler类（自定义样式）

public class StyleHandler extends AbstractVerticalCellStyleStrategy {

    private static String fontName = "等线";
    private static Integer[] columnIndex = {1, 2, 3, 4, 7, 8, 9};

    @Override
    protected WriteCellStyle headCellStyle(Head head) {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为灰色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        headWriteFont.setFontName(fontName);
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);
        //自动换行
        headWriteCellStyle.setWrapped(false);
        // 水平对齐方式
        if (Arrays.asList(columnIndex).contains(head.getColumnIndex())) {
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        } else {
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        }
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headWriteFont.setBold(true);
        return headWriteCellStyle;
    }

    @Override
    protected WriteCellStyle contentCellStyle(Head head) {
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        if (Arrays.asList(columnIndex).contains(head.getColumnIndex())) {
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        } else {
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        }
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        contentWriteFont.setFontName(fontName);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setWrapped(true);
        return contentWriteCellStyle;
    }
}

LongestCellWidthHandler类（根据表头设置列宽）

public class LongestCellWidthHandler extends AbstractColumnWidthStyleStrategy {

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (Boolean.TRUE.equals(isHead)) {
            int columnWidth = cell.getStringCellValue().length();
            columnWidth = Math.max(columnWidth * 5, 25);
            if (columnWidth > 255) {
                columnWidth = 255;
            }
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }
}

LongestCellRowHandler类（高度设置）

public class LongestCellRowHandler extends AbstractRowHeightStyleStrategy {

    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 500;

    @Override
    protected void setHeadColumnHeight(Row row, int i) {

    }

    @Override
    protected void setContentColumnHeight(Row row, int i) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }
        int maxHeight = 3;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (Objects.requireNonNull(cell.getCellType() == CellType.STRING)) {
                if (cell.getStringCellValue().contains("\n")) {
                    int length = cell.getStringCellValue().split("\n").length;
                    maxHeight = Math.max(maxHeight, length);
                }
            }
        }
        row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
    }
}