apache poi XSSFClientAnchor 未相对于 dx1、dy1、dx2、dy2 定位图片

我正在尝试使用添加图像到Excelapache-poi 版本3.16。我能够做到这一点HSSFWorkbook and XSSFWorkbook。但是当我尝试为图像添加间距时，即如果我设置dx1, dy1, dx2, dy2坐标在XSSFClientAnchor它没有生效。同样的事情正在做HSSFClientAnchor。我附上这两个类以及生成的相应 excel 文件。你能帮我吗我怎样才能达到相同的结果XSSFClientAnchor.

HSSF级

package poisamples;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class HSSFImage {
    public static void main(String[] args) throws IOException {
        String imageFile = "test.png";
        String outputFile = "image-sutpid.xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Image");
        HSSFClientAnchor anchor = new HSSFClientAnchor(100,100,100,100,(short)0, (short)0, (short)0, (short)3);

        sheet.setColumnWidth(0, 6000);

        anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
        int index = sheet.getWorkbook().addPicture(imageToBytes(imageFile), HSSFWorkbook.PICTURE_TYPE_PNG);

        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        HSSFPicture picture = patriarch.createPicture(anchor, index);
        picture.resize();
        FileOutputStream fos = new FileOutputStream(outputFile);
        workbook.write(fos);
    }

    private static byte[] imageToBytes(String imageFilename) throws IOException {
        File imageFile;
        FileInputStream fis = null;
        ByteArrayOutputStream bos;
        int read;
        try {
            imageFile = new File(imageFilename);
            fis = new FileInputStream(imageFile);
            bos = new ByteArrayOutputStream();
            while ((read = fis.read()) != -1) {
                bos.write(read);
            }
            return (bos.toByteArray());
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                    fis = null;
                } catch (IOException ioEx) {
                    // Nothing to do here
                }
            }
        }
    }
}

XSSF类

package poisamples;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;

public class XSSFImage {
    public static void main(String[] args) throws IOException {
        String imageFile = "test.png";
        String outputFile = "image-sutpid.xlsx";
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Image");
        XSSFClientAnchor anchor = new XSSFClientAnchor(100,100,100,100,0, 0, 0, 3);

        sheet.setColumnWidth(0, 6000);

        anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
        int index = sheet.getWorkbook().addPicture(imageToBytes(imageFile), XSSFWorkbook.PICTURE_TYPE_PNG);

        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        XSSFPicture picture = patriarch.createPicture(anchor, index);
        picture.resize();
        FileOutputStream fos = new FileOutputStream(outputFile);
        workbook.write(fos);
    }

    private static byte[] imageToBytes(String imageFilename) throws IOException {
        File imageFile;
        FileInputStream fis = null;
        ByteArrayOutputStream bos;
        int read;
        try {
            imageFile = new File(imageFilename);
            fis = new FileInputStream(imageFile);
            bos = new ByteArrayOutputStream();
            while ((read = fis.read()) != -1) {
                bos.write(read);
            }
            return (bos.toByteArray());
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                    fis = null;
                } catch (IOException ioEx) {
                    // Nothing to do here
                }
            }
        }
    }
}

HSSF 结果：

XSSF 结果：

使用的图像：

问题是微软使用的不同奇怪的测量单位以及二进制文件系统的事实*.xls和 Office Open XML*.xlsx不仅在文件存储方面而且在一般方法方面也有很大不同。

正如中提到的客户锚 https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/ClientAnchor.html：“注意 - XSSF 和 HSSF 的坐标系略有不同，XSSF 中的值要大出 Units.EMU_PER_PIXEL 倍”。但这并不是全部事实。的含义dx and dy是完全不同的。在二进制文件系统中*.xls，这些值取决于以下因素column-width / default column-width and row-height / default row-height。别问我这个因素14.75在我的例子中使用。这只是反复试验。

值得一提的是，如果您想将图片大小调整为其原始大小，则只需要一个单元格锚点。这将固定图片的左上边缘。仅当锚点确定图片的大小时才需要两个单元锚点。然后，锚点中的第一个单元格锚定图片的左上边缘，而锚点中的第二个单元格锚定图片的右下边缘。

以下示例使用测量单位1/256th of a character width for dx因为列宽也采用此测量单位。它使用point作为测量单位dy因为行高也采用此测量单位。

import java.io.*;

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

import org.apache.poi.util.Units;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;

public class CreateExcelWithPictures {

