I need help replacing an image with another image in an excel sheet using Apache POI.

int myPictureId=0;
List<XSSFShape> pictures = drawing.getShapes();
for(XSSFShape shape : pictures) {
    XSSFPicture picture = (XSSFPicture)shape; //Get picture
    byte[] data = picture.getPictureData().getData(); //Get picture data
    XSSFClientAnchor anchor = picture.getClientAnchor(); //Get anchor
    ByteArrayInputStream byteInput = new ByteArrayInputStream(data);
    BufferedImage image = ImageIO.read(byteInput);
    data = getByteArrayFromImage(image, 400, data, picture.getPictureData().suggestFileExtension());
    myPictureId = workbook.addPicture(new ByteArrayInputStream(data), Workbook.PICTURE_TYPE_PNG);
    picture = drawing.createPicture(anchor, myPictureId);
}

I am using the getByteArrayFromImage method to compress my image and replace the original image with the compressed one. I also tried using XSSFClientAnchor to attach the compressed image to the original image but excel is overlapping the original image with the compressed image.

CodePudding user response：

Not clear what getByteArrayFromImage is doing. But as it seems it only changes something on the data bytes of the source BufferedImage got from the data bytes from source XSSFPictureData and returns the changed data bytes then.

If this new data bytes shall be the replacement of the source data bytes from source XSSFPictureData, then following method can do this:

 void replacePictureData(XSSFPictureData source, byte[] data) {
  try ( ByteArrayInputStream in = new ByteArrayInputStream(data); 
        OutputStream out = source.getPackagePart().getOutputStream();
       ) {
   byte[] buffer = new byte[2048];
   int length;
   while ((length = in.read(buffer)) > 0) {
    out.write(buffer, 0, length);
   }
  } catch (Exception ex) {
   ex.printStackTrace();  
  }
 }

XSSFPictureData is a package part of the *.xlsx ZIP archive and so it provides a getOutputStream method.

The following complete example shows it in whole. My getByteArrayFromImage writes the text "CHANGED" onto the pictures in the Excel file.

import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.Font;
import java.awt.FontMetrics;
import java.awt.Graphics;
import java.awt.Graphics2D;

import java.util.List;

class ExcelReplacePictures {
    
 static void replacePictureData(XSSFPictureData source, byte[] data) {
  try ( ByteArrayInputStream in = new ByteArrayInputStream(data); 
        OutputStream out = source.getPackagePart().getOutputStream();
       ) {
   byte[] buffer = new byte[2048];
   int length;
   while ((length = in.read(buffer)) > 0) {
    out.write(buffer, 0, length);
   }
  } catch (Exception ex) {
   ex.printStackTrace();  
  }
 }

 static byte[] getByteArrayFromImage(BufferedImage image, Dimension dimension, byte[] data, String type) {
  try {
   Graphics g = image.getGraphics();  
   g.setColor(Color.red);
   g.setFont(new Font("Serif", Font.BOLD, 40));
   String s = "CHANGED";
   FontMetrics fm = g.getFontMetrics();
   int x = image.getWidth() / 2 - fm.stringWidth(s) / 2;
   int y = image.getHeight() / 2   fm.getHeight() / 2;
   g.drawString(s, x, y);
   g.dispose();
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   ImageIO.write(image, type, baos);
   data = baos.toByteArray(); 
  } catch (Exception ex) {
   ex.printStackTrace();  
  }  
  return data;
 }
 
 public static void main(String[] args) throws Exception {

  try (XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("./ExcelWithPictures.xlsx"));
       FileOutputStream out = new FileOutputStream("./ExcelWithPicturesNew.xlsx");
      ) {

   for (int i = 0; i < workbook.getNumberOfSheets(); i  ) {
    XSSFSheet sheet = workbook.getSheetAt(i);   
    XSSFDrawing drawing = sheet.getDrawingPatriarch();
    List<XSSFShape> shapes = drawing.getShapes();
    for (XSSFShape shape : shapes) {
     if (shape instanceof XSSFPicture) {
      XSSFPicture picture = (XSSFPicture)shape; 
      XSSFPictureData pictureData =  picture.getPictureData();
      byte[] data = pictureData.getData();
      ByteArrayInputStream byteInput = new ByteArrayInputStream(data);
      BufferedImage image = ImageIO.read(byteInput);
      data = getByteArrayFromImage(image, picture.getImageDimension(), data, pictureData.suggestFileExtension());
      replacePictureData(pictureData, data);
     }
    } 
   } 
   workbook.write(out);
  } catch (Exception ex) {
    ex.printStackTrace();  
  } 
 }
}