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);
}
