public void Consolidate()
{
Func <IXLDataValidation, IXLDataValidation, bool> 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 &&
dv1.Value == dv2.Value);
};
var rules = _dataValidations.ToList();
rules.ForEach(Delete);
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();
IXLRanges consolidatedRanges = new XLRanges();
ranges.ForEach(r => consolidatedRanges.Add(r));
consolidatedRanges = consolidatedRanges.Consolidate();
consRule.ClearRanges();
consRule.AddRanges(consolidatedRanges);
Add(consRule);
}
}
/// <summary>
/// The method consolidate the same conditional formats, which are located in adjacent ranges.
/// </summary>
internal void Consolidate()
{
var formats = _conditionalFormats
.Where(cf => cf.Ranges.Any())
.ToList();
_conditionalFormats.Clear();
while (formats.Count > 0)
{
var item = formats.First();
if (!_conditionalFormatTypesExcludedFromConsolidation.Contains(item.ConditionalFormatType))
{
var rangesToJoin = new XLRanges();
item.Ranges.ForEach(r => rangesToJoin.Add(r));
var firstRange = item.Ranges.First();
var skippedRanges = new XLRanges();
Func <IXLConditionalFormat, bool> IsSameFormat = f =>
f != item && f.Ranges.First().Worksheet.Position == firstRange.Worksheet.Position &&
XLConditionalFormat.NoRangeComparer.Equals(f, item);
//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 = firstRange.Worksheet.Cell(baseAddress) as XLCell;
int i = 1;
bool stop = false;
List <IXLConditionalFormat> similarFormats = new List <IXLConditionalFormat>();
do
{
stop = (i >= formats.Count);
if (!stop)
{
var nextFormat = formats[i];
var intersectsSkipped =
skippedRanges.Any(left => nextFormat.Ranges.GetIntersectedRanges(left.RangeAddress).Any());
var isSameFormat = IsSameFormat(nextFormat);
if (isSameFormat && !intersectsSkipped)
{
similarFormats.Add(nextFormat);
nextFormat.Ranges.ForEach(r => rangesToJoin.Add(r));
}
else if (rangesToJoin.Any(left => nextFormat.Ranges.GetIntersectedRanges(left.RangeAddress).Any()) ||
intersectsSkipped)
{
// if we reached the rule intersecting any of captured ranges stop for not breaking the priorities
stop = true;
}
if (!isSameFormat)
{
nextFormat.Ranges.ForEach(r => skippedRanges.Add(r));
}
}
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);
similarFormats.ForEach(cf => formats.Remove(cf));
}
_conditionalFormats.Add(item);
formats.Remove(item);
}
}