Java: How to center an image in an Excel cell?

Question

I want to put an image set in the center of a cell of excel, I use XSSFClientAnchor to anchor the picture position, but still not working like in picture 1.

How to set the image in the center of a cell, like in picture 2.

InputStream iStream = new FileInputStream(iList.get(q.getProductID()));
byte[] bytes = IOUtils.toByteArray(iStream);
int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.setCol1(1);
anchor.setRow1(row);
anchor.setCol2(2);
anchor.setRow2(row + 1);
Picture pic = patriarch.createPicture(anchor, pictureIdx);
pic.resize();

Answer 1

Images are not cell contents but hover over the sheet in a separate layer called drawing. They are anchored to the cells. A ClientAnchor provides following settings:

col1 = column index where left edge of picture is anchored on. So left edge of picture is anchored on left column edge of col1.

dx1 = difference in x direction. So left edge of picture is anchored on left column edge of col1 + dx1.

row1 = row index where top edge of picture is anchored on. So top edge of picture is anchored on top row edge of row1.

dy1 = difference in y direction. So top edge of picture is anchored on top row edge of row1 + dy1.

col2 = column index where right edge of picture is anchored on. So right edge of picture is anchored on left column edge of col2.

dx2 = difference in x direction. So right edge of picture is anchored on left column edge of col1 + dx2.

row2 = row index where bottom edge of picture is anchored on. So bottom edge of picture is anchored on top row edge of row2.

dy2 = difference in y direction. So bottom edge of picture is anchored on top row edge of row2 + dy2.

Thus, given a full two-cell-anchor, this determines the position of picture well as it's size.

If size of picture shall be it's native size, then only one-cell-anchor is needed. There col1+dx1 and row1+dy1 determines the position of top left edge of picture. The size is given by the native size of the picture.

If only col1 and row1 is set without dx1 and dy1, then top left edge of picture always is anchored to left edge of col1 and top edge of row1. So if centering over a cell is needed, then dx1 and dy1 needs to be calculated. To calculate dx1 and dy1 one needs to know the width and height of the picture as well as the width and height of the cell. Sounds simple but there are multiple different measurement units used for width and height of the cell and there are big differences between binary BIFF (*.xls) file system and Office Open XML (*.xlsx) file system.

The following code provides putPictureCentered method which puts a picture in sheet's drawing anchored to a cell given by colIdx and rowIdx. If possible, it calculates dx1 and dy1 so that the picture is anchored centered over the cell. It uses pixels as the common measurement unit. It considers differences between binary BIFF (*.xls) file system and Office Open XML (*.xlsx) file system. So it works for Sheet, may it be XSSFSheet or HSSFSheet.

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;

class CenterImageOverCell {

 static void putPictureCentered(Sheet sheet, String picturePath, int pictureType, int colIdx, int rowIdx) throws Exception {
  Workbook wb = sheet.getWorkbook();

  //load the picture
  InputStream inputStream = new FileInputStream(picturePath);
  byte[] bytes = IOUtils.toByteArray(inputStream);
  int pictureIdx = wb.addPicture(bytes, pictureType);
  inputStream.close();

  //create an anchor with upper left cell colIdx/rowIdx, only one cell anchor since bottom right depends on resizing
  CreationHelper helper = wb.getCreationHelper();
  ClientAnchor anchor = helper.createClientAnchor();
  anchor.setCol1(colIdx);
  anchor.setRow1(rowIdx);

  //create a picture anchored to colIdx and rowIdx
  Drawing drawing = sheet.createDrawingPatriarch();
  Picture pict = drawing.createPicture(anchor, pictureIdx);

  //get the picture width in px
  int pictWidthPx = pict.getImageDimension().width;
  //get the picture height in px
  int pictHeightPx = pict.getImageDimension().height;

  //get column width of column in px
  float columnWidthPx = sheet.getColumnWidthInPixels(colIdx);

  //get the height of row in px
  Row row = sheet.getRow(rowIdx);
  float rowHeightPt = row.getHeightInPoints();
  float rowHeightPx = rowHeightPt * Units.PIXEL_DPI / Units.POINT_DPI;

  //is horizontal centering possible?
  if (pictWidthPx <= columnWidthPx) {

   //calculate the horizontal center position
   int horCenterPosPx = Math.round(columnWidthPx/2f - pictWidthPx/2f);
   //set the horizontal center position as Dx1 of anchor
   if (wb instanceof XSSFWorkbook) {
    anchor.setDx1(horCenterPosPx * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
   } else if (wb instanceof HSSFWorkbook) {
    //see https://stackoverflow.com/questions/48567203/apache-poi-xssfclientanchor-not-positioning-picture-with-respect-to-dx1-dy1-dx/48607117#48607117 for HSSF
    int DEFAULT_COL_WIDTH = 10 * 256;
    anchor.setDx1(Math.round(horCenterPosPx * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75f * DEFAULT_COL_WIDTH / columnWidthPx));
   }

  } else {
   System.out.println("Picture is too width. Horizontal centering is not possible.");
   //TODO: Log instead of System.out.println
  }

  //is vertical centering possible?
  if (pictHeightPx <= rowHeightPx) {

   //calculate the vertical center position
   int vertCenterPosPx = Math.round(rowHeightPx/2f - pictHeightPx/2f);
   //set the vertical center position as Dy1 of anchor
   if (wb instanceof XSSFWorkbook) {
    anchor.setDy1(Math.round(vertCenterPosPx * Units.EMU_PER_PIXEL)); //in unit EMU for XSSF
   } else if (wb instanceof HSSFWorkbook) {
    //see https://stackoverflow.com/questions/48567203/apache-poi-xssfclientanchor-not-positioning-picture-with-respect-to-dx1-dy1-dx/48607117#48607117 for HSSF
    float DEFAULT_ROW_HEIGHT = 12.75f;
    anchor.setDy1(Math.round(vertCenterPosPx * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightPx));
   }

  } else {
   System.out.println("Picture is too height. Vertical centering is not possible.");
   //TODO: Log instead of System.out.println
  }

  //resize the picture to it's native size
  pict.resize();

 }

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

  //Workbook wb = new HSSFWorkbook(); String resultName = "CenterImageTest.xls";
  Workbook wb = new XSSFWorkbook(); String resultName = "CenterImageTest.xlsx";
  Sheet sheet = wb.createSheet("Sheet1");

  int colIdx = 1;
  int colWidth = 20; //in default character widths
  int rowIdx = 1;
  float rowHeight = 100; //in points

  //========================prepare sheet
  //create cell
  Row row = sheet.createRow(rowIdx);
  Cell cell = row.createCell(colIdx);
  //set column width of colIdx in default character widths
  sheet.setColumnWidth(colIdx, colWidth * 256);
  //set row height of rowIdx in points
  row.setHeightInPoints(rowHeight);
  //========================end prepare sheet

  //put image centered
  String picturePath = "./pict100x100.png"; // small image
  //String picturePath = "./pict100x200.png"; // image too height
  //String picturePath = "./pict200x100.png"; // image too width
  //String picturePath = "./pict200x200.png"; // image too big
  putPictureCentered(sheet, picturePath, Workbook.PICTURE_TYPE_PNG, colIdx, rowIdx);

  FileOutputStream fileOut = new FileOutputStream("./" + resultName);
  wb.write(fileOut);
  fileOut.close();
  wb.close();

 }
}