AddPicture is slow when adding a lot of images

[Rob-Post]

Hi,

I am creating an Excel file with about 750 rows. Each row contains an unique image which I add via the ExcelWorkSheet.Drawings.AddPicture() method. However, this is getting slow after having added a couple of images.

I was wondering if there is a more efficient method for adding a lot of images to an Excel sheet.

This issue is similar to the question asked here: https://stackoverflow.com/questions/31165922/are-there-alternatives-to-addpicture-c-excel

[0xc4ea70]

I was seeing some very slow times using the same AddPicture() function. It was almost like it would get progressively slower with each call.

However, I recompiled EPPlus to instead use .NET 4.5.2, and this had an immediate 3x speed improvement. FWIW, my report has ~4000 images, one per row, and after making that change it took 6 minutes to create it down from 20 minutes.

Hope this helps!

[hbruun]

I'm seeing the same issue. It becomes unusable around 1000 small 150x150 images.

[Sejushi]

Still works very slow when you add many pictures...

[vk4jlm]

I am seeing the same behaviour, it becomes very slow around 400 - 500 images in my case and takes >9 minutes to generate a sheet with around 1100 images in it.

[hbruun]

I've been experimenting with a local build of the latest EPPlus and a test program that will add 50, 100, 200, etc small pictures (10-15K each) to a new Excel file.

It looks like the problem is somewhere in the OfficeOpenXml.ExcelRow.set_Height() function.

Here are some performance profiler pictures comparing the 50, 100, 200 image runs. When I go much above that number of images then the captured profile gets too big to work with (many GB in size).

I'm brand new to the EPPlus code/project, so I'm not sure where to look next. I was hoping that given the set_Height() clue, that someone more intimate with the project would know what to look for.

Here is the loop where I add image files to the sheet. The commented out line is the one that kills performance when there is a high number of images in the file. (25 seconds with the height assignment, 3.3 seconds when commented out on 500 images)

           foreach (string sFile in listFiles)
           {
              nRows++;

              var img = System.Drawing.Image.FromFile(sFile);
              var pic = sheet.Drawings.AddPicture(nRows.ToString(), img);
              pic.SetPosition(nRows - 1, 2, nCol, 2);
              //sheet.Row(nRows).Height = (img.Height + 4) * 72.0 / 96.0;
           }

[JanKallman]

Optimized the Add method, so It should be a lot faster now. Feel free to try it out

[hbruun]

I pulled down the source and rebuilt my local project. I'm seeing slightly faster 'AddPicture' calls, but I'm seeing the same problem as before when I try to adjust the height of the rows that I'm adding pictures to (see the line I had commented out in my example code above).

Is there a source file I can look for to make sure I pull the source that included your update?

[DominicEliot]

I just added about 6000 small unique images to a spreadsheet in about 17 seconds. Thanks for the update! P.S. don't forget to dispose your images and bitmaps, I was having really long execution times when I wasn't.

[romcode]

Hi, even with the changes of the optimized version ('25 April) I'm experimenting the same problems, as @hbruun noticed, EPPlus is still slow (almost freeze) with excel files with a lot of images (e.g. >1000 ), especially when you try to set the height of the row in order to properly adjust to the image size.

Note that if you don't adjust the row height to be able to content the image, the excel file would be totally illegible by the end user, and also when the excel is sorted by the user, the images aren't properly attached to their cells.

Hoping anyone has a solution or workaround... Thanks!

[hbruun]

@romcode is correct the problem is there. Setting the row height kills performance on sheets with a lot of images.

[KalpeshPopat]

I have noticed that while AddPicture has improved speed after the latest update - however setting the row and column offset of the picture is getting progressively slower. And when i analysed the source code it seems for each of this property lot of background work is happening writing it on xml.