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); } }