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

ocebsuys  于 2023-10-23  发布在  Apache
关注(0)|答案(1)|浏览(408)

我正在尝试使用apach-poi version 3.16将图像添加到excel。我可以用HSSFWorkbook和XSSFWorkbook来实现。但是当我试图为图像添加间距时，即如果我在XSSFClientAnchor上设置dx1，dy1，dx2，dy2坐标，则不会生效。同样的事情也发生在HSSFClientAnchor上。我附上这两个类和相应的Excel文件生成。请问你可以帮助我如何使用XSSFClientAnchor实现相同的结果。
HSSF类

  1. package poisamples;
  3. import java.io.ByteArrayOutputStream;
  4. import java.io.File;
  5. import java.io.FileInputStream;
  6. import java.io.FileOutputStream;
  7. import java.io.IOException;
  9. import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
  10. import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
  11. import org.apache.poi.hssf.usermodel.HSSFPatriarch;
  12. import org.apache.poi.hssf.usermodel.HSSFPicture;
  13. import org.apache.poi.hssf.usermodel.HSSFSheet;
  14. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  16. public class HSSFImage {
  17.     public static void main(String[] args) throws IOException {
  18.         String imageFile = "test.png";
  19.         String outputFile = "image-sutpid.xls";
  20.         HSSFWorkbook workbook = new HSSFWorkbook();
  21.         HSSFSheet sheet = workbook.createSheet("Image");
  22.         HSSFClientAnchor anchor = new HSSFClientAnchor(100,100,100,100,(short)0, (short)0, (short)0, (short)3);
  24.         sheet.setColumnWidth(0, 6000);
  26.         anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
  27.         int index = sheet.getWorkbook().addPicture(imageToBytes(imageFile), HSSFWorkbook.PICTURE_TYPE_PNG);
  29.         HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
  30.         HSSFPicture picture = patriarch.createPicture(anchor, index);
  31.         picture.resize();
  32.         FileOutputStream fos = new FileOutputStream(outputFile);
  33.         workbook.write(fos);
  34.     }
  36.     private static byte[] imageToBytes(String imageFilename) throws IOException {
  37.         File imageFile;
  38.         FileInputStream fis = null;
  39.         ByteArrayOutputStream bos;
  40.         int read;
  41.         try {
  42.             imageFile = new File(imageFilename);
  43.             fis = new FileInputStream(imageFile);
  44.             bos = new ByteArrayOutputStream();
  45.             while ((read = fis.read()) != -1) {
  46.                 bos.write(read);
  47.             }
  48.             return (bos.toByteArray());
  49.         } finally {
  50.             if (fis != null) {
  51.                 try {
  52.                     fis.close();
  53.                     fis = null;
  54.                 } catch (IOException ioEx) {
  55.                     // Nothing to do here
  56.                 }
  57.             }
  58.         }
  59.     }
  60. }

XSSF类

  1. package poisamples;
  3. import java.io.ByteArrayOutputStream;
  4. import java.io.File;
  5. import java.io.FileInputStream;
  6. import java.io.FileOutputStream;
  7. import java.io.IOException;
  9. import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
  10. import org.apache.poi.xssf.usermodel.XSSFDrawing;
  11. import org.apache.poi.xssf.usermodel.XSSFPicture;
  12. import org.apache.poi.xssf.usermodel.XSSFSheet;
  13. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  14. import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
  16. public class XSSFImage {
  17.     public static void main(String[] args) throws IOException {
  18.         String imageFile = "test.png";
  19.         String outputFile = "image-sutpid.xlsx";
  20.         XSSFWorkbook workbook = new XSSFWorkbook();
  21.         XSSFSheet sheet = workbook.createSheet("Image");
  22.         XSSFClientAnchor anchor = new XSSFClientAnchor(100,100,100,100,0, 0, 0, 3);
  24.         sheet.setColumnWidth(0, 6000);
  26.         anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
  27.         int index = sheet.getWorkbook().addPicture(imageToBytes(imageFile), XSSFWorkbook.PICTURE_TYPE_PNG);
  29.         XSSFDrawing patriarch = sheet.createDrawingPatriarch();
  30.         XSSFPicture picture = patriarch.createPicture(anchor, index);
  31.         picture.resize();
  32.         FileOutputStream fos = new FileOutputStream(outputFile);
  33.         workbook.write(fos);
  34.     }
  36.     private static byte[] imageToBytes(String imageFilename) throws IOException {
  37.         File imageFile;
  38.         FileInputStream fis = null;
  39.         ByteArrayOutputStream bos;
  40.         int read;
  41.         try {
  42.             imageFile = new File(imageFilename);
  43.             fis = new FileInputStream(imageFile);
  44.             bos = new ByteArrayOutputStream();
  45.             while ((read = fis.read()) != -1) {
  46.                 bos.write(read);
  47.             }
  48.             return (bos.toByteArray());
  49.         } finally {
  50.             if (fis != null) {
  51.                 try {
  52.                     fis.close();
  53.                     fis = null;
  54.                 } catch (IOException ioEx) {
  55.                     // Nothing to do here
  56.                 }
  57.             }
  58.         }
  59.     }
  60. }

