public static object GetCellsValue(this IExcelContext ctx, string cells, CellValueKind valueKind = CellValueKind.Value) { var range = ((ExcelContext)ctx).WS.Cells[ctx.DecodeAddress(cells)]; switch (valueKind) { case CellValueKind.Value: return(range.Value); case CellValueKind.Text: return(range.Text); case CellValueKind.AutoFilter: return(range.AutoFilter); case CellValueKind.Comment: return(range.Comment.Text); case CellValueKind.ConditionalFormattingMore: return(null); case CellValueKind.Formula: return(range.Formula); case CellValueKind.FormulaR1C1: return(range.FormulaR1C1); case CellValueKind.Hyperlink: return(range.Hyperlink); case CellValueKind.Merge: return(range.Merge); case CellValueKind.StyleName: return(range.StyleName); default: throw new ArgumentOutOfRangeException(nameof(valueKind)); } }
public static void CellsValue(this IExcelContext ctx, string cells, object value, CellValueKind valueKind = CellValueKind.Value) { var range = ((ExcelContext)ctx).WS.Cells[ctx.DecodeAddress(cells)]; switch (valueKind) { case CellValueKind.Value: range.Value = value; break; case CellValueKind.AutoFilter: range.AutoFilter = value.CastValue <bool>(); break; case CellValueKind.AutoFitColumns: range.AutoFitColumns(); break; case CellValueKind.Comment: range.Comment.Text = (string)value; break; case CellValueKind.CommentMore: break; case CellValueKind.ConditionalFormattingMore: break; case CellValueKind.Copy: var range2 = ((ExcelContext)ctx).WS.Cells[ctx.DecodeAddress((string)value)]; range.Copy(range2); break; case CellValueKind.Formula: range.Formula = (string)value; break; case CellValueKind.FormulaR1C1: range.FormulaR1C1 = (string)value; break; case CellValueKind.Hyperlink: range.Hyperlink = new Uri((string)value); break; case CellValueKind.Merge: range.Merge = value.CastValue <bool>(); break; case CellValueKind.RichText: range.RichText.Add((string)value); break; case CellValueKind.RichTextClear: range.RichText.Clear(); break; case CellValueKind.StyleName: range.StyleName = (string)value; break; default: throw new ArgumentOutOfRangeException(nameof(valueKind)); } if (value is DateTime) { range.Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern; } }
public static void CellsStyle(this IExcelContext ctx, string cells, string[] styles) { var range = ((ExcelContext)ctx).WS.Cells[ctx.DecodeAddress(cells)]; 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:")) { range.Style.Numberformat.Format = style.Substring(2); } else if (style.StartsWith("n$")) { range.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%")) { range.Style.Numberformat.Format = $"{NumberformatPrec(style.Substring(2), "0")}%"; } else if (style.StartsWith("n,")) { range.Style.Numberformat.Format = $"_(* #,##{NumberformatPrec(style.Substring(2), "0.00")}_);_(* \\(#,##{NumberformatPrec(style.Substring(2), "0.00")}\\);_(* \"-\"??_);_(@_)"; } else if (style == "nd") { range.Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern; } else { throw new InvalidOperationException($"{style} not defined"); } } // font else if (style.StartsWith("f")) { if (style.StartsWith("f:")) { range.Style.Font.Name = style.Substring(2); } else if (style.StartsWith("fx")) { range.Style.Font.Size = float.Parse(style.Substring(2)); } else if (style.StartsWith("ff")) { range.Style.Font.Family = int.Parse(style.Substring(2)); } else if (style.StartsWith("fc:")) { range.Style.Font.Color.SetColor(ToColor(style.Substring(3))); } else if (style.StartsWith("fs:")) { range.Style.Font.Scheme = style.Substring(2); } else if (style == "fB") { range.Style.Font.Bold = true; } else if (style == "fb") { range.Style.Font.Bold = false; } else if (style == "fI") { range.Style.Font.Italic = true; } else if (style == "fi") { range.Style.Font.Italic = false; } else if (style == "fS") { range.Style.Font.Strike = true; } else if (style == "fs") { range.Style.Font.Strike = false; } else if (style == "f_") { range.Style.Font.UnderLine = true; } else if (style == "f!_") { range.Style.Font.UnderLine = false; } //else if (style == "") range.Style.Font.UnderLineType = ?; else if (style.StartsWith("fv")) { range.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 (range.Style.Fill.PatternType == ExcelFillStyle.None || range.Style.Fill.PatternType == ExcelFillStyle.Solid) { range.Style.Fill.PatternType = ExcelFillStyle.Solid; } range.Style.Fill.BackgroundColor.SetColor(ToColor(style.Substring(3))); } else if (style.StartsWith("lf")) { range.Style.Fill.PatternType = (ExcelFillStyle)int.Parse(style.Substring(2)); } } // border else if (style.StartsWith("b")) { if (style.StartsWith("bl")) { range.Style.Border.Left.Style = (ExcelBorderStyle)int.Parse(style.Substring(2)); } else if (style.StartsWith("br")) { range.Style.Border.Right.Style = (ExcelBorderStyle)int.Parse(style.Substring(2)); } else if (style.StartsWith("bt")) { range.Style.Border.Top.Style = (ExcelBorderStyle)int.Parse(style.Substring(2)); } else if (style.StartsWith("bb")) { range.Style.Border.Bottom.Style = (ExcelBorderStyle)int.Parse(style.Substring(2)); } else if (style.StartsWith("bd")) { range.Style.Border.Diagonal.Style = (ExcelBorderStyle)int.Parse(style.Substring(2)); } else if (style == "bdU") { range.Style.Border.DiagonalUp = true; } else if (style == "bdu") { range.Style.Border.DiagonalUp = false; } else if (style == "bdD") { range.Style.Border.DiagonalDown = true; } else if (style == "bdd") { range.Style.Border.DiagonalDown = false; } else if (style.StartsWith("ba")) { range.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")) { range.Style.HorizontalAlignment = (ExcelHorizontalAlignment)int.Parse(style.Substring(2)); } // vertical-alignment else if (style.StartsWith("va")) { range.Style.VerticalAlignment = (ExcelVerticalAlignment)int.Parse(style.Substring(2)); } // vertical-alignment else if (style.StartsWith("W")) { range.Style.WrapText = true; } else if (style.StartsWith("w")) { range.Style.WrapText = false; } else { throw new InvalidOperationException($"{style} not defined"); } } }
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"); } } } }