private int AddDateGroupItems(ExcelPivotTableFieldGroup group, eDateGroupBy GroupBy, DateTime StartDate, DateTime EndDate, int interval)
{
XmlElement groupItems = group.TopNode.SelectSingleNode("d:fieldGroup/d:groupItems", group.NameSpaceManager) as XmlElement;
int items = 2;
//First date
AddGroupItem(groupItems, "<" + StartDate.ToString("s", CultureInfo.InvariantCulture).Substring(0, 10));
switch (GroupBy)
{
case eDateGroupBy.Seconds:
case eDateGroupBy.Minutes:
AddTimeSerie(60, groupItems);
items += 60;
break;
case eDateGroupBy.Hours:
AddTimeSerie(24, groupItems);
items += 24;
break;
case eDateGroupBy.Days:
if (interval == 1)
{
DateTime dt = new DateTime(2008, 1, 1); //pick a year with 366 days
while (dt.Year == 2008)
{
AddGroupItem(groupItems, dt.ToString("dd-MMM"));
dt = dt.AddDays(1);
}
items += 366;
}
else
{
DateTime dt = StartDate;
items = 0;
while (dt < EndDate)
{
AddGroupItem(groupItems, dt.ToString("dd-MMM"));
dt = dt.AddDays(interval);
items++;
}
}
break;
case eDateGroupBy.Months:
AddGroupItem(groupItems, "jan");
AddGroupItem(groupItems, "feb");
AddGroupItem(groupItems, "mar");
AddGroupItem(groupItems, "apr");
AddGroupItem(groupItems, "may");
AddGroupItem(groupItems, "jun");
AddGroupItem(groupItems, "jul");
AddGroupItem(groupItems, "aug");
AddGroupItem(groupItems, "sep");
AddGroupItem(groupItems, "oct");
AddGroupItem(groupItems, "nov");
AddGroupItem(groupItems, "dec");
items += 12;
break;
case eDateGroupBy.Quarters:
AddGroupItem(groupItems, "Qtr1");
AddGroupItem(groupItems, "Qtr2");
AddGroupItem(groupItems, "Qtr3");
AddGroupItem(groupItems, "Qtr4");
items += 4;
break;
case eDateGroupBy.Years:
if(StartDate.Year>=1900 && EndDate!=DateTime.MaxValue)
{
for (int year = StartDate.Year; year <= EndDate.Year; year++)
{
AddGroupItem(groupItems, year.ToString());
}
items += EndDate.Year - StartDate.Year+1;
}
break;
default:
throw (new Exception("unsupported grouping"));
}
//Lastdate
AddGroupItem(groupItems, ">" + EndDate.ToString("s", CultureInfo.InvariantCulture).Substring(0, 10));
return items;
}