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

　　背景 业务中,客户需要导出各种数据统计报表,如果客户要求,我希望导出的报表中能插入这些数据的统计图表该如何操作,如图:
　　传统POI     org.apache.poi     poi-ooxml     4.1.2 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(推荐)     e-iceblue     spire.xls.free     5.1.0 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了,   骚的一批,当然也封装了其他文档转换,这里不赘述