Include an image to the right of a cell in Excel in Java using POI

Question

Anyways wherein one can shift the image to the right of the cell in excel through java using poi. Tried all possible anchor position and co-ordinates but the image stays to the left side of the cell this

But required position is

Following is the code used to draw image to excel in java :-

private static void drawImageOnExcelSheet(XSSFSheet sheet, int row, int col, int height, int width, int pictureIdx)
        throws Exception {

    CreationHelper helper = sheet.getWorkbook().getCreationHelper();

    Drawing drawing = sheet.createDrawingPatriarch();

    ClientAnchor anchor1 = helper.createClientAnchor();
    anchor1.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);

    anchor1.setRow2(row); // second anchor determines bottom right position
    anchor1.setCol2(col);
    anchor1.setDx2(Units.toEMU(width)); // dx = left + wanted width
    anchor1.setDy2(Units.toEMU(height)); // dy= top + wanted height

    Picture pic = drawing.createPicture(anchor1, pictureIdx);
    pic.resize();

}

Answer 1

I suspect the code you are showing here is taken from this answer: on adding image and text in same cell in excel using poi then image override the text. Your code has the same comments as my code in this answer. This leads to this suspicion.

In the answer you will find clearly described how anchors working to anchoring pictures which hovers in a layer over the cells.

For placing something on right side of the cell, you need to know the width of the cell (column). Then the dx of the second anchor must be set to the same amount as the cell width. Because the dx will be added to the column's position to determine the final position it will then be on right edge of the cell.

But for sure you will need a two cell anchor and so you should not do pic.resize() since the two cell anchor determines the size already.

Example:

import java.io.*;

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

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;

public class ExcelDrawImagesOnCell {

 private static void drawImageOnExcelSheet(XSSFSheet sheet, int row, int col, 
  int picHeight/*in px*/, int picWidth/*in px*/, int pictureIdx, boolean right) throws Exception {

  CreationHelper helper = sheet.getWorkbook().getCreationHelper();

  Drawing drawing = sheet.createDrawingPatriarch();

  ClientAnchor anchor = helper.createClientAnchor();
  anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

  int columWidthInPx = Math.round(sheet.getColumnWidthInPixels(col));

  anchor.setCol1(col); //first anchor determines upper left position
  anchor.setRow1(row);
  if (right) {
   anchor.setDx1(Units.pixelToEMU(columWidthInPx) - Units.pixelToEMU(picWidth)); //dx = right - wanted width
   anchor.setDy1(0); //dy = top
  } else {
   anchor.setDx1(0); //dx = left
   anchor.setDy1(0); //dy = top
  }

  anchor.setCol2(col); //second anchor determines bottom right position
  anchor.setRow2(row); 
  if (right) {
   anchor.setDx2(Units.pixelToEMU(columWidthInPx)); //dx = right
   anchor.setDy2(Units.pixelToEMU(picHeight)); //dy = top + wanted height
  } else {
   anchor.setDx2(Units.pixelToEMU(picWidth)); //dx = left + wanted width
   anchor.setDy2(Units.pixelToEMU(picHeight)); //dy = top + wanted height
  }

  drawing.createPicture(anchor, pictureIdx);

 }

 public static void main(String[] args) throws Exception {
  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet();

  InputStream is = new FileInputStream("samplePict.jpeg");
  byte[] bytes = IOUtils.toByteArray(is);
  int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
  is.close();

  sheet.setColumnWidth(1, 15*256); //set the column width to 15 character widths
  for (int r = 0; r < 10; r++ ) {
   sheet.createRow(r).createCell(1).setCellValue("Picture " + (r+1));
   //drawImageOnExcelSheet((XSSFSheet)sheet, r, 1, 16/*px*/, 16/*px*/, pictureIdx, false);
   drawImageOnExcelSheet((XSSFSheet)sheet, r, 1, 16/*px*/, 16/*px*/, pictureIdx, true/*right*/);
  }

  wb.write(new FileOutputStream("ExcelDrawImagesOnCell.xlsx"));
  wb.close();
 }
}

Result:

---

Supplement May 14, 2018:

Having a solution using XSSF as well as HSSF for this is complicated because of the different strange measurement units which Microsoft is using and the fact that the binary file system BIFF *.xls and the Office Open XML *.xlsx are very different not only in file storing but in general approaches also. In my answer in apache poi XSSFClientAnchor not positioning picture with respect to dx1, dy1, dx2, dy2 I have described that problem already.

