【干货:Excel中插入图片的两种方式】

心已赠人 2023-10-04 22:39 66阅读 0赞

![format_png][]

## 背景 ##

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

![format_png 1][]

## 传统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;
        }
    }
    复制代码

![format_png 2][]

## 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);
        }
    
    }
    复制代码

![format_png 3][]

## 方案对比 ##

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

![format_png 4][]

[format_png]: https://img-blog.csdnimg.cn/img_convert/554ab67f386eeee3761f00d87b92e8a3.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/ec7dacb1a9be4d275cbb3f6c8a052dab.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/ac715d37fa41136d6fdaa19cdb0299f8.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/61d2470ed1795b99e85ac7eafa75e987.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/eea18977b6075a258c333297b8d3580a.webp?x-oss-process=image/format,png