EPPlus/Table/PivotTable/ExcelPivotTableField.cs

/*******************************************************************************
 * 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);
      if (int.TryParse(x, out var 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);
      }

      var index = 0;
      foreach (XmlElement rowfield in rowColFields.ChildNodes) {
        if (int.TryParse(rowfield.GetAttribute("x"), out var 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);
  }
}