【工具集】【后端】【EasyExcel】动态Excel模版生成Excel

1.需求描述

• 将数据动态保存到Excel中，其中数据包含文本文字、图片base64的格式
• Excel中支持sheet之间的跳转、字体颜色的控制
• sheet数量不固定，需要动态创建sheet模板

2.实现思路

• 创建Excel模板
• 组织数据
• Excel模板的制作
• 向制作出的Excel模板，填充数据

注意：excel生成的sheet，有数量上限，这个需要根据业务需求权衡控制

3.具体实现

实现框架以Spring Boot为基础建设的。

3.1.Excel模板

3.2.添加Maven依赖

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-json</artifactId>
    <version>5.5.7</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.83</version>
</dependency>
<dependency>
  <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>

3.3.Java 引用

import cn.hutool.core.io.resource.Resource;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.HyperlinkData;
import com.alibaba.excel.metadata.data.RichTextStringData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.metadata.style.WriteFont;

3.4.Java 实现

private static final String ReportPath = "C:\\XXX\\XXX\\xxx"; // excel模版文件根路径
private static final Resource templateFileObj = ResourceUtil.getResourceObj(ReportPath + "\\excelTemplate\\template.xlsx"); // excel模版文件名称

@Override
public String makeReport(ReportRespMsg data) throws IOException {

    if (data == null) {
        return null;
    }

    String rootPath = templateFileObj.getUrl().getPath();
    rootPath = rootPath.substring(0, rootPath.lastIndexOf("excelTemplate/template.xlsx"));

    System.out.println(templateFileObj.getUrl().getPath());

    String excelName = "report_" + System.currentTimeMillis();
    String fileName = rootPath + "excelWrite/"+ excelName + ".xlsx";
    String excelWriteDirPath = rootPath + "excelWrite";
    System.out.println(fileName);

    try {
        File excelWriteFileDir = new File(excelWriteDirPath);
        if (!excelWriteFileDir.exists()) {
            excelWriteFileDir.mkdirs();
        }
        writeExcel(rootPath, fileName, data);
        return excelName;
    } catch (Exception e) {
        log.error(e.getMessage());
    }
    return null;
}

/**
 * 制作Excel模版并写入数据.
 *
 * @param rootPath 文件根路径
 * @param fileName 生成的文件名称
 * @param data 填充到excel中的数据
 * @throws IOException
 */
private void writeExcel(String rootPath, String fileName, ReportRespMsg data) throws IOException {
    // 模版制作
    XSSFWorkbook workbook = new XSSFWorkbook(templateFileObj.getStream());
    if (data != null && data.getDetail() != null && data.getDetail().size() > 0) {
        for (int i = 1; i < data.getErrorDetail().size() && data.getDetail().size() <= data.getMaxSize(); i++) {
            int index = i+1;
            String sheetName = "sheet" + index;
            workbook.cloneSheet(1, sheetName);
        }
    } else {
        // 删除默认模版Sheet
        workbook.removeSheetAt(1);
    }
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    workbook.write(bos);
    InputStream is = new ByteArrayInputStream(bos.toByteArray());
    // 模版制作-结束

    // 写入数据
    ExcelWriter excelWriter = EasyExcel.write(fileName)
            .withTemplate(is)
            .build();

    WriteSheet writeSheet = EasyExcel.writerSheet(0,"Sheet0").build();

    excelWriter.fill(new FillWrapper("a", data.getA()), writeSheet);
    excelWriter.fill(new FillWrapper("b", data.getB()), writeSheet);
    excelWriter.fill(new FillWrapper("c", excelData(data.getDetail())), writeSheet);

    Map<String, Object> map = new HashMap<>(5);

    map.put("t1", data.getT1());
    map.put("t2", "");

    if (data.getT3s() != null) {
        map.put("t3s", data.getT3s().stream().collect(Collectors.joining(", ")));
    }

    excelWriter.fill(map, writeSheet);

    WriteFont CUSTOM_FONT = new WriteFont();
    CUSTOM_FONT.setFontName("微软雅黑");
    CUSTOM_FONT.setColor(IndexedColors.RED.getIndex());
    CUSTOM_FONT.setFontHeightInPoints((short) 12);

    // 写入数据
    for (int i = 0; i < data.getDetail().size() && i < data.getMaxSize(); i++) {
        int index = i+1;
        String sheetName = "sheet" + index;
        WriteSheet childSheet = EasyExcel.writerSheet(index, sheetName)
                .registerWriteHandler(new CellWriteHandler() {
                    @Override
                    public void afterCellDispose(CellWriteHandlerContext context) {
                        if (BooleanUtils.isNotTrue(context.getHead())) {
                            context.getFirstCellData().getOrCreateStyle().setWriteFont(CUSTOM_FONT);
                        }
                    }
                })
                .build();

        ExcelData item = data.getDetail().get(i);

        excelWriter.fill(new FillWrapper("d1", item.getContentTable().getD1()), childSheet);

        Map<String, Object> map1 = new HashMap<>(6);

        map1.put("id", item.getId());
        map1.put("m1", item.getContentTable().getM1());
        map1.put("m2", item.getContentTable().getM2());

        excelWriter.fill(map1, childSheet);
    }
    // 写入数据-结束

    excelWriter.finish();

    writeImg(fileName, data.getEcharts());
}

