/*******************************************************************************
|
* You may amend and distribute as you like, but don't remove this header!
|
*
|
* EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
|
* See http://www.codeplex.com/EPPlus for details.
|
*
|
* Copyright (C) 2011 Jan Källman
|
*
|
* This library is free software; you can redistribute it and/or
|
* modify it under the terms of the GNU Lesser General Public
|
* License as published by the Free Software Foundation; either
|
* version 2.1 of the License, or (at your option) any later version.
|
|
* This library is distributed in the hope that it will be useful,
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
|
* See the GNU Lesser General Public License for more details.
|
*
|
* The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
|
* If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
|
*
|
* All code and executables are provided "as is" with no warranty either express or implied.
|
* The author accepts no liability for any damage or loss of business that this product may cause.
|
*
|
* Code change notes:
|
*
|
* Author Change Date
|
* ******************************************************************************
|
* Jan Källman Added 21-MAR-2011
|
* Jan Källman License changed GPL-->LGPL 2011-12-16
|
*******************************************************************************/
|
using System;
|
using System.Collections.Generic;
|
using System.Text;
|
using System.Xml;
|
using System.Globalization;
|
|
namespace HH.WMS.Utils.EPPlus.Table.PivotTable
|
{
|
|
/// <summary>
|
/// Defines the axis for a PivotTable
|
/// </summary>
|
public enum ePivotFieldAxis
|
{
|
/// <summary>
|
/// None
|
/// </summary>
|
None=-1,
|
/// <summary>
|
/// Column axis
|
/// </summary>
|
Column,
|
/// <summary>
|
/// Page axis (Include Count Filter)
|
///
|
/// </summary>
|
Page,
|
/// <summary>
|
/// Row axis
|
/// </summary>
|
Row,
|
/// <summary>
|
/// Values axis
|
/// </summary>
|
Values
|
}
|
/// <summary>
|
/// Build-in table row functions
|
/// </summary>
|
public enum DataFieldFunctions
|
{
|
Average,
|
Count,
|
CountNums,
|
Max,
|
Min,
|
Product,
|
None,
|
StdDev,
|
StdDevP,
|
Sum,
|
Var,
|
VarP
|
}
|
/// <summary>
|
/// Built-in subtotal functions
|
/// </summary>
|
[Flags]
|
public enum eSubTotalFunctions
|
{
|
None=1,
|
Count=2,
|
CountA=4,
|
Avg=8,
|
Default=16,
|
Min=32,
|
Max=64,
|
Product=128,
|
StdDev=256,
|
StdDevP=512,
|
Sum=1024,
|
Var=2048,
|
VarP=4096
|
}
|
/// <summary>
|
/// Data grouping
|
/// </summary>
|
[Flags]
|
public enum eDateGroupBy
|
{
|
Years = 1,
|
Quarters = 2,
|
Months = 4,
|
Days = 8,
|
Hours = 16,
|
Minutes = 32,
|
Seconds = 64
|
}
|
/// <summary>
|
/// Sorting
|
/// </summary>
|
public enum eSortType
|
{
|
None,
|
Ascending,
|
Descending
|
}
|
/// <summary>
|
/// A pivot table field.
|
/// </summary>
|
public class ExcelPivotTableField : XmlHelper
|
{
|
internal ExcelPivotTable _table;
|
internal ExcelPivotTableField(XmlNamespaceManager ns, XmlNode topNode,ExcelPivotTable table, int index, int baseIndex) :
|
base(ns, topNode)
|
{
|
Index = index;
|
BaseIndex = baseIndex;
|
_table = table;
|
}
|
public int Index
|
{
|
get;
|
set;
|
}
|
internal int BaseIndex
|
{
|
get;
|
set;
|
}
|
/// <summary>
|
/// Name of the field
|
/// </summary>
|
public string Name
|
{
|
get
|
{
|
string v = GetXmlNodeString("@name");
|
if (v == "")
|
{
|
return _cacheFieldHelper.GetXmlNodeString("@name");
|
}
|
else
|
{
|
return GetXmlNodeString("@name");
|
}
|
}
|
set
|
{
|
SetXmlNodeString("@name", value);
|
}
|
}
|
/// <summary>
|
/// Compact mode
|
/// </summary>
|
public bool Compact
|
{
|
get
|
{
|
return GetXmlNodeBool("@compact");
|
}
|
set
|
{
|
SetXmlNodeBool("@compact",value);
|
}
|
}
|
/// <summary>
|
/// A boolean that indicates whether the items in this field should be shown in Outline form
|
/// </summary>
|
public bool Outline
|
{
|
get
|
{
|
return GetXmlNodeBool("@outline");
|
}
|
set
|
{
|
SetXmlNodeBool("@outline",value);
|
}
|
}
|
/// <summary>
|
/// The custom text that is displayed for the subtotals label
|
/// </summary>
|
public bool SubtotalTop
|
{
|
get
|
{
|
return GetXmlNodeBool("@subtotalTop");
|
}
|
set
|
{
|
SetXmlNodeBool("@subtotalTop",value);
|
}
|
}
|
/// <summary>
|
/// A boolean that indicates whether to show all items for this field
|
/// </summary>
|
public bool ShowAll
|
{
|
get
|
{
|
return GetXmlNodeBool("@showAll");
|
}
|
set
|
{
|
SetXmlNodeBool("@showAll",value);
|
}
|
}
|
/// <summary>
|
/// The type of sort that is applied to this field
|
/// </summary>
|
public eSortType Sort
|
{
|
get
|
{
|
string v = GetXmlNodeString("@sortType");
|
return v == "" ? eSortType.None : (eSortType)Enum.Parse(typeof(eSortType), v, true);
|
}
|
set
|
{
|
if (value == eSortType.None)
|
{
|
DeleteNode("@sortType");
|
}
|
else
|
{
|
SetXmlNodeString("@sortType", value.ToString().ToLower());
|
}
|
}
|
}
|
/// <summary>
|
/// A boolean that indicates whether manual filter is in inclusive mode
|
/// </summary>
|
public bool IncludeNewItemsInFilter
|
{
|
get
|
{
|
return GetXmlNodeBool("@includeNewItemsInFilter");
|
}
|
set
|
{
|
SetXmlNodeBool("@includeNewItemsInFilter",value);
|
}
|
}
|
/// <summary>
|
/// Enumeration of the different subtotal operations that can be applied to page, row or column fields
|
/// </summary>
|
public eSubTotalFunctions SubTotalFunctions
|
{
|
get
|
{
|
eSubTotalFunctions ret = 0;
|
XmlNodeList nl = TopNode.SelectNodes("d:items/d:item/@t", NameSpaceManager);
|
if (nl.Count == 0) return eSubTotalFunctions.None;
|
foreach (XmlAttribute item in nl)
|
{
|
try
|
{
|
ret |= (eSubTotalFunctions)Enum.Parse(typeof(eSubTotalFunctions), item.Value, true);
|
}
|
catch (ArgumentException ex)
|
{
|
throw new ArgumentException("Unable to parse value of " + item.Value + " to a valid pivot table subtotal function", ex);
|
}
|
}
|
return ret;
|
}
|
set
|
{
|
if ((value & eSubTotalFunctions.None) == eSubTotalFunctions.None && (value != eSubTotalFunctions.None))
|
{
|
throw (new ArgumentException("Value None can not be combined with other values."));
|
}
|
if ((value & eSubTotalFunctions.Default) == eSubTotalFunctions.Default && (value != eSubTotalFunctions.Default))
|
{
|
throw (new ArgumentException("Value Default can not be combined with other values."));
|
}
|
|
|
// remove old attribute
|
XmlNodeList nl = TopNode.SelectNodes("d:items/d:item/@t", NameSpaceManager);
|
if (nl.Count > 0)
|
{
|
foreach (XmlAttribute item in nl)
|
{
|
DeleteNode("@" + item.Value + "Subtotal");
|
item.OwnerElement.ParentNode.RemoveChild(item.OwnerElement);
|
}
|
}
|
|
|
if (value==eSubTotalFunctions.None)
|
{
|
// for no subtotals, set defaultSubtotal to off
|
SetXmlNodeBool("@defaultSubtotal", false);
|
TopNode.InnerXml = "";
|
}
|
else
|
{
|
string innerXml = "";
|
int count = 0;
|
foreach (eSubTotalFunctions e in Enum.GetValues(typeof(eSubTotalFunctions)))
|
{
|
if ((value & e) == e)
|
{
|
var newTotalType = e.ToString();
|
var totalType = char.ToLower(newTotalType[0]) + newTotalType.Substring(1);
|
// add new attribute
|
SetXmlNodeBool("@" + totalType + "Subtotal", true);
|
innerXml += "<item t=\"" + totalType + "\" />";
|
count++;
|
}
|
}
|
TopNode.InnerXml = string.Format("<items count=\"{0}\">{1}</items>", count, innerXml);
|
}
|
}
|
}
|
/// <summary>
|
/// Type of axis
|
/// </summary>
|
public ePivotFieldAxis Axis
|
{
|
get
|
{
|
switch(GetXmlNodeString("@axis"))
|
{
|
case "axisRow":
|
return ePivotFieldAxis.Row;
|
case "axisCol":
|
return ePivotFieldAxis.Column;
|
case "axisPage":
|
return ePivotFieldAxis.Page;
|
case "axisValues":
|
return ePivotFieldAxis.Values;
|
default:
|
return ePivotFieldAxis.None;
|
}
|
}
|
internal set
|
{
|
switch (value)
|
{
|
case ePivotFieldAxis.Row:
|
SetXmlNodeString("@axis","axisRow");
|
break;
|
case ePivotFieldAxis.Column:
|
SetXmlNodeString("@axis","axisCol");
|
break;
|
case ePivotFieldAxis.Values:
|
SetXmlNodeString("@axis", "axisValues");
|
break;
|
case ePivotFieldAxis.Page:
|
SetXmlNodeString("@axis", "axisPage");
|
break;
|
default:
|
DeleteNode("@axis");
|
break;
|
}
|
}
|
}
|
/// <summary>
|
/// If the field is a row field
|
/// </summary>
|
public bool IsRowField
|
{
|
get
|
{
|
return (TopNode.SelectSingleNode(string.Format("../../d:rowFields/d:field[@x={0}]", Index), NameSpaceManager) != null);
|
}
|
internal set
|
{
|
if (value)
|
{
|
var rowsNode = TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
|
if (rowsNode == null)
|
{
|
_table.CreateNode("d:rowFields");
|
}
|
rowsNode = TopNode.SelectSingleNode("../../d:rowFields", NameSpaceManager);
|
|
AppendField(rowsNode, Index, "field", "x");
|
if (BaseIndex == Index)
|
{
|
TopNode.InnerXml = "<items count=\"1\"><item t=\"default\" /></items>";
|
}
|
else
|
{
|
TopNode.InnerXml = "<items count=\"0\"></items>";
|
}
|
}
|
else
|
{
|
XmlElement node = TopNode.SelectSingleNode(string.Format("../../d:rowFields/d:field[@x={0}]", Index), NameSpaceManager) as XmlElement;
|
if (node != null)
|
{
|
node.ParentNode.RemoveChild(node);
|
}
|
}
|
}
|
}
|
/// <summary>
|
/// If the field is a column field
|
/// </summary>
|
public bool IsColumnField
|
{
|
get
|
{
|
return (TopNode.SelectSingleNode(string.Format("../../d:colFields/d:field[@x={0}]", Index), NameSpaceManager) != null);
|
}
|
internal set
|
{
|
if (value)
|
{
|
var columnsNode = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
|
if (columnsNode == null)
|
{
|
_table.CreateNode("d:colFields");
|
}
|
columnsNode = TopNode.SelectSingleNode("../../d:colFields", NameSpaceManager);
|
|
AppendField(columnsNode, Index, "field", "x");
|
if (BaseIndex == Index)
|
{
|
TopNode.InnerXml = "<items count=\"1\"><item t=\"default\" /></items>";
|
}
|
else
|
{
|
TopNode.InnerXml = "<items count=\"0\"></items>";
|
}
|
}
|
else
|
{
|
XmlElement node = TopNode.SelectSingleNode(string.Format("../../d:colFields/d:field[@x={0}]", Index), NameSpaceManager) as XmlElement;
|
if (node != null)
|
{
|
node.ParentNode.RemoveChild(node);
|
}
|
}
|
}
|
}
|
/// <summary>
|
/// If the field is a datafield
|
/// </summary>
|
public bool IsDataField
|
{
|
get
|
{
|
return GetXmlNodeBool("@dataField", false);
|
}
|
}
|
/// <summary>
|
/// If the field is a page field.
|
/// </summary>
|
public bool IsPageField
|
{
|
get
|
{
|
return (Axis==ePivotFieldAxis.Page);
|
}
|
internal set
|
{
|
if (value)
|
{
|
var dataFieldsNode = TopNode.SelectSingleNode("../../d:pageFields", NameSpaceManager);
|
if (dataFieldsNode == null)
|
{
|
_table.CreateNode("d:pageFields");
|
dataFieldsNode = TopNode.SelectSingleNode("../../d:pageFields", NameSpaceManager);
|
}
|
|
TopNode.InnerXml = "<items count=\"1\"><item t=\"default\" /></items>";
|
|
XmlElement node = AppendField(dataFieldsNode, Index, "pageField", "fld");
|
_pageFieldSettings = new ExcelPivotTablePageFieldSettings(NameSpaceManager, node, this, Index);
|
}
|
else
|
{
|
_pageFieldSettings = null;
|
XmlElement node = TopNode.SelectSingleNode(string.Format("../../d:pageFields/d:pageField[@fld={0}]", Index), NameSpaceManager) as XmlElement;
|
if (node != null)
|
{
|
node.ParentNode.RemoveChild(node);
|
}
|
}
|
}
|
}
|
//public ExcelPivotGrouping DateGrouping
|
//{
|
|
//}
|
internal ExcelPivotTablePageFieldSettings _pageFieldSettings = null;
|
public ExcelPivotTablePageFieldSettings PageFieldSettings
|
{
|
get
|
{
|
return _pageFieldSettings;
|
}
|
}
|
internal eDateGroupBy DateGrouping
|
{
|
get;
|
set;
|
}
|
ExcelPivotTableFieldGroup _grouping=null;
|
/// <summary>
|
/// Grouping settings.
|
/// Null if the field has no grouping otherwise ExcelPivotTableFieldNumericGroup or ExcelPivotTableFieldNumericGroup.
|
/// </summary>
|
public ExcelPivotTableFieldGroup Grouping
|
{
|
get
|
{
|
return _grouping;
|
}
|
}
|
#region Private & internal Methods
|
internal XmlElement AppendField(XmlNode rowsNode, int index, string fieldNodeText, string indexAttrText)
|
{
|
XmlElement prevField = null, newElement;
|
foreach (XmlElement field in rowsNode.ChildNodes)
|
{
|
string x = field.GetAttribute(indexAttrText);
|
int fieldIndex;
|
if(int.TryParse(x, out fieldIndex))
|
{
|
if (fieldIndex == index) //Row already exists
|
{
|
return field;
|
}
|
else if (fieldIndex > index)
|
{
|
newElement = rowsNode.OwnerDocument.CreateElement(fieldNodeText, ExcelPackage.schemaMain);
|
newElement.SetAttribute(indexAttrText, index.ToString());
|
rowsNode.InsertAfter(newElement, field);
|
}
|
}
|
prevField=field;
|
}
|
newElement = rowsNode.OwnerDocument.CreateElement(fieldNodeText, ExcelPackage.schemaMain);
|
newElement.SetAttribute(indexAttrText, index.ToString());
|
rowsNode.InsertAfter(newElement, prevField);
|
|
return newElement;
|
}
|
internal XmlHelperInstance _cacheFieldHelper = null;
|
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);
|
}
|
}
|
}
|
#endregion
|
#region Grouping
|
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 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;
|
}
|
|
private int AddNumericGroupItems(ExcelPivotTableFieldNumericGroup group, double start, double end, double interval)
|
{
|
if (interval < 0)
|
{
|
throw (new Exception("The interval must be a positiv"));
|
}
|
if (start > end)
|
{
|
throw(new Exception("Then End number must be larger than the Start number"));
|
}
|
|
XmlElement groupItems = group.TopNode.SelectSingleNode("d:fieldGroup/d:groupItems", group.NameSpaceManager) as XmlElement;
|
int items = 2;
|
//First date
|
double index=start;
|
double nextIndex=start+interval;
|
AddGroupItem(groupItems, "<" + start.ToString(CultureInfo.InvariantCulture));
|
|
while (index < end)
|
{
|
AddGroupItem(groupItems, string.Format("{0}-{1}", index.ToString(CultureInfo.InvariantCulture), nextIndex.ToString(CultureInfo.InvariantCulture)));
|
index=nextIndex;
|
nextIndex+=interval;
|
items++;
|
}
|
AddGroupItem(groupItems, ">" + nextIndex.ToString(CultureInfo.InvariantCulture));
|
return items;
|
}
|
|
private void AddFieldItems(int items)
|
{
|
XmlElement prevNode = null;
|
XmlElement itemsNode = TopNode.SelectSingleNode("d:items", NameSpaceManager) as XmlElement;
|
for (int x = 0; x < items; x++)
|
{
|
var itemNode = itemsNode.OwnerDocument.CreateElement("item", ExcelPackage.schemaMain);
|
itemNode.SetAttribute("x", x.ToString());
|
if (prevNode == null)
|
{
|
itemsNode.PrependChild(itemNode);
|
}
|
else
|
{
|
itemsNode.InsertAfter(itemNode, prevNode);
|
}
|
prevNode = itemNode;
|
}
|
itemsNode.SetAttribute("count", (items + 1).ToString());
|
}
|
|
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;
|
}
|
|
private void AddTimeSerie(int count, XmlElement groupItems)
|
{
|
for (int i = 0; i < count; i++)
|
{
|
AddGroupItem(groupItems, string.Format("{0:00}", i));
|
}
|
}
|
|
private void AddGroupItem(XmlElement groupItems, string value)
|
{
|
var s = groupItems.OwnerDocument.CreateElement("s", ExcelPackage.schemaMain);
|
s.SetAttribute("v", value);
|
groupItems.AppendChild(s);
|
}
|
#endregion
|
internal ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> _items=null;
|
/// <summary>
|
/// Pivottable field Items. Used for grouping.
|
/// </summary>
|
public ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> Items
|
{
|
get
|
{
|
if (_items == null)
|
{
|
_items = new ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem>(_table);
|
foreach (XmlNode node in TopNode.SelectNodes("d:items//d:item", NameSpaceManager))
|
{
|
var item = new ExcelPivotTableFieldItem(NameSpaceManager, node,this);
|
if (item.T == "")
|
{
|
_items.AddInternal(item);
|
}
|
}
|
//if (_grouping is ExcelPivotTableFieldDateGroup)
|
//{
|
// ExcelPivotTableFieldDateGroup dtgrp = ((ExcelPivotTableFieldDateGroup)_grouping);
|
|
// ExcelPivotTableFieldItem minItem=null, maxItem=null;
|
// foreach (var item in _items)
|
// {
|
// if (item.X == 0)
|
// {
|
// minItem = item;
|
// }
|
// else if (maxItem == null || maxItem.X < item.X)
|
// {
|
// maxItem = item;
|
// }
|
// }
|
// if (dtgrp.AutoStart)
|
// {
|
// _items._list.Remove(minItem);
|
// }
|
// if (dtgrp.AutoEnd)
|
// {
|
// _items._list.Remove(maxItem);
|
// }
|
|
//}
|
}
|
return _items;
|
}
|
}
|
/// <summary>
|
/// Add numberic grouping to the field
|
/// </summary>
|
/// <param name="Start">Start value</param>
|
/// <param name="End">End value</param>
|
/// <param name="Interval">Interval</param>
|
public void AddNumericGrouping(double Start, double End, double Interval)
|
{
|
ValidateGrouping();
|
SetNumericGroup(Start, End, Interval);
|
}
|
/// <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);
|
}
|
/// <summary>
|
/// Add a date grouping on this field.
|
/// </summary>
|
/// <param name="days">Number of days when grouping on days</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(int days, DateTime startDate, DateTime endDate)
|
{
|
AddDateGrouping(eDateGroupBy.Days, startDate, endDate, days);
|
}
|
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;
|
}
|
|
private void ValidateGrouping()
|
{
|
if (!(IsColumnField || IsRowField))
|
{
|
throw (new Exception("Field must be a row or column field"));
|
}
|
foreach (var field in _table.Fields)
|
{
|
if (field.Grouping != null)
|
{
|
throw (new Exception("Grouping already exists"));
|
}
|
}
|
}
|
private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField)
|
{
|
return AddField(groupBy, startDate, endDate, ref firstField,1);
|
}
|
private ExcelPivotTableField AddField(eDateGroupBy groupBy, DateTime startDate, DateTime endDate, ref bool firstField, int interval)
|
{
|
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, interval);
|
return field;
|
}
|
else
|
{
|
firstField = false;
|
DateGrouping = groupBy;
|
Compact = false;
|
SetDateGroup(groupBy, startDate, endDate, interval);
|
return this;
|
}
|
}
|
private void AddCacheField(ExcelPivotTableField field, DateTime startDate, DateTime endDate, int interval)
|
{
|
//Add Cache definition field.
|
var cacheTopNode = _table.CacheDefinition.CacheDefinitionXml.SelectSingleNode("//d:cacheFields", _table.NameSpaceManager);
|
var cacheFieldNode = _table.CacheDefinition.CacheDefinitionXml.CreateElement("cacheField", ExcelPackage.schemaMain);
|
|
cacheFieldNode.SetAttribute("name", field.DateGrouping.ToString());
|
cacheFieldNode.SetAttribute("databaseField", "0");
|
cacheTopNode.AppendChild(cacheFieldNode);
|
field.SetCacheFieldNode(cacheFieldNode);
|
|
field.SetDateGroup(field.DateGrouping, startDate, endDate, interval);
|
}
|
}
|
}
|
