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 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, int interval) { 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, interval); AddFieldItems(items); _grouping = group; return(group); }
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 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, int interval) { 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, interval); 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); }