So following code should work with both, XSSFas well as HSSF. It is commented where it uses special approaches. In general the values of dx and dy in BIFF are defined in 2.5.193 OfficeArtClientAnchorSheet.

dx: The value is expressed as 1024th’s of that cell’s width.

dy: The value is expressed as 256th’s of that cell’s height.

import java.io.*;

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

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;

public class ExcelDrawImagesOnCell {

 private static void drawImageOnExcelSheet(Sheet sheet, int row, int col, 
  int picHeight/*in px*/, int picWidth/*in px*/, int pictureIdx, boolean right) throws Exception {

  Row rowObject = sheet.getRow(row);
  if (rowObject == null) rowObject = sheet.createRow(row);
  float rowHeight = rowObject.getHeightInPoints();
  int rowHeightPx = Units.pointsToPixel(rowHeight);

  int columWidthInPx = Math.round(sheet.getColumnWidthInPixels(col));
  
  CreationHelper helper = sheet.getWorkbook().getCreationHelper();

  Drawing drawing = sheet.createDrawingPatriarch();

  ClientAnchor anchor = helper.createClientAnchor();
  anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

  anchor.setCol1(col); //first anchor determines upper left position
  anchor.setRow1(row);
  if (sheet instanceof XSSFSheet) {
   if (right) {
    anchor.setDx1(Units.pixelToEMU(columWidthInPx) - Units.pixelToEMU(picWidth)); //dx = right - wanted width
    anchor.setDy1(0); //dy = top
   } else {
    anchor.setDx1(0); //dx = left
    anchor.setDy1(0); //dy = top
   }
  } else if (sheet instanceof HSSFSheet) {
   if (right) {
    anchor.setDx1((int)Math.round((columWidthInPx - picWidth) * 1024f / columWidthInPx)); //dx = right - wanted width
    anchor.setDy1(0); //dy = top
   } else {
    anchor.setDx1(0); //dx = left
    anchor.setDy1(0); //dy = top
   }
  }

  anchor.setCol2(col); //second anchor determines bottom right position
  anchor.setRow2(row); 
  if (sheet instanceof XSSFSheet) {
   if (right) {
    anchor.setDx2(Units.pixelToEMU(columWidthInPx)); //dx = right
    anchor.setDy2(Units.pixelToEMU(picHeight)); //dy = top + wanted height
   } else {
    anchor.setDx2(Units.pixelToEMU(picWidth)); //dx = left + wanted width
    anchor.setDy2(Units.pixelToEMU(picHeight)); //dy = top + wanted height
   }
  } else if (sheet instanceof HSSFSheet) {
   if (right) {
    anchor.setDx2((int)Math.round(columWidthInPx * 1024f / columWidthInPx)); //dx = right
    anchor.setDy2((int)Math.round(picHeight * 256 / rowHeightPx)); //dy = top + wanted height
   } else {
    anchor.setDx2((int)Math.round(picWidth * 1024f / columWidthInPx)); //dx = left + wanted width
    anchor.setDy2((int)Math.round(picHeight * 256 / rowHeightPx)); //dy = top + wanted height
   }
  }

  drawing.createPicture(anchor, pictureIdx);

 }

 public static void main(String[] args) throws Exception {
  //Workbook wb = new XSSFWorkbook();
  Workbook wb = new HSSFWorkbook();
  Sheet sheet = wb.createSheet();

  InputStream is = new FileInputStream("samplePict.jpeg");
  byte[] bytes = IOUtils.toByteArray(is);
  int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
  is.close();

  sheet.setColumnWidth(1, 20*256); //set the column width to 20 character widths
  for (int r = 0; r < 10; r++ ) {
   sheet.createRow(r).createCell(1).setCellValue("    Picture " + (r+1));
   if ((r % 2) == 0) {
    drawImageOnExcelSheet(sheet, r, 1, 16/*px*/, 16/*px*/, pictureIdx, false);
   } else {
    drawImageOnExcelSheet(sheet, r, 1, 16/*px*/, 16/*px*/, pictureIdx, true/*right*/);
   }
  }

  if (wb instanceof XSSFWorkbook) {
   wb.write(new FileOutputStream("ExcelDrawImagesOnCell.xlsx"));
  } else if (wb instanceof HSSFWorkbook) {
   wb.write(new FileOutputStream("ExcelDrawImagesOnCell.xls"));
  }
  wb.close();
 }
}

Result: