| /******************************************************************************* | |
| * 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 OfficeOpenXml.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> | |
| /// Defines the data formats for a field in the PivotTable | |
| /// </summary> | |
| public enum eShowDataAs | |
| { | |
| /// <summary> | |
| /// Indicates the field is shown as the "difference from" a value. | |
| /// </summary> | |
| Difference, | |
| /// <summary> | |
| /// Indicates the field is shown as the "index. | |
| /// </summary> | |
| Index, | |
| /// <summary> | |
| /// Indicates that the field is shown as its normal datatype. | |
| /// </summary> | |
| Normal, | |
| /// <summary> | |
| /// /Indicates the field is show as the "percentage of" a value | |
| /// </summary> | |
| Percent, | |
| /// <summary> | |
| /// Indicates the field is shown as the "percentage difference from" a value. | |
| /// </summary> | |
| PercentDiff, | |
| /// <summary> | |
| /// Indicates the field is shown as the percentage of column. | |
| /// </summary> | |
| PercentOfCol, | |
| /// <summary> | |
| /// Indicates the field is shown as the percentage of row | |
| /// </summary> | |
| PercentOfRow, | |
| /// <summary> | |
| /// Indicates the field is shown as percentage of total. | |
| /// </summary> | |
| PercentOfTotal, | |
| /// <summary> | |
| /// Indicates the field is shown as running total in the table. | |
| /// </summary> | |
| RunTotal, | |
| } | |
| /// <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 v; | |
| } | |
| } | |
| 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(CultureInfo.InvariantCulture)); | |
| } | |
| } | |
| } | |
| /// <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], CultureInfo.InvariantCulture) + 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(CultureInfo.InvariantCulture)); | |
| 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); | |
| } | |
| } | |
| } |