Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4ec4b30

Browse files
committed
Enforce one-dv-per-cell invariant
When the the XLDataValidation had the IXLRanges replaced withthe XLAreaList, the DataValications also stopped enforcingone-dv-per-cell invariant. There were also no failing tests forthe invariant.The switch and removal was done to fix data validation shifting,but invariant wasn't implemented in the change to keep scopereasonable.To make sure the invariant is enforced, adding an area to datavalidation is done in a single function in XLDataValidations.The collection contains all DVs in a sheet and adjusts/removesDVs that no longer have any area.The invariant has to be enforced only when area is added to DV,removal of areas can't violate invariant. Thus IXLDV.ClearRanges()and other area removals can be done directly in XLDataValidation.This is basically emergecy repair, in the long run, whole conceptualmodel of DVs + API should be replaced with something less concernedwith actual data structures and more with behavior. Addition or removalof XLDataValidations should be basically transparent to user.Example: ws.Range("A1:C3").DataValidations.List("one,two") ws.Range("A1:C3").DataValidations.Clear()And all this DV uses these areas should be hidden away. I thinkproviding API that treats XML DV element as an API object is a mistake.
1 parentfb8f754 commit4ec4b30

File tree

9 files changed

+193
-101
lines changed

9 files changed

+193
-101
lines changed

‎ClosedXML.Tests/Excel/DataValidations/DataValidationTests.cs‎

Lines changed: 7 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -292,12 +292,6 @@ public void ListLengthOverflow()
292292
}
293293
}
294294

295-
[Test]
296-
publicvoidCannotCreateDataValidationWithoutRange()
297-
{
298-
Assert.Throws<ArgumentNullException>(()=>newXLDataValidation(null));
299-
}
300-
301295
[Test]
302296
publicvoidDataValidationHasWorksheetAndRangesWhenCreated()
303297
{
@@ -306,9 +300,9 @@ public void DataValidationHasWorksheetAndRangesWhenCreated()
306300
varws=wb.AddWorksheet();
307301
varrange=ws.Range("A1:A3");
308302

309-
vardv=newXLDataValidation(range);
303+
vardv=range.CreateDataValidation();
310304

311-
Assert.AreSame(ws,dv.Worksheet);
305+
Assert.AreSame(ws,((XLDataValidation)dv).Worksheet);
312306
Assert.AreSame(range,dv.Ranges.Single());
313307
}
314308
}
@@ -322,7 +316,7 @@ public void CanAddRangeFromSameWorksheet()
322316
varrange1=ws.Range("A1:A3");
323317
varrange2=ws.Range("C1:C3");
324318
varranges3=ws.Ranges("D1:D3,F1:F3");
325-
vardv=newXLDataValidation(range1);
319+
vardv=range1.CreateDataValidation();
326320

327321
dv.AddRange(range2);
328322
dv.AddRanges(ranges3);
@@ -343,7 +337,7 @@ public void CanAddRangeFromAnotherWorksheet()
343337
varws2=wb.AddWorksheet();
344338
varrange1=ws1.Range("A1:A3");
345339
varrange2=ws2.Range("C1:C3");
346-
vardv=newXLDataValidation(range1);
340+
vardv=range1.CreateDataValidation();
347341

348342
dv.AddRange(range2);
349343

@@ -360,7 +354,7 @@ public void CanClearRanges()
360354
varrange1=ws.Range("A1:A3");
361355
varrange2=ws.Range("C1:C3");
362356
varranges3=ws.Ranges("D1:D3,F1:F3");
363-
vardv=newXLDataValidation(range1);
357+
vardv=range1.CreateDataValidation();
364358
dv.AddRange(range2);
365359
dv.AddRanges(ranges3);
366360

@@ -379,7 +373,7 @@ public void CanRemoveExistingRange()
379373
varrange1=ws.Range("A1:A3");
380374
varrange2=ws.Range("C1:C3");
381375

382-
vardv=newXLDataValidation(range1);
376+
vardv=range1.CreateDataValidation();
383377
dv.AddRange(range2);
384378

385379
dv.RemoveRange(range1);
@@ -397,7 +391,7 @@ public void RemovingExistingRangeDoesNoFail()
397391
varrange1=ws.Range("A1:A3");
398392
varrange2=ws.Range("C1:C3");
399393

400-
vardv=newXLDataValidation(range1);
394+
vardv=range1.CreateDataValidation();
401395

