如何通过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是像素。

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

下面的完整示例将图片放置在高度为100pt、宽度为50个默认字符的单元格B2上，大小为3 cm x 1.5 cm，水平和垂直居中。它既适用于XSSF，也适用于HSSF。

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

 }
}