How to set image size via HSSFWorkbook

Question

I want to insert an image in my excel workbook using Apache Poi but I can't set size of image. I wrote this code but it's not what I want.

    InputStream inputStream2=new FileInputStream("C:\\Users\\ftk1187\\Desktop\\tec.png");
    byte[] imageBytes2 = IOUtils.toByteArray(inputStream2);
    int pictureureIdx2 = wb.addPicture(imageBytes2, Workbook.PICTURE_TYPE_PNG);
    inputStream2.close();
    CreationHelper helper2 = wb.getCreationHelper();
    Drawing drawing2 = sheet.createDrawingPatriarch();
    ClientAnchor anchor2 = helper2.createClientAnchor();
    anchor2.setDx1(0);
    anchor2.setDy1(0);
    anchor2.setDx2(100);
    anchor2.setDy2(120);
    anchor2.setCol1(8);
    anchor2.setRow1(1);
    Picture pict2 = drawing2.createPicture(anchor2, pictureureIdx2);
    pict2.resize(2);

It scaling the image but I want to resize it with centimeters. I looked some forums but I didn't find anything to solve this problem. Also I want to align this picture to middle of cell. What can I try next?

Answer 1

try {
        
        Resource resource = new ClassPathResource(IMAGE);
        // FileInputStream obtains input bytes from the image file
        java.io.InputStream inputStream = resource.getInputStream();
        // Get the contents of an InputStream as a byte[].
        byte[] bytes = IOUtils.toByteArray(inputStream);
        // Adds a picture to the workbook
        int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
        // close the input stream
        inputStream.close();

        // Returns an object that handles instantiating concrete classes
        CreationHelper helper = workbook.getCreationHelper();

        // Creates the top-level drawing patriarch.
        Drawing drawing = sheet.createDrawingPatriarch();

        // Create an anchor that is attached to the worksheet
        ClientAnchor anchor = helper.createClientAnchor();
        // set top-left corner for the image
        anchor.setCol1(0);
        anchor.setDx1(100);

        // Creates a picture
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        // Reset the image to the original size
        pict.resize();
    } catch (Exception e) {
        // TODO: handle exception
    }

Answer 2

This is not as simple as you might think. Not only that there are many different measurement units to take into account, unfortunately there also are big differences between binary *.xls and Office Open XML *.xlsx file formats.

Best measurement unit to work with here will be pixels. This can be converted to EMU using Units.EMU_PER_PIXEL. So instead of resizing the picture in centimeters, it should be done in pixels. But one can convert centimeter to pixels using following formula:

float pixels = cm / 2.54f * 72f * Units.PIXEL_DPI / Units.POINT_DPI

That is: 1 inch = 2.54 cm, so cm / 2.54 are inches, inches * 72 are points and points * pixel DPI / points DPI are pixels.

If we need working using pixels and want placing the picture horizontal and vertical centered over a cell, of course we also need having the cell width and the row height in pixels. Having this, we can calculate the horizontal and vertical start and end position of the picture over the cell. Then we can set picture's anchor as starting on top left of the cell plus dx1 = horizontal start position and plus dy1 = vertical start position. Also picture's anchor ends on top left of the cell plus dx2 = horizontal end position and plus dy2 = vertical end position.

The following complete example places the picture in size of 3 cm x 1.5 cm horizontal and vertical centered over cell B2 which is 100pt height and 50 default characters width. It works for HSSF as well as for XSSF.

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

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

class CreateExcelPictureOverCell {

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

  Workbook workbook = new HSSFWorkbook(); String filePath = "./Excel.xls";
  //Workbook workbook = new XSSFWorkbook(); String filePath = "./Excel.xlsx";

  Sheet sheet = workbook.createSheet();

  Row row = sheet.createRow(1); // row 2
  float rowHeightInPoints = 100f;
  row.setHeightInPoints(rowHeightInPoints); 
  float rowHeightInPixels = rowHeightInPoints * Units.PIXEL_DPI / Units.POINT_DPI;
  Cell cell = row.createCell(1); // col B
  sheet.setColumnWidth(1, 50*256); // 50 default characters width
  float colWidthInPixels = sheet.getColumnWidthInPixels(1); 

  InputStream inputStream = new FileInputStream("./logo.png");
  byte[] imageBytes = IOUtils.toByteArray(inputStream);
  int pictureureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
  inputStream.close();

  CreationHelper helper = workbook.getCreationHelper();
  Drawing drawing = sheet.createDrawingPatriarch();
  ClientAnchor anchor = helper.createClientAnchor();

  //set start position of picture's anchor to top left B2
  anchor.setRow1(1);
  anchor.setCol1(1);

  //create picture
  Picture pict = drawing.createPicture(anchor, pictureureIdx);

  //get picture's original size
  int pictOriginalWidthInPixels = pict.getImageDimension().width;
  int pictOriginalHeightInPixels = pict.getImageDimension().height;

  //set picture's wanted size
  float pictWidthInCm = 3f;
  float pictWidthInPixels = pictWidthInCm / 2.54f * 72f * Units.PIXEL_DPI / Units.POINT_DPI;
  //want scaling in aspect ratio?
  //float scale = pictWidthInPixels / pictOriginalWidthInPixels;
  //float pictHeightInPixels = pictOriginalHeightInPixels * scale;
  //want explicit set height too?
  float pictHeightInCm = 1.5f;
  float pictHeightInPixels = pictHeightInCm / 2.54f * 72f * Units.PIXEL_DPI / Units.POINT_DPI;

  //calculate the horizontal center position
  int horCenterPosInPixels = Math.round(colWidthInPixels/2f - pictWidthInPixels/2f);
  //set the horizontal center position as Dx1 of anchor
  if (workbook instanceof XSSFWorkbook) {
   anchor.setDx1(horCenterPosInPixels * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
  } else if (workbook 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(horCenterPosInPixels * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75f * DEFAULT_COL_WIDTH / colWidthInPixels));
  }

  //calculate the vertical center position
  int vertCenterPosInPixels = Math.round(rowHeightInPixels/2f - pictHeightInPixels/2f);
  //set the vertical center position as Dy1 of anchor
  if (workbook instanceof XSSFWorkbook) {
   anchor.setDy1(Math.round(vertCenterPosInPixels * Units.EMU_PER_PIXEL)); //in unit EMU for XSSF
  } else if (workbook 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(vertCenterPosInPixels * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightInPixels));
  }

  //set end position of picture's anchor to top left B2
  anchor.setRow2(1);
  anchor.setCol2(1);

  //calculate the horizontal end position of picture
  int horCenterEndPosInPixels = Math.round(horCenterPosInPixels + pictWidthInPixels);
  //set the horizontal end position as Dx2 of anchor
  if (workbook instanceof XSSFWorkbook) {
   anchor.setDx2(horCenterEndPosInPixels * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
  } else if (workbook 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.setDx2(Math.round(horCenterEndPosInPixels * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75f * DEFAULT_COL_WIDTH / colWidthInPixels));
  }  

  //calculate the vertical end position of picture
  int vertCenterEndPosInPixels = Math.round(vertCenterPosInPixels + pictHeightInPixels);
  //set the vertical end position as Dy2 of anchor
  if (workbook instanceof XSSFWorkbook) {
   anchor.setDy2(Math.round(vertCenterEndPosInPixels * Units.EMU_PER_PIXEL)); //in unit EMU for XSSF
  } else if (workbook 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.setDy2(Math.round(vertCenterEndPosInPixels * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightInPixels));
  }

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}