private void HighlightCellsWithUniques(SLDifferentialFormat HighlightStyle)
{
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
cfr.Type = ConditionalFormatValues.UniqueValues;
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
private void HighlightCellsWithDatesOccurring(TimePeriodValues DatePeriod, SLDifferentialFormat HighlightStyle)
{
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
cfr.Type = ConditionalFormatValues.TimePeriod;
cfr.TimePeriod = DatePeriod;
cfr.HasTimePeriod = true;
Formula f = new Formula();
string sRef = string.Empty;
if (this.SequenceOfReferences.Count > 0)
{
sRef = SLTool.ToCellReference(this.SequenceOfReferences[0].StartRowIndex, this.SequenceOfReferences[0].StartColumnIndex);
}
switch (DatePeriod)
{
case TimePeriodValues.Yesterday:
f.Text = string.Format("FLOOR({0},1)=TODAY()-1", sRef);
break;
case TimePeriodValues.Today:
f.Text = string.Format("FLOOR({0},1)=TODAY()", sRef);
break;
case TimePeriodValues.Tomorrow:
f.Text = string.Format("FLOOR({0},1)=TODAY()+1", sRef);
break;
case TimePeriodValues.Last7Days:
f.Text = string.Format("AND(TODAY()-FLOOR({0},1)<=6,FLOOR({0},1)<=TODAY())", sRef);
break;
case TimePeriodValues.LastWeek:
f.Text = string.Format("AND(TODAY()-ROUNDDOWN({0},0)>=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN({0},0)<(WEEKDAY(TODAY())+7))", sRef);
break;
case TimePeriodValues.ThisWeek:
f.Text = string.Format("AND(TODAY()-ROUNDDOWN({0},0)<=WEEKDAY(TODAY())-1,ROUNDDOWN({0},0)-TODAY()<=7-WEEKDAY(TODAY()))", sRef);
break;
case TimePeriodValues.NextWeek:
f.Text = string.Format("AND(ROUNDDOWN({0},0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN({0},0)-TODAY()<(15-WEEKDAY(TODAY())))", sRef);
break;
case TimePeriodValues.LastMonth:
f.Text = string.Format("AND(MONTH({0})=MONTH(EDATE(TODAY(),0-1)),YEAR({0})=YEAR(EDATE(TODAY(),0-1)))", sRef);
break;
case TimePeriodValues.ThisMonth:
f.Text = string.Format("AND(MONTH({0})=MONTH(TODAY()),YEAR({0})=YEAR(TODAY()))", sRef);
break;
case TimePeriodValues.NextMonth:
f.Text = string.Format("AND(MONTH({0})=MONTH(TODAY())+1,OR(YEAR({0})=YEAR(TODAY()),AND(MONTH({0})=12,YEAR({0})=YEAR(TODAY())+1)))", sRef);
break;
}
cfr.Formulas.Add(f);
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
private void HighlightCellsWithFormula(string Formula, SLDifferentialFormat HighlightStyle)
{
if (Formula.StartsWith("=")) Formula = Formula.Substring(1);
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
cfr.Type = ConditionalFormatValues.Expression;
cfr.Formulas.Add(new DocumentFormat.OpenXml.Spreadsheet.Formula(Formula));
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
private void HighlightCellsInTopRange(bool IsTopRange, uint Rank, bool IsPercent, SLDifferentialFormat HighlightStyle)
{
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
cfr.Type = ConditionalFormatValues.Top10;
cfr.Bottom = !IsTopRange;
cfr.Rank = Rank;
cfr.Percent = IsPercent;
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
private void HighlightCellsLessThan(bool IncludeEquality, string Value, SLDifferentialFormat HighlightStyle)
{
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
cfr.Type = ConditionalFormatValues.CellIs;
cfr.Operator = IncludeEquality ? ConditionalFormattingOperatorValues.LessThanOrEqual : ConditionalFormattingOperatorValues.LessThan;
cfr.HasOperator = true;
cfr.Formulas.Add(this.GetFormulaFromText(Value));
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
private void HighlightCellsEndingWith(string Value, SLDifferentialFormat HighlightStyle)
{
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
cfr.Text = Value;
Formula f = new Formula();
string sRef = string.Empty;
if (this.SequenceOfReferences.Count > 0)
{
sRef = SLTool.ToCellReference(this.SequenceOfReferences[0].StartRowIndex, this.SequenceOfReferences[0].StartColumnIndex);
}
cfr.Type = ConditionalFormatValues.EndsWith;
cfr.Operator = ConditionalFormattingOperatorValues.EndsWith;
cfr.HasOperator = true;
f.Text = string.Format("RIGHT({0},{1})={2}", sRef, Value.Length, this.GetCleanedStringFromText(Value));
cfr.Formulas.Add(f);
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
private void HighlightCellsContainingText(bool IsContaining, string Value, SLDifferentialFormat HighlightStyle)
{
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
cfr.Text = Value;
Formula f = new Formula();
string sRef = string.Empty;
if (this.SequenceOfReferences.Count > 0)
{
sRef = SLTool.ToCellReference(this.SequenceOfReferences[0].StartRowIndex, this.SequenceOfReferences[0].StartColumnIndex);
}
if (IsContaining)
{
cfr.Type = ConditionalFormatValues.ContainsText;
cfr.Operator = ConditionalFormattingOperatorValues.ContainsText;
cfr.HasOperator = true;
f.Text = string.Format("NOT(ISERROR(SEARCH({0},{1})))", this.GetCleanedStringFromText(Value), sRef);
}
else
{
cfr.Type = ConditionalFormatValues.NotContainsText;
cfr.Operator = ConditionalFormattingOperatorValues.NotContains;
cfr.HasOperator = true;
f.Text = string.Format("ISERROR(SEARCH({0},{1}))", this.GetCleanedStringFromText(Value), sRef);
}
cfr.Formulas.Add(f);
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
private void HighlightCellsContainingErrors(bool ContainsErrors, SLDifferentialFormat HighlightStyle)
{
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
Formula f = new Formula();
string sRef = string.Empty;
if (this.SequenceOfReferences.Count > 0)
{
sRef = SLTool.ToCellReference(this.SequenceOfReferences[0].StartRowIndex, this.SequenceOfReferences[0].StartColumnIndex);
}
if (ContainsErrors)
{
cfr.Type = ConditionalFormatValues.ContainsErrors;
f.Text = string.Format("ISERROR({0})", sRef);
}
else
{
cfr.Type = ConditionalFormatValues.NotContainsErrors;
f.Text = string.Format("NOT(ISERROR({0}))", sRef);
}
cfr.Formulas.Add(f);
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
private void HighlightCellsBetween(bool IsBetween, string Value1, string Value2, SLDifferentialFormat HighlightStyle)
{
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
cfr.Type = ConditionalFormatValues.CellIs;
cfr.Operator = IsBetween ? ConditionalFormattingOperatorValues.Between : ConditionalFormattingOperatorValues.NotBetween;
cfr.HasOperator = true;
cfr.Formulas.Add(this.GetFormulaFromText(Value1));
cfr.Formulas.Add(this.GetFormulaFromText(Value2));
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
private void HighlightCellsAboveAverage(SLHighlightCellsAboveAverageValues AverageType, SLDifferentialFormat HighlightStyle)
{
SLConditionalFormattingRule cfr = new SLConditionalFormattingRule();
cfr.Type = ConditionalFormatValues.AboveAverage;
switch (AverageType)
{
case SLHighlightCellsAboveAverageValues.Above:
// that's all is needed!
break;
case SLHighlightCellsAboveAverageValues.Below:
cfr.AboveAverage = false;
break;
case SLHighlightCellsAboveAverageValues.EqualOrAbove:
cfr.EqualAverage = true;
break;
case SLHighlightCellsAboveAverageValues.EqualOrBelow:
cfr.EqualAverage = true;
cfr.AboveAverage = false;
break;
case SLHighlightCellsAboveAverageValues.OneStdDevAbove:
cfr.StdDev = 1;
break;
case SLHighlightCellsAboveAverageValues.OneStdDevBelow:
cfr.AboveAverage = false;
cfr.StdDev = 1;
break;
case SLHighlightCellsAboveAverageValues.TwoStdDevAbove:
cfr.StdDev = 2;
break;
case SLHighlightCellsAboveAverageValues.TwoStdDevBelow:
cfr.AboveAverage = false;
cfr.StdDev = 2;
break;
case SLHighlightCellsAboveAverageValues.ThreeStdDevAbove:
cfr.StdDev = 3;
break;
case SLHighlightCellsAboveAverageValues.ThreeStdDevBelow:
cfr.AboveAverage = false;
cfr.StdDev = 3;
break;
}
cfr.DifferentialFormat = HighlightStyle.Clone();
cfr.HasDifferentialFormat = true;
this.AppendRule(cfr);
}
