使用 poi 在 excel 中的同一单元格中添加图像和文本，然后图像覆盖文本 [英] on adding image and text in same cell in excel using poi then image override the text

问题描述

我正在尝试使用

 anchor.setCol1(1);
     anchor.setRow1(1); 
     anchor.setCol2(2); 
     anchor.setRow2(1); 
     Picture pict = drawing.createPicture(anchor, pictureIdx);
     pict.resize();

但图像覆盖了该单元格中的文本.有没有办法在单个单元格中一个接一个地添加带有文本的图像而无需多行.我也使用过 autoSizeColumn() 方法，但没有解决.

but the the image override the text present in that cell. Is there any way to add image with text one after other in single cell without having in multiple line. I have also used autoSizeColumn() method but not worked out.

推荐答案

在 Excel 工作表中，图片不在单元格中，而是悬停在单元格上方的图层中.它们以下列方式锚定到单元格:

In Excel sheets pictures are not in cells but hovers in a layer over the cells. They are anchored to the cells in the following manner:

一个单元格锚点决定了图片的左上角位置.如果使用，图片必须调整为原始大小.

A one cell anchor determines the upper left position of the picture. If used, the picture must be resized to its native size.

两个单元格的锚点决定了图片的左上位置和.第一个锚点确定左上角的位置，而第二个锚点确定右下角的位置.所以尺寸是给定的.

A two cell anchor determines the upper left position and the size of the picture. The first anchor determines the upper left position while the second anchor determines the bottom right position. So the size is given.

每个锚点可以有一行和一列，但也有 dx 和 dy.dx 和 dy 将被添加到列和行的位置以确定最终位置.dx 和 dy 的度量单位是 EMU.Apache poi 提供了 org.apache.poi.util.Units 来计算 EMU 例如从像素.

Each anchor can have a row and column given but also a dx and dy. The dx and dy will be added to the column's and row's position to determine the final position. The measurement unit for dx and dy is EMU. Apache poi provides org.apache.poi.util.Units to calculate EMU from pixels for example.

示例:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;

public class ExcelDrawImagesOnCellLeft {

 private static void drawImageOnExcelSheet(XSSFSheet sheet, int row, int col, 
  int height, int width, int pictureIdx) throws Exception {

  CreationHelper helper = sheet.getWorkbook().getCreationHelper();

  Drawing drawing = sheet.createDrawingPatriarch();

  ClientAnchor anchor = helper.createClientAnchor();
  anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

  anchor.setCol1(col); //first anchor determines upper left position
  anchor.setRow1(row);

  anchor.setRow2(row); //second anchor determines bottom right position
  anchor.setCol2(col);
  anchor.setDx2(Units.toEMU(width)); //dx = left + wanted width
  anchor.setDy2(Units.toEMU(height)); //dy= top + wanted height

  drawing.createPicture(anchor, pictureIdx);

 }

 public static void main(String[] args) throws Exception {
  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet();

  InputStream is = new FileInputStream("samplePict.jpeg");
  byte[] bytes = IOUtils.toByteArray(is);
  int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
  is.close();

  String gap = "      ";

  for (int r = 0; r < 10; r++ ) {
   sheet.createRow(r).createCell(1).setCellValue(gap + "Picture " + (r+1));
   drawImageOnExcelSheet((XSSFSheet)sheet, r, 1, 12, 12, pictureIdx);
  }

  sheet.autoSizeColumn(1);

  wb.write(new FileOutputStream("ExcelDrawImagesOnCellLeft.xlsx"));
  wb.close();
 }
}

结果:

这篇关于使用 poi 在 excel 中的同一单元格中添加图像和文本，然后图像覆盖文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！