java利用POI向Excel(xls)写入图片，并对图片引用超链接

清疚 2022-06-03 06:43 399阅读 0赞

1.maven依赖

<dependency>
    	<groupId>org.apache.poi</groupId>
    	<artifactId>poi</artifactId>
    	<version>3.9</version>
    </dependency>

2.代码示例

import java.awt.image.BufferedImage;
    import java.io.ByteArrayOutputStream;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    
    import javax.imageio.ImageIO;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFHyperlink;
    import org.apache.poi.hssf.usermodel.HSSFPatriarch;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.Hyperlink;
    
    public class WriteImgUtil {
    	
    	/**
    	 * 写入图片，并插入链接
    	 * @param cell 要插入链接的单元格位置
    	 * @param sheetName 插入的图片所在的工作表
    	 * @param patriarch 画图的顶级管理器，一个sheet只能获取一次，多次插入图片请使用同一个patriarch对象
    	 * @param wb HSSFWorkbook对象
    	 * @param file 图片文件
    	 * @param cellPoint 自定义的对象，指定要插入图片的坐标(x, y)
    	 * @return cellPoint 自定义的对象，返回下一个要插入图片的坐标(x, y)
    	 * @throws IOException
    	 */
    	public static CellPoint whiteImg(HSSFCell cell, String sheetName, HSSFPatriarch patriarch, HSSFWorkbook wb, File file, CellPoint cellPoint) throws IOException {
    		Hyperlink hyperlink = new HSSFHyperlink(Hyperlink.LINK_DOCUMENT);    
            // "'18 Q2截图'"表示sheet页名称  "A10"表示第几列第几行
            hyperlink.setAddress("'18 Q2截图'!A" + (cellPoint.getY() + 1));    
            cell.setHyperlink(hyperlink);
            
            /* 设置为超链接的样式*/    
            HSSFCellStyle linkStyle = wb.createCellStyle();    
            HSSFFont cellFont= wb.createFont();
            cellFont.setUnderline((byte) 1);    
            cellFont.setColor(HSSFColor.BLUE.index);    
            linkStyle.setFont(cellFont);    
            cell.setCellStyle(linkStyle); 
    		
    		BufferedImage bufferImg = null;
    		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
    		bufferImg = ImageIO.read(file);
    		ImageIO.write(bufferImg, "png", byteArrayOut);
    
    		int x1 = cellPoint.getX();
    		int y1 = cellPoint.getY();
    		int width = bufferImg.getWidth() / 64;
    		int height = bufferImg.getHeight() / 18;
    		System.out.println(width + "..." + height);
    
    		int x2 = x1 + width;
    		int y2 = y1 + height;
    		// anchor主要用于设置图片的属性
    		HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 0, 0, (short) x1, y1, (short) x2, y2);
    		// 插入图片
    		patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
    		cellPoint = new CellPoint(x1, y2 + 1);
    		return cellPoint;
    	}
    	
    	public static class CellPoint {
    		private int x;
    		private int y;
    		
            public CellPoint(int x, int y) {
    	        super();
    	        this.x = x;
    	        this.y = y;
            }
            public int getX() {
            	return x;
            }
            public void setX(int x) {
            	this.x = x;
            }
            public int getY() {
            	return y;
            }
            public void setY(int y) {
            	this.y = y;
            }
    	}
    	
    	public static void main(String[] args) {
    		FileOutputStream fileOut = null;
    		
    		// 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
    		try {
    			HSSFWorkbook wb = new HSSFWorkbook();
    			HSSFSheet sheet1 = wb.createSheet("test href");
    			String sheetName = "18 Q2截图";
    			HSSFSheet sheet2 = wb.createSheet(sheetName);
    			// 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
    			HSSFPatriarch patriarch = sheet2.createDrawingPatriarch();
    			CellPoint cellPoint = new CellPoint(0, 1);
    			for (int i = 0; i < 10; i++) {
    				HSSFRow row = sheet1.createRow(i);
    				HSSFCell cell = row.createCell(0);
    				// 点击进行跳转    
    		        cell.setCellValue("第" + (i + 1) + "个图片链接");
    		        
    		        File file = null;
    		        if (i%2 == 0) {
    		        	file = new File("C:/Users/dulinan/Desktop/3333.png");
    		        } else {
    		        	file = new File("C:/Users/dulinan/Desktop/Desert.jpg");
    		        }
    		        
    				
    				cellPoint = whiteImg(cell, sheetName, patriarch, wb, file, cellPoint);
    			}
    			
    			
    			fileOut = new FileOutputStream("F:/测试Excel3.xls");
    			// 写入excel文件
    			wb.write(fileOut);
    			System.out.println("----Excle文件已生成------");
    		}
    		catch (Exception e) {
    			e.printStackTrace();
    		}
    		finally {
    			if (fileOut != null) {
    				try {
    					fileOut.close();
    				}
    				catch (IOException e) {
    					e.printStackTrace();
    				}
    			}
    		}
    	}
    }

![Center][]

![Center 1][]

[Center]: /images/20220603/12d360bcac3243bf8380405315746095.png
[Center 1]: /images/20220603/102bb2314ab74338959f48a8ae2a1596.png