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

Inserting Row works too slow

Mar 20, 2012 at 12:36 PM
Edited Mar 20, 2012 at 12:37 PM

Hi!

I've tried to insert some rows in the cycle and discover that with every next iteration method InsertRowBellow() works slower and slower (even if I insert only 1 row it can work 1,5,15... seconds). What's wrong?

for (int i = 0; i < 50; i++)
    range.LastRow().WorksheetRow().InsertRowsBelow(1);

I know that I can do it by call InsertRowsBelow(50), but this is only example.

Actually, I inserting rows in recursive method. And I can't insert all rows by one call InsertRowsBelow()

 

Thanks,

Vlad

Mar 21, 2012 at 12:02 AM

Pick up the latest check-in... InsertRowsBelow has dramatically improved

Mar 21, 2012 at 5:21 AM
Edited Mar 21, 2012 at 5:22 AM

Problem is still the same((

Nov 1, 2012 at 8:28 PM

I have the same problem here. Every time I insert a row it takes ~30 seconds. I believe it's because I have a lot of formulas and NamedRanges in them and ClosedXML tries to update them all?

Coordinator
Nov 1, 2012 at 8:35 PM

Kyopaxa, how can I reproduce this? Create an issue if you need to attach a file.

Nov 1, 2012 at 10:03 PM

Hi,

I just tired with the following code and could reproduce the delay when inserting rows above.

    class Program
    {
        static Random r = new Random();
        static Stopwatch timeCount = new Stopwatch();

        static void Main(string[] args)
        {
            using (XLWorkbook wb = new XLWorkbook(XLEventTracking.Disabled))
            {
                using (IXLWorksheet sheet = wb.Worksheets.Add("Test"))
                {
                    InsertRandomData(sheet);

                    // Try 10 times
                    foreach (int time in Enumerable.Range(1, 10))
                    {
                        // Generate a random row number
                        int randomRowNumber = r.Next(1, sheet.LastRowUsed().RowNumber());
                        
                        Console.WriteLine("Insert row above {0}...", randomRowNumber);
                        timeCount.Restart();

                        // Insert a row above
                        sheet.Row(randomRowNumber).InsertRowsAbove(1);

                        Console.WriteLine("Done! Took {0} time.", timeCount.Elapsed);
                        timeCount.Stop();
                    }
                }
            }

            Console.ReadKey();
        }


        static void InsertRandomData(IXLWorksheet sheet)
        {
            foreach (int row in Enumerable.Range(1, 100))
            {
                foreach (int col in Enumerable.Range(1, 60))
                {
                    sheet.Cell(row, col).Value = r.Next();
                }
            }
        }
    }
Coordinator
Nov 1, 2012 at 10:58 PM

/wave hand

These aren't the performance issues you're looking for.

 

Pick up the latest source code.

Nov 2, 2012 at 12:26 AM

With the latest source it works a lot faster now, thanks for the quick fix!

Dec 4, 2012 at 2:38 PM

Hi , 

 

I have the latest version of the dll but when i try to insert one row below the current , my page is always loading and nothing happens.

 

First ,  I copy a xls ( template) to a client folder and then i open it with closedXML to insert new rows in it , but i have an issue at that moment .

 

I put a breakpoint on the line where i call the method like this "currentRow.insertRowsBelow(1)"

 

After that , the page is loading but nothing ... :'(

 

The original xslx file contains a few formulas .

 

Please help me , it's very important .

 

Thanks a lot

 

Coordinator
Dec 4, 2012 at 8:22 PM

Give me a way to reproduce it oesebus.

Dec 6, 2012 at 7:15 PM

Yes, I downloaded the latest dll, and exactly the same problem.  Inserting one row like so ReportWorksheet.Row(foundrownumber + 1).InsertRowsBelow(1);  takes several minutes!  But, I'm not sure why, as it was inserting fairly quickly on a different spreadsheet.  But, I don't see why different spreadsheets produce different results, as I'm just inserting a blank row. 

There are several very slow (and resource heavy) statements such as:

 amendedrow.CopyTo(ReportWorksheet.Cell(foundrownumber + 3, 2));  that take a few seconds, and use several MB of data just for one row of 100 cells.  Very concerning, as I can see the memory usage jump from 20 MB to 200MB when writing only about 250 rows to a single spreadsheet using ClosedXML.  It took me a while to try different statements, to minimize memory usage.  At one point, it was using 1.3 GB of memory just to write 250 rows to a single spreadsheet.  There needs to be some serious performance improvements or we will have to stop using this library.

 

Dec 6, 2012 at 7:17 PM

FYI,  I implemented all your performance and memory optimizations, which helped a lot, but I'm stuck on InsertRowsBelow(), which is taking 5 minutes to insert one blank row.  Any help would be appreciated.

 

Coordinator
Dec 6, 2012 at 7:19 PM

Post a piece of code I can use to reproduce it.

Dec 6, 2012 at 7:33 PM
Edited Dec 6, 2012 at 7:41 PM

I'm building a library which mimicks excel compare, and outputs a spreadsheet report which is almost identical to excel compare output.  It's working very well so far, except for the performance issues, and my last hurdle is to attempt to insert added rows below matching deleted rows (so if someone deletes row 3, then adds row 3, I want to insert the added row, below the deleted row on the output spreadsheet report.

Dec 6, 2012 at 7:39 PM

//NOTE:  amendedrowsmatched is just a list (of int) of any rows which matched the master spreadsheet.
// I am comparing 2 spreadsheets (similar to excel compare).  and, the code below is attempting to insert any rows which were added to the 2nd (amended) spreadsheet
//If I comment out the ReportWorksheet.Row(foundrownumber + 1).InsertRowsBelow(2); code, it runs fast, but ideally, I want to insert added rows
//below deleted rows in the final compare spreadsheet.

List<int> amendedrowsmatched = new List<int>();


           int amendedrowcount = amendedWorksheet.LastRowUsed().RowNumber();
            for (int currentamendedrow = 1; currentamendedrow <= amendedrowcount; currentamendedrow++)
            {
              
                if (!amendedrowsmatched.Contains(currentamendedrow))
                {
                    var amendedrow = amendedWorksheet.Row(currentamendedrow);
                    var amendedrowkey = amendedrow.Cell(uniqueColumn).Value.ToString();
                   
                    IXLRows reportrowtoinsert = null;
                    if (string.IsNullOrEmpty(amendedrowkey))
                    {
                        string searchterm  = "Row " + amendedrow.RowNumber().ToString() + " of the Master has been deleted";
                        reportrowtoinsert = ReportWorksheet.RowsUsed(r => r.FirstCellUsed().GetString() == searchterm);
                    }
                    else
                    {
                        reportrowtoinsert = ReportWorksheet.RowsUsed(r => r.Cell(uniqueColumn).GetString() == amendedrowkey);
                    }

                    if (reportrowtoinsert == null || reportrowtoinsert.Count() > 1 || reportrowtoinsert.Count() < 1)
                    {

                        reportrow++;
                        ReportWorksheet.Cell(reportrow, 1).Value = "Row " + amendedrow.RowNumber().ToString() + " has been added";
                        ReportWorksheet.Cell(reportrow, 1).Style.Font.FontColor = XLColor.Red;
                        ReportWorksheet.Cell(reportrow, 1).Style.Fill.BackgroundColor = XLColor.White;
                        reportrow++;
                        ReportWorksheet.Cell(reportrow, 1).Value = ">";
                        ReportWorksheet.Cell(reportrow, 1).Style.Fill.BackgroundColor = XLColor.BabyBlue;
                        amendedrow.CopyTo(ReportWorksheet.Cell(reportrow, 2));

                    }
                    else
                    {
                        var foundrow = reportrowtoinsert.First();
                        var foundrownumber = foundrow.RowNumber();
                        ReportWorksheet.Row(foundrownumber + 1).InsertRowsBelow(2);
                        ReportWorksheet.Cell(foundrownumber + 2, 1).Value = "Row " + amendedrow.RowNumber().ToString() + " has been added";
                        ReportWorksheet.Cell(foundrownumber + 2, 1).Style.Font.FontColor = XLColor.Red;
                        ReportWorksheet.Cell(foundrownumber + 2, 1).Style.Fill.BackgroundColor = XLColor.White;

                        ReportWorksheet.Cell(foundrownumber + 3, 1).Value = ">";
                        ReportWorksheet.Cell(foundrownumber + 3, 1).Style.Fill.BackgroundColor = XLColor.BabyBlue;
                        amendedrow.CopyTo(ReportWorksheet.Cell(foundrownumber + 3, 2));
                        reportrow = reportrow + 2;
                    }
                }
            }

Dec 6, 2012 at 9:27 PM

After more testing on different spreadsheets, I found that the more rows existing in the spreadsheet, the slower the InsertRowsBelow() function is.  Or, that could be more of a memory problem or something like that.  For example, Row 5 was about .5 second,  down to row 22, and it was a second, row 26, 1.25 seconds,  constantly getting slower and slower every time it was called in the loop above. 

 

Coordinator
Dec 6, 2012 at 10:25 PM

I can't copy/paste/run it. Create an issue and attach a project that reproduces the error.

Dec 12, 2012 at 7:50 PM
Edited Dec 12, 2012 at 7:51 PM

I've attached a class.  See here:  http://closedxml.codeplex.com/workitem/8458

 

You can use any 2 spreadsheets, each worksheet should be about 20 rows or more to reproduce my results.

FYI, I'm also running it in an ASP.NET application.

Thanks.

Apr 15, 2013 at 4:20 PM
Hello,
I have experimented that when using this line of code:
worksheet.LastRow();
then InsertRowsBelow(1) works extremely slow.
While I have removed the above calling of LastRow() method then InsertRowsBelow() started to work quickly.

AltWN