java - apache poi XSSFClientAnchor not positioning picture with respect to dx1, dy1, dx2, dy2

Question

I am trying to add an image to excel using apach-poi version 3.16. I am able to do that with HSSFWorkbook and XSSFWorkbook. But when i am trying to add spacing for the image i.e if I set dx1, dy1, dx2, dy2 coordinates on XSSFClientAnchor it is not taking effect. Same thing is working on HSSFClientAnchor. I am attaching both classes and corresponding excel file generated. Could you please help me how can i do achieve the same result using XSSFClientAnchor.

HSSF Class

package poisamples;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class HSSFImage {
    public static void main(String[] args) throws IOException {
        String imageFile = "test.png";
        String outputFile = "image-sutpid.xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Image");
        HSSFClientAnchor anchor = new HSSFClientAnchor(100,100,100,100,(short)0, (short)0, (short)0, (short)3);

        sheet.setColumnWidth(0, 6000);

        anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
        int index = sheet.getWorkbook().addPicture(imageToBytes(imageFile), HSSFWorkbook.PICTURE_TYPE_PNG);

        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        HSSFPicture picture = patriarch.createPicture(anchor, index);
        picture.resize();
        FileOutputStream fos = new FileOutputStream(outputFile);
        workbook.write(fos);
    }

    private static byte[] imageToBytes(String imageFilename) throws IOException {
        File imageFile;
        FileInputStream fis = null;
        ByteArrayOutputStream bos;
        int read;
        try {
            imageFile = new File(imageFilename);
            fis = new FileInputStream(imageFile);
            bos = new ByteArrayOutputStream();
            while ((read = fis.read()) != -1) {
                bos.write(read);
            }
            return (bos.toByteArray());
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                    fis = null;
                } catch (IOException ioEx) {
                    // Nothing to do here
                }
            }
        }
    }
}

XSSF Class

package poisamples;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;

public class XSSFImage {
    public static void main(String[] args) throws IOException {
        String imageFile = "test.png";
        String outputFile = "image-sutpid.xlsx";
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Image");
        XSSFClientAnchor anchor = new XSSFClientAnchor(100,100,100,100,0, 0, 0, 3);

        sheet.setColumnWidth(0, 6000);

        anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
        int index = sheet.getWorkbook().addPicture(imageToBytes(imageFile), XSSFWorkbook.PICTURE_TYPE_PNG);

        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        XSSFPicture picture = patriarch.createPicture(anchor, index);
        picture.resize();
        FileOutputStream fos = new FileOutputStream(outputFile);
        workbook.write(fos);
    }

    private static byte[] imageToBytes(String imageFilename) throws IOException {
        File imageFile;
        FileInputStream fis = null;
        ByteArrayOutputStream bos;
        int read;
        try {
            imageFile = new File(imageFilename);
            fis = new FileInputStream(imageFile);
            bos = new ByteArrayOutputStream();
            while ((read = fis.read()) != -1) {
                bos.write(read);
            }
            return (bos.toByteArray());
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                    fis = null;
                } catch (IOException ioEx) {
                    // Nothing to do here
                }
            }
        }
    }
}

HSSF Result:

XSSF Result:

Image used:

Answer 1

The problems are the different strange measurement units which Microsoft is using and the fact that the binary file system *.xls and the Office Open XML *.xlsx are very different not only in file storing but in general approaches also.

As mentioned in ClientAnchor: "Note - XSSF and HSSF have a slightly different coordinate system, values in XSSF are larger by a factor of Units.EMU_PER_PIXEL". But this is not the whole truth. The meaning of the dx and dy is totally different. In the binary file system *.xls, the values are dependent on the factor of column-width / default column-width and row-height / default row-height. Don't ask me about the factor 14.75 used in my example. It is just trial&error.

To mention about your code is that if you wants resizing the picture to its native size, then only a one cell anchor is needed. This anchors the picture's upper left edge. A two cell anchor only is needed if the anchor shall determining the picture's size. Then the first cell in the anchor anchors the picture's upper left edge while the second cell in the anchor anchors the picture's bottom right edge.

The following example uses the measurement unit 1/256th of a character width for dx since column widths also are in this measurement unit. And it uses point as measurement unit for dy since row heights also are in this measurement unit.

import java.io.*;

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

import org.apache.poi.util.Units;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;

