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

Closed

Row insertion very slow

description

Row insertion gets progressively slower and slower upon the length of a worksheet getting longer than 20 rows or so. Class is attached. You can simply instantiate an object. parameters to CompareSpreadsheets() are:

spreadsheet1 path, spreadsheet2 path, desired output path, unique column number (Need this to be able to compare identical lines in different spreadsheets.)

file attachments

Closed Apr 25 at 8:39 PM by igitur
Please try the latest version of ClosedXML (v0.87.1 at this stage). If you still experience the issue, log it on the new GitHub page.

comments

MDeLeon wrote Dec 12, 2012 at 7:18 PM

Attach two files I can use because the ones I created with random data are processed quickly.

intrinsic wrote Dec 12, 2012 at 9:25 PM

I can simulate it consistently with one spreadsheet with say 150 rows, the 2nd with 300. The 150 extra rows are added on the 2nd spreadsheet, and I commented out the appending code, so only the insertion code is executing like so:

//commented out temporarily to test insertions: //if (reportrowtoinsert == null || reportrowtoinsert.Count() > 1 || reportrowtoinsert.Count() //{ // 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)); ////Slow insertion below. Looking for a solution. //} //else //{ //var foundrow = reportrowtoinsert.First(); var foundrownumber = 20; // 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; //}

intrinsic wrote Dec 12, 2012 at 9:42 PM

Use the new .cs file and these 2 spreadsheets. It slowed down to 1 min to insert 2 rows after a few iterations.

intrinsic wrote Dec 12, 2012 at 9:42 PM

Spreadsheet 1

intrinsic wrote Dec 12, 2012 at 9:43 PM

Spreadsheet 2

Use the latest version of excelcompareprovider.cs which has the appropriate code commented out.

intrinsic wrote Dec 12, 2012 at 10:48 PM

One other thing I would like to do is copy the result of formulas instead of the formulas themselves. Right now, I copy row by row which is relatively fast, but I tried copying cell by cell and it is extremely slow, but that seems to be the only way to copy the result of a formula (not the formula itself) from a given number of cells.

intrinsic wrote Dec 13, 2012 at 11:50 PM

One other way would be to copy to a datatable then output the entire datatable row to the spreadsheet. But, then I would have to copy the styling, and other info along with it. Is this a good approach?

MDeLeon wrote Dec 14, 2012 at 7:10 PM

I'm using your files and code and the insert operation always take 30-50 ms. In other words, I can't reproduce it. Create a project for me.

intrinsic wrote Dec 17, 2012 at 10:59 PM

Is it because it's used in the context of an asp.net web application? every asp.net project takes up huge resources only when I incorporate closedxml into it.

intrinsic wrote Dec 21, 2012 at 5:10 PM

Is your test application a Windows application or Web application?

MDeLeon wrote Dec 21, 2012 at 6:20 PM

A console app. If you can't reproduce it in a console app then it's your environment, not the library.

intrinsic wrote Dec 21, 2012 at 8:18 PM

It very well could be. I'll try a new web app with just the closed xml as well as a console app.