/// <summary>
/// Sets a cell-value-based conditional formatting.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="rules">The conditional formatting rules to apply.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetCellValueConditionalFormatting(
this Range range,
IReadOnlyList <CellValueConditionalFormattingRule> rules)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (rules != null)
{
var formatConditionsIndex = range.Worksheet.ConditionalFormattings.Add();
var formatConditions = range.Worksheet.ConditionalFormattings[formatConditionsIndex];
var cellArea = range.GetCellArea();
formatConditions.AddArea(cellArea);
foreach (var rule in rules)
{
var operatorType = rule.Operator.ToOperatorType();
var conditionIndex = formatConditions.AddCondition(FormatConditionType.CellValue, operatorType, rule.Operand1Formula, rule.Operand2Formula);
var formatCondition = formatConditions[conditionIndex];
formatCondition.StopIfTrue = rule.StopIfTrue;
// need a way to leverage our Set... methods above to inflate the style.
var backgroundColor = rule.RangeStyle?.BackgroundColor;
if (backgroundColor != null)
{
formatCondition.Style.BackgroundColor = (Color)backgroundColor;
}
}
}
}
/// <summary>
/// Sets a data validation.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="dataValidation">The validation styling to apply.</param>
/// <exception cref="ArgumentNullException"><paramref name="range"/> is null.</exception>
public static void SetDataValidation(
this Range range,
DataValidation dataValidation)
{
if (range == null)
{
throw new ArgumentNullException(nameof(range));
}
if (dataValidation != null)
{
var validations = range.Worksheet.Validations;
var cellArea = range.GetCellArea();
var validation = validations[validations.Add(cellArea)];
validation.Type = dataValidation.Kind.ToValidationType();
validation.Operator = dataValidation.Operator.ToOperatorType();
if (dataValidation.Operand1Value != null)
{
validation.Value1 = dataValidation.Operand1Value;
}
if (dataValidation.Operand2Value != null)
{
validation.Value2 = dataValidation.Operand2Value;
}
if (dataValidation.Operand1Formula != null)
{
validation.Formula1 = dataValidation.Operand1Formula;
}
if (dataValidation.Operand2Formula != null)
{
validation.Formula2 = dataValidation.Operand2Formula;
}
validation.IgnoreBlank = dataValidation.IgnoreBlank;
validation.ShowInput = dataValidation.ShowInputMessage;
validation.InputTitle = dataValidation.InputMessageTitle;
validation.InputMessage = dataValidation.InputMessageBody;
validation.ShowError = dataValidation.ShowErrorAlertAfterInvalidDataIsEntered;
validation.AlertStyle = dataValidation.ErrorAlertStyle.ToValidationAlertType();
validation.ErrorTitle = dataValidation.ErrorAlertTitle;
validation.ErrorMessage = dataValidation.ErrorAlertBody;
validation.InCellDropDown = dataValidation.ShowListDropdown;
}
}
