public static void ApplyAsiaCountriesConditionalFormatting(Worksheet sheet)
{
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
FormulaExpressionConditionalFormatting cfRule = conditionalFormattings.AddFormulaExpressionConditionalFormatting(sheet["Table[Country]"], "=$B6=\"Asia\"");
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xBC, 0xDA, 0xF7);
}
static void AddFormulaExpressionConditionalFormatting(IWorkbook workbook)
{
#region #FormulaExpressionConditionalFormatting
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
FormulaExpressionConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddFormulaExpressionConditionalFormatting(worksheet.Range["$B$5:$H$18"], "=MOD(ROW(),2)=1");
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xBC, 0xDA, 0xF7);
worksheet["B2"].Value = "Shade alternate rows in light blue without applying a new style.";
worksheet.Visible = true;
#endregion #FormulaExpressionConditionalFormatting
}
public void ApplyNotExistsArticleFormatingforImportLog(Worksheet sheet, Range LogRange, Worksheet LogSheet)
{
try
{
string stRange = LogRange.GetReferenceA1();
ConditionalFormattingCollection conditionalFormattings = sheet.ConditionalFormattings;
FormulaExpressionConditionalFormatting cfRule =
conditionalFormattings.AddFormulaExpressionConditionalFormatting(sheet.GetDataRange(), "=$A1=VLOOKUP($A1,'Import des Logs'!$A:$A,1,0)");
cfRule.Formatting.Fill.BackgroundColor = Color.Red;
}
catch (Exception ex) { }
}
static void AddFormulaExpressionConditionalFormatting(IWorkbook workbook)
{
workbook.Calculate();
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets["cfBooks"];
workbook.Worksheets.ActiveWorksheet = worksheet;
#region #FormulaExpressionConditionalFormatting
// Create the rule to shade alternate rows without applying a new style.
FormulaExpressionConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddFormulaExpressionConditionalFormatting(worksheet.Range["$A$2:$G$15"], "=MOD(ROW(),2)=1");
// Specify formatting options to be applied to cells if the condition is true.
// Set the background color to light blue.
cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xBC, 0xDA, 0xF7);
#endregion #FormulaExpressionConditionalFormatting
// Add an explanation to the created rule.
CellRange ruleExplanation = worksheet.Range["A17:G18"];
ruleExplanation.Value = "Shade alternate rows in light blue without applying a new style.";
}
finally
{
workbook.EndUpdate();
}
}
