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

JAVA-使用Apache POI导出数据到Excel，并把每条数据的图片打包成zip附件项

news 2025/5/6 2:59:53

最近项目要实现一个功能，就是在导出报表的时候，要把每条数据的所有图片都要打包成zip附件在excel里一起导出。

1. 添加依赖

           <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.5</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.5</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>5.2.5</version></dependency><dependency><groupId>org.ehcache</groupId><artifactId>ehcache</artifactId><version>3.9.11</version></dependency><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.16.1</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-api</artifactId><version>1.7.36</version></dependency>

2. 实现代码

以下是一个完整的Java实现：

2.1 把图片打包成zip

 /*** 把图片和视频打包成一个zip文件** @param zipFilePath* @param imagePaths* @throws IOException*/public File createZipFile(String zipFilePath, List<String> imagePaths) throws IOException, InvalidFormatException {File zipFile = new File(zipFilePath);if (!zipFile.exists()) {if (!zipFile.getParentFile().exists()) {zipFile.getParentFile().mkdirs();}zipFile.createNewFile();}try (FileOutputStream fos = new FileOutputStream(zipFilePath);ZipOutputStream zos = new ZipOutputStream(fos)) {for (String imagePath : imagePaths) {File fileToZip = new File(imagePath);FileInputStream fis = new FileInputStream(fileToZip);ZipEntry zipEntry = new ZipEntry(fileToZip.getName());zos.putNextEntry(zipEntry);byte[] bytes = new byte[1024];int length;while ((length = fis.read(bytes)) >= 0) {zos.write(bytes, 0, length);}fis.close();}}return zipFile;}

2.2 把Zip文件添加到excel附件，并生成缩略图

package com.bdspace.gateway.util;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;import javax.imageio.ImageIO;
import javax.swing.*;
import javax.swing.filechooser.FileSystemView;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.*;/*** poi打包图片成zip文件添加到excel附件** @author lyl* @version v1.0* @since 2025/4/24*/
public class PoiAttachUtil {/*** 创建附件* @param workbook* @param sheet* @param row* @param zipFile* @throws IOException*/public void createAttch(Workbook workbook, Sheet sheet, int row, String zipFile) throws IOException {//只有HSSFWorkbook才能使用OLE对象，并且poi需要在4.0之上// 创建工作簿和工作表对象// 读取需要添加的文件，如果有需要添加多个文件的需求，可以循环表格来添加File pdfFile = new File(zipFile);FileInputStream fis = new FileInputStream(pdfFile);byte[] pdfBytes = new byte[(int) pdfFile.length()];fis.read(pdfBytes);fis.close();// 获取文件系统视图FileSystemView view = FileSystemView.getFileSystemView();Icon systemIcon = view.getSystemIcon(pdfFile);// 将 Icon 对象转换为 Image 对象Image image = iconToImage(systemIcon);// 将 Image 对象转换为字节数组byte[] imageBytes = imageToByteArray(image);//将文件的图标添加进入到Excel文件内int iconid = workbook.addPicture(imageBytes, HSSFWorkbook.PICTURE_TYPE_JPEG);// 在工作表中创建OLE对象，就是将文件插入到Excel文件中int pdfIdx = workbook.addOlePackage(pdfBytes, pdfFile.getName(), row + pdfFile.getName(), pdfFile.getName());// 创建画布和锚点Drawing<?> drawing = sheet.createDrawingPatriarch();ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 6, row, 7, row + 1);//这里的参数后续根据传过来的信息来变化。row，colanchor.setAnchorType(HSSFClientAnchor.AnchorType.MOVE_AND_RESIZE);drawing.createObjectData(anchor, pdfIdx, iconid);}// 将 Icon 对象转换为 Image 对象private Image iconToImage(Icon icon) {if (icon instanceof ImageIcon) {return ((ImageIcon) icon).getImage();} else {int width = icon.getIconWidth();int height = icon.getIconHeight();BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_ARGB);Graphics graphics = image.createGraphics();icon.paintIcon(null, graphics, 0, 0);graphics.dispose();return image;}}// 将 Image 对象转换为字节数组private byte[] imageToByteArray(Image image) throws IOException {ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();ImageIO.write(imageToBufferedImage(image), "png", byteArrayOutputStream);return byteArrayOutputStream.toByteArray();}// 将 Image 对象转换为 BufferedImage 对象private BufferedImage imageToBufferedImage(Image image) {if (image instanceof BufferedImage) {return (BufferedImage) image;}BufferedImage bufferedImage = new BufferedImage(image.getWidth(null), image.getHeight(null), BufferedImage.TYPE_INT_ARGB);Graphics2D graphics2D = bufferedImage.createGraphics();graphics2D.drawImage(image, 0, 0, null);graphics2D.dispose();return bufferedImage;}public static void main(String[] args) {PoiAttachUtil p = new PoiAttachUtil();Workbook workbook = new HSSFWorkbook();Sheet sheet = workbook.createSheet("Sheet1");Row row = sheet.createRow(0);//加载附件String zipFilePahth = "D:\\testaa\\image1.zip";try {p.createAttch(workbook, sheet, 1, zipFilePahth);try (OutputStream fileOut = new FileOutputStream("D:\\testaa\\事件测试2.xls")) {workbook.write(fileOut);}// 关闭工作簿workbook.close();} catch (IOException e) {throw new RuntimeException(e);}}}

3. 在Spring MVC控制器中使用

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import javax.servlet.http.HttpServletResponse;@Controller
public class ExportController {@GetMapping("/export/employees")public void exportEmployees(HttpServletResponse response) throws IOException {PoiAttachUtil p = new PoiAttachUtil();Workbook workbook = new HSSFWorkbook();Sheet sheet = workbook.createSheet("Sheet1");Row row = sheet.createRow(0);String zipFilePath="d:\\temp\test.zip";//添加图片路径。或从数据库拿List<String> files=new ArrayList<>();//加载附件String zipFilePahth =  p.createZipFile(zipFilePath, files);try {p.createAttch(workbook, sheet, 1, zipFilePahth);try (OutputStream fileOut = new FileOutputStream(response)) {workbook.write(fileOut);}// 关闭工作簿workbook.close();} catch (IOException e) {throw new RuntimeException(e);}}
}