4

I'm using NPOI to programatically create an Excel file. One of the requirements is that it needs to be able to change the background of cells based on values - green for good numbers, red for bad, etc. I have everything working perfectly and can create formulas...but I cannot for the life of me find a formula that shows how to change background color. No matter how I try to google for the answer, everything just wants to show how to open Excel and use the conditional formatting wizard. I'm overlooking something? Is there a way I can see the formula that the conditional formatting wizard created and just copy and paste it into my code?

Below is a sample I set up to change the field to Pass/Fail...but my peeps like shiny colors to go along with it...

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet("ACT");

string cf = "IF(" + engCell + (detailRow.RowNum + 1) + @">17,""Pass :)"", ""Fail :("")";
detailRow.CreateCell(detailIdx);
detailRow.GetCell(detailIdx).SetCellType(CellType.Formula);
detailRow.GetCell(detailIdx++).SetCellFormula(cf);
Jimmy Genslinger
  • 557
  • 3
  • 21

1 Answers1

5

I figured it out!!! I hope this may help others who are using NPOI XSSF for conditional formatting:

XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet("ACT");

sh.CreateRow(0).CreateCell(0).SetCellValue(14);
sh.CreateRow(1).CreateCell(0).SetCellValue(20);
sh.CreateRow(2).CreateCell(0).SetCellValue(10);
sh.CreateRow(3).CreateCell(0).SetCellValue(23);
sh.CreateRow(4).CreateCell(0).SetCellValue(19);
sh.CreateRow(5).CreateCell(0).SetCellValue(14);

XSSFSheetConditionalFormatting sCF = (XSSFSheetConditionalFormatting)sh.SheetConditionalFormatting;

//Fill Green if Passing Score
XSSFConditionalFormattingRule cfGreen = 
    (XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.GreaterThanOrEqual, "19");
XSSFPatternFormatting fillGreen = (XSSFPatternFormatting)cfGreen.CreatePatternFormatting();
fillGreen.FillBackgroundColor = IndexedColors.LightGreen.Index;
fillGreen.FillPattern = FillPattern.SolidForeground;

//Fill Red if Passing Score
XSSFConditionalFormattingRule cfRed =
    (XSSFConditionalFormattingRule)sCF.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "19");
XSSFPatternFormatting fillRed = (XSSFPatternFormatting)cfRed.CreatePatternFormatting();
fillRed.FillBackgroundColor = IndexedColors.Red.Index;
fillRed.FillPattern = FillPattern.SolidForeground;

CellRangeAddress[] cfRange = { CellRangeAddress.ValueOf("A1:A6") };
sCF.AddConditionalFormatting(cfRange, cfGreen, cfRed);
Jimmy Genslinger
  • 557
  • 3
  • 21