diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs index 5d970d5..6f38667 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFColorScaleConverter.cs @@ -7,20 +7,26 @@ namespace ClosedXML.Excel { - internal class XLCFColorScaleConverter:IXLCFConverter + internal class XLCFColorScaleConverter : IXLCFConverter { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) { var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; - + var colorScale = new ColorScale(); - for(Int32 i = 1; i <= cf.Values.Count; i++) + for (Int32 i = 1; i <= cf.ContentTypes.Count; i++) { - var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i].Value }; + var type = cf.ContentTypes[i].ToOpenXml(); + var val = (cf.Values.ContainsKey(i) && cf.Values[i] != null) ? cf.Values[i].Value : null; + + var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = type }; + if (val != null) + conditionalFormatValueObject.Val = val; + colorScale.Append(conditionalFormatValueObject); } - for (Int32 i = 1; i <= cf.Values.Count; i++) + for (Int32 i = 1; i <= cf.Colors.Count; i++) { Color color = new Color { Rgb = cf.Colors[i].Color.ToHex() }; colorScale.Append(color); diff --git a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs index 64614d8..7fdf8de 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMax.cs @@ -22,7 +22,9 @@ } public void HighestValue(XLColor color) { - Maximum(XLCFContentType.Maximum, "0", color); + _conditionalFormat.Values.Add(null); + _conditionalFormat.Colors.Add(color); + _conditionalFormat.ContentTypes.Add(XLCFContentType.Maximum); } } } diff --git a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs index 13c31ec..d868a73 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMid.cs @@ -30,7 +30,9 @@ } public void HighestValue(XLColor color) { - Midpoint(XLCFContentType.Maximum, "0", color); + _conditionalFormat.Values.Initialize(null); + _conditionalFormat.Colors.Add(color); + _conditionalFormat.ContentTypes.Add(XLCFContentType.Maximum); } } } diff --git a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs index 0c33c04..9f2b647 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLCFColorScaleMin.cs @@ -27,7 +27,10 @@ public IXLCFColorScaleMid LowestValue(XLColor color) { - return Minimum(XLCFContentType.Minimum, "0", color); + _conditionalFormat.Values.Initialize(null); + _conditionalFormat.Colors.Initialize(color); + _conditionalFormat.ContentTypes.Initialize(XLCFContentType.Minimum); + return new XLCFColorScaleMid(_conditionalFormat); } } } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 6db27f6..3a1aeb3 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1535,6 +1535,8 @@ conditionalFormat.ContentTypes.Add(c.Type.Value.ToClosedXml()); if (c.Val != null) conditionalFormat.Values.Add(new XLFormula { Value = c.Val.Value }); + else + conditionalFormat.Values.Add(null); if (c.GreaterThanOrEqual != null) conditionalFormat.IconSetOperators.Add(c.GreaterThanOrEqual.Value ? XLCFIconSetOperator.EqualOrGreaterThan : XLCFIconSetOperator.GreaterThan); diff --git a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index efd53e8..5adc52f 100644 --- a/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -18,7 +18,7 @@ ws.RangeUsed().AddConditionalFormat().ColorScale() .LowestValue(XLColor.Red) - .Midpoint(XLCFContentType.Percent, "50", XLColor.Yellow) + .Midpoint(XLCFContentType.Percent, "50", XLColor.Yellow) .HighestValue(XLColor.Green); workbook.SaveAs(filePath); @@ -46,6 +46,26 @@ } } + public class CFColorScaleMinimumMaximum : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue(1) + .CellBelow().SetValue(1) + .CellBelow().SetValue(2) + .CellBelow().SetValue(3); + + ws.RangeUsed().AddConditionalFormat().ColorScale() + .LowestValue(XLColor.FromHtml("#FFFF7128")) + .HighestValue(XLColor.FromHtml("#FFFFEF9C")); + + workbook.SaveAs(filePath); + } + } + public class CFStartsWith : IXLExample { public void Create(String filePath) @@ -567,7 +587,7 @@ var range = ws.RangeUsed(); range.AddConditionalFormat().WhenEquals("1").Font.SetBold(); range.InsertRowsAbove(1); - + workbook.SaveAs(filePath); } @@ -589,7 +609,7 @@ ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red) .LowestValue() .HighestValue(); - + workbook.SaveAs(filePath); } } diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 022bf3d..cd9b3c0 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -230,6 +230,7 @@ + @@ -246,11 +247,11 @@ - + @@ -261,4 +262,4 @@ --> - + \ No newline at end of file diff --git a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs index 823ebeb..1ab8fe2 100644 --- a/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs +++ b/ClosedXML_Tests/Examples/ConditionalFormattingTests.cs @@ -19,6 +19,12 @@ } [Test] + public void CFColorScaleMinimumMaximum() + { + TestHelper.RunTestExample(@"ConditionalFormatting\CFColorScaleMinimumMaximum.xlsx"); + } + + [Test] public void CFContains() { TestHelper.RunTestExample(@"ConditionalFormatting\CFContains.xlsx"); @@ -107,23 +113,5 @@ { TestHelper.RunTestExample(@"ConditionalFormatting\CFMultipleConditions.xlsx"); } - - //[Test] - //public void XXX() - //{ - // TestHelper.RunTestExample(@"ConditionalFormatting\XXX.xlsx"); - //} - // - //[Test] - //public void XXX() - //{ - // TestHelper.RunTestExample(@"ConditionalFormatting\XXX.xlsx"); - //} - // - //[Test] - //public void XXX() - //{ - // TestHelper.RunTestExample(@"ConditionalFormatting\XXX.xlsx"); - //} } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx index 640d65d..9bc4eb6 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleLowMidHigh.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx new file mode 100644 index 0000000..ef35d8b --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFColorScaleMinimumMaximum.xlsx Binary files differ