private void SetFormat(IXLRange numbers, double sla)
{
numbers.AddConditionalFormat().WhenGreaterThan(8 * sla).Font.SetFontColor(XLColor.OrangeRed);
numbers.AddConditionalFormat().WhenGreaterThan(4 * sla).Font.SetFontColor(XLColor.MediumRedViolet);
numbers.AddConditionalFormat().WhenGreaterThan(2 * sla).Font.SetFontColor(XLColor.RoyalBlue);
numbers.AddConditionalFormat().WhenEqualOrLessThan(2 * sla).Font.SetFontColor(XLColor.SeaGreen);
}
protected static void SetTradeConditionalFormat(IXLRange range)
{
if (range == null)
{
return;
}
range.AddConditionalFormat().WhenEquals("BUY").Font.FontColor = XLColor.Green;
range.AddConditionalFormat().WhenEquals("SELL").Font.FontColor = XLColor.Red;
}
protected static void SetProfitConditionalFormat(IXLRange range)
{
if (range is null)
{
return;
}
range.AddConditionalFormat().WhenLessThan(0).Font.FontColor = XLColor.Red;
range.AddConditionalFormat().WhenEqualOrGreaterThan(0).Font.FontColor = XLColor.Green;
}
protected static void SetAccountTallyConditionalFormat(IXLRange range)
{
if (range == null)
{
return;
}
range.AddConditionalFormat().WhenEquals("Yes").Font.FontColor = XLColor.Green;
range.AddConditionalFormat().WhenEquals("No").Font.FontColor = XLColor.Red;
}
protected static void SetTargetMetConditionalFormat(IXLRange range)
{
if (range == null)
{
return;
}
range.AddConditionalFormat().WhenEquals("Yes").Font.FontColor = XLColor.Green;
}
/// <summary>
/// Apply shading to every other data row
/// </summary>
/// <param name="worksheet">The worksheet</param>
/// <param name="startTableDataRow">The table start row</param>
/// <param name="endTableDataRow">The table end row</param>
/// <param name="lastColumn">The last column</param>
private void ApplyShadingEveryOtherRow(ref IXLWorksheet worksheet, int startTableDataRow, int endTableDataRow, int lastColumn)
{
string rangeBegin = XLHelper.GetColumnLetterFromNumber(1) + startTableDataRow;
string rangeEnd = XLHelper.GetColumnLetterFromNumber(lastColumn) + endTableDataRow;
IXLRange range = worksheet.Range(rangeBegin, rangeEnd);
range.AddConditionalFormat().WhenIsTrue("=mod(row(),2)=0").Fill.SetBackgroundColor(XLColor.LightGray);
}
private static void ApplyConditionalFormatting(IXLWorksheet worksheet, IXLRange formattingRange, string equalFormulaFormat, string missingFormulaFormat, string similarFormulaFormat, string notEqualFormulaFormat)
{
var top = worksheet.FirstRowUsed().RowNumber();
formattingRange.AddConditionalFormat().WhenIsTrue(
PrepareFormula(string.Format(CultureInfo.CurrentCulture, equalFormulaFormat, top)))
.Fill.SetBackgroundColor(XLColor.Green);
formattingRange.AddConditionalFormat().WhenIsTrue(
PrepareFormula(string.Format(CultureInfo.CurrentCulture, missingFormulaFormat, top)))
.Fill.SetBackgroundColor(XLColor.Red);
formattingRange.AddConditionalFormat().WhenIsTrue(
PrepareFormula(string.Format(CultureInfo.CurrentCulture, similarFormulaFormat, top)))
.Fill.SetBackgroundColor(XLColor.GreenRyb);
formattingRange.AddConditionalFormat().WhenIsTrue(
PrepareFormula(string.Format(CultureInfo.CurrentCulture, notEqualFormulaFormat, top)))
.Fill.SetBackgroundColor(XLColor.Yellow);
}
