问Apache POI -在合并区域内保持原始图片比例

EN

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();

 }
}

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