Apache POI-HSSF distorts image size when adding picture into Excel cell
Solution 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/examples/ss/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:-
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);
Solution 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.
Solution 3
I've the same problem and my solution was copy this class in my project AddDimensionedImage and then used this method.
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);
gene b.
Updated on June 11, 2022Comments
-
gene b. about 2 years
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.
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.
-
S. Doe over 3 yearsThe AddDimensionedImage.java seems to have moved. New URL: svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/…
-
S. Doe over 3 yearsThe AddDimensionedImage.java seems to have moved. New URL: svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/…
-
S. Doe over 3 yearsThe AddDimensionedImage.java seems to have moved. New URL: svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/…