HSSF结果：

XSSF结果：

使用的图像：

apache

1条答案

按热度按时间
e7arh2l6

e7arh2l61#

问题是微软使用的不同的奇怪测量单位，以及二进制文件系统*.xls和Office Open XML *.xlsx不仅在文件存储方面而且在一般方法上都有很大不同。
如ClientAnchor所述：“注意- XSSF和HSSF的坐标系略有不同，XSSF中的值要大Units.EMU_PER_PIXEL”。但这并不是全部真相。dx和dy的含义完全不同。在二进制文件系统*.xls中，这些值取决于column-width/default column-width和row-height/default row-height的因子。不要问我在我的例子中使用的因子14.75。这只是Trial&Error。
关于你的代码，要提到的是，如果你想将图片缩放到它的原始大小，那么只需要一个单元格锚。这将锚定图片的左上边缘。如果锚将确定图片的大小，则仅需要两个单元锚。然后，锚中的第一个单元格锚定图片的左上边缘，而锚中的第二个单元格锚定图片的右下边缘。
下面的示例使用1/256th of a character width作为dx的度量单位，因为列宽也在此度量单位中。它使用point作为dy的测量单位，因为行高也在这个测量单位中。

  1. import java.io.*;
  3. import org.apache.poi.ss.usermodel.*;
  4. import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
  5. import org.apache.poi.util.IOUtils;
  7. import org.apache.poi.util.Units;
  9. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  10. import org.apache.poi.xssf.usermodel.XSSFSheet;
  11. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  12. import org.apache.poi.hssf.usermodel.HSSFSheet;
  14. public class CreateExcelWithPictures {
  16.  private static Picture drawImageOnExcelSheet(Sheet sheet, 
  17.   int col1, int row1, int dx1/*1/256th of a character width*/, int dy1/*points*/,
  18.   int col2, int row2, int dx2/*1/256th of a character width*/, int dy2/*points*/, 
  19.   String pictureurl, int picturetype, boolean resize) throws Exception {
  21.   int DEFAULT_COL_WIDTH = 10 * 256;
  22.   float DEFAULT_ROW_HEIGHT = 12.75f;
  24.   Row row = sheet.getRow(row1);
  25.   float rowheight1 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
  26.   row = sheet.getRow(row2);
  27.   float rowheight2 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
  29.   int colwidth1 = sheet.getColumnWidth(col1);
  30.   int colwidth2 = sheet.getColumnWidth(col2);
  32.   InputStream is = new FileInputStream(pictureurl);
  33.   byte[] bytes = IOUtils.toByteArray(is);
  34.   int pictureIdx = sheet.getWorkbook().addPicture(bytes, picturetype);
  35.   is.close();
  37.   CreationHelper helper = sheet.getWorkbook().getCreationHelper();
  39.   Drawing drawing = sheet.createDrawingPatriarch();
  41.   ClientAnchor anchor = helper.createClientAnchor();
  42.   anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
  44.   anchor.setRow1(row1); //first anchor determines upper left position
  45.   if (sheet instanceof XSSFSheet) {
  46.    anchor.setDy1(dy1 * Units.EMU_PER_POINT);
  47.   } else if (sheet instanceof HSSFSheet) {
  48.    anchor.setDy1((int)Math.round(dy1 * Units.PIXEL_DPI / Units.POINT_DPI * 14.75 * DEFAULT_ROW_HEIGHT / rowheight1));
  49.   }
  50.   anchor.setCol1(col1); 
  51.   if (sheet instanceof XSSFSheet) {
  52.    anchor.setDx1((int)Math.round(dx1 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
  53.   } else if (sheet instanceof HSSFSheet) {
  54.    anchor.setDx1((int)Math.round(dx1 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH / colwidth1));
  55.   }
  57.   if (!resize) {
  58.    anchor.setRow2(row2); //second anchor determines bottom right position
  59.    if (sheet instanceof XSSFSheet) {
  60.     anchor.setDy2(dy2 * Units.EMU_PER_POINT);
  61.    } else if (sheet instanceof HSSFSheet) {
  62.     anchor.setDy2((int)Math.round(dy2 * Units.PIXEL_DPI / Units.POINT_DPI * 14.75 * DEFAULT_ROW_HEIGHT / rowheight2));
  63.    }
  64.    anchor.setCol2(col2);
  65.    if (sheet instanceof XSSFSheet) {
  66.     anchor.setDx2((int)Math.round(dx2 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
  67.    } else if (sheet instanceof HSSFSheet) {
  68.     anchor.setDx2((int)Math.round(dx2 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 14.75 * DEFAULT_COL_WIDTH / colwidth2));
  69.    }
  70.   }
  72.   Picture picture = drawing.createPicture(anchor, pictureIdx);
  74.   if (resize) picture.resize();
  76.   return picture;
  77.  }
  79.  public static void main(String[] args) throws Exception {
  81.   Workbook workbook = new XSSFWorkbook();
  82.   //Workbook workbook = new HSSFWorkbook();
  84.   Sheet sheet = workbook.createSheet("Sheet1");
  85.   sheet.setColumnWidth(1, 6000/*1/256th of a character width*/);
  87.   Row row = sheet.createRow(0);
  88.   row.setHeightInPoints(100/*points*/);
  90.   row = sheet.createRow(10);
  91.   row.setHeightInPoints(50/*points*/);
  93.   Picture picture;
  95.   //two cell anchor in the same cell (B1) used without resizing the picture
  96.   picture = drawImageOnExcelSheet(sheet, 
  97.    1, 0, 1000/*1/256th of a character width*/, 10/*points*/, 
  98.    1, 0, 5000/*1/256th of a character width*/, 90/*points*/, 
  99.    "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);
  101.   //one cell anchor (B3) used with resizing the picture
  102.   picture = drawImageOnExcelSheet(sheet, 
  103.    1, 2, 1000/*1/256th of a character width*/, 10/*points*/, 
  104.    0, 0, 0, 0, 
  105.    "mikt1.png", Workbook.PICTURE_TYPE_PNG, true);
  107.   //two cell anchor (B10 to B12) used without resizing the picture
  108.   picture = drawImageOnExcelSheet(sheet, 
  109.    1, 9, 1000/*1/256th of a character width*/, 10/*points*/, 
  110.    1, 11, 5000/*1/256th of a character width*/, 10/*points*/, 
  111.    "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);
  113.   if (workbook instanceof XSSFWorkbook) {
  114.    workbook.write(new FileOutputStream("image-sutpid.xlsx"));
  115.   } else if (workbook instanceof HSSFWorkbook) {
  116.    workbook.write(new FileOutputStream("image-sutpid.xls"));
  117.   }
  118.   workbook.close();
  120.  }
  122. }

至少找到了*-xls二进制文件格式的dx和dy的定义。它在2.5.193 OfficeArtClientAnchorSheet中定义。
dx：值表示为单元格宽度的1024次方。
dy：该值表示为该单元格高度的256次方。
有了这个，代码应该是这样的：

  1. import java.io.*;
  3. import org.apache.poi.ss.usermodel.*;
  4. import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
  5. import org.apache.poi.util.IOUtils;
  7. import org.apache.poi.util.Units;
  9. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  10. import org.apache.poi.xssf.usermodel.XSSFSheet;
  11. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  12. import org.apache.poi.hssf.usermodel.HSSFSheet;
  14. public class CreateExcelWithPictures {
  16.  private static Picture drawImageOnExcelSheet(Sheet sheet, 
  17.   int col1, int row1, int dx1/*1/256th of a character width*/, int dy1/*points*/,
  18.   int col2, int row2, int dx2/*1/256th of a character width*/, int dy2/*points*/, 
  19.   String pictureurl, int picturetype, boolean resize) throws Exception {
  21.   int DEFAULT_COL_WIDTH = 10 * 256;
  22.   float DEFAULT_ROW_HEIGHT = 12.75f;
  24.   Row row = sheet.getRow(row1);
  25.   float rowheight1 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
  26.   row = sheet.getRow(row2);
  27.   float rowheight2 = (row!=null)?row.getHeightInPoints():DEFAULT_ROW_HEIGHT;
  29.   int colwidth1 = sheet.getColumnWidth(col1);
  30.   int colwidth2 = sheet.getColumnWidth(col2);
  32.   InputStream is = new FileInputStream(pictureurl);
  33.   byte[] bytes = IOUtils.toByteArray(is);
  34.   int pictureIdx = sheet.getWorkbook().addPicture(bytes, picturetype);
  35.   is.close();
  37.   CreationHelper helper = sheet.getWorkbook().getCreationHelper();
  39.   Drawing drawing = sheet.createDrawingPatriarch();
  41.   ClientAnchor anchor = helper.createClientAnchor();
  42.   anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
  44.   anchor.setRow1(row1); //first anchor determines upper left position
  45.   if (sheet instanceof XSSFSheet) {
  46.    anchor.setDy1(dy1 * Units.EMU_PER_POINT);
  47.   } else if (sheet instanceof HSSFSheet) {
  48.    anchor.setDy1((int)Math.round(dy1 * Units.PIXEL_DPI / Units.POINT_DPI * 256f / (rowheight1 * Units.PIXEL_DPI / Units.POINT_DPI)));
  49.   }
  50.   anchor.setCol1(col1); 
  51.   if (sheet instanceof XSSFSheet) {
  52.    anchor.setDx1((int)Math.round(dx1 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
  53.   } else if (sheet instanceof HSSFSheet) {
  54.    anchor.setDx1((int)Math.round(dx1 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 1024f / (colwidth1 * Units.DEFAULT_CHARACTER_WIDTH / 256f)));
  55.   }
  57.   if (!resize) {
  58.    anchor.setRow2(row2); //second anchor determines bottom right position
  59.    if (sheet instanceof XSSFSheet) {
  60.     anchor.setDy2(dy2 * Units.EMU_PER_POINT);
  61.    } else if (sheet instanceof HSSFSheet) {
  62.     anchor.setDy2((int)Math.round(dy2 * Units.PIXEL_DPI / Units.POINT_DPI * 256f / (rowheight2 * Units.PIXEL_DPI / Units.POINT_DPI)));
  63.    }
  64.    anchor.setCol2(col2);
  65.    if (sheet instanceof XSSFSheet) {
  66.     anchor.setDx2((int)Math.round(dx2 * Units.EMU_PER_PIXEL * Units.DEFAULT_CHARACTER_WIDTH / 256f));
  67.    } else if (sheet instanceof HSSFSheet) {
  68.     anchor.setDx2((int)Math.round(dx2 * Units.DEFAULT_CHARACTER_WIDTH / 256f * 1024f / (colwidth2 * Units.DEFAULT_CHARACTER_WIDTH / 256f)));
  69.    }
  70.   }
  72.   Picture picture = drawing.createPicture(anchor, pictureIdx);
  74.   if (resize) picture.resize();
  76.   return picture;
  77.  }
  79.  public static void main(String[] args) throws Exception {
  81.   //Workbook workbook = new XSSFWorkbook();
  82.   Workbook workbook = new HSSFWorkbook();
  84.   Sheet sheet = workbook.createSheet("Sheet1");
  85.   sheet.setColumnWidth(1, 6000/*1/256th of a character width*/);
  87.   Row row = sheet.createRow(0);
  88.   row.setHeightInPoints(100/*points*/);
  90.   row = sheet.createRow(10);
  91.   row.setHeightInPoints(50/*points*/);
  93.   Picture picture;
  95.   //two cell anchor in the same cell (B1) used without resizing the picture
  96.   picture = drawImageOnExcelSheet(sheet, 
  97.    1, 0, 1000/*1/256th of a character width*/, 10/*points*/, 
  98.    1, 0, 5000/*1/256th of a character width*/, 90/*points*/, 
  99.    "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);
  101.   //one cell anchor (B3) used with resizing the picture
  102.   picture = drawImageOnExcelSheet(sheet, 
  103.    1, 2, 1000/*1/256th of a character width*/, 10/*points*/, 
  104.    0, 0, 0, 0, 
  105.    "mikt1.png", Workbook.PICTURE_TYPE_PNG, true);
  107.   //two cell anchor (B10 to B12) used without resizing the picture
  108.   picture = drawImageOnExcelSheet(sheet, 
  109.    1, 9, 1000/*1/256th of a character width*/, 10/*points*/, 
  110.    1, 11, 5000/*1/256th of a character width*/, 10/*points*/, 
  111.    "mikt1.png", Workbook.PICTURE_TYPE_PNG, false);
  113.   if (workbook instanceof XSSFWorkbook) {
  114.    workbook.write(new FileOutputStream("image-sutpid.xlsx"));
  115.   } else if (workbook instanceof HSSFWorkbook) {
  116.    workbook.write(new FileOutputStream("image-sutpid.xls"));
  117.   }
  118.   workbook.close();
  120.  }
  122. }

但是，最好将所有长度都以像素为测量单位，以避免从pt和/或字符宽度的256分之一转换为像素。例如Why the same image export excel using HSSFWorkbook can use SXSSFWorkbook can not。

展开查看全部
赞(0）分享  回复(0）举报  2023-10-23
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com