java - 如何将图像集放在excel java单元格的中心

Question

我想将一个图像集放在一个excel单元格的中心，我使用 XSSFClientAnchor 来锚定图片位置，但仍然不像图片1那样工作。



如何将图像设置在单元格的中心，如图 2 所示。

InputStream iStream = new FileInputStream(iList.get(q.getProductID()));
byte[] bytes = IOUtils.toByteArray(iStream);
int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
XSSFDrawing patriarch = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.setCol1(1);
anchor.setRow1(row);
anchor.setCol2(2);
anchor.setRow2(row + 1);
Picture pic = patriarch.createPicture(anchor, pictureIdx);
pic.resize();

Answer 1

图像不是单元格内容，而是将鼠标悬停在称为绘图的单独图层中的工作表上。它们固定在细胞上。一个 ClientAnchor提供以下设置:
col1 = 图片左边缘锚定的列索引。所以图片的左边缘锚定在col1的左列边缘.
dx1 = x 方向的差异。所以图片的左边缘锚定在col1的左列边缘+ dx1 .
row1 =图片顶部边缘锚定的行索引。所以图片的顶部边缘锚定在 row1 的顶部行边缘.
dy1 = y 方向的差异。所以图片的顶部边缘锚定在 row1 的顶部行边缘+ dy1 .
col2 =图片右边缘锚定的列索引。所以图片的右边缘锚定在col2的左列边缘.
dx2 = x 方向的差异。所以图片的右边缘锚定在col1的左列边缘+ dx2 .
row2 =图片底部边缘锚定的行索引。所以图片的底部边缘锚定在 row2 的顶部行边缘.
dy2 = y 方向的差异。所以图片的底部边缘锚定在 row2 的顶部行边缘+ dy2 .

因此，给定一个完整的两单元 anchor ，这决定了图片的位置以及它的大小。

如果图片的大小应该是它的原始大小，那么只需要一个单元 anchor 。有col1 + dx1和 row1 + dy1确定图片左上边缘的位置。大小由图片的原始大小给出。

如果只有 col1和 row1未设置 dx1和 dy1 , 那么图片的左上边缘总是锚定到 col1 的左边缘和 row1 的顶部边缘.因此，如果需要以单元格为中心，则 dx1和 dy1需要计算。计算dx1和 dy1需要知道图片的宽度和高度以及单元格的宽度和高度。听起来很简单，但单元格的宽度和高度有多种不同的测量单位，二进制 BIFF 之间存在很大差异( *.xls ) 文件系统和 Office Open XML ( *.xlsx ) 文件系统。

以下代码提供putPictureCentered将图片放入工作表的绘图中的方法，该方法锚定到 colIdx 给出的单元格和 rowIdx .如果可能，它会计算 dx1和 dy1这样图片就锚定在单元格的中心。它使用像素作为常用的测量单位。它考虑了二进制 BIFF 之间的差异( *.xls ) 文件系统和 Office Open XML ( *.xlsx ) 文件系统。所以它适用于Sheet , 可能是XSSFSheet或 HSSFSheet .

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

 static void putPictureCentered(Sheet sheet, String picturePath, int pictureType, int colIdx, int rowIdx) 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 colIdx/rowIdx, only one cell anchor since bottom right depends on resizing
  CreationHelper helper = wb.getCreationHelper();
  ClientAnchor anchor = helper.createClientAnchor();
  anchor.setCol1(colIdx);
  anchor.setRow1(rowIdx);

  //create a picture anchored to colIdx and rowIdx
  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(colIdx);

  //get the height of row in px
  Row row = sheet.getRow(rowIdx);
  float rowHeightPt = row.getHeightInPoints();
  float rowHeightPx = rowHeightPt * Units.PIXEL_DPI / Units.POINT_DPI;

  //is horizontal centering possible?
  if (pictWidthPx <= columnWidthPx) {

   //calculate the horizontal center position
   int horCenterPosPx = Math.round(columnWidthPx/2f - pictWidthPx/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));
   }

  } else {
   System.out.println("Picture is too width. Horizontal centering is not possible.");
   //TODO: Log instead of System.out.println
  }

  //is vertical centering possible?
  if (pictHeightPx <= rowHeightPx) {

   //calculate the vertical center position
   int vertCenterPosPx = Math.round(rowHeightPx/2f - pictHeightPx/2f);
   //set the vertical center position as Dy1 of anchor
   if (wb instanceof XSSFWorkbook) {
    anchor.setDy1(Math.round(vertCenterPosPx * 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 * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightPx));
   }

  } else {
   System.out.println("Picture is too height. Vertical centering is not possible.");
   //TODO: Log instead of System.out.println
  }

  //resize the picture to it's native size
  pict.resize();

 }

 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 colIdx = 1;
  int colWidth = 20; //in default character widths
  int rowIdx = 1;
  float rowHeight = 100; //in points

  //========================prepare sheet
  //create cell
  Row row = sheet.createRow(rowIdx);
  Cell cell = row.createCell(colIdx);
  //set column width of colIdx in default character widths
  sheet.setColumnWidth(colIdx, colWidth * 256);
  //set row height of rowIdx in points
  row.setHeightInPoints(rowHeight);
  //========================end prepare sheet

  //put image centered
  String picturePath = "./pict100x100.png"; // small image
  //String picturePath = "./pict100x200.png"; // image too height
  //String picturePath = "./pict200x100.png"; // image too width
  //String picturePath = "./pict200x200.png"; // image too big
  putPictureCentered(sheet, picturePath, Workbook.PICTURE_TYPE_PNG, colIdx, rowIdx);

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

 }
}