diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index f00bb8e..c0236b7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -145,8 +145,20 @@ + + + + + + + + + + + + - + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs index db8c94e..9c67c00 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/IXLConditionalFormat.cs @@ -46,16 +46,16 @@ DataBar, IconSet, Top10, - UniqueValues, - DuplicateValues, + IsUnique, + IsDuplicate, ContainsText, NotContainsText, - BeginsWith, + StartsWith, EndsWith, - ContainsBlanks, - NotContainsBlanks, - ContainsErrors, - NotContainsErrors, + IsBlank, + NotBlank, + IsError, + NotError, TimePeriod, AboveAverage } @@ -82,13 +82,13 @@ IXLStyle WhenBetween(String minValue, String maxValue); IXLStyle WhenNotBetween(String minValue, String maxValue); IXLStyle WhenIsDuplicate(); - IXLStyle WhenNotDuplicate(); + IXLStyle WhenIsUnique(); IXLStyle WhenIsTrue(String formula); IXLStyle WhenIsTop(Int32 value, XLTopBottomType topBottomType = XLTopBottomType.Items); IXLStyle WhenIsBottom(Int32 value, XLTopBottomType topBottomType); IXLCFColorScaleMin ColorScale(); - IXLCFDataBarMin DataBar(IXLColor color); + IXLCFDataBarMin DataBar(IXLColor color, Boolean showBarOnly = false); IXLCFIconSet IconSet(XLIconSetStyle iconSetStyle, Boolean reverseIconOrder = false, Boolean showIconOnly = false); XLConditionalFormatType ConditionalFormatType { get; } @@ -96,6 +96,7 @@ XLTimePeriod TimePeriod { get; } Boolean ReverseIconOrder { get; } Boolean ShowIconOnly { get; } + Boolean ShowBarOnly { get; } IXLRange Range { get; set; } XLDictionary Values { get; } @@ -107,6 +108,7 @@ Boolean Bottom { get; } Boolean Percent { get; } + } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFBeginsWithConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFBeginsWithConverter.cs deleted file mode 100644 index fb206b9..0000000 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFBeginsWithConverter.cs +++ /dev/null @@ -1,23 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; -using DocumentFormat.OpenXml.Spreadsheet; - -namespace ClosedXML.Excel -{ - internal class XLCFBeginsWithConverter: IXLCFConverter - { - public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) - { - String val = cf.Values[1]; - var conditionalFormattingRule = new ConditionalFormattingRule {FormatId = (UInt32)context.DifferentialFormats[cf.Style] , Operator = cf.Operator.ToOpenXml(), Text = val ,Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; - - var formula = new Formula {Text = "LEFT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\""}; - - conditionalFormattingRule.Append(formula); - - return conditionalFormattingRule; - } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs new file mode 100644 index 0000000..e0e14be --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFCellIsConverter.cs @@ -0,0 +1,34 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFCellIsConverter : IXLCFConverter + { + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + String val = cf.Values[1].Replace("\"", "\"\""); + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var formula = new Formula(); + if (cf.Operator == XLCFOperator.Equal || cf.Operator == XLCFOperator.NotEqual) + formula.Text = "\"" + val + "\""; + else + formula.Text = val; + conditionalFormattingRule.Append(formula); + + if(cf.Operator == XLCFOperator.Between || cf.Operator == XLCFOperator.NotBetween) + { + var formula2 = new Formula {Text = cf.Values[2].Replace("\"", "\"\"")}; + conditionalFormattingRule.Append(formula2); + } + + return conditionalFormattingRule; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs new file mode 100644 index 0000000..96b69d9 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFContainsConverter.cs @@ -0,0 +1,25 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFContainsConverter : IXLCFConverter + { + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + String val = cf.Values[1]; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var formula = new Formula { Text = "NOT(ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")))" }; + + conditionalFormattingRule.Append(formula); + + return conditionalFormattingRule; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs index 1de99f2..e4ed8bb 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFConverters.cs @@ -13,7 +13,21 @@ { Converters = new Dictionary(); Converters.Add(XLConditionalFormatType.ColorScale, new XLCFColorScaleConverter()); - Converters.Add(XLConditionalFormatType.BeginsWith, new XLCFBeginsWithConverter()); + Converters.Add(XLConditionalFormatType.StartsWith, new XLCFStartsWithConverter()); + Converters.Add(XLConditionalFormatType.EndsWith, new XLCFEndsWithConverter()); + Converters.Add(XLConditionalFormatType.IsBlank, new XLCFIsBlankConverter()); + Converters.Add(XLConditionalFormatType.NotBlank, new XLCFNotBlankConverter()); + Converters.Add(XLConditionalFormatType.IsError, new XLCFIsErrorConverter()); + Converters.Add(XLConditionalFormatType.NotError, new XLCFNotErrorConverter()); + Converters.Add(XLConditionalFormatType.ContainsText, new XLCFContainsConverter()); + Converters.Add(XLConditionalFormatType.NotContainsText, new XLCFNotContainsConverter()); + Converters.Add(XLConditionalFormatType.CellIs, new XLCFCellIsConverter()); + Converters.Add(XLConditionalFormatType.IsUnique, new XLCFUniqueConverter()); + Converters.Add(XLConditionalFormatType.IsDuplicate, new XLCFUniqueConverter()); + Converters.Add(XLConditionalFormatType.Expression, new XLCFCellIsConverter()); + Converters.Add(XLConditionalFormatType.Top10, new XLCFTopConverter()); + Converters.Add(XLConditionalFormatType.DataBar, new XLCFDataBarConverter()); + Converters.Add(XLConditionalFormatType.IconSet, new XLCFIconSetConverter()); } public static ConditionalFormattingRule Convert(IXLConditionalFormat conditionalFormat, Int32 priority, XLWorkbook.SaveContext context) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs new file mode 100644 index 0000000..4bc9b79 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs @@ -0,0 +1,30 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFDataBarConverter:IXLCFConverter + { + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) + { + var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var dataBar = new DataBar {ShowValue = !cf.ShowBarOnly}; + var conditionalFormatValueObject1 = new ConditionalFormatValueObject { Type = cf.ContentTypes[1].ToOpenXml() , Val = cf.Values[1] }; + var conditionalFormatValueObject2 = new ConditionalFormatValueObject { Type = cf.ContentTypes[2].ToOpenXml(), Val = cf.Values[2] }; + var color = new Color { Rgb = cf.Colors[1].Color.ToHex() }; + + dataBar.Append(conditionalFormatValueObject1); + dataBar.Append(conditionalFormatValueObject2); + dataBar.Append(color); + + conditionalFormattingRule.Append(dataBar); + + return conditionalFormattingRule; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs new file mode 100644 index 0000000..d8ca520 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFEndsWithConverter.cs @@ -0,0 +1,25 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFEndsWithConverter : IXLCFConverter + { + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + String val = cf.Values[1]; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var formula = new Formula { Text = "RIGHT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; + + conditionalFormattingRule.Append(formula); + + return conditionalFormattingRule; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs new file mode 100644 index 0000000..9656913 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIconSetConverter.cs @@ -0,0 +1,28 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFIconSetConverter:IXLCFConverter + { + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) + { + var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var iconSet = new IconSet {ShowValue = !cf.ShowIconOnly, Reverse = cf.ReverseIconOrder, IconSetValue = cf.IconSetStyle.ToOpenXml()}; + Int32 count = cf.Values.Count; + for(Int32 i=1;i<= count; i++ ) + { + var conditionalFormatValueObject = new ConditionalFormatValueObject { Type = cf.ContentTypes[i].ToOpenXml(), Val = cf.Values[i], GreaterThanOrEqual = cf.IconSetOperators[i] == XLCFIconSetOperator.EqualOrGreaterThan}; + iconSet.Append(conditionalFormatValueObject); + + } + conditionalFormattingRule.Append(iconSet); + return conditionalFormattingRule; + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs new file mode 100644 index 0000000..21e5e65 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsBlankConverter.cs @@ -0,0 +1,24 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFIsBlankConverter : IXLCFConverter + { + + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var formula = new Formula { Text = "LEN(TRIM(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))=0" }; + + conditionalFormattingRule.Append(formula); + + return conditionalFormattingRule; + } + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs new file mode 100644 index 0000000..7fc1d7a --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFIsErrorConverter.cs @@ -0,0 +1,24 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFIsErrorConverter : IXLCFConverter + { + + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var formula = new Formula { Text = "ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + ")" }; + + conditionalFormattingRule.Append(formula); + + return conditionalFormattingRule; + } + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs new file mode 100644 index 0000000..aaeef02 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotBlankConverter.cs @@ -0,0 +1,24 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFNotBlankConverter : IXLCFConverter + { + + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var formula = new Formula { Text = "LEN(TRIM(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))>0" }; + + conditionalFormattingRule.Append(formula); + + return conditionalFormattingRule; + } + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs new file mode 100644 index 0000000..46ed6ca --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotContainsConverter.cs @@ -0,0 +1,25 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFNotContainsConverter : IXLCFConverter + { + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + String val = cf.Values[1]; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var formula = new Formula { Text = "ISERROR(SEARCH(\"" + val + "\"," + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" }; + + conditionalFormattingRule.Append(formula); + + return conditionalFormattingRule; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs new file mode 100644 index 0000000..2510a0a --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFNotErrorConverter.cs @@ -0,0 +1,24 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFNotErrorConverter : IXLCFConverter + { + + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var formula = new Formula { Text = "NOT(ISERROR(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "))" }; + + conditionalFormattingRule.Append(formula); + + return conditionalFormattingRule; + } + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs new file mode 100644 index 0000000..68f41a1 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFStartsWithConverter.cs @@ -0,0 +1,25 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFStartsWithConverter : IXLCFConverter + { + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + String val = cf.Values[1]; + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Operator = cf.Operator.ToOpenXml(), Text = val, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + + var formula = new Formula { Text = "LEFT(" + cf.Range.RangeAddress.FirstAddress.ToStringRelative(false) + "," + val.Length.ToString() + ")=\"" + val + "\"" }; + + conditionalFormattingRule.Append(formula); + + return conditionalFormattingRule; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs new file mode 100644 index 0000000..12f7731 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFTopConverter.cs @@ -0,0 +1,20 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFTopConverter : IXLCFConverter + { + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + UInt32 val = UInt32.Parse(cf.Values[1]); + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Percent = cf.Percent, Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority, Rank = val, Bottom = cf.Bottom}; + return conditionalFormattingRule; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs new file mode 100644 index 0000000..c6b4ef9 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/Save/XLCFUniqueConverter.cs @@ -0,0 +1,19 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using DocumentFormat.OpenXml.Spreadsheet; + +namespace ClosedXML.Excel +{ + internal class XLCFUniqueConverter : IXLCFConverter + { + public ConditionalFormattingRule Convert(IXLConditionalFormat cf, int priority, XLWorkbook.SaveContext context) + { + var conditionalFormattingRule = new ConditionalFormattingRule { FormatId = (UInt32)context.DifferentialFormats[cf.Style], Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; + return conditionalFormattingRule; + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index b80a2cd..3b90f5b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -69,25 +69,26 @@ public Boolean Percent { get; private set; } public Boolean ReverseIconOrder { get; private set; } public Boolean ShowIconOnly { get; private set; } + public Boolean ShowBarOnly { get; private set; } public IXLStyle WhenIsBlank() { - ConditionalFormatType = XLConditionalFormatType.ContainsBlanks; + ConditionalFormatType = XLConditionalFormatType.IsBlank; return Style; } public IXLStyle WhenNotBlank() { - ConditionalFormatType = XLConditionalFormatType.NotContainsBlanks; + ConditionalFormatType = XLConditionalFormatType.NotBlank; return Style; } public IXLStyle WhenIsError() { - ConditionalFormatType = XLConditionalFormatType.ContainsErrors; + ConditionalFormatType = XLConditionalFormatType.IsError; return Style; } public IXLStyle WhenNotError() { - ConditionalFormatType = XLConditionalFormatType.NotContainsErrors; + ConditionalFormatType = XLConditionalFormatType.NotError; return Style; } public IXLStyle WhenDateIs(XLTimePeriod timePeriod) @@ -100,18 +101,20 @@ { Values.Initialize(value); ConditionalFormatType = XLConditionalFormatType.ContainsText; + Operator = XLCFOperator.Contains; return Style; } public IXLStyle WhenNotContains(String value) { Values.Initialize(value); ConditionalFormatType = XLConditionalFormatType.NotContainsText; + Operator = XLCFOperator.NotContains; return Style; } public IXLStyle WhenStartsWith(String value) { Values.Initialize(value); - ConditionalFormatType = XLConditionalFormatType.BeginsWith; + ConditionalFormatType = XLConditionalFormatType.StartsWith; Operator = XLCFOperator.StartsWith; return Style; } @@ -119,6 +122,7 @@ { Values.Initialize(value); ConditionalFormatType = XLConditionalFormatType.EndsWith; + Operator = XLCFOperator.EndsWith; return Style; } public IXLStyle WhenEqualTo(String value) @@ -181,12 +185,12 @@ } public IXLStyle WhenIsDuplicate() { - ConditionalFormatType = XLConditionalFormatType.DuplicateValues; + ConditionalFormatType = XLConditionalFormatType.IsDuplicate; return Style; } - public IXLStyle WhenNotDuplicate() + public IXLStyle WhenIsUnique() { - ConditionalFormatType = XLConditionalFormatType.UniqueValues; + ConditionalFormatType = XLConditionalFormatType.IsUnique; return Style; } public IXLStyle WhenIsTrue(String formula) @@ -217,9 +221,10 @@ ConditionalFormatType = XLConditionalFormatType.ColorScale; return new XLCFColorScaleMin(this); } - public IXLCFDataBarMin DataBar(IXLColor color) + public IXLCFDataBarMin DataBar(IXLColor color, Boolean showBarOnly = false) { Colors.Initialize(color); + ShowBarOnly = showBarOnly; ConditionalFormatType = XLConditionalFormatType.DataBar; return new XLCFDataBarMin(this); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs index d85a9b0..e1384ca 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs @@ -543,16 +543,16 @@ case XLConditionalFormatType.DataBar: return ConditionalFormatValues.DataBar; case XLConditionalFormatType.IconSet: return ConditionalFormatValues.IconSet; case XLConditionalFormatType.Top10: return ConditionalFormatValues.Top10; - case XLConditionalFormatType.UniqueValues: return ConditionalFormatValues.UniqueValues; - case XLConditionalFormatType.DuplicateValues: return ConditionalFormatValues.DuplicateValues; + case XLConditionalFormatType.IsUnique: return ConditionalFormatValues.UniqueValues; + case XLConditionalFormatType.IsDuplicate: return ConditionalFormatValues.DuplicateValues; case XLConditionalFormatType.ContainsText: return ConditionalFormatValues.ContainsText; case XLConditionalFormatType.NotContainsText: return ConditionalFormatValues.NotContainsText; - case XLConditionalFormatType.BeginsWith: return ConditionalFormatValues.BeginsWith; + case XLConditionalFormatType.StartsWith: return ConditionalFormatValues.BeginsWith; case XLConditionalFormatType.EndsWith: return ConditionalFormatValues.EndsWith; - case XLConditionalFormatType.ContainsBlanks: return ConditionalFormatValues.ContainsBlanks; - case XLConditionalFormatType.NotContainsBlanks: return ConditionalFormatValues.NotContainsBlanks; - case XLConditionalFormatType.ContainsErrors: return ConditionalFormatValues.ContainsErrors; - case XLConditionalFormatType.NotContainsErrors: return ConditionalFormatValues.NotContainsErrors; + case XLConditionalFormatType.IsBlank: return ConditionalFormatValues.ContainsBlanks; + case XLConditionalFormatType.NotBlank: return ConditionalFormatValues.NotContainsBlanks; + case XLConditionalFormatType.IsError: return ConditionalFormatValues.ContainsErrors; + case XLConditionalFormatType.NotError: return ConditionalFormatValues.NotContainsErrors; case XLConditionalFormatType.TimePeriod: return ConditionalFormatValues.TimePeriod; case XLConditionalFormatType.AboveAverage: return ConditionalFormatValues.AboveAverage; #region default @@ -561,7 +561,6 @@ #endregion } } - public static ConditionalFormatValueObjectValues ToOpenXml(this XLCFContentType value) { switch (value) @@ -602,6 +601,34 @@ #endregion } } + public static IconSetValues ToOpenXml(this XLIconSetStyle value) + { + switch (value) + { + case XLIconSetStyle.ThreeArrows: return IconSetValues.ThreeArrows; + case XLIconSetStyle.ThreeArrowsGray: return IconSetValues.ThreeArrowsGray; + case XLIconSetStyle.ThreeFlags: return IconSetValues.ThreeFlags; + case XLIconSetStyle.ThreeTrafficLights1: return IconSetValues.ThreeTrafficLights1; + case XLIconSetStyle.ThreeTrafficLights2: return IconSetValues.ThreeTrafficLights2; + case XLIconSetStyle.ThreeSigns: return IconSetValues.ThreeSigns; + case XLIconSetStyle.ThreeSymbols: return IconSetValues.ThreeSymbols; + case XLIconSetStyle.ThreeSymbols2: return IconSetValues.ThreeSymbols2; + case XLIconSetStyle.FourArrows: return IconSetValues.FourArrows; + case XLIconSetStyle.FourArrowsGray: return IconSetValues.FourArrowsGray; + case XLIconSetStyle.FourRedToBlack: return IconSetValues.FourRedToBlack; + case XLIconSetStyle.FourRating: return IconSetValues.FourRating; + case XLIconSetStyle.FourTrafficLights: return IconSetValues.FourTrafficLights; + case XLIconSetStyle.FiveArrows: return IconSetValues.FiveArrows; + case XLIconSetStyle.FiveArrowsGray: return IconSetValues.FiveArrowsGray; + case XLIconSetStyle.FiveRating: return IconSetValues.FiveRating; + case XLIconSetStyle.FiveQuarters: return IconSetValues.FiveQuarters; + + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } #endregion #region To ClosedXml public static XLFontUnderlineValues ToClosedXml(this UnderlineValues value) @@ -1141,16 +1168,16 @@ case ConditionalFormatValues.DataBar: return XLConditionalFormatType.DataBar; case ConditionalFormatValues.IconSet: return XLConditionalFormatType.IconSet; case ConditionalFormatValues.Top10: return XLConditionalFormatType.Top10; - case ConditionalFormatValues.UniqueValues: return XLConditionalFormatType.UniqueValues; - case ConditionalFormatValues.DuplicateValues: return XLConditionalFormatType.DuplicateValues; + case ConditionalFormatValues.UniqueValues: return XLConditionalFormatType.IsUnique; + case ConditionalFormatValues.DuplicateValues: return XLConditionalFormatType.IsDuplicate; case ConditionalFormatValues.ContainsText: return XLConditionalFormatType.ContainsText; case ConditionalFormatValues.NotContainsText: return XLConditionalFormatType.NotContainsText; - case ConditionalFormatValues.BeginsWith: return XLConditionalFormatType.BeginsWith; + case ConditionalFormatValues.BeginsWith: return XLConditionalFormatType.StartsWith; case ConditionalFormatValues.EndsWith: return XLConditionalFormatType.EndsWith; - case ConditionalFormatValues.ContainsBlanks: return XLConditionalFormatType.ContainsBlanks; - case ConditionalFormatValues.NotContainsBlanks: return XLConditionalFormatType.NotContainsBlanks; - case ConditionalFormatValues.ContainsErrors: return XLConditionalFormatType.ContainsErrors; - case ConditionalFormatValues.NotContainsErrors: return XLConditionalFormatType.NotContainsErrors; + case ConditionalFormatValues.ContainsBlanks: return XLConditionalFormatType.IsBlank; + case ConditionalFormatValues.NotContainsBlanks: return XLConditionalFormatType.NotBlank; + case ConditionalFormatValues.ContainsErrors: return XLConditionalFormatType.IsError; + case ConditionalFormatValues.NotContainsErrors: return XLConditionalFormatType.NotError; case ConditionalFormatValues.TimePeriod: return XLConditionalFormatType.TimePeriod; case ConditionalFormatValues.AboveAverage: return XLConditionalFormatType.AboveAverage; @@ -1160,7 +1187,6 @@ #endregion } } - public static XLCFContentType ToClosedXml(this ConditionalFormatValueObjectValues value) { switch (value) @@ -1201,6 +1227,36 @@ #endregion } } + + public static XLIconSetStyle ToClosedXml(this IconSetValues value) + { + switch (value) + { + case IconSetValues.ThreeArrows: return XLIconSetStyle.ThreeArrows; + case IconSetValues.ThreeArrowsGray: return XLIconSetStyle.ThreeArrowsGray; + case IconSetValues.ThreeFlags: return XLIconSetStyle.ThreeFlags; + case IconSetValues.ThreeTrafficLights1: return XLIconSetStyle.ThreeTrafficLights1; + case IconSetValues.ThreeTrafficLights2: return XLIconSetStyle.ThreeTrafficLights2; + case IconSetValues.ThreeSigns: return XLIconSetStyle.ThreeSigns; + case IconSetValues.ThreeSymbols: return XLIconSetStyle.ThreeSymbols; + case IconSetValues.ThreeSymbols2: return XLIconSetStyle.ThreeSymbols2; + case IconSetValues.FourArrows: return XLIconSetStyle.FourArrows; + case IconSetValues.FourArrowsGray: return XLIconSetStyle.FourArrowsGray; + case IconSetValues.FourRedToBlack: return XLIconSetStyle.FourRedToBlack; + case IconSetValues.FourRating: return XLIconSetStyle.FourRating; + case IconSetValues.FourTrafficLights: return XLIconSetStyle.FourTrafficLights; + case IconSetValues.FiveArrows: return XLIconSetStyle.FiveArrows; + case IconSetValues.FiveArrowsGray: return XLIconSetStyle.FiveArrowsGray; + case IconSetValues.FiveRating: return XLIconSetStyle.FiveRating; + case IconSetValues.FiveQuarters: return XLIconSetStyle.FiveQuarters; + + + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 294e6c7..9bfb567 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1843,12 +1843,15 @@ { var differentialFormat = new DifferentialFormat(); differentialFormat.Append(GetNewFont(new FontInfo {Font = cf.Style.Font})); - differentialFormat.Append(new NumberingFormat - { - NumberFormatId = (UInt32)(differentialFormats.Count() + 164), - FormatCode = cf.Style.NumberFormat.Format - }); - + if (!StringExtensions.IsNullOrWhiteSpace(cf.Style.NumberFormat.Format)) + { + var numberFormat = new NumberingFormat + { + NumberFormatId = (UInt32) (differentialFormats.Count() + 164), + FormatCode = cf.Style.NumberFormat.Format + }; + differentialFormat.Append(numberFormat); + } differentialFormat.Append(GetNewFill(new FillInfo {Fill = cf.Style.Fill})); differentialFormat.Append(GetNewBorder(new BorderInfo { Border = cf.Style.Border })); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs index a737048..a8f1322 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ConditionalFormatting/ConditionalFormatting.cs @@ -61,7 +61,430 @@ ws.RangeUsed().AddConditionalFormat().WhenStartsWith("Hell") .Fill.SetBackgroundColor(XLColor.Red) .Border.SetOutsideBorder(XLBorderStyleValues.Thick) - .Border.SetOutsideBorderColor(XLColor.Blue); + .Border.SetOutsideBorderColor(XLColor.Blue) + .Font.SetBold(); + + workbook.SaveAs(filePath); + } + } + + public class CFEndsWith : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue("Hello") + .CellBelow().SetValue("Hellos") + .CellBelow().SetValue("Hell") + .CellBelow().SetValue("Holl"); + + ws.RangeUsed().AddConditionalFormat().WhenEndsWith("ll") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFIsBlank : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue("Hello") + .CellBelow().SetValue("") + .CellBelow().SetValue("") + .CellBelow().SetValue("Holl"); + + ws.RangeUsed().AddConditionalFormat().WhenIsBlank() + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFNotBlank : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue("Hello") + .CellBelow().SetValue("") + .CellBelow().SetValue("") + .CellBelow().SetValue("Holl"); + + ws.RangeUsed().AddConditionalFormat().WhenNotBlank() + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFIsError : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue("Hello") + .CellBelow().SetFormulaA1("1/0") + .CellBelow().SetFormulaA1("1/0") + .CellBelow().SetValue("Holl"); + + ws.RangeUsed().AddConditionalFormat().WhenIsError() + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFNotError : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue("Hello") + .CellBelow().SetFormulaA1("1/0") + .CellBelow().SetFormulaA1("1/0") + .CellBelow().SetValue("Holl"); + + ws.RangeUsed().AddConditionalFormat().WhenNotError() + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFContains : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue("Hello") + .CellBelow().SetValue("Hellos") + .CellBelow().SetValue("Hell") + .CellBelow().SetValue("Holl"); + + ws.RangeUsed().AddConditionalFormat().WhenContains("Hell") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFNotContains : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue("Hello") + .CellBelow().SetValue("Hellos") + .CellBelow().SetValue("Hell") + .CellBelow().SetValue("Holl"); + + ws.RangeUsed().AddConditionalFormat().WhenNotContains("Hell") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + + public class CFEquals : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue("Hello") + .CellBelow().SetValue("Hellos") + .CellBelow().SetValue("Hell") + .CellBelow().SetValue("Holl"); + + ws.RangeUsed().AddConditionalFormat().WhenEqualTo("Hell") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFNotEquals : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.AddWorksheet("Sheet1"); + + ws.FirstCell().SetValue("Hello") + .CellBelow().SetValue("Hellos") + .CellBelow().SetValue("Hell") + .CellBelow().SetValue("Holl"); + + ws.RangeUsed().AddConditionalFormat().WhenNotEqualTo("Hell") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFGreaterThan : 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().WhenGreaterThan("2") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFEqualOrGreaterThan : 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().WhenEqualOrGreaterThan("2") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFLessThan : 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().WhenLessThan("2") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFEqualOrLessThan : 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().WhenEqualOrLessThan("2") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFBetween : 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().WhenBetween("2", "3") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFNotBetween : 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().WhenNotBetween("2", "3") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFUnique : 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().WhenIsUnique() + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFDuplicate : 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().WhenIsDuplicate() + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFIsTrue : 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().WhenIsTrue("TRUE") + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFTop : 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().WhenIsTop(2) + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFBottom : 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().WhenIsBottom(10, XLTopBottomType.Percent) + .Fill.SetBackgroundColor(XLColor.Red); + + workbook.SaveAs(filePath); + } + } + + public class CFDataBar : 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().DataBar(XLColor.Red, true) + .LowestValue() + .Maximum(XLCFContentType.Percent, "100"); + + workbook.SaveAs(filePath); + } + } + + public class CFIconSet : 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().IconSet(XLIconSetStyle.ThreeTrafficLights2, true, true) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "0", XLCFContentType.Number) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "2", XLCFContentType.Number) + .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "3", XLCFContentType.Number); workbook.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index e772bc6..926014a 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -240,15 +240,51 @@ Excel\ConditionalFormats\Save\IXLCFConverter.cs - - Excel\ConditionalFormats\Save\XLCFBeginsWithConverter.cs + + Excel\ConditionalFormats\Save\XLCFCellIsConverter.cs Excel\ConditionalFormats\Save\XLCFColorScaleConverter.cs + + Excel\ConditionalFormats\Save\XLCFContainsConverter.cs + Excel\ConditionalFormats\Save\XLCFConverters.cs + + Excel\ConditionalFormats\Save\XLCFDataBarConverter.cs + + + Excel\ConditionalFormats\Save\XLCFEndsWithConverter.cs + + + Excel\ConditionalFormats\Save\XLCFIconSetConverter.cs + + + Excel\ConditionalFormats\Save\XLCFIsBlankConverter.cs + + + Excel\ConditionalFormats\Save\XLCFIsErrorConverter.cs + + + Excel\ConditionalFormats\Save\XLCFNotBlankConverter.cs + + + Excel\ConditionalFormats\Save\XLCFNotContainsConverter.cs + + + Excel\ConditionalFormats\Save\XLCFNotErrorConverter.cs + + + Excel\ConditionalFormats\Save\XLCFStartsWithConverter.cs + + + Excel\ConditionalFormats\Save\XLCFTopConverter.cs + + + Excel\ConditionalFormats\Save\XLCFUniqueConverter.cs + Excel\ConditionalFormats\XLCFColorScaleMax.cs