Apache POI write image and text excel

Question

I am having trouble with writing image and text in a same cell and questions similar to StackOverflow on addding image and text in same cell in excel and POI Excel HSSFPicture Image and ALT TEXT , but the expected output is different and I cannot figure out what wrong with my code? and expected output like as below

Here is my code;

            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");

            //FileInputStream obtains input bytes from the image file
            InputStream inputStream = new FileInputStream(k_pipe_img_file);
            //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();

            ClientAnchor anchor1 = new XSSFClientAnchor();
            anchor1.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);

            anchor1.setCol1(1);
            anchor1.setCol2(1);
            anchor1.setRow1(2);
            anchor1.setRow2(2);

            //Creates a picture
            Picture pict = drawing.createPicture(anchor1, pictureIdx);
            pict.resize(1, 1);

            Row row = sheet.createRow(2);
            row.setHeight((short) 4000);

            sheet.setColumnWidth(0, 4000);
            Cell cell = row.createCell(0, CellType.STRING);
            cell.setCellValue("Task 1");

            sheet.setColumnWidth(1, 5000);
            cell = row.createCell(1, CellType.STRING);
            cell.setCellValue("Replace Kemplon-Pipe");
            CellStyle style=row.getSheet().getWorkbook().createCellStyle();
            style.setVerticalAlignment(VerticalAlignment.TOP);
            cell.setCellStyle(style);

            //Write the Excel file
            FileOutputStream fileOut = new FileOutputStream(k_Task_file);
            workbook.write(fileOut);
            fileOut.close();

Code Output:

image occupies entire cell and text is behind the image.

Is there any possible solution?

Answer 1

In Excel sheets pictures are not in cells but hovers in a layer over the cells. They are anchored to the cells in the following manner:

A one cell anchor determines the upper left position of the picture. If used, the picture must be resized to its native size.

A two cell anchor determines the upper left position and the size of the picture. The first anchor determines the upper left position while the second anchor determines the bottom right position. So the size of the picture is given.

Each anchor can have a row and column given but also a dx and dy. The dx and dy will be added to the column's and row's position to determine the final position. The measurement unit for dx and dy is EMU. Apache poi provides org.apache.poi.util.Units to calculate EMU from points or pixels for example.

One of the problems is that apache poi has a method to get the width of a column in pixels while it only has a method to get the height of the row in points. So we have two different measurement units to be considered.

Another problem is that if we wants to respect the aspect ratio of the picture, then we need determining the native size of the picture file first. There are many questions/answers how to get the native size of a jpg file in Java. All answers are very complex and none is really good in my opinion.

The following example does positioning a picture over cell B3 anchored 20px from left cell border, 20pt from top cell border, in width up to 20px from right cell border and in height up to 10pt from bottom cell border.

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 ExcelDrawImagesOverCell {

 private static void drawImageOnExcelSheet(XSSFSheet sheet, int row, int col, 
  int left/*in px*/, int top/*in pt*/, int width/*in px*/, int height/*in pt*/, int pictureIdx) throws Exception {

  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);
  anchor.setDx1(Units.pixelToEMU(left)); //dx = left in px
  anchor.setDy1(Units.toEMU(top)); //dy = top in pt

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

  drawing.createPicture(anchor, pictureIdx);

 }

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

  Workbook wb = new XSSFWorkbook();

  CellStyle styleVertAlingTop = wb.createCellStyle();
  styleVertAlingTop.setVerticalAlignment(VerticalAlignment.TOP);

  Sheet sheet = wb.createSheet();
  sheet.setColumnWidth(0, 15 * 256); //15 default characters width
  sheet.setColumnWidth(1, 30 * 256); //30 default characters width

  Row row = sheet.createRow(2);
  row.setHeight((short)(100 * 20)); //100pt height * 20 = twips (twentieth of an inch point)

  Cell cell = row.createCell(0);
  cell.setCellValue("Task 1");

  cell = row.createCell(1);
  cell.setCellValue("Replace Kemplon-Pipe");
  cell.setCellStyle(styleVertAlingTop);

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

  int left = 20; // 20px
  int top = 20; // 20pt
  int width = Math.round(sheet.getColumnWidthInPixels(1) - left - left); //width in px
  int height = Math.round(row.getHeightInPoints() - top - 10/*pt*/); //height in pt

  drawImageOnExcelSheet((XSSFSheet)sheet, 2, 1, left, top, width, height, pictureIdx);

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

Result: