Performance creating an ExcelPackage with EPPlus drops dramatically when using ExcelGenerator.Cells[x,y].AddComment

[hedenm]

We're creating an ExcelPackage with EPplus in our .NET MVC WebApplication. The number of rows is about 180 and the number of columns is 90. Not all cells have content, but I think we are filling about 10.000 cells.

The problem is: When we don't add comments to the cells it takes 1 minute to generate the package. But when we do add comments to the cells (see code below) it takes 3 minutes! And when we increase the amount of rows or columns we even get an Out-Of-Memory exception in our WebApp. What is causing this big perfomance drop when we use addComment? Is there another way to add comments with EPPlus?

var comment = _worksheet.Cells[rn, _col].AddComment($"{act.LeesbareTijden} \r\n" +
                 locatieNaam +
                 $"{act.Activiteittype.Naam}\r\n" +
                 $"{act.Omschrijving}"
               );                            
comment.AutoFit = true;

[JanKallman]

Yes, comments will likely be slower as it works with the XML DOM to update the data. Comments has separate xml files with both a CommentX.xml and a vml file for the drawing. We should likely look at an alternative way of working with these files to get better performance. I'll add this as an enhancement.

[hedenm]

Thank you for your answer, Jan. Could this also be the reason that we get an out-of-memory exception when we export toom many rows and cells? Regards, Martijn

[JanKallman]

Hard to tell, but maybe so. you can check that you compile for x64 or All Platforms and that you don't that the prefer-32-bit flag set, so all memory can be used. You can also try to upgrade EPPlus to our latest version as we have made a few memory optimizations in the latest versions

[hedenm]

Jan, Thank you, We're allready building for Any CPU ánd are using the latest version of EPPlus. But we will solve it by using a webjob for large excelsheets.

But now I have another error: I have upgraded all the NuGet Packages that we use in our Solution, but when we try to create an export we get the following error: I have checked if the Microsoft.IO.RecycableMemoryStream is installed and even re-installed this package ánd the EPPlus package but that doesn't seem to fix the problem

[JanKallman]

Try to clean your solution and Delete the /bin and /obj folder in your project folders to make sure older references are removed.

[hedenm]

Hello Jan, It turned out that we get this problem when we update the package Microsoft.IO.RecycableMemoryStream from 1.4.1 to 2.2.0. So for now we just keep this package on version 1.4.1 and then our problem is solved. Thanks for thinking with us about our problems! Regards, Martijn