diff --git a/ClosedXML/ClosedXML/ClosedXML.sln b/ClosedXML/ClosedXML/ClosedXML.sln index 67b4c91..2ec88b1 100644 --- a/ClosedXML/ClosedXML/ClosedXML.sln +++ b/ClosedXML/ClosedXML/ClosedXML.sln @@ -135,6 +135,9 @@ GlobalSection(SolutionProperties) = preSolution HideSolutionNode = FALSE EndGlobalSection + GlobalSection(Performance) = preSolution + HasPerformanceSessions = true + EndGlobalSection GlobalSection(TestCaseManagementSettings) = postSolution CategoryFile = ClosedXML.vsmdi EndGlobalSection diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index 6cd4edb..157017f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -250,6 +250,7 @@ IXLWorksheet Worksheet { get; } IXLDataValidation DataValidation { get; } + IXLDataValidation NewDataValidation { get; } IXLDataValidation SetDataValidation(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index d6b7826..281e85a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -140,6 +140,17 @@ } } + public IXLDataValidation NewDataValidation + { + get + { + using (var asRange = AsRange()) + { + return asRange.NewDataValidation; // Call the data validation without breaking it into pieces + } + } + } + public XLDataValidation DataValidation { get @@ -159,9 +170,6 @@ return d as XLDataValidation; } } - //return - // Worksheet.DataValidations.First( - // d => d.Ranges.Count == 1 && d.Ranges.Single().RangeAddress.ToStringRelative().Equals(asRange.RangeAddress.ToStringRelative())) as XLDataValidation; } return null; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 6b946c8..3ac3162 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -95,6 +95,18 @@ get { return RangeAddress.Worksheet; } } + public XLDataValidation NewDataValidation + { + get + { + var newRanges = new XLRanges { AsRange() }; + var dataValidation = new XLDataValidation(newRanges); + + Worksheet.DataValidations.Add(dataValidation); + return dataValidation; + } + } + public XLDataValidation DataValidation { get diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index afed638..112221e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -73,7 +73,7 @@ if (_fieldNames.ContainsKey(name)) throw new ArgumentException("The header row contains more than one field name '" + name + "'."); - _fieldNames.Add(name, new XLTableField(this) {Index = cellPos++, Name = name}); + _fieldNames.Add(name, new XLTableField(this) {Index = cellPos++ }); } } else @@ -87,7 +87,7 @@ { var name = "Column" + i; - _fieldNames.Add(name, new XLTableField(this) {Index = i - 1, Name = name}); + _fieldNames.Add(name, new XLTableField(this) {Index = i - 1 }); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index f9cf5a0..5600666 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -1,5 +1,6 @@ using System; using System.Collections.Generic; +using System.Diagnostics; using System.Linq; using System.Text; using ClosedXML.Excel; @@ -14,18 +15,42 @@ { private static void Main(string[] args) { - var workbook = new XLWorkbook(); - var worksheet = workbook.AddWorksheet("Test"); - worksheet.Cell(2, 2).SetValue("Text"); - var cf = worksheet.Cell(2, 2).AddConditionalFormat(); - var style = cf.WhenNotBlank(); - style - .Fill.SetBackgroundColor(XLColor.Red) - .Border.SetOutsideBorder(XLBorderStyleValues.Thick) - .Border.SetOutsideBorderColor(XLColor.Blue); - workbook.SaveAs(@"C:\temp\saved.xlsx"); - Console.WriteLine("Done"); - //Console.ReadKey(); + var timer = new Stopwatch(); + var timerAll = new Stopwatch(); + timerAll.Start(); + 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"); + + timer.Start(); + for (int i = 1; i <= totalRows; i++) + { + ws.Cell(i, 1).NewDataValidation.List(listRange); + Console.Clear(); + } + timer.Stop(); + } + + wb.SaveAs(@"C:\temp\test.xlsx"); + } + timerAll.Stop(); + Console.WriteLine(); + Console.WriteLine("Add validation Took {0}s", timer.Elapsed.TotalSeconds); + Console.WriteLine("Complete Took {0}s", timerAll.Elapsed.TotalSeconds); + Console.ReadKey(); } }