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