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

Cell Merge Performance

May 30, 2014 at 7:21 PM
First off, after working with OpenOfficeXML for a while, I want to thank you for ClosedXML - it truly has made Excel document reading / writing a breeze.

All pandering aside, I could use some help with optimizing my code. I have a very large excel document with some complex formatting that I need programmatically generated. I have noticed some performance hits on merging ranges. I was hoping for some suggestions on this.

Here is some code that represents a small piece of what I am doing:
var timer = System.Diagnostics.Stopwatch.StartNew();
using (XLWorkbook wb = new XLWorkbook(XLEventTracking.Disabled))
{
using (var ws = wb.AddWorksheet("MergeCellsWorksheet"))
{
int total = 5000;

// Insert rows first
ws.Row(total).InsertRowsAbove(total);

// Get Ranges
var merge1 = ws.Ranges("ZZ1:ZZ1");
var merge2 = ws.Ranges("ZZ2:ZZ2");
var merge3 = ws.Ranges("ZZ3:ZZ3");
var merge4 = ws.Ranges("ZZ4:ZZ4");

// Insert some values
for (int i = 1; i <= total; i+=2)
{
ws.Cell(i, 1).Value = "Merge Cell 1";
ws.Cell(i, 3).Value = "Merge Cell 2";
ws.Cell(i, 4).Value = "Merge Cell 3";
ws.Cell((i+1), 4).Value = "Merge Cell 4";

merge1.Add(ws.Range("A" + i + ":B" + (i + 1)));
merge2.Add(ws.Range("C" + i + ":C" + (i + 1)));
merge3.Add(ws.Range("D" + i + ":E" + i));
merge4.Add(ws.Range("D" + (i + 1) + ":E" + (i + 1)));

Console.Clear();
Console.Write(i);
}

// Attempt to merge all cells.
merge1.ForEach(x => x.Merge());
merge2.ForEach(x => x.Merge());
merge3.ForEach(x => x.Merge());
merge4.ForEach(x => x.Merge());
}

wb.SaveAs(@"C:\Test2.xlsm");
}
timer.Stop();
Console.WriteLine("Took {0}s", timer.Elapsed.TotalSeconds);
You can see it takes a while for this to execute.

Help is VERY appreciated!
Coordinator
May 30, 2014 at 8:52 PM
Pick up the latest source code, it exposes range.Merge(Boolean checkIntersects) which allows you to turn off a check to see if the new merge breaks another merge. If you know what you're doing (you know you're not intersecting merged ranges) then you don't need this.

I don't know about your real code but the example you gave does a whole lot of unnecessary steps. Here's the shorter version:
int total = 5000;
var timer = System.Diagnostics.Stopwatch.StartNew();       
using (XLWorkbook wb = new XLWorkbook(XLEventTracking.Disabled))
{
    using (var ws = wb.AddWorksheet("MergeCellsWorksheet"))
    {
        // Insert some values
        for (int i = 1; i <= total; i += 2)
        {
            ws.Cell(i, 1).Value = "Merge Cell 1";
            ws.Cell(i, 3).Value = "Merge Cell 2";
            ws.Cell(i, 4).Value = "Merge Cell 3";
            ws.Cell((i + 1), 4).Value = "Merge Cell 4";

            ws.Range("A" + i + ":B" + (i + 1)).Merge(false);
            ws.Range("C" + i + ":C" + (i + 1)).Merge(false);
            ws.Range("D" + i + ":E" + i).Merge(false);
            ws.Range("D" + (i + 1) + ":E" + (i + 1)).Merge(false);
        }
    }
    wb.SaveAs(@"c:\temp\saved.xlsx");
}
timer.Stop();
Console.WriteLine("Took {0}s", timer.Elapsed.TotalSeconds);
Console.WriteLine("Done");
Jun 2, 2014 at 1:55 PM
That worked perfectly, thanks so much!
Jun 4, 2014 at 2:16 AM
MDeLeon Hello.

Your answer is the solution to the problem I'm in trouble right now.

But ...
And I tried to download version 0.71.2 for. Net Framework3.5, but they may not be able to put a boolean argument to range.Merge.

I'm developing in Visual Studio 2008.

Would you please tell me the solution if you do not mind?

Thank you
Excuse my English.
Coordinator
Jun 4, 2014 at 4:01 PM
You're right takeo. I fix it so download the latest source code (until I make the next release).

Thanks
Coordinator
Jun 4, 2014 at 4:35 PM
Never mind about the source code. Download the latest release: 0.72.0
Jun 5, 2014 at 1:44 AM
Hello, MDeLeon.

Download the latest release, I was replaced with older versions.
It was a movement of as might have been expected just.
Fast, fast!

Thank you very much.