/****************************************************************************************/

    #region Constructors
    /// <summary>
    /// Initialize the <see cref="ExcelConditionalFormattingRule"/>
    /// </summary>
    /// <param name="type"></param>
    /// <param name="address"></param>
    /// <param name="priority">Used also as the cfRule unique key</param>
    /// <param name="worksheet"></param>
    /// <param name="itemElementNode"></param>
    /// <param name="namespaceManager"></param>
    internal ExcelConditionalFormattingRule(
      eExcelConditionalFormattingRuleType type,
      ExcelAddress address,
      int priority,
      ExcelWorksheet worksheet,
      XmlNode itemElementNode,
      XmlNamespaceManager namespaceManager)
      : base(
        namespaceManager,
        itemElementNode)
    {
      Require.Argument(address).IsNotNull("address");

  	  // While MSDN states that 1 is the "highest priority," it also defines this
	  // field as W3C XML Schema int, which would allow values less than 1. Excel
	  // itself will, on occasion, use a value of 0, so this check will allow a 0.
      Require.Argument(priority).IsInRange(0, int.MaxValue, "priority");

      Require.Argument(worksheet).IsNotNull("worksheet");

      _type = type;
      _worksheet = worksheet;
      SchemaNodeOrder = _worksheet.SchemaNodeOrder;

      if (itemElementNode == null)
      {
        // Create/Get the <cfRule> inside <conditionalFormatting>
        itemElementNode = CreateComplexNode(
          _worksheet.WorksheetXml.DocumentElement,
          string.Format(
            "{0}[{1}='{2}']/{1}='{2}'/{3}[{4}='{5}']/{4}='{5}'",
          //{0}
            ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
          // {1}
            ExcelConditionalFormattingConstants.Paths.SqrefAttribute,
          // {2}
            address.AddressSpaceSeparated,          //CF node don't what to have comma between multi addresses, use space instead.
          // {3}
            ExcelConditionalFormattingConstants.Paths.CfRule,
          //{4}
            ExcelConditionalFormattingConstants.Paths.PriorityAttribute,
          //{5}
            priority));
      }

      // Point to <cfRule>
      TopNode = itemElementNode;

      Address = address;
      Priority = priority;
      Type = type;
      if (DxfId >= 0)
      {
          worksheet.Workbook.Styles.Dxfs[DxfId].AllowChange = true;  //This Id is referenced by CF, so we can use it when we save.
          _style = worksheet.Workbook.Styles.Dxfs[DxfId].Clone();    //Clone, so it can be altered without effecting other dxf styles
      }
    }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="address"></param>
        /// <param name="priority"></param>
        /// <param name="worksheet"></param>
        /// <param name="itemElementNode"></param>
        /// <param name="namespaceManager"></param>
        internal ExcelConditionalFormattingThreeColorScale(
            ExcelAddress address,
            int priority,
            ExcelWorksheet worksheet,
            XmlNode itemElementNode,
            XmlNamespaceManager namespaceManager)
            : base(eExcelConditionalFormattingRuleType.ThreeColorScale,
        address,
        priority,
        worksheet,
        itemElementNode,
        (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
        {
            if (itemElementNode == null)
            {
                // Create the <colorScale> node inside the <cfRule> node
                var colorScaleNode = CreateComplexNode(
                  Node,
                  ExcelConditionalFormattingConstants.Paths.ColorScale);

                // LowValue default
                LowValue = new ExcelConditionalFormattingColorScaleValue(
                  eExcelConditionalFormattingValueObjectPosition.Low,
                  eExcelConditionalFormattingValueObjectType.Min,
                  ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoLowValue),
                  eExcelConditionalFormattingRuleType.ThreeColorScale,
                  address,
                  priority,
                  worksheet,
                  NameSpaceManager);

                // MiddleValue default
                MiddleValue = new ExcelConditionalFormattingColorScaleValue(
                  eExcelConditionalFormattingValueObjectPosition.Middle,
                  eExcelConditionalFormattingValueObjectType.Percent,
                  ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoMiddleValue),
                  50,
                  string.Empty,
                  eExcelConditionalFormattingRuleType.ThreeColorScale,
                  address,
                  priority,
                  worksheet,
                  NameSpaceManager);

                // HighValue default
                HighValue = new ExcelConditionalFormattingColorScaleValue(
                  eExcelConditionalFormattingValueObjectPosition.High,
                  eExcelConditionalFormattingValueObjectType.Max,
                  ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoHighValue),
                  eExcelConditionalFormattingRuleType.ThreeColorScale,
                  address,
                  priority,
                  worksheet,
                  NameSpaceManager);
            }
        }

    public RangeConditionalFormatting(
      ExcelWorksheet worksheet,
      ExcelAddress address)
    {
      Require.Argument(worksheet).IsNotNull("worksheet");
      Require.Argument(address).IsNotNull("address");

      _worksheet = worksheet;
      _address = address;
    }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 internal ExcelConditionalFormattingUniqueValues(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode)
     : this(address,
 priority,
 worksheet,
 itemElementNode,
 null)
 {
 }

        /// <summary>
        /// Initialize the <see cref="ExcelConditionalFormattingRule"/>
        /// </summary>
        /// <param name="type"></param>
        /// <param name="address"></param>
        /// <param name="priority">Used also as the cfRule unique key</param>
        /// <param name="worksheet"></param>
        /// <param name="itemElementNode"></param>
        /// <param name="namespaceManager"></param>
        internal ExcelConditionalFormattingRule(
            eExcelConditionalFormattingRuleType type,
            ExcelAddress address,
            int priority,
            ExcelWorksheet worksheet,
            XmlNode itemElementNode,
            XmlNamespaceManager namespaceManager)
            : base(namespaceManager,
        itemElementNode)
        {
            Require.Argument(address).IsNotNull("address");
              Require.Argument(priority).IsInRange(1, int.MaxValue, "priority");
              Require.Argument(worksheet).IsNotNull("worksheet");

              _type = type;
              _worksheet = worksheet;
              SchemaNodeOrder = _worksheet.SchemaNodeOrder;

              if (itemElementNode == null)
              {
            // Create/Get the <cfRule> inside <conditionalFormatting>
            itemElementNode = CreateComplexNode(
              _worksheet.WorksheetXml.DocumentElement,
              string.Format(
            "{0}[{1}='{2}']/{1}='{2}'/{3}[{4}='{5}']/{4}='{5}'",
              //{0}
            ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
              // {1}
            ExcelConditionalFormattingConstants.Paths.SqrefAttribute,
              // {2}
            address.Address,
              // {3}
            ExcelConditionalFormattingConstants.Paths.CfRule,
              //{4}
            ExcelConditionalFormattingConstants.Paths.PriorityAttribute,
              //{5}
            priority));
              }

              // Point to <cfRule>
              TopNode = itemElementNode;

              Address = address;
              Priority = priority;
              Type = type;
              if (DxfId >= 0)
              {
              worksheet.Workbook.Styles.Dxfs[DxfId].AllowChange = true;  //This Id is referenced by CF, so we can use it when we save.
              _style = worksheet.Workbook.Styles.Dxfs[DxfId].Clone();    //Clone, so it can be altered without effecting other dxf styles
              }
        }

        public LookupArguments(IEnumerable<FunctionArgument> arguments, ArgumentParsers argumentParsers, ParsingContext context)
        {
            _argumentParsers = argumentParsers;
            SearchedValue = arguments.ElementAt(0).Value;
            var arg1 = arguments.ElementAt(1).Value;
            var dataArray = arg1 as IEnumerable<FunctionArgument>;
            if (dataArray != null)
            {
                DataArray = dataArray;
                ArgumentDataType = LookupArgumentDataType.DataArray;
            }
            else
            {
                //if (arg1 is ExcelDataProvider.INameInfo) arg1 = ((ExcelDataProvider.INameInfo) arg1).Value;
                var rangeInfo = arg1 as ExcelDataProvider.IRangeInfo;
                if (rangeInfo != null)
                {
                    RangeAddress = string.IsNullOrEmpty(rangeInfo.Address.WorkSheet) ? rangeInfo.Address.Address : "'" + rangeInfo.Address.WorkSheet + "'!" + rangeInfo.Address.Address;
                    RangeInfo = rangeInfo;
                    ArgumentDataType = LookupArgumentDataType.ExcelRange;
                }
                else
                {
                    RangeAddress = arg1.ToString();
                    ArgumentDataType = LookupArgumentDataType.ExcelRange;
                }
            }
            var indexVal = arguments.ElementAt(2);

            if (indexVal.DataType == DataType.ExcelAddress)
            {
                var address = new ExcelAddress(indexVal.Value.ToString());
                var indexObj = context.ExcelDataProvider.GetRangeValue(address.WorkSheet, address._fromRow, address._fromCol);
                LookupIndex = (int) _argumentParsers.GetParser(DataType.Integer).Parse(indexObj);
            }
            else
            {
                LookupIndex = (int)_argumentParsers.GetParser(DataType.Integer).Parse(arguments.ElementAt(2).Value);
            }

            if (arguments.Count() > 3)
            {
                RangeLookup = (bool)_argumentParsers.GetParser(DataType.Boolean).Parse(arguments.ElementAt(3).Value);
            }
            else
            {
                RangeLookup = true;
            }
        }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="type"></param>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingExpression(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.Expression,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     Formula = string.Empty;
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="type"></param>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingTimePeriodGroup(
     eExcelConditionalFormattingRuleType type,
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(type,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingAboveAverage(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.AboveAverage,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     AboveAverage = true;
       EqualAverage = false;
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingBelowStdDev(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.BelowStdDev,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     AboveAverage = false;
       StdDev = 1;
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="type"></param>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingNotEqual(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.NotEqual,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     Operator = eExcelConditionalFormattingOperatorType.NotEqual;
       Formula = string.Empty;
 }

        public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
        {
            var functionArguments = arguments as FunctionArgument[] ?? arguments.ToArray();
            ValidateArguments(functionArguments, 3);
            var startRange = ArgToString(functionArguments, 0);
            var rowOffset = ArgToInt(functionArguments, 1);
            var colOffset = ArgToInt(functionArguments, 2);
            int width = 0, height = 0;
            if (functionArguments.Length > 3)
            {
                height = ArgToInt(functionArguments, 3);
                ThrowExcelErrorValueExceptionIf(() => height == 0, eErrorType.Ref);
            }
            if (functionArguments.Length > 4)
            {
                width = ArgToInt(functionArguments, 4);
                ThrowExcelErrorValueExceptionIf(() => width == 0, eErrorType.Ref);
            }

            var adr = new ExcelAddress(startRange);
            var ws = adr.WorkSheet;

            var fromRow = adr._fromRow + rowOffset;
            var fromCol = adr._fromCol + colOffset;
            var toRow = (height != 0 ? height : adr._toRow) + rowOffset;
            var toCol = (width != 0 ? width : adr._toCol) + colOffset;
            //var toRow = (height != 0 ? fromRow + height : adr._toRow + rowOffset);
            //var toCol = (width != 0 ? fromCol + width : adr._toCol + colOffset);

            var newRange = context.ExcelDataProvider.GetRange(ws, fromRow, fromCol, toRow, toCol);
            if (!newRange.IsMulti)
            {
                if (newRange.IsEmpty) return CompileResult.Empty;
                var val = newRange.GetValue(fromRow, fromCol);
                if (IsNumeric(val))
                {
                    return CreateResult(val, DataType.Decimal);
                }
                if (val is ExcelErrorValue)
                {
                    return CreateResult(val, DataType.ExcelError);
                }
                return CreateResult(val, DataType.String);
            }
            return CreateResult(newRange, DataType.Enumerable);
        }

 public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
 {
     ValidateArguments(arguments, 1);
     var address = ArgToString(arguments, 0);
     var adr = new ExcelAddress(address);
     var ws = adr.WorkSheet;
     if (string.IsNullOrEmpty(ws))
     {
         ws = context.Scopes.Current.Address.Worksheet;
     }
     var result = context.ExcelDataProvider.GetRange(ws, adr._fromRow, adr._fromCol, address);
     if (result.IsEmpty)
     {
         return CompileResult.Empty;
     }
     return new CompileResult(result, DataType.Enumerable);
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="type"></param>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingNotContainsBlanks(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.NotContainsBlanks,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     Formula = string.Format(
     "LEN(TRIM({0}))>0",
     Address.Start.Address);
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="type"></param>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingBottom(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.Bottom,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     Bottom = true;
       Percent = false;
       Rank = 10;  // Last 10 values
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="type"></param>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingContainsErrors(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.ContainsErrors,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     Formula = string.Format(
     "ISERROR({0})",
     Address.Start.Address);
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingNextMonth(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.NextMonth,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     TimePeriod = eExcelConditionalFormattingTimePeriodType.NextMonth;
       Formula = string.Format(
     "AND(MONTH({0})=MONTH(EDATE(TODAY(),0+1)), YEAR({0})=YEAR(EDATE(TODAY(),0+1)))",
     Address.Start.Address);
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingToday(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.Today,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     TimePeriod = eExcelConditionalFormattingTimePeriodType.Today;
       Formula = string.Format(
     "FLOOR({0},1)=TODAY()",
     Address.Start.Address);
 }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="priority"></param>
 /// <param name="address"></param>
 /// <param name="worksheet"></param>
 /// <param name="itemElementNode"></param>
 /// <param name="namespaceManager"></param>
 internal ExcelConditionalFormattingNextWeek(
     ExcelAddress address,
     int priority,
     ExcelWorksheet worksheet,
     XmlNode itemElementNode,
     XmlNamespaceManager namespaceManager)
     : base(eExcelConditionalFormattingRuleType.NextWeek,
 address,
 priority,
 worksheet,
 itemElementNode,
 (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
 {
     TimePeriod = eExcelConditionalFormattingTimePeriodType.NextWeek;
       Formula = string.Format(
     "AND(ROUNDDOWN({0},0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN({0},0)-TODAY()<(15-WEEKDAY(TODAY())))",
     Address.Start.Address);
 }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="priority"></param>
        /// <param name="address"></param>
        /// <param name="worksheet"></param>
        /// <param name="itemElementNode"></param>
        /// <param name="namespaceManager"></param>
        internal ExcelConditionalFormattingTwoColorScale(
            ExcelAddress address,
            int priority,
            ExcelWorksheet worksheet,
            XmlNode itemElementNode,
            XmlNamespaceManager namespaceManager)
            : base(eExcelConditionalFormattingRuleType.TwoColorScale,
        address,
        priority,
        worksheet,
        itemElementNode,
        (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
        {
            // If the node is not null, parse values out of it instead of clobbering it.
            if (itemElementNode == null)
            {
                // Create the <colorScale> node inside the <cfRule> node
                var colorScaleNode = CreateComplexNode(
                  Node,
                  ExcelConditionalFormattingConstants.Paths.ColorScale);

                // LowValue default
                LowValue = new ExcelConditionalFormattingColorScaleValue(
                  eExcelConditionalFormattingValueObjectPosition.Low,
                  eExcelConditionalFormattingValueObjectType.Min,
                  ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoLowValue),
                  eExcelConditionalFormattingRuleType.TwoColorScale,
                  address,
                  priority,
                  worksheet,
                  NameSpaceManager);

                // HighValue default
                HighValue = new ExcelConditionalFormattingColorScaleValue(
                  eExcelConditionalFormattingValueObjectPosition.High,
                  eExcelConditionalFormattingValueObjectType.Max,
                  ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoHighValue),
                  eExcelConditionalFormattingRuleType.TwoColorScale,
                  address,
                  priority,
                  worksheet,
                  NameSpaceManager);
            }
        }