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

Actual

But required position is

Expected

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();

}

Yes but still it remains at the left side only. Also I am not sure about the co-ordinates to be used within the same cell. — viji shetty, Apr 30 '18 at 19:33
For placing something on right side of the cell, you need to know the width of the cell (column). Then the `Dx2` must be set `.setDx2(Units.toEMU(widthOfCellInPoints))` or `.setDx2(Units.pixelToEMU(widthOfCellInPixels))`. — Axel Richter, May 01 '18 at 05:40
Axel, tried the option you suggested as follows drawImageOnExcelSheet((XSSFSheet) sheet, row, i, 40, sheet.getColumnWidthInPixels(i), pictureIdx1); and setting to the anchor is as follows: anchor1.setRow2(row); anchor1.setCol2(col); anchor1.setDx2(Units.pixelToEMU((int) width)); anchor1.setDy2(Units.toEMU(height)); Picture pic = drawing.createPicture(anchor1, pictureIdx); pic.resize(); But it doesn't work. Do I have to set any other position other than x2? — viji shetty, May 02 '18 at 07:28

Axel Richter · Answer 1 · 2018-05-14T16:49:58.397

I suspect the code you are showing here is taken from this answer: on addding 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 dependent on the factor of column-width / default column-width and row-height / default row-height. So a dx of the same amount as the column width seems to be constant for all possible column widths. And again, don't ask me about the factor 14.75 used in my example. It is just trial&error.

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 {

  int DEFAULT_COL_WIDTH = 10 * 256; // 1/256th of a character width
  float DEFAULT_ROW_HEIGHT = 12.75f; //255 twips = 12.75 pt

  Row rowObject = sheet.getRow(row);
  float rowHeight = (rowObject!=null)?rowObject.getHeightInPoints():DEFAULT_ROW_HEIGHT;

  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));
  int columWidth = sheet.getColumnWidth(col); // 1/256th of a character width

  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(
     Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH //right = constant for all possible column widths
     -
     picWidth * 14.75 * DEFAULT_COL_WIDTH / columWidth //wanted width = in relation to column width
    )); //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(Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH)); //dx = right = constant for all possible column widths
    anchor.setDy2((int)Math.round(picHeight * 14.75 * DEFAULT_ROW_HEIGHT / rowHeight)); //dy = top + wanted height
   } else {
    anchor.setDx2((int)Math.round(picWidth * 14.75 * DEFAULT_COL_WIDTH / columWidth)); //dx = left + wanted width
    anchor.setDy2((int)Math.round(picHeight * 14.75 * DEFAULT_ROW_HEIGHT / rowHeight)); //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:

Axel I have one more question. I am working with HSSFWorkbook wherein Units.pixelToEMU doesn't work and also I have sheet.getColumnWidth and not width in pixels. How should I deal with the unit conversion in this case and what standard unit should I use ?. — viji shetty, May 14 '18 at 10:11

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

1 Answers1