private static void SetFullMonth(ExcelDynamicFilterColumn column, DateTime dt)
{
var minValue = new DateTime(dt.Year, dt.Month, 1);
column.Value = minValue.ToOADate();
column.MaxValue = minValue.AddMonths(1).AddDays(-1).ToOADate();
}
/// <summary>
/// Adds a dynamic filter for the specified column position
/// </summary>
/// <param name="position">The column position</param>
/// <returns>The dynamic filter</returns>
public ExcelDynamicFilterColumn AddDynamicFilterColumn(int position)
{
var node = Add(position, "dynamicFilter");
var col = new ExcelDynamicFilterColumn(NameSpaceManager, node);
_columns.Add(position, col);
return(col);
}
private static void SetFullQuarter(ExcelDynamicFilterColumn column, DateTime dt)
{
var quarter = ((dt.Month - (dt.Month - 1) % 3) + 1) / 3;
var minDate = new DateTime(dt.Year, (quarter * 3) + 1, 1);
column.Value = minDate.ToOADate();
column.MaxValue = minDate.AddMonths(3).AddDays(-1).ToOADate();
}
private static void SetWeek(ExcelDynamicFilterColumn column, DateTime dt)
{
while (dt.DayOfWeek != DayOfWeek.Sunday)
{
dt = dt.AddDays(-1);
}
column.Value = dt.ToOADate();
column.MaxValue = dt.AddDays(6).ToOADate();
}
internal static void SetMatchDates(ExcelDynamicFilterColumn column)
{
switch (column.Type)
{
case eDynamicFilterType.Yesterday:
SetDay(column, DateTime.Today.AddDays(-1));
break;
case eDynamicFilterType.Today:
SetDay(column, DateTime.Today);
break;
case eDynamicFilterType.Tomorrow:
SetDay(column, DateTime.Today.AddDays(1));
break;
case eDynamicFilterType.LastWeek:
SetWeek(column, DateTime.Today.AddDays(-7));
break;
case eDynamicFilterType.ThisWeek:
SetWeek(column, DateTime.Today);
break;
case eDynamicFilterType.NextWeek:
SetWeek(column, DateTime.Today.AddDays(7));
break;
case eDynamicFilterType.LastMonth:
SetFullMonth(column, DateTime.Today.AddMonths(-1));
break;
case eDynamicFilterType.ThisMonth:
SetFullMonth(column, DateTime.Today);
break;
case eDynamicFilterType.NextMonth:
SetFullMonth(column, DateTime.Today.AddMonths(1));
break;
case eDynamicFilterType.LastQuarter:
SetFullQuarter(column, DateTime.Today.AddMonths(-3));
break;
case eDynamicFilterType.ThisQuarter:
SetFullQuarter(column, DateTime.Today);
break;
case eDynamicFilterType.NextQuarter:
SetFullQuarter(column, DateTime.Today.AddMonths(3));
break;
case eDynamicFilterType.LastYear:
SetFullYear(column, DateTime.Today.Year - 1);
break;
case eDynamicFilterType.ThisYear:
SetFullYear(column, DateTime.Today.Year);
break;
case eDynamicFilterType.NextYear:
SetFullYear(column, DateTime.Today.Year + 1);
break;
case eDynamicFilterType.YearToDate:
SetYearToDate(column);
break;
default:
SetFixed(column);
break;
}
}
internal static bool Match(ExcelDynamicFilterColumn column, DateTime?value)
{
switch (column.Type)
{
case eDynamicFilterType.Yesterday:
return(value.Value.Date == DateTime.Today.AddDays(-1));
case eDynamicFilterType.Today:
return(value.Value.Date == DateTime.Today);
case eDynamicFilterType.Tomorrow:
return(value.Value.Date == DateTime.Today.AddDays(1));
case eDynamicFilterType.M1:
return(value.Value.Month == 1);
case eDynamicFilterType.M2:
return(value.Value.Month == 2);
case eDynamicFilterType.M3:
return(value.Value.Month == 3);
case eDynamicFilterType.M4:
return(value.Value.Month == 4);
case eDynamicFilterType.M5:
return(value.Value.Month == 5);
case eDynamicFilterType.M6:
return(value.Value.Month == 6);
case eDynamicFilterType.M7:
return(value.Value.Month == 7);
case eDynamicFilterType.M8:
return(value.Value.Month == 8);
case eDynamicFilterType.M9:
return(value.Value.Month == 9);
case eDynamicFilterType.M10:
return(value.Value.Month == 10);
case eDynamicFilterType.M11:
return(value.Value.Month == 11);
case eDynamicFilterType.M12:
return(value.Value.Month == 12);
case eDynamicFilterType.Q1:
return(value.Value.Month >= 1 && value.Value.Month <= 3);
case eDynamicFilterType.Q2:
return(value.Value.Month >= 4 && value.Value.Month <= 6);
case eDynamicFilterType.Q3:
return(value.Value.Month >= 7 && value.Value.Month <= 9);
case eDynamicFilterType.Q4:
return(value.Value.Month >= 10 && value.Value.Month <= 12);
default:
var v = value.Value.ToOADate();
return(v >= column.Value && v <= column.MaxValue);
}
}
private static void SetFullYear(ExcelDynamicFilterColumn column, int year)
{
column.Value = new DateTime(year, 1, 1).ToOADate();
column.MaxValue = new DateTime(year, 12, 31).ToOADate();
}
private static void SetYearToDate(ExcelDynamicFilterColumn column)
{
column.Value = new DateTime(DateTime.Today.Year, 1, 1).ToOADate();
column.MaxValue = DateTime.Today.ToOADate();
}
private static void SetDay(ExcelDynamicFilterColumn column, DateTime dt)
{
column.Value = dt.ToOADate();
column.MaxValue = dt.AddDays(1).ToOADate();
}
private static void SetFixed(ExcelDynamicFilterColumn column)
{
column.Value = null;
column.MaxValue = null;
}