| /******************************************************************************* |
| * 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.Globalization; |
| using System.Xml; |
| |
| 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"); |
| } |
| return v; |
| } |
| set => SetXmlNodeString("@name", value); |
| } |
| |
| /// <summary> |
| /// Compact mode |
| /// </summary> |
| public bool Compact { |
| get => 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 => GetXmlNodeBool("@outline"); |
| set => SetXmlNodeBool("@outline", value); |
| } |
| |
| /// <summary> |
| /// The custom text that is displayed for the subtotals label |
| /// </summary> |
| public bool SubtotalTop { |
| get => GetXmlNodeBool("@subtotalTop"); |
| set => SetXmlNodeBool("@subtotalTop", value); |
| } |
| |
| /// <summary> |
| /// A boolean that indicates whether to show all items for this field |
| /// </summary> |
| public bool ShowAll { |
| get => 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 => 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 => |
| (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 => |
| (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 => GetXmlNodeBool("@dataField", false); |
| |
| /// <summary> |
| /// If the field is a page field. |
| /// </summary> |
| public bool IsPageField { |
| get => (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(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; |
| |
| public ExcelPivotTablePageFieldSettings PageFieldSettings => _pageFieldSettings; |
| |
| internal eDateGroupBy DateGrouping { get; set; } |
| |
| private ExcelPivotTableFieldGroup _grouping; |
| |
| /// <summary> |
| /// Grouping settings. |
| /// Null if the field has no grouping otherwise ExcelPivotTableFieldNumericGroup or ExcelPivotTableFieldNumericGroup. |
| /// </summary> |
| public ExcelPivotTableFieldGroup Grouping => _grouping; |
| |
| 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; |
| |
| internal void SetCacheFieldNode(XmlNode cacheField) { |
| _cacheFieldHelper = new(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(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(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("The interval must be a positiv")); |
| } |
| if (start > end) { |
| throw (new("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("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); |
| } |
| |
| internal ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> _items; |
| |
| /// <summary> |
| /// Pivottable field Items. Used for grouping. |
| /// </summary> |
| public ExcelPivotTableFieldCollectionBase<ExcelPivotTableFieldItem> Items { |
| get { |
| if (_items == null) { |
| _items = new(_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("Field must be a row or column field")); |
| } |
| foreach (var field in _table.Fields) { |
| if (field.Grouping != null) { |
| throw (new("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; |
| } |
| 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); |
| } |
| } |
