This project has moved. For the latest updates, please go here.

Inserting Rows without a Loop

Feb 26, 2015 at 1:31 PM
Hello!

I've got a problem with inserting rows.
I have an Excel spreadsheet with about 5k rows. I want to insert an additional row after each row that matches a certain criteria and add a formula to it (SUMIF). Doing this in a loop works with small data, but with about 3,5k rows to insert, I always run into an Out Of Memory-exception (because of the recalculation of row numbers and formulas for each iteration).

Is there a better way to accomplish this? From the documentation I've drawn that it is faster to insert them all at once, but if I do this, I can't properly keep track of where they belong (just below the original row) and the data I want them to contain.

I simply cannot wrap my head around this.
Thank you for your help and for creating this awesome library!

-galatyr
Coordinator
Feb 26, 2015 at 4:25 PM
1) Compile your app to 64 bits (to get rid of Out of Memory exception)

2) Turn off events (to speed up the process)

var wb = new XLWorkbook(XLEventTracking.Disabled);

https://closedxml.codeplex.com/wikipage?title=Turning%20off%20events&referringTitle=Documentation
Mar 2, 2015 at 11:31 AM
Thank you for your quick response!

I've already tried turning off events, but that resulted in my algorithm not working anymore...

Compiling to x64 at least liberated me from the Out of Memory exception, but it's still much more than my machine could ever handle.

It seems I have to rebuild my algorithm from scratch then...