diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 39e00c1..692f9dd 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -1748,7 +1748,7 @@ Worksheet.Cell( Address.RowNumber + sourceCell.Address.RowNumber - minRow, Address.ColumnNumber + sourceCell.Address.ColumnNumber - minColumn - ).CopyFrom(sourceCell, true); + ).CopyFromInternal(sourceCell as XLCell, true); } var rangesToMerge = (from mergedRange in (asRange.Worksheet).Internals.MergedRanges @@ -1765,7 +1765,9 @@ Worksheet.Range(initialRo, initialCo, initialRo + mergedRange.RowCount() - 1, initialCo + mergedRange.ColumnCount() - 1)).Cast(). ToList(); - rangesToMerge.ForEach(r => r.Merge()); + rangesToMerge.ForEach(r => r.Merge(false)); + + CopyConditionalFormatsFrom(asRange); return true; } @@ -1773,6 +1775,72 @@ return false; } + private void CopyConditionalFormatsFrom(XLRangeBase fromRange) + { + 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))) + { + var fs = srcSheet.ConditionalFormats.Where(r => r.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); + } + } + int rCnt = minRo - fromRange.RangeAddress.FirstAddress.RowNumber + 1; + int cCnt = minCo - fromRange.RangeAddress.FirstAddress.ColumnNumber + 1; + rCnt = Math.Min(rCnt, fromRange.RowCount()); + cCnt = Math.Min(cCnt, fromRange.ColumnCount()); + var toRange = Worksheet.Range(this, Worksheet.Cell(Address.RowNumber + rCnt - 1, Address.ColumnNumber + cCnt - 1)); + var formats = srcSheet.ConditionalFormats.Where(f => f.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); + } + + c.Values[v.Key] = new XLFormula {_value = f, IsFormula = v.Value.IsFormula}; + } + + _worksheet.ConditionalFormats.Add(c); + } + } + + private static IXLRangeBase Intersection(IXLRangeBase range, IXLRangeBase crop) + { + var sheet = range.Worksheet; + using (var xlRange = sheet.Range( + Math.Max(range.RangeAddress.FirstAddress.RowNumber, crop.RangeAddress.FirstAddress.RowNumber), + Math.Max(range.RangeAddress.FirstAddress.ColumnNumber, crop.RangeAddress.FirstAddress.ColumnNumber), + Math.Min(range.RangeAddress.LastAddress.RowNumber, crop.RangeAddress.LastAddress.RowNumber), + Math.Min(range.RangeAddress.LastAddress.ColumnNumber, crop.RangeAddress.LastAddress.ColumnNumber))) + { + return sheet.Range(xlRange.RangeAddress); + } + } + + private static IXLRange Relative(IXLRangeBase range, IXLRangeBase baseRange, IXLRangeBase targetBase) + { + using (var xlRange = targetBase.Worksheet.Range( + range.RangeAddress.FirstAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1, + range.RangeAddress.FirstAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1, + range.RangeAddress.LastAddress.RowNumber - baseRange.RangeAddress.FirstAddress.RowNumber + 1, + range.RangeAddress.LastAddress.ColumnNumber - baseRange.RangeAddress.FirstAddress.ColumnNumber + 1)) + { + return ((XLRangeBase)targetBase).Range(xlRange.RangeAddress); + } + } + private bool SetDataTable(object o) { var dataTable = o as DataTable; @@ -1882,7 +1950,7 @@ _cellValue = val; } - private string GetFormulaR1C1(string value) + internal string GetFormulaR1C1(string value) { return GetFormula(value, FormulaConversionType.A1ToR1C1, 0, 0); } @@ -2106,14 +2174,35 @@ return defaultWorksheet.Workbook.Worksheet(wsName).Cell(pair[1]); } + internal IXLCell CopyFromInternal(XLCell otherCell, Boolean copyDataValidations) + { + CopyValuesFrom(otherCell); + + if (otherCell._styleCacheId.HasValue) + SetStyle(otherCell._style ?? otherCell.Worksheet.Workbook.GetStyleById(otherCell._styleCacheId.Value)); + + if (copyDataValidations) + { + var eventTracking = Worksheet.EventTrackingEnabled; + Worksheet.EventTrackingEnabled = false; + if (otherCell.HasDataValidation) + CopyDataValidation(otherCell, otherCell.DataValidation); + else if (HasDataValidation) + { + using (var asRange = AsRange()) + Worksheet.DataValidations.Delete(asRange); + } + Worksheet.EventTrackingEnabled = eventTracking; + } + + return this; + } + public IXLCell CopyFrom(IXLCell otherCell, Boolean copyDataValidations) { var source = otherCell as XLCell; // To expose GetFormulaR1C1, etc - CopyValuesFrom(source); - - if (source._styleCacheId.HasValue) - SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId.Value)); + CopyFromInternal(source, copyDataValidations); var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(source)).ToList(); foreach (var cf in conditionalFormats) @@ -2136,20 +2225,6 @@ _worksheet.ConditionalFormats.Add(c); } - if (copyDataValidations) - { - var eventTracking = Worksheet.EventTrackingEnabled; - Worksheet.EventTrackingEnabled = false; - if (source.HasDataValidation) - CopyDataValidation(source, source.DataValidation); - else if (HasDataValidation) - { - using (var asRange = AsRange()) - Worksheet.DataValidations.Delete(asRange); - } - Worksheet.EventTrackingEnabled = eventTracking; - } - return this; } diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index 03aa8ca..5023880 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -1,11 +1,108 @@ using System; using System.Collections.Generic; +using System.Linq; using ClosedXML.Utils; namespace ClosedXML.Excel { internal class XLConditionalFormat : IXLConditionalFormat, IXLStylized { + private sealed class FullEqualityComparer : IEqualityComparer + { + private readonly bool _compareRange; + private readonly DictionaryComparer _colorsComparer = new DictionaryComparer(); + private readonly EnumerableComparer _listComparer = new EnumerableComparer(); + private readonly DictionaryComparer _contentsTypeComparer = new DictionaryComparer(); + private readonly DictionaryComparer _iconSetTypeComparer = new DictionaryComparer(); + + public FullEqualityComparer(bool compareRange) + { + _compareRange = compareRange; + } + + public bool Equals(IXLConditionalFormat x, IXLConditionalFormat y) + { + var xx = (XLConditionalFormat) x; + var yy = (XLConditionalFormat) y; + if (ReferenceEquals(xx, yy)) return true; + if (ReferenceEquals(xx, null)) return false; + if (ReferenceEquals(yy, null)) return false; + if (xx.GetType() != yy.GetType()) return false; + + 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 xStyle = xx._style ?? xx.Range.Worksheet.Workbook.GetStyleById(xx._styleCacheId); + var yStyle = yy._style ?? yy.Range.Worksheet.Workbook.GetStyleById(yy._styleCacheId); + + return Equals(xStyle, yStyle) + && xx.CopyDefaultModify == yy.CopyDefaultModify + && xx.UpdatingStyle == yy.UpdatingStyle + && xx.ConditionalFormatType == yy.ConditionalFormatType + && xx.TimePeriod == yy.TimePeriod + && xx.IconSetStyle == yy.IconSetStyle + && xx.Operator == yy.Operator + && xx.Bottom == yy.Bottom + && xx.Percent == yy.Percent + && xx.ReverseIconOrder == yy.ReverseIconOrder + && xx.StopIfTrueInternal == yy.StopIfTrueInternal + && xx.ShowIconOnly == yy.ShowIconOnly + && xx.ShowBarOnly == yy.ShowBarOnly + && _listComparer.Equals(xxValues, yyValues) + && _listComparer.Equals(xxFormulas, yyFormulas) + && _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)) ; + } + + public int GetHashCode(IXLConditionalFormat obj) + { + var xx = (XLConditionalFormat)obj; + var xStyle = xx._style ?? xx.Range.Worksheet.Workbook.GetStyleById(xx._styleCacheId); + 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))); + + unchecked + { + var hashCode = xStyle.GetHashCode(); + hashCode = (hashCode * 397) ^ xx._styleCacheId; + hashCode = (hashCode * 397) ^ xx.CopyDefaultModify.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.UpdatingStyle.GetHashCode(); + hashCode = (hashCode * 397) ^ xValues.GetHashCode(); + 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) ^ (int)xx.ConditionalFormatType; + hashCode = (hashCode * 397) ^ (int)xx.TimePeriod; + hashCode = (hashCode * 397) ^ (int)xx.IconSetStyle; + hashCode = (hashCode * 397) ^ (int)xx.Operator; + hashCode = (hashCode * 397) ^ xx.Bottom.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.Percent.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.ReverseIconOrder.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.ShowIconOnly.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.ShowBarOnly.GetHashCode(); + hashCode = (hashCode * 397) ^ xx.StopIfTrueInternal.GetHashCode(); + return hashCode; + } + } + } + + private static readonly IEqualityComparer FullComparerInstance = new FullEqualityComparer(true); + public static IEqualityComparer FullComparer + { + get { return FullComparerInstance; } + } + + private static readonly IEqualityComparer NoRangeComparerInstance = new FullEqualityComparer(false); + public static IEqualityComparer NoRangeComparer + { + get { return NoRangeComparerInstance; } + } + public XLConditionalFormat(XLRange range, Boolean copyDefaultModify = false) { Id = Guid.NewGuid(); @@ -376,5 +473,59 @@ return new XLCFIconSet(this); } } + + internal class DictionaryComparer : + IEqualityComparer> + { + private readonly IEqualityComparer _valueComparer; + public DictionaryComparer(IEqualityComparer valueComparer = null) + { + this._valueComparer = valueComparer ?? EqualityComparer.Default; + } + public bool Equals(Dictionary x, Dictionary y) + { + if (x.Count != y.Count) + return false; + if (x.Keys.Except(y.Keys).Any()) + return false; + if (y.Keys.Except(x.Keys).Any()) + return false; + foreach (var pair in x) + if (!_valueComparer.Equals(pair.Value, y[pair.Key])) + return false; + return true; + } + + public int GetHashCode(Dictionary obj) + { + throw new NotImplementedException(); + } + } + + internal class EnumerableComparer : IEqualityComparer> + { + private readonly IEqualityComparer _valueComparer; + public EnumerableComparer(IEqualityComparer valueComparer = null) + { + this._valueComparer = valueComparer ?? EqualityComparer.Default; + } + + public bool Equals(IEnumerable x, IEnumerable y) + { + return SetEquals(x, y, _valueComparer); + } + + public int GetHashCode(IEnumerable obj) + { + throw new NotImplementedException(); + } + + public static bool SetEquals(IEnumerable first, IEnumerable second, + IEqualityComparer comparer) + { + return new HashSet(second, comparer ?? EqualityComparer.Default) + .SetEquals(first); + } + } } diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs index c968484..940bd22 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormats.cs @@ -13,6 +13,22 @@ _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(); @@ -29,6 +45,66 @@ _conditionalFormats.RemoveAll(predicate); } + /// + /// The method consolidate the same conditional formats, which are located in adjacent ranges. + /// + internal void Consolidate() + { + var formats = _conditionalFormats + .OrderByDescending(x => x.Range.RangeAddress.FirstAddress.RowNumber) + .ThenByDescending(x => x.Range.RangeAddress.FirstAddress.ColumnNumber); + + var orderedFormats = formats.ToList(); + + foreach (var item in formats) + { + var itemAddr = item.Range.RangeAddress; + var itemRowNum = itemAddr.FirstAddress.RowNumber; + + Func IsSameFormat = f => f != item && f.Range.Worksheet.Position == item.Range.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) + { + 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) + { + Merge(bottom, format); + _conditionalFormats.Remove(format); + orderedFormats.Remove(format); + } + 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); + } + } + } + + 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()); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index a0e3fdc..30c3287 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -4102,6 +4102,8 @@ private static void GenerateWorksheetPartContent( WorksheetPart worksheetPart, XLWorksheet xlWorksheet, bool evaluateFormulae, SaveContext context) { + ((XLConditionalFormats)xlWorksheet.ConditionalFormats).Consolidate(); + #region Worksheet if (worksheetPart.Worksheet == null) diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index fbf309b..f5e3a0d 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1360,6 +1360,7 @@ conditionalFormat.Range.Dispose(); } ResumeEvents(); + newConditionalFormats.Consolidate(); ConditionalFormats = newConditionalFormats; } diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index c2d1067..19d108d 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -176,6 +176,11 @@ { return value.ToString(CultureInfo.InvariantCulture.NumberFormat); } + + public static bool Between(this int val, int from, int to) + { + return val >= from && val <= to; + } } public static class DecimalExtensions diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index cf928a7..b559252 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -87,6 +87,7 @@ + diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs new file mode 100644 index 0000000..222fbeb --- /dev/null +++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatsConsolidateTests.cs @@ -0,0 +1,136 @@ +using System.Linq; +using ClosedXML.Excel; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel.ConditionalFormats +{ + [TestFixture] + public class ConditionalFormatsConsolidateTests + { + [Test] + public void ConsecutivelyRowsConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B2:C2").AddConditionalFormat()); + SetFormat1(ws.Range("B4:C4").AddConditionalFormat()); + SetFormat1(ws.Range("B3:C3").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B2:C4", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F2", format.Values.Values.First().Value); + } + + [Test] + public void ConsecutivelyColumnsConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("D2:D3").AddConditionalFormat()); + SetFormat1(ws.Range("B2:B3").AddConditionalFormat()); + SetFormat1(ws.Range("C2:C3").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B2:D3", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F2", format.Values.Values.First().Value); + } + + [Test] + public void Contains1ConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B11:D12").AddConditionalFormat()); + SetFormat1(ws.Range("C12:D12").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B11:D12", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F11", format.Values.Values.First().Value); + } + + [Test] + public void Contains2ConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B14:C14").AddConditionalFormat()); + SetFormat1(ws.Range("B14:B14").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B14:C14", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F14", format.Values.Values.First().Value); + } + + [Test] + public void SuperimposedConsolidateTest() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B16:D18").AddConditionalFormat()); + SetFormat1(ws.Range("B18:D19").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(1, ws.ConditionalFormats.Count()); + var format = ws.ConditionalFormats.First(); + Assert.AreEqual("B16:D19", format.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual("F16", format.Values.Values.First().Value); + } + + [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(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + SetFormat1(ws.Range("B11:D12").AddConditionalFormat()); + SetFormat2(ws.Range("C12:D12").AddConditionalFormat()); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + Assert.AreEqual(2, ws.ConditionalFormats.Count()); + } + + private static void SetFormat1(IXLConditionalFormat format) + { + format.WhenEquals("="+format.Range.FirstCell().CellRight(4).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue); + } + + private static void SetFormat2(IXLConditionalFormat format) + { + format.WhenEquals(5).Fill.SetBackgroundColor(XLColor.AliceBlue); + } + } +} diff --git a/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs index 426daf5..f2b8058 100644 --- a/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs @@ -1,4 +1,5 @@ using System.Drawing; +using System.Linq; using ClosedXML.Excel; using NUnit.Framework; @@ -53,13 +54,7 @@ IXLWorksheet ws = wb.Worksheets.Add("Sheet"); IXLRow row1 = ws.Row(1); - row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red); - row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1)); - row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC")); - row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26)); - row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen)); - row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue")); - row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3)); + FillRow(row1); ws.Cell(2, 1).Value = row1; ws.Cell(3, 1).Value = row1.Row(1, 7); @@ -81,6 +76,43 @@ Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor); Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor); Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor); + + Assert.AreEqual(3, ws.ConditionalFormats.Count()); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B1:B1").Values.Any(v => v.Value.Value == "G1" && v.Value.IsFormula)); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B2:B2").Values.Any(v => v.Value.Value == "G2" && v.Value.IsFormula)); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B3:B3").Values.Any(v => v.Value.Value == "G3" && v.Value.IsFormula)); + } + + [Test] + public void CopyingConditionalFormats() + { + var wb = new XLWorkbook(); + IXLWorksheet ws = wb.Worksheets.Add("Sheet"); + + FillRow(ws.Row(1)); + FillRow(ws.Row(2)); + FillRow(ws.Row(3)); + + ((XLConditionalFormats)ws.ConditionalFormats).Consolidate(); + + ws.Cell(5, 2).Value = ws.Row(2).Row(1, 7); + + Assert.AreEqual(2, ws.ConditionalFormats.Count()); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "B1:B3").Values.Any(v => v.Value.Value == "G1" && v.Value.IsFormula)); + Assert.IsTrue(ws.ConditionalFormats.Single(x => x.Range.RangeAddress.ToStringRelative() == "C5:C5").Values.Any(v => v.Value.Value == "H5" && v.Value.IsFormula)); + } + + private static void FillRow(IXLRow row1) + { + row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red); + row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1)); + row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC")); + row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26)); + row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen)); + row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue")); + row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3)); + + row1.Cell(2).AddConditionalFormat().WhenEquals("=" + row1.FirstCell().CellRight(6).Address.ToStringRelative()).Fill.SetBackgroundColor(XLColor.Blue); } } -} \ No newline at end of file +}