Search code examples
javaapache-poi

Apache POI - Keep original picture scale while fitting within a merged region

I need to fit a picture into the center of a merged cell region. The merged cell region is always only one column wide, but multiple rows high.

If the picture does not fit inside the cell region, it needs to resize, while keeping the original proportions.

    int pictureIdx = wb.addPicture(bytes, pictype);
    CreationHelper helper = wb.getCreationHelper();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = helper.createClientAnchor();
    anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
    anchor.setCol1(column);
    anchor.setRow1(row);
    anchor.setRow2(row+rowspan);
    anchor.setCol2(column+1);
    Picture pict = drawing.createPicture(anchor, pictureIdx);
    pict.resize();

    short rowHeight = (short)0;
    for (int i = row; i<row+rowspan; i++ ) {
        rowHeight += sheet.getRow(i).getHeight();
    }
    int rowHeightPx = heightUnits2Pixel(rowHeight); // (from [https://stackoverflow.com/a/31837639/1320704][1] )
    int columnWidthPx = (int) (sheet.getColumnWidthInPixels(column));
    int pictWidthPx = pict.getImageDimension().width;
    int pictHeightPx = pict.getImageDimension().height;
    float scale = 1;
    if (pictHeightPx > rowHeightPx) {
        float tmpscale = (float)rowHeightPx / (float)pictHeightPx;
        if (tmpscale < scale) scale = tmpscale;
    }
    if (pictWidthPx > columnWidthPx) {
        float tmpscale = (float)columnWidthPx / (float)pictWidthPx;
        if (tmpscale < scale) scale = tmpscale;
    }
    anchor.setDx1(
        (int) ((sheet.getColumnWidthInPixels(column)- pict.getImageDimension().width) /2)
    );
    pict.resize(scale, scale);

Here is the result:

enter image description here

As you can see I did not get the desired effect. This picture is a square (256x256) but somehow it got distorted even though scale is passed the same for x and y. Also the height does not match. And it is not centered.

For comparison, here is the same picture in the original size:

enter image description here

And here is what I actually want to achieve:

enter image description here

Solution

  • First problem is that if you wants using Picture.resize, then create an anchor with upper left cell Col1 and Row1 only. Only a one cell anchor since bottom right anchor position depends on resizing.

    Second problem is that you should do the resizing only after upper left anchor position is well known inclusive Dx1 and Dy1.

    But main problem is that your heightUnits2Pixel does not get the correct pixel heights of the rows. There is Row.getHeightInPoints which should better be used. This gets the row height in pt but this can be converted to px using rowHeightPt * Units.PIXEL_DPI / Units.POINT_DPI. See https://poi.apache.org/apidocs/dev/org/apache/poi/util/Units.html.

    Using that at least the scale factor gets calculated correct because it now has the correct row heights in pixel.

    To set the Dx1 of the anchor being the horizontal center position there is an additional problem. The meanings of Dx and Dy are very different between XSSF and HSSF.

    In XSSF it is the horizontal center position in unit EMU. So you need calculating horCenterPosPx * Units.EMU_PER_PIXEL for Dx. See https://poi.apache.org/apidocs/dev/org/apache/poi/util/Units.html.

    In HSSF the values of Dx are dependent on the factor of column-width / default column-width and the values of Dy are dependent on the factor of row-height / default row-height. See apache poi XSSFClientAnchor not positioning picture with respect to dx1, dy1, dx2, dy2.

    For calculating the vertical center position at first the correct Row1 of the anchor neeeds to be determined. Even if merged, there are different rows present. So Row1 of the anchor needs to be the correct row where the picture shall start. The remaining pixels up to the vertical center position are the Dy1 then.

    Following complete example works for me. It puts any kind of images of different size scaled and centered into the merged range A3:A12. 

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

import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;

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

class CenterImageTest {

