Excel单元格插入图片并实现单击放大或缩小功能
参考/MRZHUGH/article/details/108413504
VBA代码
ThisWorkbook的代码
Private Sub Workbook_Open()Dim cName$On Error Resume NextFor Each a In Sheet1.ShapesIf a.Type = 1 Or a.Type = 13 Thena.OnAction = "test"cName = a.TopLeftCell.Address(0,0)Doa.Name = cNameIf Err = 0 Then Exit DocName = cName & "_0"Err.ClearLoopEnd IfNextEnd Sub
新建模块的代码
Sub test()On Error Resume NextFor Each a In Sheet1.ShapesIf a.Type = 1 Or a.Type = 13 ThenIf a.Name = Application.Caller And a.AlternativeText = Empty Thena.AlternativeText = a.Height & Chr(28) & a.Widtha.Height = a.Width * 3a.Width = a.Width * 3a.ZOrder msoBringToFrontElsea.Height = Split(a.AlternativeText, Chr(28))(0)a.Width = Split(a.AlternativeText, Chr(28))(1)a.AlternativeText = EmptyEnd IfErr.ClearEnd IfNextEnd Sub
插入图片测试
保存为xlsm文件，保存后重新打开才能生效
图片失真设置
excel文件–>选项–>高级–>选中“不压缩文件中的图像”，并设置分辨率为“高保真”
使用POI插入图片后仍然可以放大缩小
public static void insertExcelImage(String excelPath,String imagePath){InputStream in = null;FileOutputStream out = null;try{in = new FileInputStream(excelPath);XSSFWorkbook wb = new XSSFWorkbook(in);XSSFSheet sheet = wb.getSheetAt(0);XSSFRow row = sheet.getRow(3);//图片插入到第4行第4列的单元格if(row ==null){row = sheet.createRow(3);}Cell cell =row.getCell(3);if(cell==null){cell = row.createCell(3);}sheet.setForceFormulaRecalculation(true);replaceImage(wb,sheet,cell,imagePath,3,3,200);out = new FileOutputStream(excelPath);// 将最新的 Excel 文件写入到文件输出流中，更新文件信息！wb.write(out);// 执行 flush 操作， 将缓存区内的信息更新到文件上out.flush();in.close();} catch (Exception e) {e.printStackTrace();} finally {if (in != null) {try {in.close();} catch (IOException e) {e.printStackTrace();}}if (out != null) {try {out.close();} catch (IOException e) {e.printStackTrace();}}}}
public static Workbook replaceImage(Workbook book, Sheet sheet, Cell cell, String jdImagePath, int jdcol, int jdrow, double width) throws Exception {InputStream jdis;byte[] jdbytes = null;try {jdis = new FileInputStream(jdImagePath);jdbytes = IOUtils.toByteArray(jdis);} catch (Exception e) {e.printStackTrace();}CreationHelper helper = book.getCreationHelper();Drawing drawing = null;if (sheet instanceof XSSFSheet) {XSSFSheet xSSFSheet = (XSSFSheet)sheet;drawing = xSSFSheet.getDrawingPatriarch();} else if (sheet instanceof HSSFSheet) {HSSFSheet hSSFSheet = (HSSFSheet)sheet;drawing = hSSFSheet.getDrawingPatriarch();}if (drawing == null) {drawing = sheet.createDrawingPatriarch();}// 图片插入坐标if (-1 != jdcol && -1 != jdrow) {int jdpictureIdx = book.addPicture(jdbytes, Workbook.PICTURE_TYPE_JPEG);// 根据需要调整参数，如果是PNG，就改为 Workbook.PICTURE_TYPE_PNGClientAnchor jdanchor = helper.createClientAnchor();jdanchor.setCol1(jdcol);jdanchor.setRow1(jdrow);// 获取原图片的宽度和高度，单位都是像素File image = new File(jdImagePath);BufferedImage sourceImg = ImageIO.read(image);double imageWidth = sourceImg.getWidth();double imageHeight = sourceImg.getHeight();// 获取单元格宽度和高度，单位都是像素double cellWidth = sheet.getColumnWidthInPixels(cell.getColumnIndex());double cellHeight = cell.getRow().getHeightInPoints() / 72 * 96;// getHeightInPoints()方法获取的是点（磅），就是excel设置的行高，1英寸有72磅，一般显示屏一英寸是96个像素// 插入图片，如果原图宽度大于最终要求的图片宽度，就按比例缩小，否则展示原图Picture pict = drawing.createPicture(jdanchor, jdpictureIdx);double newHeight = width * (imageHeight / imageWidth);if (imageWidth > width || width>cellWidth || newHeight>cellHeight) {double scaleX = width / cellWidth;// 最终图片大小与单元格宽度的比例double scaleY = 1;// 最终图片大小与单元格高度的比例// 说一下这个比例的计算方式吧：( imageHeight / imageWidth ) 是原图高于宽的比值，则 ( width * ( imageHeight / imageWidth ) ) 就是最终图片高的比值，// 那 ( width * ( imageHeight / imageWidth ) ) / cellHeight 就是所需比例了if (newHeight > cellHeight) {//true 图高=单元格高，图宽=cellHight*原图宽/原图高scaleX = cellHeight * (imageWidth / imageHeight) / cellWidth;scaleY = 1.0;} else {scaleY = (width * (imageHeight / imageWidth)) / cellHeight;}pict.resize(scaleX, scaleY);} else {pict.resize();}}return book;}
