apache poi XSSFClientAnchor相对于dx1，dy1，dx2，dy2没有定位图片

I am trying to add an image to excel using apach-poi version 3.16. I am able to do that with HSSFWorkbook and XSSFWorkbook. But when i am trying to add spacing for the image i.e if I set dx1, dy1, dx2, dy2 coordinates on XSSFClientAnchor it is not taking effect. Same thing is working on HSSFClientAnchor. I am attaching both classes and corresponding excel file generated. Could you please help me how can i do achieve the same result using XSSFClientAnchor.

我正在尝试使用apach-poi版本3.16将图像添加到Excel。我可以用HSSFWorkbook和XSSFWorkbook来做到这一点。但是当我试图为图像添加间距时,即如果我在XSSFClientAnchor上设置dx1,dy1,dx2,dy2坐标,它就不会生效。同样的事情是在HSSFClientAnchor上工作。我附加了两个类和生成的相应excel文件。你能帮我解决一下如何使用XSSFClientAnchor实现相同的结果。

HSSF Class

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 Class

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 Result:

XSSF Result:

Image used:

1 个解决方案

#1

The problems are the different strange measurement units which Microsoft is using and the fact that the binary file system *.xls and the Office Open XML *.xlsx are very different not only in file storing but in general approaches also.

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

As mentioned in ClientAnchor: "Note - XSSF and HSSF have a slightly different coordinate system, values in XSSF are larger by a factor of Units.EMU_PER_PIXEL". But this is not the whole truth. The meaning of the dx and dy is totally different. In the binary file system *.xls, the values are dependent on the factor of column-width / default column-width and row-height / default row-height. Don't ask me about the factor 14.75 used in my example. It is just trial&error.

如ClientAnchor中所述:“注意 - XSSF和HSSF的坐标系略有不同,XSSF中的值大于Units.EMU_PER_PIXEL”。但这不是全部真相。 dx和dy的含义完全不同。在二进制文件系统* .xls中,值取决于列宽/默认列宽和行高/默认行高的因子。不要问我在我的例子中使用的因子14.75。这只是试错。

To mention about your code is that if you wants resizing the picture to its native size, then only a one cell anchor is needed. This anchors the picture's upper left edge. A two cell anchor only is needed if the anchor shall determining the picture's size. Then the first cell in the anchor anchors the picture's upper left edge while the second cell in the anchor anchors the picture's bottom right edge.

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

The following example uses the measurement unit 1/256th of a character width for dx since column widths also are in this measurement unit. And it uses point as measurement unit for dy since row heights also are in this measurement unit.

以下示例使用dx的字符宽度的1/256的测量单位,因为列宽也在此测量单位中。它使用点作为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();

 }

}