/**
 * 向excel写入图片（定制）.
 *
 * @param filePath 生成的文件名称
 * @param data 填充到excel中的数据
 * @throws IOException
 */
private void writeImg(String filePath, Map<String, String> data) throws IOException {

    cn.hutool.poi.excel.ExcelWriter writer = ExcelUtil.getWriter(filePath);
    Sheet sheet = writer.getSheets().get(0);
    Drawing drawingPatriarch = sheet.createDrawingPatriarch();

    BASE64Decoder decoder = new BASE64Decoder();
    Iterator<Map.Entry<String, String>> itemIterator = data.entrySet().iterator();

    // 默认第0个图 （图片定位，也可以用填充形式fill+变量）
    int dx1 = 0;
    int dy1 = 0;
    int dx2 = 0;
    int dy2 = 0;

    int[][] img = new int[][]{
            {7, 0, 13, 3},
            {15, 0, 24, 10},
            {27, 0, 36, 10}
    };

    int num = 0;
    while (itemIterator.hasNext()) {
        Map.Entry<String, String> entry = itemIterator.next();

        byte[] b = decoder.decodeBuffer(entry.getValue().replace("data:image/png;base64,", ""));
        for (int i = 0; i < b.length; ++i) {
            if (b[i] < 0) {
                b[i] += 256;
            }
        }

        // 设置图片单元格位置
        ClientAnchor anchor = drawingPatriarch.createAnchor(dx1, dy1, dx2, dy2, img[num][1], img[num][0], img[num][3], img[num][2]);

        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

        // 添加图片
        int pictureIndex = sheet.getWorkbook().addPicture(b, HSSFWorkbook.PICTURE_TYPE_PNG);
        drawingPatriarch.createPicture(anchor, pictureIndex);

        ++num;
    }

    writer.flush().close();
}

/**
 * excel中列表的样式（定制）.
 * @param detail 详细信息
 * @return 详细excel信息
 */
private List<ExcelData> excelData(List<ExcelData> detail) {
    if (detail!= null && !detail.isEmpty()) {
        AtomicInteger index = new AtomicInteger(1);
        detail.forEach(item -> {
            WriteCellData<String> hyperlink = new WriteCellData<>();
            HyperlinkData hyperlinkData = new HyperlinkData();
            String sheetName = "#'sheet" + index + "'!A1";
            hyperlinkData.setAddress(sheetName);
            hyperlinkData.setHyperlinkType(HyperlinkData.HyperlinkType.DOCUMENT);
            hyperlink.setHyperlinkData(hyperlinkData);
            hyperlink.setType(CellDataTypeEnum.RICH_TEXT_STRING);
            RichTextStringData richTextStringData = new RichTextStringData();
            richTextStringData.setTextString("查看详细");
            WriteFont writeFont = new WriteFont();
            writeFont.setColor(IndexedColors.BLUE.getIndex());
            richTextStringData.applyFont(writeFont);
            hyperlink.setRichTextStringDataValue(richTextStringData);
            item.setDetail(hyperlink);
            index.incrementAndGet();
        });
    }

    return detail;
}

附录

参考

• EasyExcel 文档
• EasyExcel github