diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index b47bb3b..cb72637 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -1749,7 +1749,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 @@ -1766,7 +1766,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; } @@ -1774,6 +1776,59 @@ 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(cf.Range.Intersection(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 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; @@ -2149,14 +2204,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) @@ -2179,20 +2255,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_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 08ec404..575ef0e 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -86,6 +86,7 @@ + diff --git a/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/CopyingRangesTests.cs index 426daf5..1f1a29f 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)); + + ws.ConditionalFormats.Compress(); + + 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