/// <summary>
/// Highlight cells with date values occurring according to a given time period.
/// </summary>
/// <param name="DatePeriod">A given time period.</param>
/// <param name="HighlightStyle">A built-in highlight style.</param>
public void HighlightCellsWithDatesOccurring(TimePeriodValues DatePeriod, SLHighlightCellsStyleValues HighlightStyle)
{
this.HighlightCellsWithDatesOccurring(DatePeriod, this.TranslateToDifferentialFormat(HighlightStyle));
}
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);
}