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