/// <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; }
/// <summary> /// Set the numeric group. /// </summary> /// <param name="start">The start value.</param> /// <param name="end">The end value.</param> /// <param name="interval">The interval value.</param> /// <returns>The new field numeric group.</returns> internal ExcelPivotTableFieldNumericGroup SetNumericGroup(double start, double end, double interval) { ExcelPivotTableFieldNumericGroup group; group = new ExcelPivotTableFieldNumericGroup(this.NameSpaceManager, myCacheFieldHelper.TopNode); myCacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsNumber", true); myCacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsInteger", true); myCacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsSemiMixedTypes", false); myCacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsString", false); group.TopNode.InnerXml += string.Format("<fieldGroup base=\"{0}\"><rangePr autoStart=\"0\" autoEnd=\"0\" startNum=\"{1}\" endNum=\"{2}\" groupInterval=\"{3}\"/><groupItems /></fieldGroup>", this.BaseIndex, start.ToString(CultureInfo.InvariantCulture), end.ToString(CultureInfo.InvariantCulture), interval.ToString(CultureInfo.InvariantCulture)); int items = AddNumericGroupItems(group, start, end, interval); this.AddFieldItems(items); myGrouping = group; return group; }
/// <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 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; }
internal ExcelPivotTableFieldNumericGroup SetNumericGroup(double start, double end, double interval) { ExcelPivotTableFieldNumericGroup group; group = new ExcelPivotTableFieldNumericGroup(NameSpaceManager, _cacheFieldHelper.TopNode); _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsNumber", true); _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsInteger", true); _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsSemiMixedTypes", false); _cacheFieldHelper.SetXmlNodeBool("d:sharedItems/@containsString", false); group.TopNode.InnerXml += string.Format("<fieldGroup base=\"{0}\"><rangePr autoStart=\"0\" autoEnd=\"0\" startNum=\"{1}\" endNum=\"{2}\" groupInterval=\"{3}\"/><groupItems /></fieldGroup>", BaseIndex, start.ToString(CultureInfo.InvariantCulture), end.ToString(CultureInfo.InvariantCulture), interval.ToString(CultureInfo.InvariantCulture)); int items = AddNumericGroupItems(group, start, end, interval); AddFieldItems(items); _grouping = group; return group; }
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; }
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); } } }
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); }