/// <summary>
/// Initializes a new instance of the <see cref="ConditionalFormatting"/> struct.
/// </summary>
/// <param name="address">The address.</param>
/// <param name="value">The value.</param>
/// <param name="formattingKind">Kind of the formatting.</param>
/// <param name="priority">The priority.</param>
/// <param name="stopIfTrue">if set to <c>true</c> [stop if true].</param>
/// <exception cref="ArgumentNullException">address</exception>
public ConditionalFormatting(string address, object value, ConditionalFormattingKind formattingKind, int?priority = null, bool stopIfTrue = false)
{
When = When.Normal;
Address = address ?? throw new ArgumentNullException(nameof(address));
Json = value != null ? value is string @string ? @string : JsonSerializer.Serialize(value) : null;
FormattingKind = formattingKind;
Priority = priority;
StopIfTrue = stopIfTrue;
}
public ConditionalFormatting(string address, object value, ConditionalFormattingKind formattingKind, int?priority = null, bool stopIfTrue = false)
{
if (string.IsNullOrEmpty(address))
{
throw new ArgumentNullException(nameof(address));
}
When = When.Normal;
Address = address;
Value = value != null ? value is string?(string)value : JsonConvert.SerializeObject(value) : null;
FormattingKind = formattingKind;
Priority = priority;
StopIfTrue = stopIfTrue;
}
/// <summary>
/// Initializes a new instance of the <see cref="ConditionalFormatting"/> struct.
/// </summary>
/// <param name="fromRow">From row.</param>
/// <param name="fromCol">From col.</param>
/// <param name="toRow">To row.</param>
/// <param name="toCol">To col.</param>
/// <param name="value">The value.</param>
/// <param name="formattingKind">Kind of the formatting.</param>
/// <param name="priority">The priority.</param>
/// <param name="stopIfTrue">if set to <c>true</c> [stop if true].</param>
public ConditionalFormatting(int fromRow, int fromCol, int toRow, int toCol, object value, ConditionalFormattingKind formattingKind, int?priority = null, bool stopIfTrue = false)
: this(ExcelService.GetAddress(fromRow, fromCol, toRow, toCol), value, formattingKind, priority, stopIfTrue)
{
}
/// <summary>
/// Initializes a new instance of the <see cref="ConditionalFormatting"/> struct.
/// </summary>
/// <param name="r">The r.</param>
/// <param name="row">The row.</param>
/// <param name="col">The col.</param>
/// <param name="value">The value.</param>
/// <param name="formattingKind">Kind of the formatting.</param>
/// <param name="priority">The priority.</param>
/// <param name="stopIfTrue">if set to <c>true</c> [stop if true].</param>
public ConditionalFormatting(Address r, int row, int col, object value, ConditionalFormattingKind formattingKind, int?priority = null, bool stopIfTrue = false)
: this(ExcelService.GetAddress(r, row, col), value, formattingKind, priority, stopIfTrue)
{
}
public static void ConditionalFormatting(this IExcelContext ctx, string address, object value, ConditionalFormattingKind formattingKind, int?priority, bool stopIfTrue)
{
void toColorScale(ExcelConditionalFormattingColorScaleValue val, JToken t)
{
if (t == null)
{
return;
}
val.Type = ToEnum <eExcelConditionalFormattingValueObjectType>((string)t["type"]);
val.Color = ToColor((string)t["color"], Color.White);
val.Value = t["value"].CastValue <double>();
val.Formula = (string)t["formula"];
}
void toIconDataBar(ExcelConditionalFormattingIconDataBarValue val, JToken t)
{
if (t == null)
{
return;
}
val.Type = ToEnum <eExcelConditionalFormattingValueObjectType>((string)t["type"]);
val.GreaterThanOrEqualTo = t["gte"].CastValue <bool>();
val.Value = t["value"].CastValue <double>();
val.Formula = (string)t["formula"];
}
var token = value != null?JToken.Parse(value is string?(string)value : JsonConvert.SerializeObject(value)) : null;
var formatting = ((ExcelContext)ctx).WS.ConditionalFormatting;
var ruleAddress = new ExcelAddress(ctx.DecodeAddress(address));
IExcelConditionalFormattingWithStdDev stdDev = null;
IExcelConditionalFormattingWithText text = null;
IExcelConditionalFormattingWithFormula formula = null;
IExcelConditionalFormattingWithFormula2 formula2 = null;
IExcelConditionalFormattingWithRank rank = null;
IExcelConditionalFormattingRule rule;
switch (formattingKind)
{
case ConditionalFormattingKind.AboveAverage: rule = formatting.AddAboveAverage(ruleAddress); break;
case ConditionalFormattingKind.AboveOrEqualAverage: rule = formatting.AddAboveOrEqualAverage(ruleAddress); break;
case ConditionalFormattingKind.AboveStdDev: rule = formatting.AddAboveStdDev(ruleAddress); stdDev = (IExcelConditionalFormattingWithStdDev)rule; break;
case ConditionalFormattingKind.BeginsWith: rule = formatting.AddBeginsWith(ruleAddress); text = (IExcelConditionalFormattingWithText)rule; break;
case ConditionalFormattingKind.BelowAverage: rule = formatting.AddBelowAverage(ruleAddress); break;
case ConditionalFormattingKind.BelowOrEqualAverage: rule = formatting.AddBelowOrEqualAverage(ruleAddress); break;
case ConditionalFormattingKind.BelowStdDev: rule = formatting.AddBelowStdDev(ruleAddress); stdDev = (IExcelConditionalFormattingWithStdDev)rule; break;
case ConditionalFormattingKind.Between: rule = formatting.AddBetween(ruleAddress); formula = (IExcelConditionalFormattingWithFormula)rule; formula2 = (IExcelConditionalFormattingWithFormula2)rule; break;
case ConditionalFormattingKind.Bottom: rule = formatting.AddBottom(ruleAddress); rank = (IExcelConditionalFormattingWithRank)rule; break;
case ConditionalFormattingKind.BottomPercent: rule = formatting.AddBottomPercent(ruleAddress); rank = (IExcelConditionalFormattingWithRank)rule; break;
case ConditionalFormattingKind.ContainsBlanks: rule = formatting.AddContainsBlanks(ruleAddress); break;
case ConditionalFormattingKind.ContainsErrors: rule = formatting.AddContainsErrors(ruleAddress); break;
case ConditionalFormattingKind.ContainsText: rule = formatting.AddContainsText(ruleAddress); text = (IExcelConditionalFormattingWithText)rule; break;
case ConditionalFormattingKind.Databar:
{
var r = formatting.AddDatabar(ruleAddress, ToStaticEnum <Color>((string)token["color"])); rule = r;
r.ShowValue = token["showValue"].CastValue <bool>();
toIconDataBar(r.LowValue, token["low"]);
toIconDataBar(r.HighValue, token["high"]);
}
break;
case ConditionalFormattingKind.DuplicateValues: rule = formatting.AddDuplicateValues(ruleAddress); break;
case ConditionalFormattingKind.EndsWith: rule = formatting.AddEndsWith(ruleAddress); text = (IExcelConditionalFormattingWithText)rule; break;
case ConditionalFormattingKind.Equal: rule = formatting.AddEqual(ruleAddress); formula = (IExcelConditionalFormattingWithFormula)rule; break;
case ConditionalFormattingKind.Expression: rule = formatting.AddExpression(ruleAddress); formula = (IExcelConditionalFormattingWithFormula)rule; break;
case ConditionalFormattingKind.FiveIconSet:
{
var r = formatting.AddFiveIconSet(ruleAddress, eExcelconditionalFormatting5IconsSetType.Arrows); rule = r;
r.Reverse = token["reverse"].CastValue <bool>();
r.ShowValue = token["showValue"].CastValue <bool>();
toIconDataBar(r.Icon1, token["icon1"]);
toIconDataBar(r.Icon2, token["icon2"]);
toIconDataBar(r.Icon3, token["icon3"]);
toIconDataBar(r.Icon4, token["icon4"]);
toIconDataBar(r.Icon5, token["icon5"]);
}
break;
case ConditionalFormattingKind.FourIconSet:
{
var r = formatting.AddFourIconSet(ruleAddress, eExcelconditionalFormatting4IconsSetType.Arrows); rule = r;
r.Reverse = token["reverse"].CastValue <bool>();
r.ShowValue = token["showValue"].CastValue <bool>();
toIconDataBar(r.Icon1, token["icon1"]);
toIconDataBar(r.Icon2, token["icon2"]);
toIconDataBar(r.Icon3, token["icon3"]);
toIconDataBar(r.Icon4, token["icon4"]);
}
break;
case ConditionalFormattingKind.GreaterThan: rule = formatting.AddGreaterThan(ruleAddress); formula = (IExcelConditionalFormattingWithFormula)rule; break;
case ConditionalFormattingKind.GreaterThanOrEqual: rule = formatting.AddGreaterThanOrEqual(ruleAddress); formula = (IExcelConditionalFormattingWithFormula)rule; break;
case ConditionalFormattingKind.Last7Days: rule = formatting.AddLast7Days(ruleAddress); break;
case ConditionalFormattingKind.LastMonth: rule = formatting.AddLastMonth(ruleAddress); break;
case ConditionalFormattingKind.LastWeek: rule = formatting.AddLastWeek(ruleAddress); break;
case ConditionalFormattingKind.LessThan: rule = formatting.AddLessThan(ruleAddress); formula = (IExcelConditionalFormattingWithFormula)rule; break;
case ConditionalFormattingKind.LessThanOrEqual: rule = formatting.AddLessThanOrEqual(ruleAddress); formula = (IExcelConditionalFormattingWithFormula)rule; break;
case ConditionalFormattingKind.NextMonth: rule = formatting.AddNextMonth(ruleAddress); break;
case ConditionalFormattingKind.NextWeek: rule = formatting.AddNextWeek(ruleAddress); break;
case ConditionalFormattingKind.NotBetween: rule = formatting.AddNotBetween(ruleAddress); formula = (IExcelConditionalFormattingWithFormula)rule; formula2 = (IExcelConditionalFormattingWithFormula2)rule; break;
case ConditionalFormattingKind.NotContainsBlanks: rule = formatting.AddNotContainsBlanks(ruleAddress); break;
case ConditionalFormattingKind.NotContainsErrors: rule = formatting.AddNotContainsErrors(ruleAddress); break;
case ConditionalFormattingKind.NotContainsText: rule = formatting.AddNotContainsText(ruleAddress); text = (IExcelConditionalFormattingWithText)rule; break;
case ConditionalFormattingKind.NotEqual: rule = formatting.AddNotEqual(ruleAddress); formula = (IExcelConditionalFormattingWithFormula)rule; break;
case ConditionalFormattingKind.ThisMonth: rule = formatting.AddThisMonth(ruleAddress); break;
case ConditionalFormattingKind.ThisWeek: rule = formatting.AddThisWeek(ruleAddress); break;
case ConditionalFormattingKind.ThreeColorScale:
{
var r = formatting.AddThreeColorScale(ruleAddress); rule = r;
toColorScale(r.LowValue, token["low"]);
toColorScale(r.HighValue, token["high"]);
toColorScale(r.MiddleValue, token["middle"]);
}
break;
case ConditionalFormattingKind.ThreeIconSet:
{
var r = formatting.AddThreeIconSet(ruleAddress, eExcelconditionalFormatting3IconsSetType.Arrows); rule = r;
r.Reverse = token["reverse"].CastValue <bool>();
r.ShowValue = token["showValue"].CastValue <bool>();
toIconDataBar(r.Icon1, token["icon1"]);
toIconDataBar(r.Icon2, token["icon2"]);
toIconDataBar(r.Icon3, token["icon3"]);
}
break;
case ConditionalFormattingKind.Today: rule = formatting.AddToday(ruleAddress); break;
case ConditionalFormattingKind.Tomorrow: rule = formatting.AddTomorrow(ruleAddress); break;
case ConditionalFormattingKind.Top: rule = formatting.AddTop(ruleAddress); rank = (IExcelConditionalFormattingWithRank)rule; break;
case ConditionalFormattingKind.TopPercent: rule = formatting.AddTopPercent(ruleAddress); rank = (IExcelConditionalFormattingWithRank)rule; break;
case ConditionalFormattingKind.TwoColorScale:
{
var r = formatting.AddTwoColorScale(ruleAddress); rule = r;
toColorScale(r.LowValue, token["low"]);
toColorScale(r.HighValue, token["high"]);
}
break;
case ConditionalFormattingKind.UniqueValues: rule = formatting.AddUniqueValues(ruleAddress); break;
case ConditionalFormattingKind.Yesterday: rule = formatting.AddYesterday(ruleAddress); break;
default: throw new ArgumentOutOfRangeException(nameof(formattingKind));
}
// CUSTOM
if (stdDev != null)
{
stdDev.StdDev = token["stdDev"].CastValue <ushort>();
}
if (text != null)
{
text.Text = (string)token["text"];
}
if (formula != null)
{
formula.Formula = (string)token["formula"];
}
if (formula2 != null)
{
formula2.Formula2 = (string)token["formula2"];
}
if (rank != null)
{
rank.Rank = token["rank"].CastValue <ushort>();
}
// RULE
if (priority != null)
{
rule.Priority = priority.Value;
}
rule.StopIfTrue = stopIfTrue;
var stylesAsToken = token["styles"];
var styles =
stylesAsToken == null ? null :
stylesAsToken.Type == JTokenType.String ? new[] { stylesAsToken.ToObject <string>() } :
stylesAsToken.Type == JTokenType.Array ? stylesAsToken.ToObject <string[]>() :
null;
if (styles != null)
{
foreach (var style in styles)
{
// number-format
if (style.StartsWith("n"))
{
// https://support.office.com/en-us/article/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68
if (style.StartsWith("n:"))
{
rule.Style.NumberFormat.Format = style.Substring(2);
}
else if (style.StartsWith("n$"))
{
rule.Style.NumberFormat.Format = $"_(\"$\"* #,##{NumberformatPrec(style.Substring(2), "0.00")}_);_(\"$\"* \\(#,##{NumberformatPrec(style.Substring(2), "0.00")}\\);_(\"$\"* \" - \"??_);_(@_)"; // "_-$* #,##{NumberformatPrec(style.Substring(2), "0.00")}_-;-$* #,##{NumberformatPrec(style.Substring(2), "0.00")}_-;_-$* \"-\"??_-;_-@_-";
}
else if (style.StartsWith("n%"))
{
rule.Style.NumberFormat.Format = $"{NumberformatPrec(style.Substring(2), "0")}%";
}
else if (style.StartsWith("n,"))
{
rule.Style.NumberFormat.Format = $"_(* #,##{NumberformatPrec(style.Substring(2), "0.00")}_);_(* \\(#,##{NumberformatPrec(style.Substring(2), "0.00")}\\);_(* \"-\"??_);_(@_)";
}
else if (style == "nd")
{
rule.Style.NumberFormat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;
}
else
{
throw new InvalidOperationException($"{style} not defined");
}
}
// font
else if (style.StartsWith("f"))
{
//if (style.StartsWith("f:")) rule.Style.Font.Name = style.Substring(2);
//else if (style.StartsWith("fx")) rule.Style.Font.Size = float.Parse(style.Substring(2));
//else if (style.StartsWith("ff")) rule.Style.Font.Family = int.Parse(style.Substring(2));
//else if (style.StartsWith("fc:")) rule.Style.Font.Color = ToDxfColor(style.Substring(3));
//else if (style.StartsWith("fs:")) rule.Style.Font.Scheme = style.Substring(2);
if (style == "fB")
{
rule.Style.Font.Bold = true;
}
else if (style == "fb")
{
rule.Style.Font.Bold = false;
}
else if (style == "fI")
{
rule.Style.Font.Italic = true;
}
else if (style == "fi")
{
rule.Style.Font.Italic = false;
}
else if (style == "fS")
{
rule.Style.Font.Strike = true;
}
else if (style == "fs")
{
rule.Style.Font.Strike = false;
}
else if (style == "f_")
{
rule.Style.Font.Underline = ExcelUnderLineType.Single;
}
else if (style == "f!_")
{
rule.Style.Font.Underline = ExcelUnderLineType.None;
}
//else if (style == "") rule.Style.Font.UnderLineType = ?;
//else if (style.StartsWith("fv")) rule.Style.Font.VerticalAlign = (ExcelVerticalAlignmentFont)int.Parse(style.Substring(2));
else
{
throw new InvalidOperationException($"{style} not defined");
}
}
// fill
else if (style.StartsWith("l"))
{
if (style.StartsWith("lc:"))
{
if (rule.Style.Fill.PatternType == ExcelFillStyle.None)
{
rule.Style.Fill.PatternType = ExcelFillStyle.Solid;
}
rule.Style.Fill.BackgroundColor.Color = ToColor(style.Substring(3));
}
else if (style.StartsWith("lf"))
{
rule.Style.Fill.PatternType = (ExcelFillStyle)int.Parse(style.Substring(2));
}
}
// border
else if (style.StartsWith("b"))
{
if (style.StartsWith("bl"))
{
rule.Style.Border.Left.Style = (ExcelBorderStyle)int.Parse(style.Substring(2));
}
else if (style.StartsWith("br"))
{
rule.Style.Border.Right.Style = (ExcelBorderStyle)int.Parse(style.Substring(2));
}
else if (style.StartsWith("bt"))
{
rule.Style.Border.Top.Style = (ExcelBorderStyle)int.Parse(style.Substring(2));
}
else if (style.StartsWith("bb"))
{
rule.Style.Border.Bottom.Style = (ExcelBorderStyle)int.Parse(style.Substring(2));
}
//else if (style.StartsWith("bd")) rule.Style.Border.Diagonal.Style = (ExcelBorderStyle)int.Parse(style.Substring(2));
//else if (style == "bdU") rule.Style.Border.DiagonalUp = true;
//else if (style == "bdu") rule.Style.Border.DiagonalUp = false;
//else if (style == "bdD") rule.Style.Border.DiagonalDown = true;
//else if (style == "bdd") rule.Style.Border.DiagonalDown = false;
//else if (style.StartsWith("ba")) rule.Style.Border.BorderAround((ExcelBorderStyle)int.Parse(style.Substring(2))); // add color option
else
{
throw new InvalidOperationException($"{style} not defined");
}
}
// horizontal-alignment
//else if (style.StartsWith("ha"))
//{
// rule.Style.HorizontalAlignment = (ExcelHorizontalAlignment)int.Parse(style.Substring(2));
//}
// vertical-alignment
//else if (style.StartsWith("va"))
//{
// rule.Style.VerticalAlignment = (ExcelVerticalAlignment)int.Parse(style.Substring(2));
//}
// vertical-alignment
//else if (style.StartsWith("W")) rule.Style.WrapText = true;
//else if (style.StartsWith("w")) rule.Style.WrapText = false;
else
{
throw new InvalidOperationException($"{style} not defined");
}
}
}
}
public static void ConditionalFormatting(this IExcelContext ctx, Address r, int fromRow, int fromCol, int toRow, int toCol, object value, ConditionalFormattingKind formattingKind, int?priority, bool stopIfTrue) => ConditionalFormatting(ctx, ExcelService.GetAddress(r, fromRow, fromCol, toRow, toCol), value, formattingKind, priority, stopIfTrue);
public static void ConditionalFormatting(this IExcelContext ctx, int row, int col, object value, ConditionalFormattingKind formattingKind, int?priority, bool stopIfTrue) => ConditionalFormatting(ctx, ExcelService.GetAddress(row, col), value, formattingKind, priority, stopIfTrue);