public class CreateExcelWithPictures {

 private static Picture drawImageOnExcelSheet(Sheet sheet, 
  int col1, int row1, int dx1/*1/256th of a character width*/, int dy1/*points*/,
  int col2, int row2, int dx2/*1/256th of a character width*/, int dy2/*points*/, 
  String pictureurl, int picturetype, boolean resize) throws Exception {

  int DEFAULT_COL_WIDTH = 10 * 256;
  float DEFAULT_ROW_HEIGHT = 12.75f;

  Row row = sheet.getRow(row1);
  float rowheight1 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
  row = sheet.getRow(row2);
  float rowheight2 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;

  int colwidth1 = sheet.getColumnWidth(col1);
  int colwidth2 = sheet.getColumnWidth(col2);

  InputStream is = new FileInputStream(pictureurl);
  byte[] bytes = IOUtils.toByteArray(is);
  int pictureIdx = sheet.getWorkbook().addPicture(bytes, picturetype);
  is.close();

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

  Drawing drawing = sheet.createDrawingPatriarch();

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

  anchor.setRow1(row1); //first anchor determines upper left position
  if (sheet instanceof XSSFSheet) {
   anchor.setDy1(dy1 * Units.EMU_PER_POINT);
  } else if (sheet instanceof HSSFSheet) {
   anchor.setDy1((int)Math.round(dy1 * Units.PIXEL_DPI / Units.POINT_DPI * 14.75 * DEFAULT_ROW_HEIGHT / rowheight1));
  }
  anchor.setCol1(col1); 
  if (sheet instanceof XSSFSheet) {
   anchor.setDx1((int)Math.round(dx1 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
  } else if (sheet instanceof HSSFSheet) {
   anchor.setDx1((int)Math.round(dx1 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH / colwidth1));
  }

  if (!resize) {
   anchor.setRow2(row2); //second anchor determines bottom right position
   if (sheet instanceof XSSFSheet) {
    anchor.setDy2(dy2 * Units.EMU_PER_POINT);
   } else if (sheet instanceof HSSFSheet) {
    anchor.setDy2((int)Math.round(dy2 * Units.PIXEL_DPI / Units.POINT_DPI * 14.75 * DEFAULT_ROW_HEIGHT / rowheight2));
   }
   anchor.setCol2(col2);
   if (sheet instanceof XSSFSheet) {
    anchor.setDx2((int)Math.round(dx2 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
   } else if (sheet instanceof HSSFSheet) {
    anchor.setDx2((int)Math.round(dx2 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH / colwidth2));
   }
  }

  Picture picture = drawing.createPicture(anchor, pictureIdx);

  if (resize) picture.resize();

  return picture;
 }

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

  Workbook workbook = new XSSFWorkbook();
  //Workbook workbook = new HSSFWorkbook();

  Sheet sheet = workbook.createSheet("Sheet1");
  sheet.setColumnWidth(1, 6000/*1/256th of a character width*/);

  Row row = sheet.createRow(0);
  row.setHeightInPoints(100/*points*/);

  row = sheet.createRow(10);
  row.setHeightInPoints(50/*points*/);

  Picture picture;

  //two cell anchor in the same cell (B1) used without resizing the picture
  picture = drawImageOnExcelSheet(sheet, 
   1, 0, 1000/*1/256th of a character width*/, 10/*points*/, 
   1, 0, 5000/*1/256th of a character width*/, 90/*points*/, 
   "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);

  //one cell anchor (B3) used with resizing the picture
  picture = drawImageOnExcelSheet(sheet, 
   1, 2, 1000/*1/256th of a character width*/, 10/*points*/, 
   0, 0, 0, 0, 
   "mikt1.png", Workbook.PICTURE_TYPE_PNG, true);

  //two cell anchor (B10 to B12) used without resizing the picture
  picture = drawImageOnExcelSheet(sheet, 
   1, 9, 1000/*1/256th of a character width*/, 10/*points*/, 
   1, 11, 5000/*1/256th of a character width*/, 10/*points*/, 
   "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);

  if (workbook instanceof XSSFWorkbook) {
   workbook.write(new FileOutputStream("image-sutpid.xlsx"));
  } else if (workbook instanceof HSSFWorkbook) {
   workbook.write(new FileOutputStream("image-sutpid.xls"));
  }
  workbook.close();

 }

}