java - 如何通过 HSSFWorkbook 设置图像大小

Question

我想使用 Apache Poi 在 Excel 工作簿中插入图像，但无法设置图像大小。我写了这段代码，但这不是我想要的。

    InputStream inputStream2=new FileInputStream("C:\\Users\\ftk1187\\Desktop\\tec.png");
    byte[] imageBytes2 = IOUtils.toByteArray(inputStream2);
    int pictureureIdx2 = wb.addPicture(imageBytes2, Workbook.PICTURE_TYPE_PNG);
    inputStream2.close();
    CreationHelper helper2 = wb.getCreationHelper();
    Drawing drawing2 = sheet.createDrawingPatriarch();
    ClientAnchor anchor2 = helper2.createClientAnchor();
    anchor2.setDx1(0);
    anchor2.setDy1(0);
    anchor2.setDx2(100);
    anchor2.setDy2(120);
    anchor2.setCol1(8);
    anchor2.setRow1(1);
    Picture pict2 = drawing2.createPicture(anchor2, pictureureIdx2);
    pict2.resize(2);

它缩放图像，但我想以厘米为单位调整它的大小。我查看了一些论坛，但没有找到任何可以解决这个问题的东西。我还想将这张图片与单元格的中间对齐。接下来我可以尝试什么？

Answer 1

这并不像你想象的那么简单。不仅需要考虑许多不同的测量单位，不幸的是二进制 *.xls 和 Office Open XML *.xlsx< 之间也存在很大差异 文件格式。

这里使用的最佳测量单位是像素。可以使用 Units.EMU_PER_PIXEL 将其转换为 EMU。因此，不应以厘米为单位调整图片大小，而应以像素为单位。但是可以使用以下公式将厘米转换为像素:

float pixels = cm / 2.54f * 72f * Units.PIXEL_DPI / Units.POINT_DPI

即:1英寸=2.54厘米，所以厘米/2.54是英寸，英寸*72是点和点*像素DPI/点DPI是像素。

如果我们需要使用像素并希望将图片水平和垂直放置在单元格上，当然我们还需要以像素为单位的单元格宽度和行高。有了这个，我们就可以计算出单元格上图片的水平和垂直开始和结束位置。然后我们可以将图片的 anchor 设置为从单元格的左上角开始加上dx1 =水平起始位置和加上dy1=垂直起始位置。另外，图片的 anchor 结束于单元格的左上角，加上 dx2 = 水平结束位置，加上 dy2 = 垂直结束位置。

以下完整示例将尺寸为 3 厘米 x 1.5 厘米的图片水平和垂直居中放置在单元格 B2 上，单元格高度为 100 pt，默认字符宽度为 50 个。它适用于 HSSF 以及 XSSF。

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;

class CreateExcelPictureOverCell {

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

  Workbook workbook = new HSSFWorkbook(); String filePath = "./Excel.xls";
  //Workbook workbook = new XSSFWorkbook(); String filePath = "./Excel.xlsx";

  Sheet sheet = workbook.createSheet();

  Row row = sheet.createRow(1); // row 2
  float rowHeightInPoints = 100f;
  row.setHeightInPoints(rowHeightInPoints); 
  float rowHeightInPixels = rowHeightInPoints * Units.PIXEL_DPI / Units.POINT_DPI;
  Cell cell = row.createCell(1); // col B
  sheet.setColumnWidth(1, 50*256); // 50 default characters width
  float colWidthInPixels = sheet.getColumnWidthInPixels(1); 

  InputStream inputStream = new FileInputStream("./logo.png");
  byte[] imageBytes = IOUtils.toByteArray(inputStream);
  int pictureureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);
  inputStream.close();

  CreationHelper helper = workbook.getCreationHelper();
  Drawing drawing = sheet.createDrawingPatriarch();
  ClientAnchor anchor = helper.createClientAnchor();

  //set start position of picture's anchor to top left B2
  anchor.setRow1(1);
  anchor.setCol1(1);

  //create picture
  Picture pict = drawing.createPicture(anchor, pictureureIdx);

  //get picture's original size
  int pictOriginalWidthInPixels = pict.getImageDimension().width;
  int pictOriginalHeightInPixels = pict.getImageDimension().height;

  //set picture's wanted size
  float pictWidthInCm = 3f;
  float pictWidthInPixels = pictWidthInCm / 2.54f * 72f * Units.PIXEL_DPI / Units.POINT_DPI;
  //want scaling in aspect ratio?
  //float scale = pictWidthInPixels / pictOriginalWidthInPixels;
  //float pictHeightInPixels = pictOriginalHeightInPixels * scale;
  //want explicit set height too?
  float pictHeightInCm = 1.5f;
  float pictHeightInPixels = pictHeightInCm / 2.54f * 72f * Units.PIXEL_DPI / Units.POINT_DPI;

  //calculate the horizontal center position
  int horCenterPosInPixels = Math.round(colWidthInPixels/2f - pictWidthInPixels/2f);
  //set the horizontal center position as Dx1 of anchor
  if (workbook instanceof XSSFWorkbook) {
   anchor.setDx1(horCenterPosInPixels * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
  } else if (workbook 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(horCenterPosInPixels * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75f * DEFAULT_COL_WIDTH / colWidthInPixels));
  }

  //calculate the vertical center position
  int vertCenterPosInPixels = Math.round(rowHeightInPixels/2f - pictHeightInPixels/2f);
  //set the vertical center position as Dy1 of anchor
  if (workbook instanceof XSSFWorkbook) {
   anchor.setDy1(Math.round(vertCenterPosInPixels * Units.EMU_PER_PIXEL)); //in unit EMU for XSSF
  } else if (workbook 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(vertCenterPosInPixels * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightInPixels));
  }

  //set end position of picture's anchor to top left B2
  anchor.setRow2(1);
  anchor.setCol2(1);

  //calculate the horizontal end position of picture
  int horCenterEndPosInPixels = Math.round(horCenterPosInPixels + pictWidthInPixels);
  //set the horizontal end position as Dx2 of anchor
  if (workbook instanceof XSSFWorkbook) {
   anchor.setDx2(horCenterEndPosInPixels * Units.EMU_PER_PIXEL); //in unit EMU for XSSF
  } else if (workbook 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(horCenterEndPosInPixels * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75f * DEFAULT_COL_WIDTH / colWidthInPixels));
  }  

  //calculate the vertical end position of picture
  int vertCenterEndPosInPixels = Math.round(vertCenterPosInPixels + pictHeightInPixels);
  //set the vertical end position as Dy2 of anchor
  if (workbook instanceof XSSFWorkbook) {
   anchor.setDy2(Math.round(vertCenterEndPosInPixels * Units.EMU_PER_PIXEL)); //in unit EMU for XSSF
  } else if (workbook 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(vertCenterEndPosInPixels * Units.PIXEL_DPI / Units.POINT_DPI * 14.75f * DEFAULT_ROW_HEIGHT / rowHeightInPixels));
  }

  FileOutputStream out = new FileOutputStream(filePath);
  workbook.write(out);
  out.close();
  workbook.close();

 }
}