/// <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); }
/****************************************************************************************/ #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); } } } } }