/// <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);