You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Abhi <ab...@gmail.com> on 2012/11/02 14:01:04 UTC
Images in excel - performance issue
Hi,We are implementing export to excel functionality using POI 3.8. The
requirement is to have images in one of the cell. We are using image in
120X120 pixel size and inserting into excel using POI code. For 50 rows with
image in one cell, it is approximately taking 2 hours.
Is there any way to reduce the size? If I run the same code without images
it works very fast.
Here is the sample code:
private void createImageCell(Workbook wb,Sheet sheet,CreationHelper
createHelper,String imagePath,int rowNum, int colNum,Drawing drawing) {
try {
int pictureIdx = compressImage(wb,imagePath,false);
if(pictureIdx>=0) {
//add a picture shape ClientAnchor anchor =
createHelper.createClientAnchor(); //set top-left corner of the
picture,
//subsequent call of Picture#resize() will operate relative to it
anchor.setCol1(colNum);
//anchor.setCol2(colNum+1);
anchor.setRow1(rowNum);
//anchor.setRow2(rowNum+2);
anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize(); } }
catch(Exception exe) { message +=" "+exe.getMessage();
System.out.println(" "+exe); exe.printStackTrace();
} }
private int compressImage(Workbook wb,String imagePath,boolean
compressImageFlag) {
byte[] imageBytes = null;
InputStream is = null;
int pictureIdx = -1;
Integer picIndex = null;
try {
picIndex = picIndexMap.get(imagePath);
if(picIndex == null) {
ByteArrayOutputStream byteArrayOutStream = new
ByteArrayOutputStream();
BufferedImage originalImage = ImageIO.read(new URL(imagePath));
ImageIO.write(originalImage, "jpg", byteArrayOutStream);
imageBytes = byteArrayOutStream.toByteArray();
pictureIdx = wb.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
picIndex = new Integer(pictureIdx);
picIndexMap.put(imagePath, picIndex); } }
catch (IOException e) {
message += "Compress image IOUtils "+e.getMessage();
picIndex = new Integer(pictureIdx); }
finally { if(is != null) {
IOUtils.closeQuietly(is); } }
if(picIndex != null) { return picIndex.intValue(); }
else { return -1; }
}
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Images-in-excel-performance-issue-tp5711368.html
Sent from the POI - Dev mailing list archive at Nabble.com.
Re: Images in excel - performance issue
Posted by Abhi <ab...@gmail.com>.
Hi,
I got the actual reason for the performance issue. This is not because of
images, but its due to merge region. In order to place the image in cell, I
am merging few cells. This step is taking more time.
sheet.addMergedRegion(new
org.apache.poi.ss.util.CellRangeAddress(currentRow, numberOfRowsToMerge,
currentColumn, numberOfColumnsToMerge ));
Is there any alternate for this?
Thanks
Abhilash
On Fri, Nov 2, 2012 at 7:32 PM, Abhi [via Apache POI] <
ml-node+s1045710n5711371h0@n5.nabble.com> wrote:
> And also another observation. When inserting image, the program waits for
> some time in between. I printed the time taken for createPicture, but it
> is always less than a second. I don't know where it is taking time.
>
> Please suggest how to improve the performance.
>
> Thanks
> Abhilash
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://apache-poi.1045710.n5.nabble.com/Images-in-excel-performance-issue-tp5711368p5711371.html
> To unsubscribe from Images in excel - performance issue, click here<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5711368&code=YWJoaWxhc2guZ0BnbWFpbC5jb218NTcxMTM2OHwtNDU2NjI3NDgy>
> .
> NAML<http://apache-poi.1045710.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>
--
Thanks
Abhilash
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Images-in-excel-performance-issue-tp5711368p5711398.html
Sent from the POI - Dev mailing list archive at Nabble.com.
Re: Images in excel - performance issue
Posted by Abhi <ab...@gmail.com>.
And also another observation. When inserting image, the program waits for
some time in between. I printed the time taken for createPicture, but it is
always less than a second. I don't know where it is taking time.
Please suggest how to improve the performance.
Thanks
Abhilash
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Images-in-excel-performance-issue-tp5711368p5711371.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Images in excel - performance issue
Posted by Abhi <ab...@gmail.com>.
Yes it is with POI only. Because I used one image for all the cells and also
I am not reading with ImageIO if we already have Picture Index.
Thanks
Abhi
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Images-in-excel-performance-issue-tp5711368p5711370.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Images in excel - performance issue
Posted by Yegor Kozlov <ye...@dinom.ru>.
are you sure the bottle neck is in POI ? Can it be read-write in ImageIO ?
On Fri, Nov 2, 2012 at 5:01 PM, Abhi <ab...@gmail.com> wrote:
> Hi,We are implementing export to excel functionality using POI 3.8. The
> requirement is to have images in one of the cell. We are using image in
> 120X120 pixel size and inserting into excel using POI code. For 50 rows with
> image in one cell, it is approximately taking 2 hours.
>
> Is there any way to reduce the size? If I run the same code without images
> it works very fast.
> Here is the sample code:
>
>
> private void createImageCell(Workbook wb,Sheet sheet,CreationHelper
> createHelper,String imagePath,int rowNum, int colNum,Drawing drawing) {
> try {
> int pictureIdx = compressImage(wb,imagePath,false);
> if(pictureIdx>=0) {
> //add a picture shape ClientAnchor anchor =
> createHelper.createClientAnchor(); //set top-left corner of the
> picture,
> //subsequent call of Picture#resize() will operate relative to it
> anchor.setCol1(colNum);
> //anchor.setCol2(colNum+1);
> anchor.setRow1(rowNum);
> //anchor.setRow2(rowNum+2);
> anchor.setAnchorType(ClientAnchor.DONT_MOVE_AND_RESIZE);
> Picture pict = drawing.createPicture(anchor, pictureIdx);
> pict.resize(); } }
> catch(Exception exe) { message +=" "+exe.getMessage();
> System.out.println(" "+exe); exe.printStackTrace();
> } }
>
> private int compressImage(Workbook wb,String imagePath,boolean
> compressImageFlag) {
> byte[] imageBytes = null;
> InputStream is = null;
> int pictureIdx = -1;
> Integer picIndex = null;
> try {
> picIndex = picIndexMap.get(imagePath);
> if(picIndex == null) {
> ByteArrayOutputStream byteArrayOutStream = new
> ByteArrayOutputStream();
> BufferedImage originalImage = ImageIO.read(new URL(imagePath));
> ImageIO.write(originalImage, "jpg", byteArrayOutStream);
> imageBytes = byteArrayOutStream.toByteArray();
> pictureIdx = wb.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
> picIndex = new Integer(pictureIdx);
> picIndexMap.put(imagePath, picIndex); } }
> catch (IOException e) {
> message += "Compress image IOUtils "+e.getMessage();
> picIndex = new Integer(pictureIdx); }
> finally { if(is != null) {
> IOUtils.closeQuietly(is); } }
> if(picIndex != null) { return picIndex.intValue(); }
> else { return -1; }
> }
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/Images-in-excel-performance-issue-tp5711368.html
> Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org