diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs
index a164d30..a4625e0 100644
--- a/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/Excel/Cells/XLCell.cs
@@ -1372,7 +1372,7 @@
}
if (includeConditionalFormats
- && Worksheet.ConditionalFormats.Any(cf => cf.Range.Contains(this)))
+ && Worksheet.ConditionalFormats.SelectMany(cf => cf.Ranges).Any(range => range.Contains(this)))
return false;
return true;
@@ -1907,13 +1907,13 @@
var srcSheet = fromRange.Worksheet;
int minRo = fromRange.RangeAddress.FirstAddress.RowNumber;
int minCo = fromRange.RangeAddress.FirstAddress.ColumnNumber;
- if (srcSheet.ConditionalFormats.Any(r => r.Range.Intersects(fromRange)))
+ if (srcSheet.ConditionalFormats.Any(r => r.Ranges.Any(range => range.Intersects(fromRange))))
{
- var fs = srcSheet.ConditionalFormats.Where(r => r.Range.Intersects(fromRange)).ToArray();
+ var fs = srcSheet.ConditionalFormats.SelectMany(cf => cf.Ranges).Where(range => range.Intersects(fromRange)).ToArray();
if (fs.Any())
{
- minRo = fs.Max(r => r.Range.RangeAddress.LastAddress.RowNumber);
- minCo = fs.Max(r => r.Range.RangeAddress.LastAddress.ColumnNumber);
+ minRo = fs.Max(r => r.RangeAddress.LastAddress.RowNumber);
+ minCo = fs.Max(r => r.RangeAddress.LastAddress.ColumnNumber);
}
}
int rCnt = minRo - fromRange.RangeAddress.FirstAddress.RowNumber + 1;
@@ -1921,23 +1921,17 @@
rCnt = Math.Min(rCnt, fromRange.RowCount());
cCnt = Math.Min(cCnt, fromRange.ColumnCount());
var toRange = Worksheet.Range(this, Worksheet.Cell(_rowNumber + rCnt - 1, _columnNumber + cCnt - 1));
- var formats = srcSheet.ConditionalFormats.Where(f => f.Range.Intersects(fromRange));
+ var formats = srcSheet.ConditionalFormats.Where(f => f.Ranges.Any(range => range.Intersects(fromRange)));
foreach (var cf in formats.ToList())
{
- var fmtRange = Relative(Intersection(cf.Range, fromRange), fromRange, toRange);
- var c = new XLConditionalFormat((XLRange)fmtRange, true);
- c.CopyFrom(cf);
- foreach (var v in c.Values.ToList())
- {
- var f = v.Value.Value;
- if (v.Value.IsFormula)
- {
- var r1c1 = ((XLCell)cf.Range.FirstCell()).GetFormulaR1C1(f);
- f = ((XLCell)fmtRange.FirstCell()).GetFormulaA1(r1c1);
- }
+ var fmtRanges = cf.Ranges
+ .Where(r => r.Intersects(fromRange))
+ .Select(r => Relative(Intersection(r, fromRange), fromRange, toRange) as XLRange)
+ .ToList();
- c.Values[v.Key] = new XLFormula { _value = f, IsFormula = v.Value.IsFormula };
- }
+ var c = new XLConditionalFormat(fmtRanges, true);
+ c.CopyFrom(cf);
+ c.AdjustFormulas((XLCell) cf.Ranges.First().FirstCell(), (XLCell)fmtRanges.First().FirstCell());
_worksheet.ConditionalFormats.Add(c);
}
@@ -2079,7 +2073,7 @@
return GetFormula(value, FormulaConversionType.A1ToR1C1, 0, 0);
}
- private string GetFormulaA1(string value)
+ internal string GetFormulaA1(string value)
{
return GetFormula(value, FormulaConversionType.R1C1ToA1, 0, 0);
}
@@ -2326,25 +2320,20 @@
CopyFromInternal(source, copyDataValidations);
- var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(source)).ToList();
+ var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Ranges.Any(range => range.Contains(source))).ToList();
foreach (var cf in conditionalFormats)
{
- var c = new XLConditionalFormat(cf as XLConditionalFormat, AsRange());
- var oldValues = c.Values.Values.ToList();
- c.Values.Clear();
- foreach (var v in oldValues)
+ if (source.Worksheet == Worksheet)
{
- var f = v.Value;
- if (v.IsFormula)
+ if (!cf.Ranges.Any(range => range.Contains(this)))
{
- var r1c1 = source.GetFormulaR1C1(f);
- f = GetFormulaA1(r1c1);
+ cf.Ranges.Add(this);
}
-
- c.Values.Add(new XLFormula { _value = f, IsFormula = v.IsFormula });
}
-
- _worksheet.ConditionalFormats.Add(c);
+ else
+ {
+ CopyConditionalFormatsFrom(source.AsRange());
+ }
}
return this;
diff --git a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs
index 01aedba..0128e12 100644
--- a/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs
+++ b/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs
@@ -140,8 +140,11 @@
Boolean ShowIconOnly { get; }
Boolean ShowBarOnly { get; }
Boolean StopIfTrue { get; }
+ ///
+ /// The first of the .
+ ///
IXLRange Range { get; set; }
-
+ IXLRanges Ranges { get; }
XLDictionary Values { get; }
XLDictionary Colors { get; }
XLDictionary ContentTypes { get; }
diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
index 3ff620a..d388336 100644
--- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
+++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs
@@ -31,8 +31,8 @@
var xxValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v=>v.Value);
var yyValues = yy.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value);
- var xxFormulas = xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)x.Range.FirstCell()).GetFormulaR1C1(f.Value));
- var yyFormulas = yy.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)y.Range.FirstCell()).GetFormulaR1C1(f.Value));
+ var xxFormulas = x.Ranges.Any() ? xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)x.Ranges.First().FirstCell()).GetFormulaR1C1(f.Value)) : null;
+ var yyFormulas = y.Ranges.Any() ? yy.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)y.Ranges.First().FirstCell()).GetFormulaR1C1(f.Value)) : null;
var xStyle = xx.StyleValue;
var yStyle = yy.StyleValue;
@@ -54,15 +54,17 @@
&& _colorsComparer.Equals(xx.Colors, yy.Colors)
&& _contentsTypeComparer.Equals(xx.ContentTypes, yy.ContentTypes)
&& _iconSetTypeComparer.Equals(xx.IconSetOperators, yy.IconSetOperators)
- && (!_compareRange || Equals(xx.Range.RangeAddress, yy.Range.RangeAddress)) ;
+ && (!_compareRange || XLRanges.Equals(xx.Ranges, yy.Ranges));
}
public int GetHashCode(IXLConditionalFormat obj)
{
var xx = (XLConditionalFormat)obj;
var xStyle = (obj.Style as XLStyle).Value;
- var xValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value)
- .Union(xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)obj.Range.FirstCell()).GetFormulaR1C1(f.Value)));
+ var xValues = xx.Values.Values.Where(v => !v.IsFormula).Select(v => v.Value);
+ if (obj.Ranges.Any())
+ xValues = xValues
+ .Union(xx.Values.Values.Where(v => v.IsFormula).Select(f => ((XLCell)obj.Ranges.First().FirstCell()).GetFormulaR1C1(f.Value)));
unchecked
{
@@ -73,7 +75,7 @@
hashCode = (hashCode * 397) ^ (xx.Colors != null ? xx.Colors.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (xx.ContentTypes != null ? xx.ContentTypes.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (xx.IconSetOperators != null ? xx.IconSetOperators.GetHashCode() : 0);
- hashCode = (hashCode * 397) ^ (_compareRange && xx.Range != null ? xx.Range.GetHashCode() : 0);
+ hashCode = (hashCode * 397) ^ (_compareRange && xx.Ranges != null ? xx.Ranges.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (int)xx.ConditionalFormatType;
hashCode = (hashCode * 397) ^ (int)xx.TimePeriod;
hashCode = (hashCode * 397) ^ (int)xx.IconSetStyle;
@@ -89,6 +91,19 @@
}
}
+ internal void AdjustFormulas(XLCell baseCell, XLCell targetCell)
+ {
+ var keys = Values.Keys.ToList();
+ foreach (var key in keys)
+ {
+ if (Values[key] == null || !Values[key].IsFormula)
+ continue;
+
+ var r1c1 = baseCell.GetFormulaR1C1(Values[key].Value);
+ Values[key] = new XLFormula { _value = targetCell.GetFormulaA1(r1c1), IsFormula = true };
+ }
+ }
+
private static readonly IEqualityComparer FullComparerInstance = new FullEqualityComparer(true);
public static IEqualityComparer FullComparer
{
@@ -101,29 +116,39 @@
get { return NoRangeComparerInstance; }
}
- public XLConditionalFormat(XLRange range, Boolean copyDefaultModify = false)
+ #region Constructors
+
+ private XLConditionalFormat(XLStyleValue style)
: base(XLStyle.Default.Value)
{
Id = Guid.NewGuid();
- Range = range;
+ Ranges = new XLRanges();
Values = new XLDictionary();
Colors = new XLDictionary();
ContentTypes = new XLDictionary();
IconSetOperators = new XLDictionary();
- CopyDefaultModify = copyDefaultModify;
+ }
+ public XLConditionalFormat(XLRange range, Boolean copyDefaultModify = false)
+ : this(XLStyle.Default.Value)
+ {
+ if (range != null)
+ Ranges.Add(range);
+ CopyDefaultModify = copyDefaultModify;
+ }
+
+ public XLConditionalFormat(IEnumerable ranges, Boolean copyDefaultModify = false)
+ : this(XLStyle.Default.Value)
+ {
+ ranges?.ForEach(range => Ranges.Add(range));
+ CopyDefaultModify = copyDefaultModify;
}
public XLConditionalFormat(XLConditionalFormat conditionalFormat, IXLRange targetRange)
- : base(conditionalFormat.StyleValue)
+ : this(conditionalFormat.StyleValue)
{
- Range = targetRange;
- Id = Guid.NewGuid();
- Values = new XLDictionary(conditionalFormat.Values);
- Colors = new XLDictionary(conditionalFormat.Colors);
- ContentTypes = new XLDictionary(conditionalFormat.ContentTypes);
- IconSetOperators = new XLDictionary(conditionalFormat.IconSetOperators);
-
+ if (targetRange != null)
+ Ranges.Add(targetRange);
ConditionalFormatType = conditionalFormat.ConditionalFormatType;
TimePeriod = conditionalFormat.TimePeriod;
@@ -135,9 +160,8 @@
ShowIconOnly = conditionalFormat.ShowIconOnly;
ShowBarOnly = conditionalFormat.ShowBarOnly;
StopIfTrue = OpenXmlHelper.GetBooleanValueAsBool(conditionalFormat.StopIfTrue, true);
-
-
}
+ #endregion Constructors
public Guid Id { get; internal set; }
internal Int32 OriginalPriority { get; set; }
@@ -166,7 +190,16 @@
public XLDictionary ContentTypes { get; private set; }
public XLDictionary IconSetOperators { get; private set; }
- public IXLRange Range { get; set; }
+ public IXLRange Range
+ {
+ get { return Ranges.FirstOrDefault(); }
+ set
+ {
+ Ranges.RemoveAll();
+ Ranges.Add(value);
+ }
+ }
+ public IXLRanges Ranges { get; private set; }
public XLConditionalFormatType ConditionalFormatType { get; set; }
public XLTimePeriod TimePeriod { get; set; }
public XLIconSetStyle IconSetStyle { get; set; }
diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs
index f986db8..38aecec 100644
--- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs
+++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs
@@ -13,22 +13,6 @@
_conditionalFormats.Add(conditionalFormat);
}
- private bool IsRangeAbove(IXLRangeAddress newAddr, IXLRangeAddress addr)
- {
- return newAddr.FirstAddress.ColumnNumber == addr.FirstAddress.ColumnNumber
- && newAddr.LastAddress.ColumnNumber == addr.LastAddress.ColumnNumber
- && newAddr.FirstAddress.RowNumber < addr.FirstAddress.RowNumber
- && (newAddr.LastAddress.RowNumber+1).Between(addr.FirstAddress.RowNumber, addr.LastAddress.RowNumber);
- }
-
- private bool IsRangeToLeft(IXLRangeAddress newAddr, IXLRangeAddress addr)
- {
- return newAddr.FirstAddress.RowNumber == addr.FirstAddress.RowNumber
- && newAddr.LastAddress.RowNumber == addr.LastAddress.RowNumber
- && newAddr.FirstAddress.ColumnNumber < addr.FirstAddress.ColumnNumber
- && (newAddr.LastAddress.ColumnNumber+1).Between(addr.FirstAddress.ColumnNumber, addr.LastAddress.ColumnNumber);
- }
-
public IEnumerator GetEnumerator()
{
return _conditionalFormats.GetEnumerator();
@@ -41,7 +25,7 @@
public void Remove(Predicate predicate)
{
- _conditionalFormats.Where(cf=>predicate(cf)).ForEach(cf=>cf.Range.Dispose());
+ _conditionalFormats.Where(cf => predicate(cf)).SelectMany(cf => cf.Ranges).ForEach(range => range.Dispose());
_conditionalFormats.RemoveAll(predicate);
}
@@ -51,63 +35,75 @@
internal void Consolidate()
{
var formats = _conditionalFormats
- .OrderByDescending(x => x.Range.RangeAddress.FirstAddress.RowNumber)
- .ThenByDescending(x => x.Range.RangeAddress.FirstAddress.ColumnNumber);
+ .Where(cf => cf.Ranges.Any())
+ .ToList();
+ _conditionalFormats.Clear();
- var orderedFormats = formats.ToList();
-
- foreach (var item in formats)
+ while (formats.Count > 0)
{
- var itemAddr = item.Range.RangeAddress;
- var itemRowNum = itemAddr.FirstAddress.RowNumber;
+ var item = formats.First();
- Func IsSameFormat = f => f != item && f.Range.Worksheet.Position == item.Range.Worksheet.Position &&
- XLConditionalFormat.NoRangeComparer.Equals(f, item);
+ var rangesToJoin = item.Ranges;
+ var skippedRanges = new XLRanges();
+ Func IsSameFormat = f =>
+ f != item && f.Ranges.First().Worksheet.Position == item.Ranges.First().Worksheet.Position &&
+ XLConditionalFormat.NoRangeComparer.Equals(f, item);
- // search for an adjacent range
- var format = orderedFormats
- .TakeWhile(f => f.Range.RangeAddress.FirstAddress.RowNumber >= itemRowNum)
- .FirstOrDefault(f => (IsRangeAbove(itemAddr, f.Range.RangeAddress) || IsRangeToLeft(itemAddr, f.Range.RangeAddress)) && IsSameFormat(f));
- if (format != null)
+ //Get the top left corner of the rectangle covering all the ranges
+ var baseAddress = new XLAddress(
+ item.Ranges.Select(r => r.RangeAddress.FirstAddress.RowNumber).Min(),
+ item.Ranges.Select(r => r.RangeAddress.FirstAddress.ColumnNumber).Min(),
+ false, false);
+ var baseCell = item.Ranges.First().Worksheet.Cell(baseAddress) as XLCell;
+
+ int i = 1;
+ bool stop = false;
+ List similarFormats = new List();
+ do
{
- Merge(format, item);
- _conditionalFormats.Remove(item);
- orderedFormats.Remove(item);
- // compress with bottom range
- var newaddr = format.Range.RangeAddress;
- var newRowNum = newaddr.FirstAddress.RowNumber;
- var bottom = orderedFormats
- .TakeWhile(f => f.Range.RangeAddress.FirstAddress.RowNumber >= newRowNum)
- .FirstOrDefault(f => IsRangeAbove(newaddr, f.Range.RangeAddress) && IsSameFormat(f));
- if (bottom != null)
+ stop = (i >= formats.Count);
+
+ if (!stop)
{
- Merge(bottom, format);
- _conditionalFormats.Remove(format);
- orderedFormats.Remove(format);
+ var nextFormat = formats[i];
+
+ var intersectsSkipped =
+ skippedRanges.Any(left => nextFormat.Ranges.Any(right => left.Intersects(right)));
+
+ if (IsSameFormat(nextFormat) && !intersectsSkipped)
+ {
+ similarFormats.Add(nextFormat);
+ nextFormat.Ranges.ForEach(r => rangesToJoin.Add(r));
+ }
+ else if (rangesToJoin.Any(left => nextFormat.Ranges.Any(right => left.Intersects(right))) ||
+ intersectsSkipped)
+ {
+ // if we reached the rule intersecting any of captured ranges stop for not breaking the priorities
+ stop = true;
+ }
+ nextFormat.Ranges.ForEach(r => skippedRanges.Add(r));
}
- continue;
- }
- // search for an encompassable range
- format = _conditionalFormats.FirstOrDefault(f => f.Range.Contains(item.Range) && IsSameFormat(f));
- if (format != null)
- {
- _conditionalFormats.Remove(item);
- orderedFormats.Remove(item);
- }
+ i++;
+ } while (!stop);
+
+ var consRanges = rangesToJoin.Consolidate();
+ item.Ranges.RemoveAll();
+ consRanges.ForEach(r => item.Ranges.Add(r));
+
+ var targetCell = item.Ranges.First().FirstCell() as XLCell;
+ (item as XLConditionalFormat).AdjustFormulas(baseCell, targetCell);
+
+ _conditionalFormats.Add(item);
+
+ similarFormats.ForEach(cf => formats.Remove(cf));
+ formats.Remove(item);
}
}
- private static void Merge(IXLConditionalFormat format, IXLConditionalFormat item)
- {
- foreach (var v in format.Values.ToList())
- format.Values[v.Key] = item.Values[v.Key];
- format.Range.RangeAddress.FirstAddress = item.Range.RangeAddress.FirstAddress;
- }
-
public void RemoveAll()
{
- _conditionalFormats.ForEach(cf => cf.Range.Dispose());
+ _conditionalFormats.SelectMany(cf => cf.Ranges).ForEach(range => range.Dispose());
_conditionalFormats.Clear();
}
diff --git a/ClosedXML/Excel/DataValidation/XLDataValidations.cs b/ClosedXML/Excel/DataValidation/XLDataValidations.cs
index 3387588..92fb784 100644
--- a/ClosedXML/Excel/DataValidation/XLDataValidations.cs
+++ b/ClosedXML/Excel/DataValidation/XLDataValidations.cs
@@ -51,6 +51,43 @@
_dataValidations.RemoveAll(dv => dv.Ranges.Contains(range));
}
+ public void Consolidate()
+ {
+ Func areEqual = (dv1, dv2) =>
+ {
+ return
+ dv1.IgnoreBlanks == dv2.IgnoreBlanks &&
+ dv1.InCellDropdown == dv2.InCellDropdown &&
+ dv1.ShowErrorMessage == dv2.ShowErrorMessage &&
+ dv1.ShowInputMessage == dv2.ShowInputMessage &&
+ dv1.InputTitle == dv2.InputTitle &&
+ dv1.InputMessage == dv2.InputMessage &&
+ dv1.ErrorTitle == dv2.ErrorTitle &&
+ dv1.ErrorMessage == dv2.ErrorMessage &&
+ dv1.ErrorStyle == dv2.ErrorStyle &&
+ dv1.AllowedValues == dv2.AllowedValues &&
+ dv1.Operator == dv2.Operator &&
+ dv1.MinValue == dv2.MinValue &&
+ dv1.MaxValue == dv2.MaxValue;
+ };
+
+ var rules = _dataValidations.ToList();
+ _dataValidations.Clear();
+
+ while (rules.Any())
+ {
+ var similarRules = rules.Where(r => areEqual(rules.First(), r)).ToList();
+ similarRules.ForEach(r => rules.Remove(r));
+
+ var consRule = similarRules.First();
+ var ranges = similarRules.SelectMany(dv => dv.Ranges).ToList();
+ consRule.Ranges.RemoveAll();
+ ranges.ForEach(r => consRule.Ranges.Add(r));
+ consRule.Ranges = consRule.Ranges.Consolidate();
+ _dataValidations.Add(consRule);
+ }
+ }
+
public void Dispose()
{
_dataValidations.ForEach(dv => dv.Dispose());
diff --git a/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/Excel/Ranges/IXLRanges.cs
index 815f6f0..66ccda8 100644
--- a/ClosedXML/Excel/Ranges/IXLRanges.cs
+++ b/ClosedXML/Excel/Ranges/IXLRanges.cs
@@ -99,6 +99,11 @@
/// Specify what you want to clear.
IXLRanges Clear(XLClearOptions clearOptions = XLClearOptions.All);
+ ///
+ /// Create a new collection of ranges which are consolidated version of source ranges.
+ ///
+ IXLRanges Consolidate();
+
void Select();
}
}
diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs
index 313d82a..baebfeb 100644
--- a/ClosedXML/Excel/Ranges/XLRangeBase.cs
+++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs
@@ -363,49 +363,62 @@
{
var mf = RangeAddress.FirstAddress;
var ml = RangeAddress.LastAddress;
- foreach (var format in Worksheet.ConditionalFormats.Where(x => x.Range.Intersects(this)).ToList())
+ foreach (var format in Worksheet.ConditionalFormats.Where(x => x.Ranges.Any(r => r.Intersects(this))).ToList())
{
- var f = format.Range.RangeAddress.FirstAddress;
- var l = format.Range.RangeAddress.LastAddress;
- bool byWidth = false, byHeight = false;
- XLRange rng1 = null, rng2 = null;
- if (mf.ColumnNumber <= f.ColumnNumber && ml.ColumnNumber >= l.ColumnNumber)
- {
- if (mf.RowNumber.Between(f.RowNumber, l.RowNumber) || ml.RowNumber.Between(f.RowNumber, l.RowNumber))
- {
- if (mf.RowNumber > f.RowNumber)
- rng1 = Worksheet.Range(f.RowNumber, f.ColumnNumber, mf.RowNumber - 1, l.ColumnNumber);
- if (ml.RowNumber < l.RowNumber)
- rng2 = Worksheet.Range(ml.RowNumber + 1, f.ColumnNumber, l.RowNumber, l.ColumnNumber);
- }
- byWidth = true;
- }
+ var cfRanges = format.Ranges.ToList();
+ format.Ranges.RemoveAll();
- if (mf.RowNumber <= f.RowNumber && ml.RowNumber >= l.RowNumber)
+ foreach (var cfRange in cfRanges)
{
- if (mf.ColumnNumber.Between(f.ColumnNumber, l.ColumnNumber) || ml.ColumnNumber.Between(f.ColumnNumber, l.ColumnNumber))
+ if (!cfRange.Intersects(this))
{
- if (mf.ColumnNumber > f.ColumnNumber)
- rng1 = Worksheet.Range(f.RowNumber, f.ColumnNumber, l.RowNumber, mf.ColumnNumber - 1);
- if (ml.ColumnNumber < l.ColumnNumber)
- rng2 = Worksheet.Range(f.RowNumber, ml.ColumnNumber + 1, l.RowNumber, l.ColumnNumber);
+ format.Ranges.Add(cfRange);
+ continue;
}
- byHeight = true;
- }
- if (rng1 != null)
- {
- format.Range = rng1;
+ var f = cfRange.RangeAddress.FirstAddress;
+ var l = cfRange.RangeAddress.LastAddress;
+ bool byWidth = false, byHeight = false;
+ XLRange rng1 = null, rng2 = null;
+ if (mf.ColumnNumber <= f.ColumnNumber && ml.ColumnNumber >= l.ColumnNumber)
+ {
+ if (mf.RowNumber.Between(f.RowNumber, l.RowNumber) || ml.RowNumber.Between(f.RowNumber, l.RowNumber))
+ {
+ if (mf.RowNumber > f.RowNumber)
+ rng1 = Worksheet.Range(f.RowNumber, f.ColumnNumber, mf.RowNumber - 1, l.ColumnNumber);
+ if (ml.RowNumber < l.RowNumber)
+ rng2 = Worksheet.Range(ml.RowNumber + 1, f.ColumnNumber, l.RowNumber, l.ColumnNumber);
+ }
+ byWidth = true;
+ }
+
+ if (mf.RowNumber <= f.RowNumber && ml.RowNumber >= l.RowNumber)
+ {
+ if (mf.ColumnNumber.Between(f.ColumnNumber, l.ColumnNumber) || ml.ColumnNumber.Between(f.ColumnNumber, l.ColumnNumber))
+ {
+ if (mf.ColumnNumber > f.ColumnNumber)
+ rng1 = Worksheet.Range(f.RowNumber, f.ColumnNumber, l.RowNumber, mf.ColumnNumber - 1);
+ if (ml.ColumnNumber < l.ColumnNumber)
+ rng2 = Worksheet.Range(f.RowNumber, ml.ColumnNumber + 1, l.RowNumber, l.ColumnNumber);
+ }
+ byHeight = true;
+ }
+
+ if (rng1 != null)
+ {
+ format.Ranges.Add(rng1);
+ }
+ if (rng2 != null)
+ {
+ //TODO: reflect the formula for a new range
+ format.Ranges.Add(rng2);
+ }
+
+ if (!byWidth && !byHeight)
+ format.Ranges.Add(cfRange); // Not split, preserve original
}
- if (rng2 != null)
- {
- //TODO: reflect the formula for a new range
- if (rng1 == null)
- format.Range = rng2;
- else
- ((XLConditionalFormat)rng2.AddConditionalFormat()).CopyFrom(format);
- }
- if (byWidth && byHeight) Worksheet.ConditionalFormats.Remove(x => x == format);
+ if (!format.Ranges.Any())
+ Worksheet.ConditionalFormats.Remove(x => x == format);
}
}
diff --git a/ClosedXML/Excel/Ranges/XLRangeConsolidationEngine.cs b/ClosedXML/Excel/Ranges/XLRangeConsolidationEngine.cs
new file mode 100644
index 0000000..4904c32
--- /dev/null
+++ b/ClosedXML/Excel/Ranges/XLRangeConsolidationEngine.cs
@@ -0,0 +1,215 @@
+using System;
+using System.Collections;
+using System.Collections.Generic;
+using System.Linq;
+
+namespace ClosedXML.Excel
+{
+ ///
+ /// Engine for ranges consolidation. Supports IXLRanges including ranges from either one or multiple worksheets.
+ ///
+ internal class XLRangeConsolidationEngine
+ {
+ #region Public Constructors
+
+ public XLRangeConsolidationEngine(IXLRanges ranges)
+ {
+ if (ranges == null)
+ throw new ArgumentNullException(nameof(ranges));
+ _allRanges = ranges;
+ }
+
+ #endregion Public Constructors
+
+ #region Public Methods
+
+ public IXLRanges Consolidate()
+ {
+ if (!_allRanges.Any())
+ return _allRanges;
+
+ var worksheets = _allRanges.Select(r => r.Worksheet).Distinct().OrderBy(ws => ws.Position);
+
+ IXLRanges retVal = new XLRanges();
+ foreach (var ws in worksheets)
+ {
+ var matrix = new XLRangeConsolidationMatrix(ws, _allRanges.Where(r => r.Worksheet == ws));
+ var consRanges = matrix.GetConsolidatedRanges();
+ foreach (var consRange in consRanges)
+ {
+ retVal.Add(consRange);
+ }
+ }
+
+ return retVal;
+ }
+
+ #endregion Public Methods
+
+ #region Private Fields
+
+ private readonly IXLRanges _allRanges;
+
+ #endregion Private Fields
+
+ #region Private Classes
+
+ ///
+ /// Class representing the area covering ranges to be consolidated as a set of bit matrices. Does all the dirty job
+ /// of ranges consolidation.
+ ///
+ private class XLRangeConsolidationMatrix
+ {
+ #region Public Constructors
+
+ ///
+ /// Constructor.
+ ///
+ /// Current worksheet.
+ /// Ranges to be consolidated. They are expected to belong to the current worksheet, no check is performed.
+ public XLRangeConsolidationMatrix(IXLWorksheet worksheet, IEnumerable ranges)
+ {
+ _worksheet = worksheet;
+ PrepareBitMatrix(ranges);
+ FillBitMatrix(ranges);
+ }
+
+ #endregion Public Constructors
+
+ #region Public Methods
+
+ ///
+ /// Get consolidated ranges equivalent to the input ones.
+ ///
+ public IEnumerable GetConsolidatedRanges()
+ {
+ var rowNumbers = _bitMatrix.Keys.OrderBy(k => k).ToArray();
+ for (int i = 0; i < rowNumbers.Length; i++)
+ {
+ var startRow = rowNumbers[i];
+ var startings = GetRangesBoundariesStartingByRow(_bitMatrix[startRow]);
+
+ foreach (var starting in startings)
+ {
+ int j = i + 1;
+ while (j < rowNumbers.Length && RowIncludesRange(_bitMatrix[rowNumbers[j]], starting)) j++;
+
+ var endRow = rowNumbers[j - 1];
+ var startColumn = starting.Item1 + _minColumn - 1;
+ var endColumn = starting.Item2 + _minColumn - 1;
+
+ yield return _worksheet.Range(startRow, startColumn, endRow, endColumn);
+
+ while (j > i)
+ {
+ ClearRangeInRow(_bitMatrix[rowNumbers[j - 1]], starting);
+ j--;
+ }
+ }
+ }
+ }
+
+ #endregion Public Methods
+
+ #region Private Fields
+
+ private readonly IXLWorksheet _worksheet;
+ private Dictionary _bitMatrix;
+ private int _maxColumn = 0;
+ private int _minColumn = XLHelper.MaxColumnNumber + 1;
+
+ #endregion Private Fields
+
+ #region Private Methods
+
+ private void AddToBitMatrix(IXLRangeAddress rangeAddress)
+ {
+ var rows = _bitMatrix.Keys
+ .Where(k => k >= rangeAddress.FirstAddress.RowNumber &&
+ k <= rangeAddress.LastAddress.RowNumber);
+
+ var minIndex = rangeAddress.FirstAddress.ColumnNumber - _minColumn + 1;
+ var maxIndex = rangeAddress.LastAddress.ColumnNumber - _minColumn + 1;
+
+ foreach (var rowNum in rows)
+ {
+ for (int i = minIndex; i <= maxIndex; i++)
+ {
+ _bitMatrix[rowNum][i] = true;
+ }
+ }
+ }
+
+ private void ClearRangeInRow(BitArray rowArray, Tuple rangeBoundaries)
+ {
+ for (int i = rangeBoundaries.Item1; i <= rangeBoundaries.Item2; i++)
+ {
+ rowArray[i] = false;
+ }
+ }
+
+ private void FillBitMatrix(IEnumerable ranges)
+ {
+ foreach (var range in ranges)
+ {
+ AddToBitMatrix(range.RangeAddress);
+ }
+
+ System.Diagnostics.Debug.Assert(
+ _bitMatrix.Values.All(r => r[0] == false && r[r.Length - 1] == false));
+ }
+
+ private IEnumerable> GetRangesBoundariesStartingByRow(BitArray rowArray)
+ {
+ int startIdx = 0;
+ for (int i = 1; i < rowArray.Length - 1; i++)
+ {
+ if (!rowArray[i - 1] && rowArray[i])
+ startIdx = i;
+ if (rowArray[i] && !rowArray[i + 1])
+ yield return new Tuple(startIdx, i);
+ }
+ }
+
+ private void PrepareBitMatrix(IEnumerable ranges)
+ {
+ _bitMatrix = new Dictionary();
+ foreach (var range in ranges)
+ {
+ var address = range.RangeAddress;
+ _minColumn = (_minColumn <= address.FirstAddress.ColumnNumber)
+ ? _minColumn
+ : address.FirstAddress.ColumnNumber;
+ _maxColumn = (_maxColumn >= address.LastAddress.ColumnNumber)
+ ? _maxColumn
+ : address.LastAddress.ColumnNumber;
+
+ if (!_bitMatrix.ContainsKey(address.FirstAddress.RowNumber))
+ _bitMatrix.Add(address.FirstAddress.RowNumber, null);
+ if (!_bitMatrix.ContainsKey(address.LastAddress.RowNumber))
+ _bitMatrix.Add(address.LastAddress.RowNumber, null);
+ }
+
+ var keys = _bitMatrix.Keys.ToList();
+ foreach (var rowNum in keys)
+ {
+ _bitMatrix[rowNum] = new BitArray(_maxColumn - _minColumn + 3, false);
+ }
+ }
+ private bool RowIncludesRange(BitArray rowArray, Tuple rangeBoundaries)
+ {
+ for (int i = rangeBoundaries.Item1; i <= rangeBoundaries.Item2; i++)
+ {
+ if (!rowArray[i])
+ return false;
+ }
+
+ return true;
+ }
+
+ #endregion Private Methods
+ }
+
+ #endregion Private Classes
+ }
+}
diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs
index b97fc4b..3b58d3d 100644
--- a/ClosedXML/Excel/Ranges/XLRanges.cs
+++ b/ClosedXML/Excel/Ranges/XLRanges.cs
@@ -250,5 +250,11 @@
foreach (var range in this)
range.Select();
}
+
+ public IXLRanges Consolidate()
+ {
+ var engine = new XLRangeConsolidationEngine(this);
+ return engine.Consolidate();
+ }
}
}
diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs
index 95c292a..f8b93a9 100644
--- a/ClosedXML/Excel/XLWorkbook_Load.cs
+++ b/ClosedXML/Excel/XLWorkbook_Load.cs
@@ -2115,100 +2115,103 @@
/// Loads the conditional formatting.
///
// https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.conditionalformattingrule%28v=office.15%29.aspx?f=255&MSPPError=-2147217396
- private void LoadConditionalFormatting(ConditionalFormatting conditionalFormatting, XLWorksheet ws, Dictionary differentialFormats)
+ private void LoadConditionalFormatting(ConditionalFormatting conditionalFormatting, XLWorksheet ws,
+ Dictionary differentialFormats)
{
if (conditionalFormatting == null) return;
- foreach (var sor in conditionalFormatting.SequenceOfReferences.Items)
+ foreach (var fr in conditionalFormatting.Elements())
{
- foreach (var fr in conditionalFormatting.Elements())
+ var ranges = conditionalFormatting.SequenceOfReferences.Items
+ .Select(sor => ws.Range(sor.Value));
+ var conditionalFormat = new XLConditionalFormat(ranges);
+
+ conditionalFormat.StopIfTrue = OpenXmlHelper.GetBooleanValueAsBool(fr.StopIfTrue, false);
+
+ if (fr.FormatId != null)
{
- var conditionalFormat = new XLConditionalFormat(ws.Range(sor.Value));
+ LoadFont(differentialFormats[(Int32) fr.FormatId.Value].Font, conditionalFormat.Style.Font);
+ LoadFill(differentialFormats[(Int32) fr.FormatId.Value].Fill, conditionalFormat.Style.Fill,
+ differentialFillFormat: true);
+ LoadBorder(differentialFormats[(Int32) fr.FormatId.Value].Border, conditionalFormat.Style.Border);
+ LoadNumberFormat(differentialFormats[(Int32) fr.FormatId.Value].NumberingFormat,
+ conditionalFormat.Style.NumberFormat);
+ }
- conditionalFormat.StopIfTrue = OpenXmlHelper.GetBooleanValueAsBool(fr.StopIfTrue, false);
+ // The conditional formatting type is compulsory. If it doesn't exist, skip the entire rule.
+ if (fr.Type == null) continue;
+ conditionalFormat.ConditionalFormatType = fr.Type.Value.ToClosedXml();
+ conditionalFormat.OriginalPriority = fr.Priority?.Value ?? Int32.MaxValue;
- if (fr.FormatId != null)
- {
- LoadFont(differentialFormats[(Int32)fr.FormatId.Value].Font, conditionalFormat.Style.Font);
- LoadFill(differentialFormats[(Int32)fr.FormatId.Value].Fill, conditionalFormat.Style.Fill, differentialFillFormat: true);
- LoadBorder(differentialFormats[(Int32)fr.FormatId.Value].Border, conditionalFormat.Style.Border);
- LoadNumberFormat(differentialFormats[(Int32)fr.FormatId.Value].NumberingFormat, conditionalFormat.Style.NumberFormat);
- }
-
- // The conditional formatting type is compulsory. If it doesn't exist, skip the entire rule.
- if (fr.Type == null) continue;
- conditionalFormat.ConditionalFormatType = fr.Type.Value.ToClosedXml();
- conditionalFormat.OriginalPriority = fr.Priority?.Value ?? Int32.MaxValue;
-
- if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs && fr.Operator != null)
- conditionalFormat.Operator = fr.Operator.Value.ToClosedXml();
+ if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs && fr.Operator != null)
+ conditionalFormat.Operator = fr.Operator.Value.ToClosedXml();
if (!String.IsNullOrWhiteSpace(fr.Text))
conditionalFormat.Values.Add(GetFormula(fr.Text.Value));
- if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.Top10)
- {
- if (fr.Percent != null)
- conditionalFormat.Percent = fr.Percent.Value;
- if (fr.Bottom != null)
- conditionalFormat.Bottom = fr.Bottom.Value;
- if (fr.Rank != null)
- conditionalFormat.Values.Add(GetFormula(fr.Rank.Value.ToString()));
- }
- else if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.TimePeriod)
- {
- if (fr.TimePeriod != null)
- conditionalFormat.TimePeriod = fr.TimePeriod.Value.ToClosedXml();
- else
- conditionalFormat.TimePeriod = XLTimePeriod.Yesterday;
- }
+ if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.Top10)
+ {
+ if (fr.Percent != null)
+ conditionalFormat.Percent = fr.Percent.Value;
+ if (fr.Bottom != null)
+ conditionalFormat.Bottom = fr.Bottom.Value;
+ if (fr.Rank != null)
+ conditionalFormat.Values.Add(GetFormula(fr.Rank.Value.ToString()));
+ }
+ else if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.TimePeriod)
+ {
+ if (fr.TimePeriod != null)
+ conditionalFormat.TimePeriod = fr.TimePeriod.Value.ToClosedXml();
+ else
+ conditionalFormat.TimePeriod = XLTimePeriod.Yesterday;
+ }
- if (fr.Elements().Any())
- {
- var colorScale = fr.Elements().First();
- ExtractConditionalFormatValueObjects(conditionalFormat, colorScale);
- }
- else if (fr.Elements().Any())
- {
- var dataBar = fr.Elements().First();
- if (dataBar.ShowValue != null)
- conditionalFormat.ShowBarOnly = !dataBar.ShowValue.Value;
+ if (fr.Elements().Any())
+ {
+ var colorScale = fr.Elements().First();
+ ExtractConditionalFormatValueObjects(conditionalFormat, colorScale);
+ }
+ else if (fr.Elements().Any())
+ {
+ var dataBar = fr.Elements().First();
+ if (dataBar.ShowValue != null)
+ conditionalFormat.ShowBarOnly = !dataBar.ShowValue.Value;
var id = fr.Descendants().FirstOrDefault();
if (id != null && id.Text != null && !String.IsNullOrWhiteSpace(id.Text))
conditionalFormat.Id = new Guid(id.Text.Substring(1, id.Text.Length - 2));
- ExtractConditionalFormatValueObjects(conditionalFormat, dataBar);
- }
- else if (fr.Elements().Any())
- {
- var iconSet = fr.Elements().First();
- if (iconSet.ShowValue != null)
- conditionalFormat.ShowIconOnly = !iconSet.ShowValue.Value;
- if (iconSet.Reverse != null)
- conditionalFormat.ReverseIconOrder = iconSet.Reverse.Value;
+ ExtractConditionalFormatValueObjects(conditionalFormat, dataBar);
+ }
+ else if (fr.Elements().Any())
+ {
+ var iconSet = fr.Elements().First();
+ if (iconSet.ShowValue != null)
+ conditionalFormat.ShowIconOnly = !iconSet.ShowValue.Value;
+ if (iconSet.Reverse != null)
+ conditionalFormat.ReverseIconOrder = iconSet.Reverse.Value;
- if (iconSet.IconSetValue != null)
- conditionalFormat.IconSetStyle = iconSet.IconSetValue.Value.ToClosedXml();
- else
- conditionalFormat.IconSetStyle = XLIconSetStyle.ThreeTrafficLights1;
-
- ExtractConditionalFormatValueObjects(conditionalFormat, iconSet);
- }
+ if (iconSet.IconSetValue != null)
+ conditionalFormat.IconSetStyle = iconSet.IconSetValue.Value.ToClosedXml();
else
+ conditionalFormat.IconSetStyle = XLIconSetStyle.ThreeTrafficLights1;
+
+ ExtractConditionalFormatValueObjects(conditionalFormat, iconSet);
+ }
+ else
+ {
+ foreach (var formula in fr.Elements())
{
- foreach (var formula in fr.Elements())
- {
- if (formula.Text != null
- && (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs
+ if (formula.Text != null
+ && (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs
|| conditionalFormat.ConditionalFormatType == XLConditionalFormatType.Expression))
- {
- conditionalFormat.Values.Add(GetFormula(formula.Text));
- }
+ {
+ conditionalFormat.Values.Add(GetFormula(formula.Text));
}
}
- ws.ConditionalFormats.Add(conditionalFormat);
}
+
+ ws.ConditionalFormats.Add(conditionalFormat);
}
}
diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs
index 2934e39..454e004 100644
--- a/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -5056,7 +5056,7 @@
foreach (var cfGroup in conditionalFormats
.GroupBy(
- c => c.Range.RangeAddress.ToStringRelative(false),
+ c => string.Join(" ", c.Ranges.Select(r => r.RangeAddress.ToStringRelative(false))),
c => c,
(key, g) => new { RangeId = key, CfList = g.ToList() }
)
@@ -5103,7 +5103,7 @@
foreach (var cfGroup in exlst
.GroupBy(
- c => c.Range.RangeAddress.ToStringRelative(false),
+ c => string.Join(" ", c.Ranges.Select(r => r.RangeAddress.ToStringRelative(false))),
c => c,
(key, g) => new { RangeId = key, CfList = g.ToList() }
)
@@ -5150,6 +5150,8 @@
var dataValidations = worksheetPart.Worksheet.Elements().First();
cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, dataValidations);
dataValidations.RemoveAllChildren();
+ xlWorksheet.DataValidations.Consolidate();
+
foreach (var dv in xlWorksheet.DataValidations)
{
var sequence = dv.Ranges.Aggregate(String.Empty, (current, r) => current + (r.RangeAddress + " "));
diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs
index e35f2e4..8e391f6 100644
--- a/ClosedXML/Excel/XLWorksheet.cs
+++ b/ClosedXML/Excel/XLWorksheet.cs
@@ -1201,23 +1201,37 @@
foreach (var cf in ConditionalFormats.ToList())
{
- var cfAddress = cf.Range.RangeAddress;
- if (cf.Range.Intersects(model))
+ var cfRanges = cf.Ranges.ToList();
+ cf.Ranges.RemoveAll();
+
+ foreach (var cfRange in cfRanges)
{
- cf.Range = Range(cfAddress.FirstAddress.RowNumber,
- cfAddress.FirstAddress.ColumnNumber,
- cfAddress.LastAddress.RowNumber,
- cfAddress.LastAddress.ColumnNumber + columnsShifted);
+ var cfAddress = cfRange.RangeAddress;
+ IXLRange newRange;
+ if (cfRange.Intersects(model))
+ {
+ newRange = Range(cfAddress.FirstAddress.RowNumber,
+ cfAddress.FirstAddress.ColumnNumber,
+ cfAddress.LastAddress.RowNumber,
+ cfAddress.LastAddress.ColumnNumber + columnsShifted);
+ }
+ else if (cfAddress.FirstAddress.ColumnNumber >= firstCol)
+ {
+ newRange = Range(cfAddress.FirstAddress.RowNumber,
+ Math.Max(cfAddress.FirstAddress.ColumnNumber + columnsShifted, firstCol),
+ cfAddress.LastAddress.RowNumber,
+ cfAddress.LastAddress.ColumnNumber + columnsShifted);
+ }
+ else
+ newRange = cfRange;
+
+ if (newRange.RangeAddress.IsValid &&
+ newRange.RangeAddress.FirstAddress.ColumnNumber <=
+ newRange.RangeAddress.LastAddress.ColumnNumber)
+ cf.Ranges.Add(newRange);
}
- else if (cfAddress.FirstAddress.ColumnNumber >= firstCol)
- {
- cf.Range = Range(cfAddress.FirstAddress.RowNumber,
- Math.Max(cfAddress.FirstAddress.ColumnNumber + columnsShifted, firstCol),
- cfAddress.LastAddress.RowNumber,
- cfAddress.LastAddress.ColumnNumber + columnsShifted);
- }
- if (!cf.Range.RangeAddress.IsValid ||
- cf.Range.RangeAddress.FirstAddress.ColumnNumber > cf.Range.RangeAddress.LastAddress.ColumnNumber)
+
+ if (!cf.Ranges.Any())
ConditionalFormats.Remove(f => f == cf);
}
@@ -1273,25 +1287,39 @@
int rowNum = rowsShifted > 0 ? firstRow - 1 : firstRow;
var model = Row(rowNum).AsRange();
+
foreach (var cf in ConditionalFormats.ToList())
{
- var cfAddress = cf.Range.RangeAddress;
- if (cf.Range.Intersects(model))
+ var cfRanges = cf.Ranges.ToList();
+ cf.Ranges.RemoveAll();
+
+ foreach (var cfRange in cfRanges)
{
- cf.Range = Range(cfAddress.FirstAddress.RowNumber,
- cfAddress.FirstAddress.ColumnNumber,
- cfAddress.LastAddress.RowNumber + rowsShifted,
- cfAddress.LastAddress.ColumnNumber);
+ var cfAddress = cfRange.RangeAddress;
+ IXLRange newRange;
+ if (cfRange.Intersects(model))
+ {
+ newRange = Range(cfAddress.FirstAddress.RowNumber,
+ cfAddress.FirstAddress.ColumnNumber,
+ cfAddress.LastAddress.RowNumber + rowsShifted,
+ cfAddress.LastAddress.ColumnNumber);
+ }
+ else if (cfAddress.FirstAddress.RowNumber >= firstRow)
+ {
+ newRange = Range(Math.Max(cfAddress.FirstAddress.RowNumber + rowsShifted, firstRow),
+ cfAddress.FirstAddress.ColumnNumber,
+ cfAddress.LastAddress.RowNumber + rowsShifted,
+ cfAddress.LastAddress.ColumnNumber);
+ }
+ else
+ newRange = cfRange;
+
+ if (newRange.RangeAddress.IsValid &&
+ newRange.RangeAddress.FirstAddress.RowNumber <= newRange.RangeAddress.LastAddress.RowNumber)
+ cf.Ranges.Add(newRange);
}
- else if (cfAddress.FirstAddress.RowNumber >= firstRow)
- {
- cf.Range = Range(Math.Max(cfAddress.FirstAddress.RowNumber + rowsShifted, firstRow),
- cfAddress.FirstAddress.ColumnNumber,
- cfAddress.LastAddress.RowNumber + rowsShifted,
- cfAddress.LastAddress.ColumnNumber);
- }
- if (!cf.Range.RangeAddress.IsValid ||
- cf.Range.RangeAddress.FirstAddress.RowNumber > cf.Range.RangeAddress.LastAddress.RowNumber)
+
+ if (!cf.Ranges.Any())
ConditionalFormats.Remove(f => f == cf);
}
diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs
index 4114bcd..5ef7095 100644
--- a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs
+++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs
@@ -13,14 +13,68 @@
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Sheet");
var format = ws.Range("A1:A1").AddConditionalFormat();
- format.WhenEquals("=" + format.Range.FirstCell().CellRight(4).Address.ToStringRelative()).Fill
+ format.WhenEquals("=" + format.Ranges.First().FirstCell().CellRight(4).Address.ToStringRelative()).Fill
.SetBackgroundColor(XLColor.Blue);
var wb2 = new XLWorkbook();
var ws2 = wb2.Worksheets.Add("Sheet2");
ws2.FirstCell().CopyFrom(ws.FirstCell());
Assert.That(ws2.ConditionalFormats.First().Style.Fill.BackgroundColor, Is.EqualTo(XLColor.Blue)); //Added blue style
+ }
+ [Test]
+ public void CopyConditionalFormatSingleWorksheet()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet");
+ var format = ws.Range("A1:A1").AddConditionalFormat();
+ format.WhenEquals("=" + format.Ranges.First().FirstCell().CellRight(4).Address.ToStringRelative()).Fill
+ .SetBackgroundColor(XLColor.Blue);
+
+ ws.Cell("A1").CopyTo("B2");
+
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ Assert.AreEqual(2, ws.ConditionalFormats.First().Ranges.Count);
+ Assert.AreEqual("A1:A1", ws.ConditionalFormats.First().Ranges.First().RangeAddress.ToString());
+ Assert.AreEqual("B2:B2", ws.ConditionalFormats.First().Ranges.Last().RangeAddress.ToString());
+ }
+
+
+ [Test]
+ public void CopyConditionalFormatSameRange()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet");
+ var format = ws.Range("A1:C3").AddConditionalFormat();
+ format.WhenEquals("=" + format.Ranges.First().FirstCell().CellRight(4).Address.ToStringRelative()).Fill
+ .SetBackgroundColor(XLColor.Blue);
+
+ ws.Cell("A1").CopyTo("B2");
+
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ Assert.AreEqual(1, ws.ConditionalFormats.First().Ranges.Count);
+ Assert.AreEqual("A1:C3", ws.ConditionalFormats.First().Ranges.First().RangeAddress.ToString());
+ }
+
+ [Test]
+ public void CopyConditionalFormatDiferentWorksheets()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ var format = ws1.Range("A1:A1").AddConditionalFormat();
+ format.WhenEquals("=" + format.Ranges.First().FirstCell().CellRight(4).Address.ToStringRelative()).Fill
+ .SetBackgroundColor(XLColor.Blue);
+ var ws2 = wb.Worksheets.Add("Sheet2");
+ var otherCell = ws2.Cell("B2");
+
+ ws1.Cell("A1").CopyTo(otherCell);
+
+ Assert.AreEqual(1, ws1.ConditionalFormats.Count());
+ Assert.AreEqual(1, ws2.ConditionalFormats.Count());
+ Assert.AreEqual(1, ws1.ConditionalFormats.First().Ranges.Count);
+ Assert.AreEqual(1, ws2.ConditionalFormats.First().Ranges.Count);
+ Assert.AreEqual("A1:A1", ws1.ConditionalFormats.First().Ranges.First().RangeAddress.ToString());
+ Assert.AreEqual("B2:B2", ws2.ConditionalFormats.First().Ranges.First().RangeAddress.ToString());
}
}
}
diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs
index 222fbeb..0872d61 100644
--- a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs
+++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs
@@ -1,4 +1,5 @@
-using System.Linq;
+using System.IO;
+using System.Linq;
using ClosedXML.Excel;
using NUnit.Framework;
@@ -39,7 +40,7 @@
Assert.AreEqual(1, ws.ConditionalFormats.Count());
var format = ws.ConditionalFormats.First();
- Assert.AreEqual("B2:D3", format.Range.RangeAddress.ToStringRelative());
+ Assert.AreEqual("B2:D3", format.Ranges.First().RangeAddress.ToStringRelative());
Assert.AreEqual("F2", format.Values.Values.First().Value);
}
@@ -95,21 +96,6 @@
}
[Test]
- public void DifferentRangesNoConsolidateTest()
- {
- var wb = new XLWorkbook();
- IXLWorksheet ws = wb.Worksheets.Add("Sheet");
-
- SetFormat1(ws.Range("B7:C7").AddConditionalFormat());
- SetFormat1(ws.Range("B8:B8").AddConditionalFormat());
- SetFormat1(ws.Range("B9:C9").AddConditionalFormat());
-
- ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
-
- Assert.AreEqual(3, ws.ConditionalFormats.Count());
- }
-
- [Test]
public void DifferentFormatNoConsolidateTest()
{
var wb = new XLWorkbook();
@@ -123,6 +109,69 @@
Assert.AreEqual(2, ws.ConditionalFormats.Count());
}
+ [Test]
+ public void ConsolidatePreservesPriorities()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ SetFormat1(ws.Range("A1:A5").AddConditionalFormat());
+ SetFormat2(ws.Range("A1:A5").AddConditionalFormat());
+ SetFormat2(ws.Range("A6:A10").AddConditionalFormat());
+ SetFormat1(ws.Range("A6:A10").AddConditionalFormat());
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(3, ws.ConditionalFormats.Count());
+ Assert.AreEqual((ws.ConditionalFormats.First().Style as XLStyle).Value, (ws.ConditionalFormats.Last().Style as XLStyle).Value);
+ Assert.AreNotEqual((ws.ConditionalFormats.First().Style as XLStyle).Value, (ws.ConditionalFormats.ElementAt(1).Style as XLStyle).Value);
+ }
+
+
+ [Test]
+ public void ConsolidatePreservesPriorities2()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ SetFormat1(ws.Range("A1:A1").AddConditionalFormat());
+ SetFormat2(ws.Range("A2:A3").AddConditionalFormat());
+ SetFormat1(ws.Range("A2:A6").AddConditionalFormat());
+ SetFormat1(ws.Range("A7:A8").AddConditionalFormat());
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(3, ws.ConditionalFormats.Count());
+ Assert.AreEqual((ws.ConditionalFormats.First().Style as XLStyle).Value, (ws.ConditionalFormats.Last().Style as XLStyle).Value);
+ Assert.AreNotEqual((ws.ConditionalFormats.First().Style as XLStyle).Value, (ws.ConditionalFormats.ElementAt(1).Style as XLStyle).Value);
+ Assert.IsTrue(ws.ConditionalFormats.All(cf => cf.Ranges.Count == 1), "Number of ranges in consolidated conditional formats is expected to be 1");
+ Assert.AreEqual("A1:A1", ws.ConditionalFormats.ElementAt(0).Ranges.Single().RangeAddress.ToString());
+ Assert.AreEqual("A2:A3", ws.ConditionalFormats.ElementAt(1).Ranges.Single().RangeAddress.ToString());
+ Assert.AreEqual("A2:A8", ws.ConditionalFormats.ElementAt(2).Ranges.Single().RangeAddress.ToString());
+ }
+
+
+ [Test]
+ public void ConsolidateShiftsFormulaRelativelyToTopMostCell()
+ {
+ var wb = new XLWorkbook();
+ IXLWorksheet ws = wb.Worksheets.Add("Sheet");
+
+ var ranges = ws.Ranges("B3:B8,C3:C4,A3:A4,C5:C8,A5:A8").Cast();
+ var cf = new XLConditionalFormat(ranges);
+ cf.Values.Add(new XLFormula("=A3=$D3"));
+ cf.Style.Fill.SetBackgroundColor(XLColor.Red);
+ ws.ConditionalFormats.Add(cf);
+
+ ((XLConditionalFormats)ws.ConditionalFormats).Consolidate();
+
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ Assert.AreEqual((ws.ConditionalFormats.Single().Style as XLStyle).Value, (cf.Style as XLStyle).Value);
+ Assert.AreEqual("A3:C8", ws.ConditionalFormats.Single().Ranges.Single().RangeAddress.ToString());
+ Assert.IsTrue(ws.ConditionalFormats.Single().Values.Single().Value.IsFormula);
+ Assert.AreEqual("A3=$D3", ws.ConditionalFormats.Single().Values.Single().Value.Value);
+ }
+
private static void SetFormat1(IXLConditionalFormat format)
{
format.WhenEquals("="+format.Range.FirstCell().CellRight(4).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue);
diff --git a/ClosedXML_Tests/Excel/Ranges/RangesConsolidationTests.cs b/ClosedXML_Tests/Excel/Ranges/RangesConsolidationTests.cs
new file mode 100644
index 0000000..42e314f
--- /dev/null
+++ b/ClosedXML_Tests/Excel/Ranges/RangesConsolidationTests.cs
@@ -0,0 +1,98 @@
+using ClosedXML.Excel;
+using NUnit.Framework;
+using System.Linq;
+
+namespace ClosedXML_Tests.Excel.Ranges
+{
+ [TestFixture]
+ public class RangesConsolidationTests
+ {
+ [Test]
+ public void ConsolidateRangesSameWorksheet()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ var ranges = new XLRanges();
+ ranges.Add(ws.Range("A1:E3"));
+ ranges.Add(ws.Range("A4:B10"));
+ ranges.Add(ws.Range("E2:F12"));
+ ranges.Add(ws.Range("C6:I8"));
+ ranges.Add(ws.Range("G9:G9"));
+ ranges.Add(ws.Range("C9:D9"));
+ ranges.Add(ws.Range("H9:H9"));
+ ranges.Add(ws.Range("I9:I13"));
+ ranges.Add(ws.Range("C4:D5"));
+
+ var consRanges = ranges.Consolidate().ToList();
+
+ Assert.AreEqual(6, consRanges.Count);
+ Assert.AreEqual("A1:E9", consRanges[0].RangeAddress.ToString());
+ Assert.AreEqual("F2:F12", consRanges[1].RangeAddress.ToString());
+ Assert.AreEqual("G6:I9", consRanges[2].RangeAddress.ToString());
+ Assert.AreEqual("A10:B10", consRanges[3].RangeAddress.ToString());
+ Assert.AreEqual("E10:E12", consRanges[4].RangeAddress.ToString());
+ Assert.AreEqual("I10:I13", consRanges[5].RangeAddress.ToString());
+ }
+
+ [Test]
+ public void ConsolidateWideRangesSameWorksheet()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.Worksheets.Add("Sheet1");
+ var ranges = new XLRanges();
+ ranges.Add(ws.Row(5));
+ ranges.Add(ws.Row(7));
+ ranges.Add(ws.Row(6));
+ ranges.Add(ws.Column("D"));
+ ranges.Add(ws.Column("F"));
+ ranges.Add(ws.Column("E"));
+
+ var consRanges = ranges.Consolidate().ToList();
+
+ Assert.AreEqual(3, consRanges.Count);
+ Assert.AreEqual("D1:F1048576", consRanges[0].RangeAddress.ToString());
+ Assert.AreEqual("A5:C7", consRanges[1].RangeAddress.ToString());
+ Assert.AreEqual("G5:XFD7", consRanges[2].RangeAddress.ToString());
+ }
+
+ [Test]
+ public void ConsolidateRangesDifferentWorksheets()
+ {
+ var wb = new XLWorkbook();
+ var ws1 = wb.Worksheets.Add("Sheet1");
+ var ws2 = wb.Worksheets.Add("Sheet2");
+ var ranges = new XLRanges();
+ ranges.Add(ws1.Range("A1:E3"));
+ ranges.Add(ws1.Range("A4:B10"));
+ ranges.Add(ws1.Range("E2:F12"));
+ ranges.Add(ws1.Range("C6:I8"));
+ ranges.Add(ws1.Range("G9:G9"));
+
+ ranges.Add(ws2.Row(5));
+ ranges.Add(ws2.Row(7));
+ ranges.Add(ws2.Row(6));
+ ranges.Add(ws2.Column("D"));
+ ranges.Add(ws2.Column("F"));
+ ranges.Add(ws2.Column("E"));
+
+ ranges.Add(ws1.Range("C9:D9"));
+ ranges.Add(ws1.Range("H9:H9"));
+ ranges.Add(ws1.Range("I9:I13"));
+ ranges.Add(ws1.Range("C4:D5"));
+
+ var consRanges = ranges.Consolidate().ToList();
+
+ Assert.AreEqual(9, consRanges.Count);
+ Assert.AreEqual("Sheet1!$A$1:$E$9", consRanges[0].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
+ Assert.AreEqual("Sheet1!$F$2:$F$12", consRanges[1].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
+ Assert.AreEqual("Sheet1!$G$6:$I$9", consRanges[2].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
+ Assert.AreEqual("Sheet1!$A$10:$B$10", consRanges[3].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
+ Assert.AreEqual("Sheet1!$E$10:$E$12", consRanges[4].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
+ Assert.AreEqual("Sheet1!$I$10:$I$13", consRanges[5].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
+
+ Assert.AreEqual("Sheet2!$D$1:$F$1048576", consRanges[6].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
+ Assert.AreEqual("Sheet2!$A$5:$C$7", consRanges[7].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
+ Assert.AreEqual("Sheet2!$G$5:$XFD$7", consRanges[8].RangeAddress.ToStringFixed(XLReferenceStyle.Default, true));
+ }
+ }
+}
diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs
index 5668d01..5214be4 100644
--- a/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs
+++ b/ClosedXML_Tests/Excel/Ranges/XLRangeBaseTests.cs
@@ -350,9 +350,9 @@
ws.Range("C3:D7").AddConditionalFormat();
ws.Range("C5:E5").Clear(XLClearOptions.ConditionalFormats);
- Assert.AreEqual(2, ws.ConditionalFormats.Count());
- Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "C3:D4"));
- Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "C6:D7"));
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ Assert.AreEqual("C3:D4", ws.ConditionalFormats.First().Ranges.First().RangeAddress.ToStringRelative());
+ Assert.AreEqual("C6:D7", ws.ConditionalFormats.First().Ranges.Last().RangeAddress.ToStringRelative());
}
[Test]
@@ -362,9 +362,9 @@
ws.Range("C3:G4").AddConditionalFormat();
ws.Range("E2:E4").Clear(XLClearOptions.ConditionalFormats);
- Assert.AreEqual(2, ws.ConditionalFormats.Count());
- Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "C3:D4"));
- Assert.IsTrue(ws.ConditionalFormats.Any(x => x.Range.RangeAddress.ToStringRelative() == "F3:G4"));
+ Assert.AreEqual(1, ws.ConditionalFormats.Count());
+ Assert.AreEqual("C3:D4", ws.ConditionalFormats.First().Ranges.First().RangeAddress.ToStringRelative());
+ Assert.AreEqual("F3:G4", ws.ConditionalFormats.First().Ranges.Last().RangeAddress.ToStringRelative());
}
[Test]
@@ -385,7 +385,8 @@
ws.Range("C2:D3").Clear(XLClearOptions.ConditionalFormats);
Assert.AreEqual(1, ws.ConditionalFormats.Count());
- Assert.AreEqual("C3:G4", ws.ConditionalFormats.Single().Range.RangeAddress.ToStringRelative());
+ Assert.AreEqual(1, ws.ConditionalFormats.Single().Ranges.Count);
+ Assert.AreEqual("C3:G4", ws.ConditionalFormats.Single().Ranges.Single().RangeAddress.ToStringRelative());
}
[Test]
diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx
index 94f5caa..5593abf 100644
--- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFDatesOccurring.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx
index 565af0c..73bb457 100644
--- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFMultipleConditions.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx
index c118e4c..adb5b86 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx
Binary files differ