 static void putPictureCentered(Sheet sheet, String picturePath, int pictureType, int column, int startRow, int rowspan) throws Exception {
  Workbook wb = sheet.getWorkbook();

  //load the picture
  InputStream inputStream = new FileInputStream(picturePath);
  byte[] bytes = IOUtils.toByteArray(inputStream);
  int pictureIdx = wb.addPicture(bytes, pictureType);
  inputStream.close();

  //create an anchor with upper left cell column/startRow, only one cell anchor since bottom right depends on resizing
  CreationHelper helper = wb.getCreationHelper();
  ClientAnchor anchor = helper.createClientAnchor();
  anchor.setCol1(column);
  anchor.setRow1(startRow);

  //create a picture anchored to Col1 and Row1
  Drawing drawing = sheet.createDrawingPatriarch();
  Picture pict = drawing.createPicture(anchor, pictureIdx);

  //get the picture width in px
  int pictWidthPx = pict.getImageDimension().width;
  //get the picture height in px
  int pictHeightPx = pict.getImageDimension().height;

  //get column width of column in px
  float columnWidthPx = sheet.getColumnWidthInPixels(column);

  //get the heights of all merged rows in px
  float[] rowHeightsPx = new float[startRow+rowspan];
  float rowsHeightPx = 0f;
  for (int r = startRow; r < startRow+rowspan; r++) {
   Row row = sheet.getRow(r);
   float rowHeightPt = row.getHeightInPoints();
   rowHeightsPx[r-startRow] = rowHeightPt * Units.PIXEL_DPI / Units.POINT_DPI;
   rowsHeightPx += rowHeightsPx[r-startRow];
  }

  //calculate scale
  float scale = 1;
  if (pictHeightPx > rowsHeightPx) {
   float tmpscale = rowsHeightPx / (float)pictHeightPx;
   if (tmpscale < scale) scale = tmpscale;
  }
  if (pictWidthPx > columnWidthPx) {
   float tmpscale = columnWidthPx / (float)pictWidthPx;
   if (tmpscale < scale) scale = tmpscale;
  }

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

  //calculate the vertical center position
  int vertCenterPosPx = Math.round(rowsHeightPx/2f - pictHeightPx*scale/2f);
  //get Row1
  Integer row1 = null;
  rowsHeightPx = 0f;
  for (int r = 0; r < rowHeightsPx.length; r++) {
   float rowHeightPx = rowHeightsPx[r];
   if (rowsHeightPx + rowHeightPx > vertCenterPosPx) {
    row1 = r + startRow;
    break;
   }
   rowsHeightPx += rowHeightPx;
  }
  //set the vertical center position as Row1 plus Dy1 of anchor
  if (row1 != null) {
   anchor.setRow1(row1);
   if (wb instanceof XSSFWorkbook) {
    anchor.setDy1(Math.round(vertCenterPosPx - rowsHeightPx) * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
   } else if (wb 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((vertCenterPosPx - rowsHeightPx) * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightsPx[row1]));
   }
  }

  //resize the picture to it's native size
  pict.resize();
  //if it must scaled down, then scale
  if (scale < 1) {
   pict.resize(scale);
  }
 }

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

  Workbook wb = new HSSFWorkbook(); String resultName = "CenterImageTest.xls";
  //Workbook wb = new XSSFWorkbook(); String resultName = "CenterImageTest.xlsx";
  Sheet sheet = wb.createSheet("Sheet1");

  int column = 2;
  int columnWidth = 100; //in default character widths
  int startRow = 2;
  int rowspan = 10;

  //========================prepare sheet
  //create cell A1 and set cell value
  Row row = sheet.createRow(1);
  Cell cell = row.createCell(column);
  cell.setCellValue("Picture of product");

  //create the cells with different heights
  for (int r = startRow; r < startRow+rowspan; r++) {
   row = sheet.createRow(r);
   row.setHeightInPoints(12 + r*2);
  }
  //merge cells
  sheet.addMergedRegion(new CellRangeAddress(startRow,startRow+rowspan-1,column,column));
  //========================end prepare sheet

  //set column width of column in default character widths
  sheet.setColumnWidth(column, columnWidth * 256);
  //put image centered
  //String picturePath = "./logo.png"; // small image
  //putPictureCentered(sheet, picturePath, Workbook.PICTURE_TYPE_PNG, column, startRow, rowspan);
  String picturePath = "./Bilder/Sample-jpg-image-1.jpg"; // bigger image
  putPictureCentered(sheet, picturePath, Workbook.PICTURE_TYPE_JPEG, column, startRow, rowspan);

  FileOutputStream fileOut = new FileOutputStream(resultName);
  wb.write(fileOut);
  fileOut.close();
  wb.close();

 }
}

    There seems to be a bug in Picture.resize if the picture is small and Dx1is set for the anchor. The scale factor scale gets correctly calculated from this code but pict.resize(scale) does not scale height and width proportionally. It scales the height using the correct factor. But it scales the width not using the same factor but a much bigger factor.

    I have filed bug https://bz.apache.org/bugzilla/show_bug.cgi?id=64213.

    To avoid the buggy Picture.resize we could calculating the full two cells anchor. We have Col1 and already are calculating Dx1 and Row1 and Dy1 to have the top left point of the picture. Now we could additional calculating Dx2 and Row2and Dy2 to have the bottom right point of the picture too.

    Example im my putPictureCentered from above:

    ...
/*
  //resize the picture to it's native size
  pict.resize();
  //if it must scaled down, then scale
  if (scale < 1) {
   pict.resize(scale);
  }
*/

  //set Col2 of anchor the same as Col1 as all is in one column
  anchor.setCol2(column);

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

  //calculate the vertical end position of picture
  int vertCenterEndPosPx = Math.round(vertCenterPosPx + pictHeightPx*scale);
  //get Row2
  Integer row2 = null;
  rowsHeightPx = 0f;
  for (int r = 0; r < rowHeightsPx.length; r++) {
   float rowHeightPx = rowHeightsPx[r];
   if (rowsHeightPx + rowHeightPx > vertCenterEndPosPx) {
    row2 = r + startRow;
    break;
   }
   rowsHeightPx += rowHeightPx;
  }

  //set the vertical end position as Row2 plus Dy2 of anchor
  if (row2 != null) {
   anchor.setRow2(row2);
   if (wb instanceof XSSFWorkbook) {
    anchor.setDy2(Math.round(vertCenterEndPosPx - rowsHeightPx) * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
   } else if (wb 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((vertCenterEndPosPx - rowsHeightPx) * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightsPx[row1]));
   }
  }
...