402396
dv.RemoveRange(range2);
403397
dv.RemoveRange(null);

‎ClosedXML.Tests/Excel/DataValidations/XLDataValidationsTests.cs‎

Lines changed: 85 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,18 +1,12 @@
1+
usingSystem.Collections.Generic;
2+
usingSystem.Linq;
13
usingClosedXML.Excel;
24
usingNUnit.Framework;
3-
usingSystem;
4-
usingSystem.Linq;
55

66
namespaceClosedXML.Tests.Excel.DataValidations
77
{
88
publicclassXLDataValidationsTests
99
{
10-
[Test]
11-
publicvoidCannotCreateWithoutWorksheet()
12-
{
13-
Assert.Throws<ArgumentNullException>(()=>newXLDataValidations(null));
14-
}
15-
1610
[Test]
1711
publicvoidAddedRangesAreTransferredToTargetSheet()
1812
{
@@ -36,6 +30,83 @@ public void AddedRangesAreTransferredToTargetSheet()
3630
}
3731
}
3832

33+
[Test]
34+
[Description("Ensure one-dv-per-cell invariant")]
35+
publicvoidAddRange_replaces_intersecting_areas_of_validation()
36+
{
37+
usingvarwb=newXLWorkbook();
38+
varws=wb.AddWorksheet();
39+
vardv=ws.Range("A1:C3").CreateDataValidation();
40+
dv.MinValue="10";
41+
42+
dv.AddRange(ws.Range("B1:D4"));
43+
44+
Assert.AreEqual("A1:A3 B1:D4",ToSpaceList(dv.Ranges));
45+
Assert.AreEqual("10",dv.MinValue);
46+
}
47+
48+
[Test]
49+
publicvoidAddRange_keeps_validation_settings_even_when_it_completely_covers_original()
50+
{
51+
usingvarwb=newXLWorkbook();
52+
varws=wb.AddWorksheet();
53+
vardv=ws.Range("B2:C3").CreateDataValidation();
54+
dv.MinValue="10";
55+
56+
dv.AddRange(ws.Range("A1:D4"));
57+
58+
Assert.AreEqual("A1:D4",ToSpaceList(dv.Ranges));
59+
Assert.AreEqual("10",dv.MinValue);
60+
}
61+
62+
[Test]
63+
[Description("Ensure one-dv-per-cell invariant")]
64+
publicvoidAddRange_replaces_area_of_other_validations()
65+
{
66+
// Arrange
67+
usingvarwb=newXLWorkbook();
68+
varws=wb.AddWorksheet();
69+
70+
vardv1=ws.Range("A1:A3").CreateDataValidation();
71+
dv1.WholeNumber.Between(1,5);
72+
73+
vardv2=ws.Range("B2:D4").CreateDataValidation();
74+
dv1.MaxValue="10";
75+
76+
// Act
77+
dv1.AddRange(ws.Range("B1:D3"));
78+
79+
// Assert
80+
Assert.AreEqual("A1:A3 B1:D3",ToSpaceList(dv1.Ranges));
81+
Assert.AreEqual("B4:D4",ToSpaceList(dv2.Ranges));
82+
}
83+
84+
[Test]
85+
publicvoidAddRange_deletes_other_validations_that_are_not_used_by_any_cell()
86+
{
87+
// Arrange
88+
usingvarwb=newXLWorkbook();
89+
varws=wb.AddWorksheet();
90+
91+
vardv1=ws.Range("A1:A3").CreateDataValidation();
92+
dv1.WholeNumber.Between(1,5);
93+
94+
vardv2=ws.Range("B1:B3").CreateDataValidation();
95+
dv1.MaxValue="10";
96+
97+
// Act
98+
dv1.AddRange(ws.Range("B1:B3"));
99+
100+
// Assert
101+
Assert.AreEqual(1,ws.DataValidations.Count());
102+
103+
Assert.IsTrue(ws.DataValidations.Contains(dv1));
104+
Assert.AreEqual("A1:A3 B1:B3",ToSpaceList(dv1.Ranges));
105+
106+
Assert.IsFalse(ws.DataValidations.Contains(dv2));
107+
Assert.IsEmpty(ToSpaceList(dv2.Ranges));
108+
}
109+
39110
[TestCase("A1:A1",true)]
40111
[TestCase("A1:A3",true)]
41112
[TestCase("A1:A4",false)]
@@ -137,7 +208,7 @@ public void ConsolidatedDataValidationsAreUnsubscribed()
137208
vardv2=ws.Range("B1:B3").CreateDataValidation();
138209
dv2.MinValue="100";
139210

140-
(ws.DataValidationsasXLDataValidations).Consolidate();
211+
((XLDataValidations)ws.DataValidations).Consolidate();
141212
dv1.AddRange(ws.Range("C1:C3"));
142213
dv2.AddRange(ws.Range("D1:D3"));
143214

@@ -147,5 +218,10 @@ public void ConsolidatedDataValidationsAreUnsubscribed()
147218
Assert.False(ws.Cell("D1").HasDataValidation);
148219
}
149220
}
221+
222+
privatestaticstringToSpaceList(IEnumerable<IXLRange>ranges)
223+
{
224+
returnstring.Join(" ",ranges.Select(x=>x.RangeAddress.ToStringRelative()));
225+
}
150226
}
151227
}

