Data Validation Performance

Jun 6, 2014 at 7:59 PM
Hi,

I posted a discussion earlier about Cell Merge Performance. Well the same project I am working on also requires many data validations placed on an excel spreadsheet (can be anywhere from 1000 - 5000 cells with data validations that all point to the same range as a list). I noticed that it takes longer and longer to create the same data validation. Below is some code that is similar to what's being accomplished:
var timer = System.Diagnostics.Stopwatch.StartNew();
using (XLWorkbook wb = new XLWorkbook(XLEventTracking.Disabled))
{
using (var ws = wb.AddWorksheet("MergeCellsWorksheet"))
{
int totalRows = 5000;

// Create some ranges
ws.Cell("AO1").Value = "A";
ws.Cell("AP1").Value = "B";
ws.Cell("AQ1").Value = "C";
ws.Cell("AR1").Value = "D";
ws.Cell("AS1").Value = "E";
ws.Cell("AT1").Value = "1";
ws.Cell("AU1").Value = "2";

var listRange = ws.Range("AO1:AU1");

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

// Insert some values
for (int i = 1; i <= totalRows; i++)
{
ws.Cell(i, 1).DataValidation.List(listRange);
Console.Clear();
}
}

wb.SaveAs(@"C:\Test2.xlsm");
}
timer.Stop();
Console.WriteLine();
Console.WriteLine("Took {0}s", timer.Elapsed.TotalSeconds);
Is there a way to optimize this?

Thank so much!
Coordinator
Jun 7, 2014 at 1:55 AM
Out of curiosity, why do you keep inserting empty rows?
Coordinator
Jun 7, 2014 at 7:49 PM
Pick up the latest source code. I added the property cell.NewDataValidation. Again, use it only if you know there are no overlapping data validations.
Jun 9, 2014 at 2:58 PM
I was under the impression that if you insert rows before you set the values / style etc that it was suppose to improve performance, perhaps I was misinterpreting this.

I altered my code a bit to apply the data validation to all cells at once:
var timer = System.Diagnostics.Stopwatch.StartNew();
            using (XLWorkbook wb = new XLWorkbook(XLEventTracking.Disabled))
            {
                using (var ws = wb.AddWorksheet("MergeCellsWorksheet"))
                {
                    int totalRows = 5000;

                    // Create some ranges
                    ws.Cell("AO1").Value = "A";
                    ws.Cell("AP1").Value = "B";
                    ws.Cell("AQ1").Value = "C";
                    ws.Cell("AR1").Value = "D";
                    ws.Cell("AS1").Value = "E";
                    ws.Cell("AT1").Value = "1";
                    ws.Cell("AU1").Value = "2";

                    var listRange = ws.Range("AO1:AU1");
                    var validationRanges = ws.Range("AA1:AA1");

                    IXLRanges ranges = ws.Ranges("AA1:AA1");
                    ranges = new XLRanges
                    // Insert rows first
                    ws.Row(totalRows).InsertRowsAbove(totalRows);

                    // Insert some values
                    for (int i = 1; i <= totalRows; i++)
                    {
                        ranges.Add(ws.Cell(i, 1));
                        Console.Clear();
                        Console.Write("{0:P1}",((double)i/(double)totalRows));
                    }

                    ranges.SetDataValidation().List(listRange);
                }

                wb.SaveAs(@"C:\Test2.xlsm");
            }
            timer.Stop();
            Console.WriteLine();
            Console.WriteLine("Took {0}s", timer.Elapsed.TotalSeconds);
This seemed to speed it up.
Coordinator
Jun 9, 2014 at 3:09 PM
Use the new cell.NewDataValidation, it's faster.

The link you provided doesn't say insert empty rows, it says that [if you're going to inserte rows] don't insert them one by one, insert them all in one shot. There's no need to insert empty rows if you don't have to.
Jun 10, 2014 at 7:14 PM
You're right, this made a significant difference.

Side note, I noticed the DocumentFormat.OpenXml.dll ClosedXML is referencing needs to be v2.5, but the dll in the root directory from the source download seems to be v2.0. I just ended up downloading it from MS.

Thanks again for all your support!