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

Closed

Conditionally Formatted row delete slow

description

Great product, I really appreaciate the time you took to develop this.
 
I am running into a performance issue when deleting a row.
When a range is conditionally formatted and a row is deleted, it is extremely slow and uses a ton of memory.
 
Running the code below with the sample files
Conditional Time : 43.222
Time : 0.061
 
 
DateTime start = DateTime.Now;
        using (XLWorkbook b = new XLWorkbook("C:\\conditional_format_test.xlsx"))
        {
 
            IXLWorksheet sh1 = b.Worksheets.Worksheet(1);
            IXLRange insertRange = sh1.Range("B1:C10");
 
            sh1.Row(5).Delete();
            sh1.Row(5).Delete();
 
            b.SaveAs("C:\\conditional_format_test_Modify.xlsx");
        }
 
        System.Diagnostics.Trace.WriteLine("Conditional Time : " + (DateTime.Now - start).TotalSeconds.ToString());
 
        start = DateTime.Now;
        using (XLWorkbook b = new XLWorkbook("C:\\test.xlsx"))
        {
 
            IXLWorksheet sh1 = b.Worksheets.Worksheet(1);
            IXLRange insertRange = sh1.Range("B1:C10");
 
 
            sh1.Row(5).Delete();
            sh1.Row(5).Delete();
 
            b.SaveAs("C:\\test_Modify.xlsx");
        }
 
        System.Diagnostics.Trace.WriteLine("Time : " + (DateTime.Now - start).TotalSeconds.ToString());

file attachments

Closed Apr 25 at 9: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 Sep 25, 2012 at 5:58 PM

Pick up the latest source code.

IS0LD0UT wrote Sep 25, 2012 at 7:21 PM

The performance is great now. Thank you for the quick patch.

One small issue.
When I look at the output file C:\conditional_format_test_Modify.xlsx, the conditional format has lost it's assigned background color. Also I noticed with the previous version, the color would get lost if I used a color outside of the main color pallate (like the 3rd color group in the picker). Now it seems the style is not applied at all. Also, if I try to set the font to bold, it is lost in the output file as well.

IS0LD0UT wrote Sep 26, 2012 at 3:58 PM

Looks like the style id applied to the conditional format is off by one ID. If I unzip the xlsx file and modify the sheet1.xml from dxfId="3" to dxfId="2", rezip and open in Excel I see the formatting in the format I used.

MDeLeon wrote Sep 26, 2012 at 5:55 PM

I don't get that behavior. Are you using the latest source code?

IS0LD0UT wrote Sep 26, 2012 at 6:14 PM

I am using the code after the fix was implemented yesterday. Let me get latest again and try.

IS0LD0UT wrote Sep 26, 2012 at 6:29 PM

Just got 76887. Same issue. Attaching new spreadsheet. conditional_format_test2.xlsx and the output conditional_format_test2_Modify.xlsx

If you look at the input file you should see some red/pink cells colored by conditional formatting. The output file has the conditional but the formatting is lost.

Running the simple code below produces the result. If you remove the delete line, you will end up with the
        DateTime start = DateTime.Now;
        using (XLWorkbook b = new XLWorkbook("C:\\conditional_format_test2.xlsx"))
        {
            IXLWorksheet sh1 = b.Worksheets.Worksheet(1);
            sh1.Row(5).Delete();
            b.SaveAs("C:\\conditional_format_test2_Modify.xlsx");
        }

        System.Diagnostics.Trace.WriteLine("Conditional Time : " + (DateTime.Now - start).TotalSeconds.ToString());

IS0LD0UT wrote Sep 26, 2012 at 6:30 PM

output file attached. This shows when the delete line is in place what is generated. You will notice no colors.

MDeLeon wrote Sep 26, 2012 at 6:33 PM

That was my bad, sorry. I'm working on it.

IS0LD0UT wrote Sep 26, 2012 at 6:36 PM

Other comment was truncated... Removing the delete line will result in a file that still has the colors.

MDeLeon wrote Sep 26, 2012 at 7:47 PM

Well... the good news is that I was able to pin point the root of the problem. The bad news is that I have no idea how I'll fix it =p

Don't worry, I'll come up with something...

MDeLeon wrote Sep 26, 2012 at 7:50 PM

Well... the good news is that I was able to pin point the root of the problem. The bad news is that I have no idea how I'll fix it =p

Don't worry, I'll come up with something...

MDeLeon wrote Sep 28, 2012 at 5:03 PM

It should be fine now. Pick up the latest source code and let me know if anything.

IS0LD0UT wrote Sep 28, 2012 at 6:41 PM

We are getting there...

The background color is coming through in the output document, however the strikethrough and bold are still lost.

Also one other issue I noticed, is if you click a cell (make it the selected) in the input file and delete the cell via code. The file is generated, although when you open the file in Excel, it will complain about being corrupt. So using my example sheets above I select cell C5 then save the input file. Then run the code which deletes row 5, and the output file is corrupt to excel.

Thanks!

MDeLeon wrote Oct 2, 2012 at 5:17 AM

Sorry about that. I'll fix it tomorrow...

MDeLeon wrote Oct 4, 2012 at 3:59 AM

If the moon and the stars are properly aligned then everything should be fine. Pick up the latest code.

IS0LD0UT wrote Oct 4, 2012 at 3:41 PM

I think you got it. Thanks!

IS0LD0UT wrote Oct 10, 2012 at 6:56 PM

Another issue showed up in my code. I have a table defined and a column with conditional formatting. When I delete all the rows except for one, then insert a number of rows above. The conditional format is lost. If I use insertRowsBelow, the conditional seems to stay however the table does not expand. I would assume that the insertRowsBelow may be expected to work this way so the issue seems to be with the insertRowsAbove.

It is also nice that the insertRowsBelow copy the format and keep the range as one conditional format, rather than 10 seperate conditionals. I am hoping the insert rows above can work the same way.

using (XLWorkbook b = new XLWorkbook("C:\conditional_format_test3.xlsx")) {
            IXLWorksheet sh1 = b.Worksheets.Worksheet(1);

            sh1.Row(4).Delete();                sh1.Row(4).Delete();                sh1.Row(4).Delete();                sh1.Row(4).Delete();                sh1.Row(4).Delete();                sh1.Row(4).InsertRowsAbove(10); // this remove the whole conditionals                

            b.SaveAs("C:\\conditional_format_test3_modifyInsertAbove.xlsx");            }

        using (XLWorkbook b = new XLWorkbook("C:\\conditional_format_test3.xlsx"))            {

            IXLWorksheet sh1 = b.Worksheets.Worksheet(1);

            sh1.Row(4).Delete();                sh1.Row(4).Delete();                sh1.Row(4).Delete();                sh1.Row(4).Delete();                sh1.Row(4).Delete();                sh1.Row(4).InsertRowsBelow(10); // this works, but does not move the table down

            b.SaveAs("C:\\conditional_format_test3_modifyInsertBelow.xlsx");            }

MDeLeon wrote Oct 11, 2012 at 11:33 PM

Fixed on v0.68.0


** Closed by MDeLeon 10/7/2012 10:27 PM

MDeLeon wrote Oct 11, 2012 at 11:33 PM

Back to work on this one.