Apache Poi, insert 2 images in one cell with differend anchor properites

Question

I want to insert 2 images in one cell, One image stick to left border of cell and another one stick to right border.

I've tried to set

anchor.setDx1(-1)   // multiple tries with 1 - 1, 2, -2 and so on
anchor.setDy1(-1)
anchor.setDx2(-1)
anchot.setDy2(-1)

with differend combinatrions and image still stick to left upper corner of cell.

I want to have similar to this in picture below

Answer 1

It's necessary a little bit math to calculate the needed Dxand Dy.

We need the pictures dimensions, the column width and the column height. First picture's top left edge is at top left edge of the cell. First picture's bottom right edge is at bottom of the cell and is picture's width away from the left edge of the cell. Second picture's top left edge is at top of the cell and is picture's width away from the right edge of the cell. Second picture's bottom right edge is at bottom right of the cell.

Example:

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.util.IOUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;

class CreateExcelTwoPicturesInOneCell {

 static String excelPath = "./ExcelWithTwoPicturesInOneCell.xlsx";
 static String[] pictureFileNames = new String[]{"./Koala.png", "./Hydrangeas.jpg"};
 static int[] pictureTypes = new int[]{Workbook.PICTURE_TYPE_PNG, Workbook.PICTURE_TYPE_JPEG};
 static int pictureWidthPx = 100;
 static int pictureHeightPx = 100;
 static XSSFWorkbook workbook;
 static XSSFSheet sheet;

 static void addImage(int col1, int row1, int col2, int row2, 
  int dx1, int dy1, int dx2, int dy2,
  String imageFileName, int pictureType, ClientAnchor.AnchorType anchorType) throws Exception {

  InputStream imageInputStream = new FileInputStream(imageFileName);
  byte[] bytes = IOUtils.toByteArray(imageInputStream);
  int pictureId = workbook.addPicture(bytes, pictureType);
  imageInputStream.close();
  XSSFClientAnchor anchor = workbook.getCreationHelper().createClientAnchor();
  anchor.setAnchorType(anchorType);
  // set Col1, Dx1, Row1, Dy1, Col2, Dx2, Row2, Dy2
  // only this determines the picture's size then
  anchor.setCol1(col1);
  anchor.setDx1(dx1);
  anchor.setRow1(row1);
  anchor.setDy1(dy1);
  anchor.setCol2(col2);
  anchor.setDx2(dx2);
  anchor.setRow2(row2);
  anchor.setDy2(dy2);
  XSSFDrawing drawing = sheet.createDrawingPatriarch();
  XSSFPicture picture = drawing.createPicture(anchor, pictureId);

 }

 public static void main(String args[]) throws Exception {

  workbook = new XSSFWorkbook();
  sheet = workbook.createSheet();

  sheet.setColumnWidth(0, 50*256);
  int columnWidthPx = Math.round(sheet.getColumnWidthInPixels(0));
  sheet.createRow(0).setHeightInPoints((float)Units.pixelToPoints(pictureHeightPx));

  addImage(0, 0, 0, 0, /*all fits in cell A1*/
   /*Dx1 = 0 and Dy1 = 0*/
   Units.pixelToEMU(0), Units.pixelToEMU(0), 
   /*Dx2 is picture's width and Dy2 is picture's height*/
   Units.pixelToEMU(pictureWidthPx), Units.pixelToEMU(pictureHeightPx),
   pictureFileNames[0], pictureTypes[0], ClientAnchor.AnchorType.MOVE_AND_RESIZE);

  addImage(0, 0, 0, 0, /*all fits in cell A1*/
   /*Dx1 = cell's width-picture's width and Dy1 = 0*/
   Units.pixelToEMU(columnWidthPx - pictureWidthPx), Units.pixelToEMU(0), 
   /*Dx2 is cell's width and Dy2 is picture's height*/
   Units.pixelToEMU(columnWidthPx), Units.pixelToEMU(pictureHeightPx),
   pictureFileNames[1], pictureTypes[1], ClientAnchor.AnchorType.MOVE_AND_RESIZE);

  FileOutputStream fos = new FileOutputStream(excelPath);
  workbook.write(fos);
  fos.close();
  workbook.close();

 }
}

The result in Excel is:

But your screenshot seems to be Google Sheets rather than Excel. And Google Sheets might using other types of anchors than Excel does. So the result might be different in Google Sheets.

---

Google Sheets has a new "feature" to insert an image in cell instead of inserting images in the drawing layer which hovers over the cells. But this only allows one image per cell. It seems that Google Sheets decides whether an image is in the cell according to whether it exactly fits the cell borders. So to avoid this there must be a gap between the picture's edges and the cell borders.

If my above code is changed like the following, then the result are two images in one cell also in Google Sheets.

...
 public static void main(String args[]) throws Exception {

  workbook = new XSSFWorkbook();
  sheet = workbook.createSheet();

  sheet.setColumnWidth(0, 50*256);
  int columnWidthPx = Math.round(sheet.getColumnWidthInPixels(0));
  sheet.createRow(0).setHeightInPoints((float)Units.pixelToPoints(pictureHeightPx));

  int gapBetwPicAndCellBordPx = 5;

  addImage(0, 0, 0, 0, /*all fits in cell A1*/
   /*Dx1 = gapBetwPicAndCellBordPx and Dy1 = gapBetwPicAndCellBordPx*/
   Units.pixelToEMU(gapBetwPicAndCellBordPx), Units.pixelToEMU(gapBetwPicAndCellBordPx), 
   /*Dx2 is picture's width and Dy2 is picture's height - gapBetweenPictureAndCellBorderPx*/
   Units.pixelToEMU(pictureWidthPx-gapBetwPicAndCellBordPx), Units.pixelToEMU(pictureHeightPx-gapBetwPicAndCellBordPx),
   pictureFileNames[0], pictureTypes[0], ClientAnchor.AnchorType.MOVE_AND_RESIZE);

  addImage(0, 0, 0, 0, /*all fits in cell A1*/
   /*Dx1 = cell's width-picture's width+gapBetwPicAndCellBordPx and Dy1 = gapBetwPicAndCellBordPx*/
   Units.pixelToEMU(columnWidthPx - pictureWidthPx + gapBetwPicAndCellBordPx), Units.pixelToEMU(gapBetwPicAndCellBordPx), 
   /*Dx2 is cell's width - gapBetwPicAndCellBordPx and Dy2 is picture's height - gapBetwPicAndCellBordPx*/
   Units.pixelToEMU(columnWidthPx - gapBetwPicAndCellBordPx), Units.pixelToEMU(pictureHeightPx - gapBetwPicAndCellBordPx),
   pictureFileNames[1], pictureTypes[1], ClientAnchor.AnchorType.MOVE_AND_RESIZE);

  FileOutputStream fos = new FileOutputStream(excelPath);
  workbook.write(fos);
  fos.close();
  workbook.close();

 }
...

Result in Google Sheets: