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");
}
}
}
}