/**
* Set conditional formats for specific rows contents.
*/
private void SetSheetSpecialConditonalFormatting(XSSFSheet sh)
{
int rowCount = sh.LastRowNum + 1;
string frechCondition = appSettings["condition-french"];
string dontVisitCondition = appSettings["condition-dont-visit"];
string conditionalRangeStartCell = appSettings["condition-data-range-start"];
string lastDataColumnsCell = appSettings["excel-last-data-column"];
Console.WriteLine("Creating conditional formatting filter...");
XSSFSheetConditionalFormatting sCF = (XSSFSheetConditionalFormatting)sh.SheetConditionalFormatting;
//Fill french speaking address
XSSFConditionalFormattingRule cfFrench =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(frechCondition);
XSSFPatternFormatting fillFrench = (XSSFPatternFormatting)cfFrench.CreatePatternFormatting();
fillFrench.FillBackgroundColor = IndexedColors.LightGreen.Index;
fillFrench.FillPattern = FillPattern.SolidForeground;
//Fill Not interested address
XSSFConditionalFormattingRule cfNotInterested =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(dontVisitCondition);
XSSFPatternFormatting fillNotInterested = (XSSFPatternFormatting)cfNotInterested.CreatePatternFormatting();
fillNotInterested.FillBackgroundColor = IndexedColors.Red.Index;
fillNotInterested.FillPattern = FillPattern.SolidForeground;
//Fill address to verify
XSSFConditionalFormattingRule cfToVerify =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule("true");
XSSFPatternFormatting fillVerify = (XSSFPatternFormatting)cfToVerify.CreatePatternFormatting();
fillVerify.FillBackgroundColor = IndexedColors.White.Index;
fillVerify.FillPattern = FillPattern.SolidForeground;
// The first forat setting wins if multiple can be applyed!!!
XSSFConditionalFormattingRule[] cfRules = { cfNotInterested, cfFrench, cfToVerify };
Console.WriteLine("Setting conditional formatting filter...");
// Setting the conditional cell range: e.g "A2:U1082"
CellRangeAddress[] cfRange = { CellRangeAddress.ValueOf(conditionalRangeStartCell + ":" + lastDataColumnsCell + rowCount) };
sCF.AddConditionalFormatting(cfRange, cfRules);
}
/// <summary>
/// Defines the conditional formatting for the issue sheet.
/// </summary>
/// <param name="sheet">the issue sheet.</param>
private void DefineColourFormattingIssueSheet(ISheet sheet)
{
// Define formatting.
XSSFSheetConditionalFormatting sCF = (XSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;
// Fill Red if High
XSSFConditionalFormattingRule cfRed =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "\"High\"");
XSSFPatternFormatting fillRed = (XSSFPatternFormatting)cfRed.CreatePatternFormatting();
fillRed.FillBackgroundColor = IndexedColors.Red.Index;
fillRed.FillPattern = FillPattern.SolidForeground;
// Fill Orange if Medium
XSSFConditionalFormattingRule cfOrange =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "\"Medium\"");
XSSFPatternFormatting fillOrange = (XSSFPatternFormatting)cfOrange.CreatePatternFormatting();
fillOrange.FillBackgroundColor = IndexedColors.Gold.Index;
fillOrange.FillPattern = FillPattern.SolidForeground;
// Fill yellow if low
XSSFConditionalFormattingRule cfYellow =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "\"Low\"");
XSSFPatternFormatting fillYellow = (XSSFPatternFormatting)cfYellow.CreatePatternFormatting();
fillYellow.FillBackgroundColor = IndexedColors.LightYellow.Index;
fillYellow.FillPattern = FillPattern.SolidForeground;
// this is in the row for impact
CellRangeAddress[] cfRange =
{
CellRangeAddress.ValueOf($"F4:F{4 + this.IssueList.Count}"),
};
sCF.AddConditionalFormatting(cfRange, new XSSFConditionalFormattingRule[] { cfRed, cfOrange, cfYellow });
}
/// <summary>
/// Defines the conditional formatting for the checklist sheet.
/// </summary>
/// <param name="sheet">the checklist sheet.</param>
private void DefineColourFormattingChecklistSheet(ISheet sheet)
{
// Define formatting.
XSSFSheetConditionalFormatting sCF = (XSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;
// Fill Green if Passing Score
XSSFConditionalFormattingRule cfGreen =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, $"\"{ResourceHelper.GetString("CriteriaPass")}\"");
XSSFPatternFormatting fillGreen = (XSSFPatternFormatting)cfGreen.CreatePatternFormatting();
fillGreen.FillBackgroundColor = IndexedColors.LightGreen.Index;
fillGreen.FillPattern = FillPattern.SolidForeground;
// Fill Red if Failing Score
XSSFConditionalFormattingRule cfRed =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, $"\"{ResourceHelper.GetString("CriteriaFail")}\"");
XSSFPatternFormatting fillRed = (XSSFPatternFormatting)cfRed.CreatePatternFormatting();
fillRed.FillBackgroundColor = IndexedColors.Rose.Index;
fillRed.FillPattern = FillPattern.SolidForeground;
// Fill yellow if blank Score
XSSFConditionalFormattingRule cfYellow =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "\"\"");
XSSFPatternFormatting fillYellow = (XSSFPatternFormatting)cfYellow.CreatePatternFormatting();
fillYellow.FillBackgroundColor = IndexedColors.LightYellow.Index;
fillYellow.FillPattern = FillPattern.SolidForeground;
// Fill yellow if not applicable too
XSSFConditionalFormattingRule cfYellow2 =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, $"\"{ResourceHelper.GetString("CriteriaNA")}\"");
XSSFPatternFormatting fillYellow2 = (XSSFPatternFormatting)cfYellow2.CreatePatternFormatting();
fillYellow2.FillBackgroundColor = IndexedColors.LightYellow.Index;
fillYellow2.FillPattern = FillPattern.SolidForeground;
// Fill blue if need manual testing
XSSFConditionalFormattingRule cfBlue =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, $"\"{ResourceHelper.GetString("CriteriaNeedManual")}\"");
XSSFPatternFormatting fillBlue = (XSSFPatternFormatting)cfBlue.CreatePatternFormatting();
fillBlue.FillBackgroundColor = IndexedColors.Aqua.Index;
fillBlue.FillPattern = FillPattern.SolidForeground;
// this is in the 'Meets Criteria' Column
CellRangeAddress[] cfRange =
{
CellRangeAddress.ValueOf("D13:D26"), CellRangeAddress.ValueOf("D29:D40"),
CellRangeAddress.ValueOf("D43:D52"), CellRangeAddress.ValueOf("D55:D56"),
};
// You can't add 4 at once
sCF.AddConditionalFormatting(cfRange, new XSSFConditionalFormattingRule[] { cfRed, cfGreen, cfYellow2, });
sCF.AddConditionalFormatting(cfRange, new XSSFConditionalFormattingRule[] { cfYellow, cfBlue });
// fill in the success criteria score
// Fill Green if Passing Score
cfGreen =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.LessThanOrEqual, "0");
fillGreen = (XSSFPatternFormatting)cfGreen.CreatePatternFormatting();
fillGreen.FillBackgroundColor = IndexedColors.LightGreen.Index;
fillGreen.FillPattern = FillPattern.SolidForeground;
// Fill Red if Failing Score
cfRed =
(XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "0");
fillRed = (XSSFPatternFormatting)cfRed.CreatePatternFormatting();
fillRed.FillBackgroundColor = IndexedColors.Rose.Index;
fillRed.FillPattern = FillPattern.SolidForeground;
sCF.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf("D63") },
new XSSFConditionalFormattingRule[] { cfRed, cfGreen, cfYellow });
}
