/// <summary>
/// Set the date group.
/// </summary>
/// <param name="groupBy">How to group the fields.</param>
/// <param name="startDate">The start date.</param>
/// <param name="endDate">The end date.</param>
/// <param name="interval">The interval of the grouping.</param>
/// <returns>The new field data group.</returns>
internal ExcelPivotTableFieldDateGroup SetDateGroup(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, int interval)
{
ExcelPivotTableFieldDateGroup group;
group = new ExcelPivotTableFieldDateGroup(this.NameSpaceManager, myCacheFieldHelper.TopNode);
myCacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsDate", true);
myCacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsNonDate", false);
myCacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsSemiMixedTypes", false);
group.TopNode.InnerXml += string.Format("<fieldGroup base=\"{0}\"><rangePr groupBy=\"{1}\" /><groupItems /></fieldGroup>", this.BaseIndex, groupBy.ToString().ToLower(CultureInfo.InvariantCulture));
if (startDate.Year < 1900)
myCacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", "1900-01-01T00:00:00");
else
{
myCacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", startDate.ToString("s", CultureInfo.InvariantCulture));
myCacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@autoStart", "0");
}
if (endDate == DateTime.MaxValue)
myCacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", "9999-12-31T00:00:00");
else
{
myCacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", endDate.ToString("s", CultureInfo.InvariantCulture));
myCacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@autoEnd", "0");
}
int items = AddDateGroupItems(group, groupBy, startDate, endDate, interval);
this.AddFieldItems(items);
myGrouping = group;
return group;
}
private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField, int interval)
{
if (firstField == false)
{
//Pivot field
var topNode = myTable.PivotTableXml.SelectSingleNode("//d:pivotFields", myTable.NameSpaceManager);
var fieldNode = myTable.PivotTableXml.CreateElement("pivotField", ExcelPackage.schemaMain);
fieldNode.SetAttribute("compact", "0");
fieldNode.SetAttribute("outline", "0");
fieldNode.SetAttribute("showAll", "0");
fieldNode.SetAttribute("defaultSubtotal", "0");
topNode.AppendChild(fieldNode);
var field = new ExcelPivotTableField(myTable.NameSpaceManager, fieldNode, myTable, myTable.Fields.Count, this.Index);
field.DateGrouping = groupBy;
XmlNode rowColFields;
if (this.IsRowField)
rowColFields = this.TopNode.SelectSingleNode("../../d:rowFields", this.NameSpaceManager);
else
rowColFields = this.TopNode.SelectSingleNode("../../d:colFields", this.NameSpaceManager);
int index = 0;
foreach (XmlElement rowfield in rowColFields.ChildNodes)
{
if (int.TryParse(rowfield.GetAttribute("x"), out var fieldIndex))
{
if (myTable.Fields[fieldIndex].BaseIndex == this.BaseIndex)
{
var newElement = rowColFields.OwnerDocument.CreateElement("field", ExcelPackage.schemaMain);
newElement.SetAttribute("x", field.Index.ToString());
rowColFields.InsertBefore(newElement, rowfield);
break;
}
}
index++;
}
if (this.IsRowField)
myTable.RowFields.Insert(field, index);
else
myTable.ColumnFields.Insert(field, index);
myTable.Fields.Add(field);
this.AddCacheField(field, startDate, endDate, interval);
return field;
}
else
{
firstField = false;
this.DateGrouping = groupBy;
this.Compact = false;
SetDateGroup(groupBy, startDate, endDate, interval);
return this;
}
}
private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField, int interval)
{
if (firstField == false)
{
ExcelPivotTableField field = _pivotTable.Fields.AddDateGroupField(Index);
XmlNode rowColFields;
if (IsRowField)
{
rowColFields = TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
}
else
{
rowColFields = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
}
var index = 0;
foreach (XmlElement rowfield in rowColFields.ChildNodes)
{
if (int.TryParse(rowfield.GetAttribute("x"), out int fieldIndex))
{
if (_pivotTable.Fields[fieldIndex].BaseIndex == BaseIndex)
{
var newElement = rowColFields.OwnerDocument.CreateElement("field", ExcelPackage.schemaMain);
newElement.SetAttribute("x", field.Index.ToString());
rowColFields.InsertBefore(newElement, rowfield);
break;
}
}
index++;
}
var cacheRef = _pivotTable.CacheDefinition._cacheReference;
field._cacheField = cacheRef.AddDateGroupField(field, groupBy, startDate, endDate, interval);
UpdatePivotTableGroupItems(field, cacheRef, false);
if (IsRowField)
{
_pivotTable.RowFields.Insert(field, index);
}
else
{
_pivotTable.ColumnFields.Insert(field, index);
}
return(field);
}
else
{
firstField = false;
Compact = false;
_cacheField.SetDateGroup(this, groupBy, startDate, endDate, interval);
UpdatePivotTableGroupItems(this, _pivotTable.CacheDefinition._cacheReference, true);
return(this);
}
}
/// <summary>
/// Returns the date group field.
/// </summary>
/// <param name="GroupBy">The type of grouping</param>
/// <returns>The matching field. If none is found null is returned</returns>
public ExcelPivotTableField GetDateGroupField(eDateGroupBy GroupBy)
{
foreach (var fld in _list)
{
if (fld.Grouping is ExcelPivotTableFieldDateGroup && (((ExcelPivotTableFieldDateGroup)fld.Grouping).GroupBy) == GroupBy)
{
return(fld);
}
}
return(null);
}
/// <summary>
/// Returns the date group field.
/// </summary>
/// <param name="groupBy">The type of grouping.</param>
/// <returns>The matching field or null if none is found.</returns>
public ExcelPivotTableField GetDateGroupField(eDateGroupBy groupBy)
{
foreach (var field in this)
{
if (field.Grouping is ExcelPivotTableFieldDateGroup && (((ExcelPivotTableFieldDateGroup)field.Grouping).GroupBy) == groupBy)
{
return(field);
}
}
return(null);
}
/// <summary>
/// Set the cache field node.
/// </summary>
/// <param name="cacheField">The cache field node being set.</param>
internal void SetCacheFieldNode(XmlNode cacheField)
{
myCacheFieldHelper = new XmlHelperInstance(this.NameSpaceManager, cacheField);
var groupNode = cacheField.SelectSingleNode("d:fieldGroup", this.NameSpaceManager);
if (groupNode != null)
{
var groupBy = groupNode.SelectSingleNode("d:rangePr/@groupBy", this.NameSpaceManager);
if (groupBy == null)
myGrouping = new ExcelPivotTableFieldNumericGroup(this.NameSpaceManager, cacheField);
else
{
this.DateGrouping = (eDateGroupBy)Enum.Parse(typeof(eDateGroupBy), groupBy.Value, true);
myGrouping = new ExcelPivotTableFieldDateGroup(this.NameSpaceManager, groupNode);
}
}
}
private void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, int groupInterval)
{
if (groupInterval < 1 || groupInterval >= Int16.MaxValue)
throw (new ArgumentOutOfRangeException("Group interval is out of range"));
if (groupInterval > 1 && groupBy != eDateGroupBy.Days)
throw (new ArgumentException("Group interval is can only be used when groupBy is Days"));
this.ValidateGrouping();
bool firstField = true;
List<ExcelPivotTableField> fields = new List<ExcelPivotTableField>();
//Seconds
if ((groupBy & eDateGroupBy.Seconds) == eDateGroupBy.Seconds)
fields.Add(this.AddField(eDateGroupBy.Seconds, startDate, endDate, ref firstField));
//Minutes
if ((groupBy & eDateGroupBy.Minutes) == eDateGroupBy.Minutes)
fields.Add(this.AddField(eDateGroupBy.Minutes, startDate, endDate, ref firstField));
//Hours
if ((groupBy & eDateGroupBy.Hours) == eDateGroupBy.Hours)
fields.Add(this.AddField(eDateGroupBy.Hours, startDate, endDate, ref firstField));
//Days
if ((groupBy & eDateGroupBy.Days) == eDateGroupBy.Days)
fields.Add(this.AddField(eDateGroupBy.Days, startDate, endDate, ref firstField, groupInterval));
//Month
if ((groupBy & eDateGroupBy.Months) == eDateGroupBy.Months)
fields.Add(this.AddField(eDateGroupBy.Months, startDate, endDate, ref firstField));
//Quarters
if ((groupBy & eDateGroupBy.Quarters) == eDateGroupBy.Quarters)
fields.Add(this.AddField(eDateGroupBy.Quarters, startDate, endDate, ref firstField));
//Years
if ((groupBy & eDateGroupBy.Years) == eDateGroupBy.Years)
fields.Add(this.AddField(eDateGroupBy.Years, startDate, endDate, ref firstField));
if (fields.Count > 1)
myCacheFieldHelper.SetXmlNodeString("d:fieldGroup/@par", (myTable.Fields.Count - 1).ToString());
if (groupInterval != 1)
myCacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@groupInterval", groupInterval.ToString());
else
myCacheFieldHelper.DeleteNode("d:fieldGroup/d:rangePr/@groupInterval");
myItems = null;
}
internal ExcelPivotTableFieldDateGroup SetDateGroup(ExcelPivotTableField field, eDateGroupBy groupBy, DateTime StartDate, DateTime EndDate, int interval)
{
ExcelPivotTableFieldDateGroup group;
group = new ExcelPivotTableFieldDateGroup(NameSpaceManager, TopNode);
SetXmlNodeBool("d:sharedItems/@containsDate", true);
SetXmlNodeBool("d:sharedItems/@containsNonDate", false);
SetXmlNodeBool("d:sharedItems/@containsSemiMixedTypes", false);
group.TopNode.InnerXml += string.Format("<fieldGroup base=\"{0}\"><rangePr groupBy=\"{1}\" /><groupItems /></fieldGroup>", field.BaseIndex, groupBy.ToString().ToLower(CultureInfo.InvariantCulture));
if (StartDate.Year < 1900)
{
SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", "1900-01-01T00:00:00");
}
else
{
SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", StartDate.ToString("s", CultureInfo.InvariantCulture));
SetXmlNodeString("d:fieldGroup/d:rangePr/@autoStart", "0");
}
if (EndDate == DateTime.MaxValue)
{
SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", "9999-12-31T00:00:00");
}
else
{
SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", EndDate.ToString("s", CultureInfo.InvariantCulture));
SetXmlNodeString("d:fieldGroup/d:rangePr/@autoEnd", "0");
}
int items = AddDateGroupItems(group, groupBy, StartDate, EndDate, interval);
Grouping = group;
DateGrouping = groupBy;
return(group);
}
private int AddDateGroupItems(ExcelPivotTableFieldGroup group, eDateGroupBy GroupBy, DateTime StartDate, DateTime EndDate)
{
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:
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;
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;
}
private ExcelPivotTableField AddField(eDateGroupBy groupBy,DateTime startDate, DateTime endDate,ref bool firstField)
{
if (firstField == false)
{
//Pivot field
var topNode = _table.PivotTableXml.SelectSingleNode("//d:pivotFields", _table.NameSpaceManager);
var fieldNode = _table.PivotTableXml.CreateElement("pivotField", ExcelPackage.schemaMain);
fieldNode.SetAttribute("compact", "0");
fieldNode.SetAttribute("outline", "0");
fieldNode.SetAttribute("showAll", "0");
fieldNode.SetAttribute("defaultSubtotal", "0");
topNode.AppendChild(fieldNode);
var field = new ExcelPivotTableField(_table.NameSpaceManager, fieldNode, _table, _table.Fields.Count, Index);
field.DateGrouping = groupBy;
XmlNode rowColFields;
if (IsRowField)
{
rowColFields=TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
}
else
{
rowColFields = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
}
int fieldIndex, index = 0;
foreach (XmlElement rowfield in rowColFields.ChildNodes)
{
if (int.TryParse(rowfield.GetAttribute("x"), out fieldIndex))
{
if (_table.Fields[fieldIndex].BaseIndex == BaseIndex)
{
var newElement = rowColFields.OwnerDocument.CreateElement("field", ExcelPackage.schemaMain);
newElement.SetAttribute("x", field.Index.ToString());
rowColFields.InsertBefore(newElement, rowfield);
break;
}
}
index++;
}
if (IsRowField)
{
_table.RowFields.Insert(field, index);
}
else
{
_table.ColumnFields.Insert(field, index);
}
_table.Fields.AddInternal(field);
AddCacheField(field, startDate, endDate);
return field;
}
else
{
firstField = false;
DateGrouping = groupBy;
Compact = false;
SetDateGroup(groupBy, startDate, endDate);
return this;
}
}
internal void SetCacheFieldNode(XmlNode cacheField)
{
_cacheFieldHelper = new XmlHelperInstance(NameSpaceManager, cacheField);
var groupNode = cacheField.SelectSingleNode("d:fieldGroup", NameSpaceManager);
if (groupNode!=null)
{
var groupBy = groupNode.SelectSingleNode("d:rangePr/@groupBy", NameSpaceManager);
if (groupBy==null)
{
_grouping = new ExcelPivotTableFieldNumericGroup(NameSpaceManager, cacheField);
}
else
{
DateGrouping=(eDateGroupBy)Enum.Parse(typeof(eDateGroupBy), groupBy.Value, true);
_grouping = new ExcelPivotTableFieldDateGroup(NameSpaceManager, groupNode);
}
}
}
internal ExcelPivotTableFieldDateGroup SetDateGroup(eDateGroupBy GroupBy, DateTime StartDate, DateTime EndDate)
{
ExcelPivotTableFieldDateGroup group;
group = new ExcelPivotTableFieldDateGroup(NameSpaceManager, _cacheFieldHelper.TopNode);
_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsDate", true);
_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsNonDate", false);
_cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsSemiMixedTypes", false);
group.TopNode.InnerXml += string.Format("<fieldGroup base=\"{0}\"><rangePr groupBy=\"{1}\" /><groupItems /></fieldGroup>", BaseIndex, GroupBy.ToString().ToLower());
if (StartDate.Year < 1900)
{
_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", "1900-01-01T00:00:00");
}
else
{
_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@startDate", StartDate.ToString("s", CultureInfo.InvariantCulture));
_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@autoStart", "0");
}
if (EndDate==DateTime.MaxValue)
{
_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", "9999-12-31T00:00:00");
}
else
{
_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@endDate", EndDate.ToString("s", CultureInfo.InvariantCulture));
_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@autoEnd", "0");
}
int items = AddDateGroupItems(group, GroupBy, StartDate, EndDate);
AddFieldItems(items);
_grouping = group;
return group;
}
/// <summary>
/// Add a date grouping on this field.
/// </summary>
/// <param name="groupBy">Group by</param>
public void AddDateGrouping(eDateGroupBy groupBy)
{
AddDateGrouping(groupBy, DateTime.MinValue, DateTime.MaxValue);
}
/// <summary>
/// Add a date grouping on this field.
/// </summary>
/// <param name="groupBy">Group by</param>
/// <param name="startDate">Fixed start date. Use DateTime.MinValue for auto</param>
/// <param name="endDate">Fixed end date. Use DateTime.MaxValue for auto</param>
public void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate)
{
ValidateGrouping();
bool firstField = true;
List<ExcelPivotTableField> fields=new List<ExcelPivotTableField>();
//Seconds
if ((groupBy & eDateGroupBy.Seconds) == eDateGroupBy.Seconds)
{
fields.Add(AddField(eDateGroupBy.Seconds, startDate, endDate, ref firstField));
}
//Minutes
if ((groupBy & eDateGroupBy.Minutes) == eDateGroupBy.Minutes)
{
fields.Add(AddField(eDateGroupBy.Minutes, startDate, endDate, ref firstField));
}
//Hours
if ((groupBy & eDateGroupBy.Hours) == eDateGroupBy.Hours)
{
fields.Add(AddField(eDateGroupBy.Hours, startDate, endDate, ref firstField));
}
//Days
if ((groupBy & eDateGroupBy.Days) == eDateGroupBy.Days)
{
fields.Add(AddField(eDateGroupBy.Days, startDate, endDate, ref firstField));
}
//Month
if ((groupBy & eDateGroupBy.Months) == eDateGroupBy.Months)
{
fields.Add(AddField(eDateGroupBy.Months, startDate, endDate, ref firstField));
}
//Quarters
if ((groupBy & eDateGroupBy.Quarters) == eDateGroupBy.Quarters)
{
fields.Add(AddField(eDateGroupBy.Quarters, startDate, endDate, ref firstField));
}
//Years
if ((groupBy & eDateGroupBy.Years) == eDateGroupBy.Years)
{
fields.Add(AddField(eDateGroupBy.Years, startDate, endDate, ref firstField));
}
_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/@par", (_table.Fields.Count - 1).ToString());
_items = null;
}
private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField)
{
return(AddField(groupBy, startDate, endDate, ref firstField, 1));
}
private void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, int groupInterval)
{
if (groupInterval < 1 || groupInterval >= Int16.MaxValue)
{
throw (new ArgumentOutOfRangeException("Group interval is out of range"));
}
if (groupInterval > 1 && groupBy != eDateGroupBy.Days)
{
throw (new ArgumentException("Group interval is can only be used when groupBy is Days"));
}
if (Cache.DatabaseField == false)
{
throw new InvalidOperationException("The field for grouping can not be a calculated field.");
}
ValidateGrouping();
_items = null;
bool firstField = true;
var fields = _pivotTable.Fields.Count;
//Seconds
if ((groupBy & eDateGroupBy.Seconds) == eDateGroupBy.Seconds)
{
AddField(eDateGroupBy.Seconds, startDate, endDate, ref firstField);
}
//Minutes
if ((groupBy & eDateGroupBy.Minutes) == eDateGroupBy.Minutes)
{
AddField(eDateGroupBy.Minutes, startDate, endDate, ref firstField);
}
//Hours
if ((groupBy & eDateGroupBy.Hours) == eDateGroupBy.Hours)
{
AddField(eDateGroupBy.Hours, startDate, endDate, ref firstField);
}
//Days
if ((groupBy & eDateGroupBy.Days) == eDateGroupBy.Days)
{
AddField(eDateGroupBy.Days, startDate, endDate, ref firstField, groupInterval);
}
//Month
if ((groupBy & eDateGroupBy.Months) == eDateGroupBy.Months)
{
AddField(eDateGroupBy.Months, startDate, endDate, ref firstField);
}
//Quarters
if ((groupBy & eDateGroupBy.Quarters) == eDateGroupBy.Quarters)
{
AddField(eDateGroupBy.Quarters, startDate, endDate, ref firstField);
}
//Years
if ((groupBy & eDateGroupBy.Years) == eDateGroupBy.Years)
{
AddField(eDateGroupBy.Years, startDate, endDate, ref firstField);
}
if (fields > _pivotTable.Fields.Count)
{
_cacheField.SetXmlNodeString("d:fieldGroup/@par", (_pivotTable.Fields.Count - 1).ToString());
}
if (groupInterval != 1)
{
_cacheField.SetXmlNodeString("d:fieldGroup/d:rangePr/@groupInterval", groupInterval.ToString());
}
else
{
_cacheField.DeleteNode("d:fieldGroup/d:rangePr/@groupInterval");
}
}
/// <summary>
/// Add a date grouping on this field.
/// </summary>
/// <param name="groupBy">Group by</param>
public void AddDateGrouping(eDateGroupBy groupBy)
{
AddDateGrouping(groupBy, DateTime.MinValue, DateTime.MaxValue, 1);
}
/// <summary>
/// Add a date grouping on this field.
/// </summary>
/// <param name="groupBy">Group by</param>
/// <param name="startDate">Fixed start date. Use DateTime.MinValue for auto</param>
/// <param name="endDate">Fixed end date. Use DateTime.MaxValue for auto</param>
public void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate)
{
AddDateGrouping(groupBy, startDate, endDate, 1);
}
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);
}
internal ExcelPivotTableCacheField AddDateGroupField(ExcelPivotTableField field, eDateGroupBy groupBy, DateTime startDate, DateTime endDate, int interval)
{
ExcelPivotTableCacheField cacheField = CreateField(groupBy.ToString(), field.Index, false);
cacheField.SetDateGroup(field, groupBy, startDate, endDate, interval);
Fields.Add(cacheField);
return(cacheField);
}
/// <summary>
/// Add a date grouping on this field.
/// </summary>
/// <param name="groupBy">Group by</param>
/// <param name="startDate">Fixed start date. Use DateTime.MinValue for auto</param>
/// <param name="endDate">Fixed end date. Use DateTime.MaxValue for auto</param>
public void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate)
{
AddDateGrouping(groupBy, startDate, endDate, 1);
}
private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField)
{
return AddField(groupBy, startDate, endDate, ref firstField,1);
}
private void AddDateGrouping(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, int groupInterval)
{
if (groupInterval < 1 || groupInterval >= Int16.MaxValue)
{
throw (new ArgumentOutOfRangeException("Group interval is out of range"));
}
if (groupInterval > 1 && groupBy != eDateGroupBy.Days)
{
throw (new ArgumentException("Group interval is can only be used when groupBy is Days"));
}
ValidateGrouping();
bool firstField = true;
List<ExcelPivotTableField> fields=new List<ExcelPivotTableField>();
//Seconds
if ((groupBy & eDateGroupBy.Seconds) == eDateGroupBy.Seconds)
{
fields.Add(AddField(eDateGroupBy.Seconds, startDate, endDate, ref firstField));
}
//Minutes
if ((groupBy & eDateGroupBy.Minutes) == eDateGroupBy.Minutes)
{
fields.Add(AddField(eDateGroupBy.Minutes, startDate, endDate, ref firstField));
}
//Hours
if ((groupBy & eDateGroupBy.Hours) == eDateGroupBy.Hours)
{
fields.Add(AddField(eDateGroupBy.Hours, startDate, endDate, ref firstField));
}
//Days
if ((groupBy & eDateGroupBy.Days) == eDateGroupBy.Days)
{
fields.Add(AddField(eDateGroupBy.Days, startDate, endDate, ref firstField, groupInterval));
}
//Month
if ((groupBy & eDateGroupBy.Months) == eDateGroupBy.Months)
{
fields.Add(AddField(eDateGroupBy.Months, startDate, endDate, ref firstField));
}
//Quarters
if ((groupBy & eDateGroupBy.Quarters) == eDateGroupBy.Quarters)
{
fields.Add(AddField(eDateGroupBy.Quarters, startDate, endDate, ref firstField));
}
//Years
if ((groupBy & eDateGroupBy.Years) == eDateGroupBy.Years)
{
fields.Add(AddField(eDateGroupBy.Years, startDate, endDate, ref firstField));
}
if (fields.Count > 1) _cacheFieldHelper.SetXmlNodeString("d:fieldGroup/@par", (_table.Fields.Count - 1).ToString());
if (groupInterval != 1)
{
_cacheFieldHelper.SetXmlNodeString("d:fieldGroup/d:rangePr/@groupInterval", groupInterval.ToString());
}
else
{
_cacheFieldHelper.DeleteNode("d:fieldGroup/d:rangePr/@groupInterval");
}
_items = null;
}
