java - Apache Poi，在一个单元格中插入具有不同 anchor 属性的 2 张图像

Question

我想在一个单元格中插入两张图像，一张图像贴在单元格的左边框上，另一张贴在单元格的右边框上。

我尝试过设置

anchor.setDx1(-1)   // multiple tries with 1 - 1, 2, -2 and so on
anchor.setDy1(-1)
anchor.setDx2(-1)
anchot.setDy2(-1)

使用不同的组合，图像仍然粘在单元格的左上角。

我想要与下图中类似的

Answer 1

需要一点数学知识来计算所需的 Dx 和 Dy。

我们需要图片尺寸、列宽和列高。第一张图片的左上边缘位于单元格的左上边缘。第一张图片的右下边缘位于单元格的底部，距离单元格左边缘的距离是图片的宽度。第二张图片的左上边缘位于单元格的顶部，距离单元格右边缘的距离是图片的宽度。第二张图片的右下边缘位于单元格的右下角。

示例:

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.util.IOUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;

class CreateExcelTwoPicturesInOneCell {

 static String excelPath = "./ExcelWithTwoPicturesInOneCell.xlsx";
 static String[] pictureFileNames = new String[]{"./Koala.png", "./Hydrangeas.jpg"};
 static int[] pictureTypes = new int[]{Workbook.PICTURE_TYPE_PNG, Workbook.PICTURE_TYPE_JPEG};
 static int pictureWidthPx = 100;
 static int pictureHeightPx = 100;
 static XSSFWorkbook workbook;
 static XSSFSheet sheet;

 static void addImage(int col1, int row1, int col2, int row2, 
  int dx1, int dy1, int dx2, int dy2,
  String imageFileName, int pictureType, ClientAnchor.AnchorType anchorType) throws Exception {

  InputStream imageInputStream = new FileInputStream(imageFileName);
  byte[] bytes = IOUtils.toByteArray(imageInputStream);
  int pictureId = workbook.addPicture(bytes, pictureType);
  imageInputStream.close();
  XSSFClientAnchor anchor = workbook.getCreationHelper().createClientAnchor();
  anchor.setAnchorType(anchorType);
  // set Col1, Dx1, Row1, Dy1, Col2, Dx2, Row2, Dy2
  // only this determines the picture's size then
  anchor.setCol1(col1);
  anchor.setDx1(dx1);
  anchor.setRow1(row1);
  anchor.setDy1(dy1);
  anchor.setCol2(col2);
  anchor.setDx2(dx2);
  anchor.setRow2(row2);
  anchor.setDy2(dy2);
  XSSFDrawing drawing = sheet.createDrawingPatriarch();
  XSSFPicture picture = drawing.createPicture(anchor, pictureId);

 }

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

  workbook = new XSSFWorkbook();
  sheet = workbook.createSheet();

  sheet.setColumnWidth(0, 50*256);
  int columnWidthPx = Math.round(sheet.getColumnWidthInPixels(0));
  sheet.createRow(0).setHeightInPoints((float)Units.pixelToPoints(pictureHeightPx));

  addImage(0, 0, 0, 0, /*all fits in cell A1*/
   /*Dx1 = 0 and Dy1 = 0*/
   Units.pixelToEMU(0), Units.pixelToEMU(0), 
   /*Dx2 is picture's width and Dy2 is picture's height*/
   Units.pixelToEMU(pictureWidthPx), Units.pixelToEMU(pictureHeightPx),
   pictureFileNames[0], pictureTypes[0], ClientAnchor.AnchorType.MOVE_AND_RESIZE);

  addImage(0, 0, 0, 0, /*all fits in cell A1*/
   /*Dx1 = cell's width-picture's width and Dy1 = 0*/
   Units.pixelToEMU(columnWidthPx - pictureWidthPx), Units.pixelToEMU(0), 
   /*Dx2 is cell's width and Dy2 is picture's height*/
   Units.pixelToEMU(columnWidthPx), Units.pixelToEMU(pictureHeightPx),
   pictureFileNames[1], pictureTypes[1], ClientAnchor.AnchorType.MOVE_AND_RESIZE);

  FileOutputStream fos = new FileOutputStream(excelPath);
  workbook.write(fos);
  fos.close();
  workbook.close();

 }
}

Excel 中的结果为:

但您的屏幕截图似乎是 Google Sheets 而不是 Excel。 Google 表格可能会使用 Excel 之外的其他类型的 anchor 。因此 Google 表格中的结果可能会有所不同。

<小时/>

Google 表格有一个新的“功能”，可以在单元格中插入图像，而不是在悬停在单元格上方的绘图层中插入图像。但这只允许每个单元格一个图像。 Google 表格似乎根据图像是否完全适合单元格边框来决定图像是否在单元格中。因此，为了避免这种情况，图片边缘和单元格边框之间必须有间隙。

如果我将上面的代码更改如下，那么结果就是 Google 表格中一个单元格中的两个图像。

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

  workbook = new XSSFWorkbook();
  sheet = workbook.createSheet();

  sheet.setColumnWidth(0, 50*256);
  int columnWidthPx = Math.round(sheet.getColumnWidthInPixels(0));
  sheet.createRow(0).setHeightInPoints((float)Units.pixelToPoints(pictureHeightPx));

  int gapBetwPicAndCellBordPx = 5;

  addImage(0, 0, 0, 0, /*all fits in cell A1*/
   /*Dx1 = gapBetwPicAndCellBordPx and Dy1 = gapBetwPicAndCellBordPx*/
   Units.pixelToEMU(gapBetwPicAndCellBordPx), Units.pixelToEMU(gapBetwPicAndCellBordPx), 
   /*Dx2 is picture's width and Dy2 is picture's height - gapBetweenPictureAndCellBorderPx*/
   Units.pixelToEMU(pictureWidthPx-gapBetwPicAndCellBordPx), Units.pixelToEMU(pictureHeightPx-gapBetwPicAndCellBordPx),
   pictureFileNames[0], pictureTypes[0], ClientAnchor.AnchorType.MOVE_AND_RESIZE);

  addImage(0, 0, 0, 0, /*all fits in cell A1*/
   /*Dx1 = cell's width-picture's width+gapBetwPicAndCellBordPx and Dy1 = gapBetwPicAndCellBordPx*/
   Units.pixelToEMU(columnWidthPx - pictureWidthPx + gapBetwPicAndCellBordPx), Units.pixelToEMU(gapBetwPicAndCellBordPx), 
   /*Dx2 is cell's width - gapBetwPicAndCellBordPx and Dy2 is picture's height - gapBetwPicAndCellBordPx*/
   Units.pixelToEMU(columnWidthPx - gapBetwPicAndCellBordPx), Units.pixelToEMU(pictureHeightPx - gapBetwPicAndCellBordPx),
   pictureFileNames[1], pictureTypes[1], ClientAnchor.AnchorType.MOVE_AND_RESIZE);

  FileOutputStream fos = new FileOutputStream(excelPath);
  workbook.write(fos);
  fos.close();
  workbook.close();

 }
...

Google 表格中的结果: