java - apache poi XSSFClientAnchor 未根据 dx1、dy1、dx2、dy2 定位图片

Question

我正在尝试使用 apach-poi 版本 3.16 将图像添加到 excel。我可以使用 HSSFWorkbook 和 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 结果:

使用的图片:

Answer 1

问题在于 Microsoft 使用的不同奇怪的度量单位以及二进制文件系统 *.xls 和 Office Open XML *.xlsx 非常不仅在文件存储方面不同，而且在一般方法方面也不同。

如 ClientAnchor 中所述:“注意 - XSSF 和 HSSF 的坐标系略有不同，XSSF 中的值更大 Units.EMU_PER_PIXEL”。但这不是全部事实。 dx 和 dy 的含义完全不同。在二进制文件系统 *.xls 中，值取决于 column-width/default column-width 和 行高/默认行高。不要问我示例中使用的因子 14.75。这只是反复试验。

提及您的代码是，如果您想将图片调整为原始大小，则只需要一个单元格 anchor 。这将锚定图片的左上边缘。如果 anchor 应确定图片的大小，则仅需要两个单元格 anchor 。然后 anchor 中的第一个单元格锚定图片的左上边缘，而 anchor 中的第二个单元格锚定图片的右下边缘。

以下示例使用 1/256th of a character width 作为 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();

 }

}