 private static Picture drawImageOnExcelSheet(Sheet sheet, 
  int col1, int row1, int dx1/*1/256th of a character width*/, int dy1/*points*/,
  int col2, int row2, int dx2/*1/256th of a character width*/, int dy2/*points*/, 
  String pictureurl, int picturetype, boolean resize) throws Exception {

  int DEFAULT_COL_WIDTH = 10 * 256;
  float DEFAULT_ROW_HEIGHT = 12.75f;

  Row row = sheet.getRow(row1);
  float rowheight1 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
  row = sheet.getRow(row2);
  float rowheight2 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;

  int colwidth1 = sheet.getColumnWidth(col1);
  int colwidth2 = sheet.getColumnWidth(col2);

  InputStream is = new FileInputStream(pictureurl);
  byte[] bytes = IOUtils.toByteArray(is);
  int pictureIdx = sheet.getWorkbook().addPicture(bytes, picturetype);
  is.close();

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

  Drawing drawing = sheet.createDrawingPatriarch();

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

  anchor.setRow1(row1); //first anchor determines upper left position
  if (sheet instanceof XSSFSheet) {
   anchor.setDy1(dy1 * Units.EMU_PER_POINT);
  } else if (sheet instanceof HSSFSheet) {
   anchor.setDy1((int)Math.round(dy1 * Units.PIXEL_DPI / Units.POINT_DPI * 14.75 * DEFAULT_ROW_HEIGHT / rowheight1));
  }
  anchor.setCol1(col1); 
  if (sheet instanceof XSSFSheet) {
   anchor.setDx1((int)Math.round(dx1 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
  } else if (sheet instanceof HSSFSheet) {
   anchor.setDx1((int)Math.round(dx1 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH / colwidth1));
  }

  if (!resize) {
   anchor.setRow2(row2); //second anchor determines bottom right position
   if (sheet instanceof XSSFSheet) {
    anchor.setDy2(dy2 * Units.EMU_PER_POINT);
   } else if (sheet instanceof HSSFSheet) {
    anchor.setDy2((int)Math.round(dy2 * Units.PIXEL_DPI / Units.POINT_DPI * 14.75 * DEFAULT_ROW_HEIGHT / rowheight2));
   }
   anchor.setCol2(col2);
   if (sheet instanceof XSSFSheet) {
    anchor.setDx2((int)Math.round(dx2 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
   } else if (sheet instanceof HSSFSheet) {
    anchor.setDx2((int)Math.round(dx2 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH / colwidth2));
   }
  }

  Picture picture = drawing.createPicture(anchor, pictureIdx);

  if (resize) picture.resize();

  return picture;
 }

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

  Workbook workbook = new XSSFWorkbook();
  //Workbook workbook = new HSSFWorkbook();

  Sheet sheet = workbook.createSheet("Sheet1");
  sheet.setColumnWidth(1, 6000/*1/256th of a character width*/);

  Row row = sheet.createRow(0);
  row.setHeightInPoints(100/*points*/);

  row = sheet.createRow(10);
  row.setHeightInPoints(50/*points*/);

  Picture picture;

  //two cell anchor in the same cell (B1) used without resizing the picture
  picture = drawImageOnExcelSheet(sheet, 
   1, 0, 1000/*1/256th of a character width*/, 10/*points*/, 
   1, 0, 5000/*1/256th of a character width*/, 90/*points*/, 
   "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);

  //one cell anchor (B3) used with resizing the picture
  picture = drawImageOnExcelSheet(sheet, 
   1, 2, 1000/*1/256th of a character width*/, 10/*points*/, 
   0, 0, 0, 0, 
   "mikt1.png", Workbook.PICTURE_TYPE_PNG, true);

  //two cell anchor (B10 to B12) used without resizing the picture
  picture = drawImageOnExcelSheet(sheet, 
   1, 9, 1000/*1/256th of a character width*/, 10/*points*/, 
   1, 11, 5000/*1/256th of a character width*/, 10/*points*/, 
   "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);

  if (workbook instanceof XSSFWorkbook) {
   workbook.write(new FileOutputStream("image-sutpid.xlsx"));
  } else if (workbook instanceof HSSFWorkbook) {
   workbook.write(new FileOutputStream("image-sutpid.xls"));
  }
  workbook.close();

 }

}

至少找到了定义dx and dy对于二进制*-xls文件格式。它定义在2.5.193 OfficeArtClientAnchorSheet https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-xls/fd656a2c-d5ee-4171-8f65-17a08b9f2262.

dx：该值表示为该单元格宽度的 1024 倍。

dy：该值表示为该单元格高度的 256 倍。

有了这个，代码应该是这样的：

import java.io.*;

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

import org.apache.poi.util.Units;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;

public class CreateExcelWithPictures {

 private static Picture drawImageOnExcelSheet(Sheet sheet, 
  int col1, int row1, int dx1/*1/256th of a character width*/, int dy1/*points*/,
  int col2, int row2, int dx2/*1/256th of a character width*/, int dy2/*points*/, 
  String pictureurl, int picturetype, boolean resize) throws Exception {

  int DEFAULT_COL_WIDTH = 10 * 256;
  float DEFAULT_ROW_HEIGHT = 12.75f;

  Row row = sheet.getRow(row1);
  float rowheight1 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
  row = sheet.getRow(row2);
  float rowheight2 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;

  int colwidth1 = sheet.getColumnWidth(col1);
  int colwidth2 = sheet.getColumnWidth(col2);

  InputStream is = new FileInputStream(pictureurl);
  byte[] bytes = IOUtils.toByteArray(is);
  int pictureIdx = sheet.getWorkbook().addPicture(bytes, picturetype);
  is.close();

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

  Drawing drawing = sheet.createDrawingPatriarch();

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

  anchor.setRow1(row1); //first anchor determines upper left position
  if (sheet instanceof XSSFSheet) {
   anchor.setDy1(dy1 * Units.EMU_PER_POINT);
  } else if (sheet instanceof HSSFSheet) {
   anchor.setDy1((int)Math.round(dy1 * Units.PIXEL_DPI / Units.POINT_DPI * 256f / (rowheight1 * Units.PIXEL_DPI / Units.POINT_DPI)));
  }
  anchor.setCol1(col1); 
  if (sheet instanceof XSSFSheet) {
   anchor.setDx1((int)Math.round(dx1 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
  } else if (sheet instanceof HSSFSheet) {
   anchor.setDx1((int)Math.round(dx1 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 1024f / (colwidth1 * Units.DEFAULT_CHARACTER_WIDTH / 256f)));
  }

  if (!resize) {
   anchor.setRow2(row2); //second anchor determines bottom right position
   if (sheet instanceof XSSFSheet) {
    anchor.setDy2(dy2 * Units.EMU_PER_POINT);
   } else if (sheet instanceof HSSFSheet) {
    anchor.setDy2((int)Math.round(dy2 * Units.PIXEL_DPI / Units.POINT_DPI * 256f / (rowheight2 * Units.PIXEL_DPI / Units.POINT_DPI)));
   }
   anchor.setCol2(col2);
   if (sheet instanceof XSSFSheet) {
    anchor.setDx2((int)Math.round(dx2 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
   } else if (sheet instanceof HSSFSheet) {
    anchor.setDx2((int)Math.round(dx2 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 1024f / (colwidth2 * Units.DEFAULT_CHARACTER_WIDTH / 256f)));
   }
  }

  Picture picture = drawing.createPicture(anchor, pictureIdx);

  if (resize) picture.resize();

  return picture;
 }

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

  //Workbook workbook = new XSSFWorkbook();
  Workbook workbook = new HSSFWorkbook();

  Sheet sheet = workbook.createSheet("Sheet1");
  sheet.setColumnWidth(1, 6000/*1/256th of a character width*/);

  Row row = sheet.createRow(0);
  row.setHeightInPoints(100/*points*/);

  row = sheet.createRow(10);
  row.setHeightInPoints(50/*points*/);

  Picture picture;

  //two cell anchor in the same cell (B1) used without resizing the picture
  picture = drawImageOnExcelSheet(sheet, 
   1, 0, 1000/*1/256th of a character width*/, 10/*points*/, 
   1, 0, 5000/*1/256th of a character width*/, 90/*points*/, 
   "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);

  //one cell anchor (B3) used with resizing the picture
  picture = drawImageOnExcelSheet(sheet, 
   1, 2, 1000/*1/256th of a character width*/, 10/*points*/, 
   0, 0, 0, 0, 
   "mikt1.png", Workbook.PICTURE_TYPE_PNG, true);

  //two cell anchor (B10 to B12) used without resizing the picture
  picture = drawImageOnExcelSheet(sheet, 
   1, 9, 1000/*1/256th of a character width*/, 10/*points*/, 
   1, 11, 5000/*1/256th of a character width*/, 10/*points*/, 
   "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);

  if (workbook instanceof XSSFWorkbook) {
   workbook.write(new FileOutputStream("image-sutpid.xlsx"));
  } else if (workbook instanceof HSSFWorkbook) {
   workbook.write(new FileOutputStream("image-sutpid.xls"));
  }
  workbook.close();

 }

}

但是，最好将所有长度都以测量单位像素为单位，以避免从 pt 和/或 256 字符宽度转换为像素。看为什么同一张图片导出excel使用HSSFWorkbook可以使用SXSSFWorkbook不行 https://stackoverflow.com/questions/76966076/why-the-same-image-export-excel-using-hssfworkbook-can-use-sxssfworkbook-can-not例如。