将图片添加到Excel单元格时，Apache POI-HSSF会扭曲图像大小

I am adding a picture into a cell using Apache POI-HSSF. The image is 120x100 but no matter what I do and how I resize it, the Excel spreadsheet always shows it spanning multiple rows and distorts it to a much bigger height than width.

我正在使用Apache POI-HSSF将图片添加到单元格中。图像是120x100，但无论我做什么以及如何调整它，Excel电子表格总是显示它跨越多行并将其扭曲到比宽度更大的高度。

How do I keep the original size?

如何保持原始尺寸？

My code:

我的代码：

InputStream is = new FileInputStream(getImageURL());
byte[] bytes = IOUtils.toByteArray(is);
int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
is.close();

//add a picture shape
CreationHelper helper = wb.getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
// Create the drawing patriarch.  This is the top level container for all shapes.
Drawing drawing = sheet1.createDrawingPatriarch();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it

anchor.setAnchorType(0);
anchor.setCol1(1);
anchor.setRow1(1);

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

//auto-size picture relative to its top-left corner
pict.resize();

I've tried all dx/dy coordinates and Col/Row. The position doesn't matter, the problem it stretches the image horizontally. Thanks

我已经尝试了所有的dx / dy坐标和Col / Row。位置无关紧要，它是水平拉伸图像的问题。谢谢

4 个解决方案

#1

I had been facing the similar issue where the image I added was getting distorted. I tried pict.resize() and sheet.autoSizeColumn() but it didn't work. Finally I found the below URL:- https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/AddDimensionedImage.java

我一直面临类似的问题，我添加的图像变形了。我尝试了pict.resize（）和sheet.autoSizeColumn（），但它没有用。最后我找到了以下网址： - https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/AddDimensionedImage.java

I added the above class into my code and used it's method to add the image into excel. I was able to add the image with little distortion. Hope this helps to you also. I wrote below code:-

我将上面的类添加到我的代码中并使用它的方法将图像添加到excel中。我能够添加少量失真的图像。希望这对你也有帮助。我在下面写了代码： -

BufferedImage imageIO = ImageIO.read(new URL(image));
int height= imageIO.getHeight();
int width=imageIO.getWidth();
int relativeHeight=(int)(((double)height/width)*28.5);
new AddDimensionedImage().addImageToSheet(2,  sheet.getPhysicalNumberOfRows()-1 , sheet, sheet.createDrawingPatriarch(),new URL(image), 30, relativeHeight, AddDimensionedImage.EXPAND_ROW);

#2

As far as i understood from documentation of Apache POI, it is because of pict.resize(); ,as it says here that if the default font size for the workbook was changed, the picture might get stretched vertically or horizontally.

据我所知，从Apache POI的文档来看，这是因为pict.resize（）;正如它在这里所说的那样，如果更改了工作簿的默认字体大小，则图片可能会垂直或水平拉伸。

#3

I created another empty image with the width taken from sheet.getColumnWidthInPixels, draw the necessary image over it and used AddDimensionedImage.EXPAND_ROW_AND_COLUMN to fit it exactly into the cell:

我创建了另一个空图像，其宽度取自sheet.getColumnWidthInPixels，在其上绘制必要的图像并使用AddDimensionedImage.EXPAND_ROW_AND_COLUMN将其精确地拟合到单元格中：

    HSSFRow imageRow = sheet.createRow(row);

    BufferedImage image = ImageIO.read(Paths.get(pathToImage).toUri().toURL());

    AddDimensionedImage addImage = new AddDimensionedImage();
    float columnWidth = sheet.getColumnWidthInPixels(0);

    BufferedImage off_Image = new BufferedImage((int) columnWidth, actualImageHeight, BufferedImage.TYPE_INT_RGB);
    Graphics2D g2 = off_Image.createGraphics();
    g2.setColor(new Color(192,192,192));
    g2.fillRect(0, 0, off_Image.getWidth(), off_Image.getHeight());
    g2.drawImage(image, 0, 0, null);
    g2.dispose();

    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    ImageIO.write(off_Image, "jpg", baos);
    byte[] bytes = baos.toByteArray();

    double reqImageWidthMM = ((double) off_Image.getWidth()) / ConvertImageUnits.PIXELS_PER_MILLIMETRES;
    double reqImageHeightMM = ((double) off_Image.getHeight()) / ConvertImageUnits.PIXELS_PER_MILLIMETRES;

    addImage.addImageToSheet("A1", sheet, bytes, reqImageWidthMM, reqImageHeightMM, AddDimensionedImage.EXPAND_ROW_AND_COLUMN);

I had to copy AddDimensionedImage.java to my classpath and overload addImageToSheet to accept byte array.

我不得不将AddDimensionedImage.java复制到我的类路径并重载addImageToSheet以接受字节数组。

Before that I tried other options mentioned here but it didn't help. Hope my answer will be useful to someone.

在此之前我尝试了这里提到的其他选项，但它没有帮助。希望我的回答对某人有用。

#4

I've the same problem and my solution was copy this class in my project AddDimensionedImage and then used this method.

我有同样的问题，我的解决方案是在我的项目AddDimensionedImage中复制这个类，然后使用这个方法。

protected void addImageInCell(Sheet sheet, URL url, Drawing<?> drawing, int colNumber, int rowNumber) {
    BufferedImage imageIO = ImageIO.read(url);
    int height = imageIO.getHeight();
    int width = imageIO.getWidth();
    int relativeHeight = (int) (((double) height / width) * 28.5);
    new AddDimensionedImage().addImageToSheet(colNumber, rowNumber, sheet, drawing, url, 30, relativeHeight,
            AddDimensionedImage.EXPAND_ROW_AND_COLUMN);

}

you can call this method with follow line:

您可以使用以下行调用此方法：

URL url = new URL("https://blog.expedia.mx/por-que-los-viajeros-internacionales-visitan-mexico/");
addImageInCell(sheet, url, sheet.createDrawingPatriarch(), 0, 0);

#1