当前位置：首页 > news >正文

使用Java将图片添加到Excel的几种方式

news 来源：原创 2024/5/20 23:00:01

1、超链接

使用POI，依赖如下

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

Java代码如下,运行该程序它会在桌面创建ImageLinks.xlsx文件。

import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;import java.io.FileOutputStream;
import java.io.IOException;public class ExportTest {public static void main(String[] args) {// 创建工作簿XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("Image Links");// 创建超链接XSSFRow row = sheet.createRow(0);XSSFCell cell = row.createCell(0);XSSFCreationHelper creationHelper = workbook.getCreationHelper();XSSFHyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);hyperlink.setAddress("https://img1.baidu.com/it/u=727029913,321119353&fm=253&app=138&size=w931&n=0&f=JPEG&fmt=auto?sec=1698771600&t=fcf922a02fa5fc68ebf888e7fc1c9dcd");// 将超链接添加到单元格cell.setHyperlink(hyperlink);// 设置字体样式为蓝色XSSFFont font = workbook.createFont();font.setColor(IndexedColors.BLUE.getIndex());XSSFCellStyle style = workbook.createCellStyle();style.setFont(font);cell.setCellStyle(style);cell.setHyperlink(hyperlink);cell.setCellValue("点击这里下载图片");// 保存Excel文件到桌面String desktopPath = System.getProperty("user.home") + "/Desktop/";String filePath = desktopPath + "ImageLinks.xlsx";try (FileOutputStream outputStream = new FileOutputStream(filePath)) {workbook.write(outputStream);System.out.println("Excel file has been saved to the desktop.");} catch (IOException e) {e.printStackTrace();}}
}

在这里插入图片描述

点击它会自动打开浏览器访问设置的超链接

2、单元格插入图片 - POI

使用POI
下面是java代码


import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;public class ExportTest {public static void main(String[] args) {// 创建工作簿XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("Images");// 从URL加载图片try {URL imageUrl = new URL("https://img1.baidu.com/it/u=727029913,321119353&fm=253&app=138&size=w931&n=0&f=JPEG&fmt=auto?sec=1698771600&t=fcf922a02fa5fc68ebf888e7fc1c9dcd");InputStream imageStream = imageUrl.openStream();byte[] bytes = IOUtils.toByteArray(imageStream);// 将图片插入到单元格XSSFRow row = sheet.createRow(0);XSSFCell cell = row.createCell(0);XSSFDrawing drawing = sheet.createDrawingPatriarch();XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, cell.getColumnIndex(), row.getRowNum(), cell.getColumnIndex() + 1, row.getRowNum() + 1);int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);XSSFPicture picture = drawing.createPicture(anchor, pictureIdx);picture.resize(); // 调整图片大小imageStream.close();} catch (IOException e) {e.printStackTrace();}// 保存Excel文件到桌面String desktopPath = System.getProperty("user.home") + "/Desktop/";String filePath = desktopPath + "ExcelWithImage.xlsx";try (FileOutputStream outputStream = new FileOutputStream(filePath)) {workbook.write(outputStream);System.out.println("Excel file with image has been saved to the desktop.");} catch (IOException e) {e.printStackTrace();}}
}

运行代码之后会在桌面生成文件ExcelWithImage.xlsx
在这里插入图片描述
可以看到图片插入到了单元格中

但是尺寸太大了并且占了n行n列，下面设置成占1行1列，只需要修改一行代码

// 设置固定尺寸picture.resize(1, 1);

在这里插入图片描述
看着还是有点别扭，再设置一下宽高，看下效果

可以看到已经非常Nice了，下面是调整后的代码


import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.*;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;public class ExportTest {public static void main(String[] args) {// 创建工作簿XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("Images");// 从URL加载图片try {URL imageUrl = new URL("https://img1.baidu.com/it/u=727029913,321119353&fm=253&app=138&size=w931&n=0&f=JPEG&fmt=auto?sec=1698771600&t=fcf922a02fa5fc68ebf888e7fc1c9dcd");InputStream imageStream = imageUrl.openStream();byte[] bytes = IOUtils.toByteArray(imageStream);// 将图片插入到单元格XSSFRow row = sheet.createRow(0);XSSFCell cell = row.createCell(0);// 设置单元格宽度，单位为字符int widthInCharacters = 10;row.getSheet().setColumnWidth(cell.getColumnIndex(), widthInCharacters * 256);// 设置单元格高度，单位为点数float heightInPoints = 100;row.setHeightInPoints(heightInPoints);XSSFDrawing drawing = sheet.createDrawingPatriarch();XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, cell.getColumnIndex(), row.getRowNum(), cell.getColumnIndex() + 1, row.getRowNum() + 1);int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);XSSFPicture picture = drawing.createPicture(anchor, pictureIdx);picture.resize(1, 1);// 调整图片大小imageStream.close();} catch (IOException e) {e.printStackTrace();}// 保存Excel文件到桌面String desktopPath = System.getProperty("user.home") + File.separator + "Desktop" + File.separator;String filePath = desktopPath + "ExcelWithImage.xlsx";try (FileOutputStream outputStream = new FileOutputStream(filePath)) {workbook.write(outputStream);System.out.println("Excel file with image has been saved to the desktop.");} catch (IOException e) {e.printStackTrace();}}
}

3、单元格插入图片 - EasyExcel

先看效果
在这里插入图片描述
代码如下：

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import org.apache.poi.util.IOUtils;import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;public class ExportTest {public static void main(String[] args) {try {// 从URL加载图片URL imageUrl = new URL("https://img1.baidu.com/it/u=727029913,321119353&fm=253&app=138&size=w931&n=0&f=JPEG&fmt=auto?sec=1698771600&t=fcf922a02fa5fc68ebf888e7fc1c9dcd");InputStream imageStream = imageUrl.openStream();byte[] bytes = IOUtils.toByteArray(imageStream);// 保存Excel文件到桌面String desktopPath = System.getProperty("user.home") + File.separator + "Desktop" + File.separator;String filePath = desktopPath + "ExcelWithImage.xlsx";// 使用EasyExcel创建ExcelEasyExcel.write(filePath).head(ImageData.class).sheet("Images").doWrite(data(bytes));System.out.println("Excel file with image has been saved to the desktop.");imageStream.close();} catch (IOException e) {e.printStackTrace();}}@ContentRowHeight(100)private static class ImageData {@ExcelProperty("图片")private byte[] imageBytes;public ImageData(byte[] imageBytes) {this.imageBytes = imageBytes;}public byte[] getImageBytes() {return imageBytes;}}private static List<ImageData> data(byte[] imageBytes) {List<ImageData> list = new ArrayList<>();list.add(new ImageData(imageBytes));return list;}
}