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]));
}
}
...