‎ClosedXML/Excel/Cells/XLCell.cs‎

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1065,9 +1065,7 @@ private IXLDataValidation FindDataValidation()
10651065

10661066
internalXLDataValidationCreateDataValidation()
10671067
{
1068-
varvalidation=newXLDataValidation(AsRange());
1069-
Worksheet.DataValidations.Add(validation);
1070-
returnvalidation;
1068+
returnWorksheet.DataValidations.Create(newXLSheetRange(SheetPoint));
10711069
}
10721070

10731071
publicvoidSelect()
@@ -1468,7 +1466,7 @@ private void CopyDataValidationFrom(XLCell otherCell)
14681466
CopyDataValidation(otherCell,otherCell.GetDataValidation());
14691467
elseif(HasDataValidation)
14701468
{
1471-
Worksheet.DataValidations.Delete(newXLBookArea(Worksheet.Name,SheetPoint));
1469+
Worksheet.DataValidations.Delete(newXLSheetRange(SheetPoint));
14721470
}
14731471
}
14741472

‎ClosedXML/Excel/DataValidation/XLDataValidation.cs‎

Lines changed: 11 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -11,24 +11,9 @@ internal class XLDataValidation : IXLDataValidation
1111
{
1212
privatereadonlyXLWorksheet_worksheet;
1313

14-
publicXLDataValidation(IXLRangerange)
15-
:this(range?.WorksheetasXLWorksheet)
16-
{
17-
if(range==null)thrownewArgumentNullException(nameof(range));
18-
19-
AddRange(range);
20-
}
21-
22-
publicXLDataValidation(XLDataValidationdataValidation,XLWorksheetworksheet)
23-
:this(worksheet)
14+
internalXLDataValidation(XLWorksheetworksheet)
2415
{
2516
_worksheet=worksheet;
26-
CopyFrom(dataValidation);
27-
}
28-
29-
privateXLDataValidation(XLWorksheetworksheet)
30-
{
31-
_worksheet=worksheet??thrownewArgumentNullException(nameof(worksheet));
3217
Initialize();
3318
}
3419

@@ -41,7 +26,7 @@ public void Clear()
4126
Initialize();
4227
}
4328

44-
publicvoidCopyFrom(IXLDataValidationdataValidation)
29+
internalvoidCopyFrom(IXLDataValidationdataValidation)
4530
{
4631
if(dataValidation==this)return;
4732

@@ -186,12 +171,17 @@ public XLWholeNumberCriteria WholeNumber
186171
/// <param name="range">A range to add.</param>
187172
publicvoidAddRange(IXLRangerange)
188173
{
189-
if(range==null)thrownewArgumentNullException(nameof(range));
174+
if(range==null)
175+
thrownewArgumentNullException(nameof(range));
190176

191-
if(range.Worksheet!=Worksheet)
192-
range=Worksheet.Range(((XLRangeAddress)range.RangeAddress).WithoutWorksheet());
177+
// Do not add area if the DV has been detached (e.g. consolidation).
178+
varisDetached=!_worksheet.DataValidations.Contains(this);
179+
if(isDetached)
180+
return;
193181

194-
Areas=Areas.With(XLBookArea.From(range).Area);
182+
// Ignore sheet of a range
183+
vararea=XLSheetRange.FromRangeAddress(range.RangeAddress);
184+
_worksheet.DataValidations.AddArea(this,area);
195185
}
196186

197187
/// <summary>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp