/// <summary>
        /// Get the "top10" rule type according to the follwoing attributes:
        /// "Bottom" and "Percent"
        ///
        /// @Bottom = "1" and @Percent = "0"/null       == Bottom
        /// @Bottom = "1" and @Percent = "1"            == BottomPercent
        /// @Bottom = "0"/null and @Percent = "0"/null  == Top
        /// @Bottom = "0"/null and @Percent = "1"       == TopPercent
        /// /// </summary>
        /// <returns>Top, TopPercent, Bottom or BottomPercent</returns>
        public static eExcelConditionalFormattingRuleType GetTop10Type(
            XmlNode topNode,
            XmlNamespaceManager nameSpaceManager)
        {
            // Get @Bottom attribute
            bool?isBottom = ExcelConditionalFormattingHelper.GetAttributeBoolNullable(
                topNode,
                ExcelConditionalFormattingConstants.Attributes.Bottom);

            // Get @Percent attribute
            bool?isPercent = ExcelConditionalFormattingHelper.GetAttributeBoolNullable(
                topNode,
                ExcelConditionalFormattingConstants.Attributes.Percent);

            if (isBottom == true)
            {
                if (isPercent == true)
                {
                    // @Bottom = "1" and @Percent = "1" == BottomPercent
                    return(eExcelConditionalFormattingRuleType.BottomPercent);
                }

                // @Bottom = "1" and @Percent = "0"/null == Bottom
                return(eExcelConditionalFormattingRuleType.Bottom);
            }

            if (isPercent == true)
            {
                // @Bottom = "0"/null and @Percent = "1" == TopPercent
                return(eExcelConditionalFormattingRuleType.TopPercent);
            }

            // @Bottom = "0"/null and @Percent = "0"/null == Top
            return(eExcelConditionalFormattingRuleType.Top);
        }
        /// <summary>
        /// Get the "aboveAverage" rule type according to the follwoing attributes:
        /// "AboveAverage", "EqualAverage" and "StdDev".
        ///
        /// @StdDev greater than "0"                              == AboveStdDev
        /// @StdDev less than "0"                                 == BelowStdDev
        /// @AboveAverage = "1"/null and @EqualAverage = "0"/null == AboveAverage
        /// @AboveAverage = "1"/null and @EqualAverage = "1"      == AboveOrEqualAverage
        /// @AboveAverage = "0" and @EqualAverage = "0"/null      == BelowAverage
        /// @AboveAverage = "0" and @EqualAverage = "1"           == BelowOrEqualAverage
        /// /// </summary>
        /// <returns>AboveAverage, AboveOrEqualAverage, BelowAverage or BelowOrEqualAverage</returns>
        internal static eExcelConditionalFormattingRuleType GetAboveAverageType(
            XmlNode topNode,
            XmlNamespaceManager nameSpaceManager)
        {
            // Get @StdDev attribute
            int?stdDev = ExcelConditionalFormattingHelper.GetAttributeIntNullable(
                topNode,
                ExcelConditionalFormattingConstants.Attributes.StdDev);

            if (stdDev > 0)
            {
                // @StdDev > "0" --> AboveStdDev
                return(eExcelConditionalFormattingRuleType.AboveStdDev);
            }

            if (stdDev < 0)
            {
                // @StdDev < "0" --> BelowStdDev
                return(eExcelConditionalFormattingRuleType.BelowStdDev);
            }

            // Get @AboveAverage attribute
            bool?isAboveAverage = ExcelConditionalFormattingHelper.GetAttributeBoolNullable(
                topNode,
                ExcelConditionalFormattingConstants.Attributes.AboveAverage);

            // Get @EqualAverage attribute
            bool?isEqualAverage = ExcelConditionalFormattingHelper.GetAttributeBoolNullable(
                topNode,
                ExcelConditionalFormattingConstants.Attributes.EqualAverage);

            if ((isAboveAverage == null) || (isAboveAverage == true))
            {
                if (isEqualAverage == true)
                {
                    // @AboveAverage = "1"/null and @EqualAverage = "1" == AboveOrEqualAverage
                    return(eExcelConditionalFormattingRuleType.AboveOrEqualAverage);
                }

                // @AboveAverage = "1"/null and @EqualAverage = "0"/null == AboveAverage
                return(eExcelConditionalFormattingRuleType.AboveAverage);
            }

            if (isEqualAverage == true)
            {
                // @AboveAverage = "0" and @EqualAverage = "1" == BelowOrEqualAverage
                return(eExcelConditionalFormattingRuleType.BelowOrEqualAverage);
            }

            // @AboveAverage = "0" and @EqualAverage = "0"/null == BelowAverage
            return(eExcelConditionalFormattingRuleType.BelowAverage);
        }
        /// <summary>
        /// Get the "timePeriod" rule type according to "TimePeriod" attribute.
        /// /// </summary>
        /// <returns>Last7Days, LastMonth etc.</returns>
        public static eExcelConditionalFormattingRuleType GetTimePeriodType(
            XmlNode topNode,
            XmlNamespaceManager nameSpaceManager)
        {
            eExcelConditionalFormattingTimePeriodType timePeriod = ExcelConditionalFormattingTimePeriodType.GetTypeByAttribute(
                ExcelConditionalFormattingHelper.GetAttributeString(
                    topNode,
                    ExcelConditionalFormattingConstants.Attributes.TimePeriod));

            switch (timePeriod)
            {
            case eExcelConditionalFormattingTimePeriodType.Last7Days:
                return(eExcelConditionalFormattingRuleType.Last7Days);

            case eExcelConditionalFormattingTimePeriodType.LastMonth:
                return(eExcelConditionalFormattingRuleType.LastMonth);

            case eExcelConditionalFormattingTimePeriodType.LastWeek:
                return(eExcelConditionalFormattingRuleType.LastWeek);

            case eExcelConditionalFormattingTimePeriodType.NextMonth:
                return(eExcelConditionalFormattingRuleType.NextMonth);

            case eExcelConditionalFormattingTimePeriodType.NextWeek:
                return(eExcelConditionalFormattingRuleType.NextWeek);

            case eExcelConditionalFormattingTimePeriodType.ThisMonth:
                return(eExcelConditionalFormattingRuleType.ThisMonth);

            case eExcelConditionalFormattingTimePeriodType.ThisWeek:
                return(eExcelConditionalFormattingRuleType.ThisWeek);

            case eExcelConditionalFormattingTimePeriodType.Today:
                return(eExcelConditionalFormattingRuleType.Today);

            case eExcelConditionalFormattingTimePeriodType.Tomorrow:
                return(eExcelConditionalFormattingRuleType.Tomorrow);

            case eExcelConditionalFormattingTimePeriodType.Yesterday:
                return(eExcelConditionalFormattingRuleType.Yesterday);
            }

            throw new Exception(
                      ExcelConditionalFormattingConstants.Errors.UnexistentTimePeriodTypeAttribute);
        }
Esempio n. 4
0
        /****************************************************************************************/

        #region Constructors
        /// <summary>
        /// Initialize the <see cref="ExcelConditionalFormattingCollection"/>
        /// </summary>
        /// <param name="worksheet"></param>
        internal ExcelConditionalFormattingCollection(
            ExcelWorksheet worksheet)
            : base(
                worksheet.NameSpaceManager,
                worksheet.WorksheetXml.DocumentElement)
        {
            Require.Argument(worksheet).IsNotNull("worksheet");

            _worksheet      = worksheet;
            SchemaNodeOrder = _worksheet.SchemaNodeOrder;

            // Look for all the <conditionalFormatting>
            var conditionalFormattingNodes = TopNode.SelectNodes(
                "//" + ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
                _worksheet.NameSpaceManager);

            // Check if we found at least 1 node
            if ((conditionalFormattingNodes != null) &&
                (conditionalFormattingNodes.Count > 0))
            {
                // Foreach <conditionalFormatting>
                foreach (XmlNode conditionalFormattingNode in conditionalFormattingNodes)
                {
                    // Check if @sqref attribute exists
                    if (conditionalFormattingNode.Attributes[ExcelConditionalFormattingConstants.Attributes.Sqref] == null)
                    {
                        throw new Exception(
                                  ExcelConditionalFormattingConstants.Errors.MissingSqrefAttribute);
                    }

                    // Get the @sqref attribute
                    ExcelAddress address = new ExcelAddress(
                        conditionalFormattingNode.Attributes[ExcelConditionalFormattingConstants.Attributes.Sqref].Value);

                    // Check for all the <cfRules> nodes and load them
                    var cfRuleNodes = conditionalFormattingNode.SelectNodes(
                        ExcelConditionalFormattingConstants.Paths.CfRule,
                        _worksheet.NameSpaceManager);

                    // Foreach <cfRule> inside the current <conditionalFormatting>
                    foreach (XmlNode cfRuleNode in cfRuleNodes)
                    {
                        // Check if @type attribute exists
                        if (cfRuleNode.Attributes[ExcelConditionalFormattingConstants.Attributes.Type] == null)
                        {
                            throw new Exception(
                                      ExcelConditionalFormattingConstants.Errors.MissingTypeAttribute);
                        }

                        // Check if @priority attribute exists
                        if (cfRuleNode.Attributes[ExcelConditionalFormattingConstants.Attributes.Priority] == null)
                        {
                            throw new Exception(
                                      ExcelConditionalFormattingConstants.Errors.MissingPriorityAttribute);
                        }

                        // Get the <cfRule> main attributes
                        string typeAttribute = ExcelConditionalFormattingHelper.GetAttributeString(
                            cfRuleNode,
                            ExcelConditionalFormattingConstants.Attributes.Type);

                        int priority = ExcelConditionalFormattingHelper.GetAttributeInt(
                            cfRuleNode,
                            ExcelConditionalFormattingConstants.Attributes.Priority);

                        // Transform the @type attribute to EPPlus Rule Type (slighty diferente)
                        var type = ExcelConditionalFormattingRuleType.GetTypeByAttrbiute(
                            typeAttribute,
                            cfRuleNode,
                            _worksheet.NameSpaceManager);

                        // Create the Rule according to the correct type, address and priority
                        var cfRule = ExcelConditionalFormattingRuleFactory.Create(
                            type,
                            address,
                            priority,
                            _worksheet,
                            cfRuleNode);

                        // Add the new rule to the list
                        if (cfRule != null)
                        {
                            _rules.Add(cfRule);
                        }
                    }
                }
            }
        }