apache poi XSSFClientAnchor 未针对 dx1、dy1、dx2、dy2 定位图片 [英] apache poi XSSFClientAnchor not positioning picture with respect to dx1, dy1, dx2, dy2
问题描述
我正在尝试使用 apach-poi 版本 3.16 将图像添加到 excel.我可以使用 HSSFWorkbook 和 XSSFWorkbook 做到这一点.但是当我尝试为图像添加间距时，即如果我设置了 dx1, dy1, dx2, dy2XSSFClientAnchor 上的坐标未生效.HSSFClientAnchor 上也有同样的事情.我附上了这两个类和相应的 excel 文件.能否请您帮助我如何使用 XSSFClientAnchor 实现相同的结果.
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.
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 结果:
使用的图片:
推荐答案
问题在于 Microsoft 使用的不同的奇怪度量单位以及二进制文件系统 *.xls 和 OfficeOpen XML *.xlsx 不仅在文件存储上有很大不同，而且在一般方法上也有很大不同.
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.
如ClientAnchor 中所述:注意 - XSSF 和 HSSF 的坐标系略有不同，XSSF 中的值大了一个 Units.EMU_PER_PIXEL".但这并不是全部的真相.dx 和 dy 的含义完全不同.在二进制文件系统 *.xls 中，值取决于 column-width/default column-width 和 的因素行高/默认行高.不要问我示例中使用的因子 14.75.这只是试错.
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.
要提一下您的代码，如果您想将图片调整为其原始大小，则只需要一个单元格锚点.这将锚定图片的左上边缘.仅当锚点确定图片大小时才需要两个单元的锚点.然后锚中的第一个单元格锚定图片的左上边缘，而锚中的第二个单元格锚定图片的右下边缘.
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.
以下示例使用度量单位 字符宽度的 1/256 作为 dx，因为列宽也在此度量单位中.它使用 point 作为 dy 的度量单位，因为行高也在这个度量单位中.
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.
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();
}
}
这篇关于apache poi XSSFClientAnchor 未针对 dx1、dy1、dx2、dy2 定位图片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
