背景

 业务中,客户需要导出各种数据统计报表,如果客户要求,我希望导出的报表中能插入这些数据的统计图表该如何操作,如图:

传统POI

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

package com.test.other.demo.report;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.platform.commons.util.StringUtils;

import java.io.*;

/**
 * POI 往excel插入图片
 */
public class POIExport {

    private Workbook workbook;

    public static void main(String[] args) throws Exception {
        Workbook workbook = getWorkBook("/Users/hui.yang/Desktop/excel演示/poi/演示1.xlsx");
        Sheet sheet = workbook.getSheet("sheet1");
        InputStream inputStream = new FileInputStream("/Users/hui.yang/Desktop/excel演示/poi/演示图表1.png");
        byte[] bytes = IOUtils.toByteArray(inputStream);
        // 这里根据实际需求选择图片类型
        int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
        CreationHelper helper = workbook.getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();
        Drawing drawing = sheet.createDrawingPatriarch();
        anchor.setRow1(10); //插入行
        anchor.setCol1(0); // 插入列
        // 插入图片
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        // 调整图片占单元格百分比的大小，1.0就是100%
        pict.resize(8, 5);
        //临时缓冲区
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        //创建临时文件
        try {
            workbook.write(byteArrayOut);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            workbook.close();
        }
        byte[] bookByteAry = byteArrayOut.toByteArray();
        File file = new File("/Users/hui.yang/Desktop/excel演示/poi/演示插入结果.xlsx");
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        if (!file.exists()) {
            file.createNewFile();
        }
        FileOutputStream fos = new FileOutputStream(file);
        fos.write(bookByteAry, 0, bookByteAry.length);
        fos.flush();
        fos.close();
    }

    /**
     * 获取workBoot 兼容xlsx xls
     */
    public static Workbook getWorkBook(String filePath) throws Exception {
        Workbook workbook = null;
        if (StringUtils.isBlank(filePath)) {
            // throw new RuntimeException("路径错误!");
        } else if (filePath.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(new FileInputStream(filePath));
        } else if (filePath.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(new FileInputStream(filePath));
        } else {
            //   throw new RuntimeException("路径错误!");
        }
        return workbook;
    }
}

e-iceblue(推荐)

<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.xls.free</artifactId>
    <version>5.1.0</version>
</dependency>

package com.test.other.demo.report;


import com.spire.xls.*;

/**
 * e-iceblue 往excel插入图片
 */
public class EiceBlueExport {

    public static void main(String[] args) {

        //创建Workbook实例
        Workbook workbook = new Workbook();
        //加载Excel文档
        workbook.loadFromFile("/Users/hui.yang/Desktop/excel演示/e-iceblue/演示1.xlsx");
        //获取第一张工作表
        Worksheet sheet = workbook.getWorksheets().get(0);
        //设置图表插入的位置
        ExcelPicture pic = sheet.getPictures().add(10, 1, "/Users/hui.yang/Desktop/excel演示/e-iceblue/演示图表1.png");
        //设置图片的宽度和高度
        pic.setWidth(600);
        pic.setHeight(120);
        //保存文档
        workbook.saveToFile("/Users/hui.yang/Desktop/excel演示/e-iceblue/演示插入结果.xlsx", ExcelVersion.Version2013);
        //文档转pdf
        transFileToPdf("/Users/hui.yang/Desktop/excel演示/e-iceblue/庞源在线-安全-安全周报-20230303130801.xlsx");
    }

    /**
     * 转pdf
     */
    public static void transFileToPdf(String fillPath) {
        Workbook wb = new Workbook();
        wb.loadFromFile(fillPath);
        wb.getWorksheets().get(0);
        ConverterSetting converterSetting = new ConverterSetting();
        converterSetting.setSheetFitToPage(true);
        wb.setConverterSetting(converterSetting);
        //调用方法保存为PDF格式
        wb.saveToFile("/Users/hui.yang/Desktop/excel演示/e-iceblue/庞源在线-安全-安全周报-20230303130801.pdf", FileFormat.PDF);
    }

}

方案对比

1 poi对xlxs xls不同版本有不同的语法,需要单独写个方法去获取WorkBook,而e-iceblue直接封装好方法,傻瓜式操作
2 poi需设置图片类型,文件流操作,而e-iceblue直接封装好方法,傻瓜式操作
3 e-iceblue有丰富的文档转换工具,如转为PDF,如以上代码,我这边转换的pdf,看了下再也不用在***网站转pdf了,
  骚的一批,当然也封装了其他文档转换,这里不赘述