Is it the setFillBackgroundColor command

Question

How do I change the font color to white on just one of the else if lines? The else if on the "INSUFFICIENT RECORDS" line that is commented out is the one that I want to change. For some reason I thought it would be the setFillBackgroundColor command.

CellStyle statusStyle = wb.createCellStyle()
statusStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
statusStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN)
if (it.NOTIFICATIONSENT.equals("SENT")) {
    statusStyle.setFillForegroundColor(new HSSFColor.BRIGHT_GREEN().getIndex())
    } else if (it.NOTIFICATIONSENT.equals("INSUFFICIENT RECORDS")) {
    statusStyle.setFillForegroundColor(new HSSFColor.RED().getIndex())
// statusStyle.setFillBackgroundColor(new HSSFColor.WHITE().getIndex())
} else {
    statusStyle.setFillForegroundColor(new HSSFColor.WHITE().getIndex())
}

You create styles for your workbook, then apply these styles to cells. You seem to be trying to generate styles on the fly as you're writing out data, but AFAIK that's not how it's done with Apache POI — tim_yates, Nov 20 '13 at 21:27

tim_yates · Accepted Answer · 2013-11-21T12:10:28.897

Here's an example of using Styles with Apache POI:

Copied here for posterity:

@Grab( 'org.apache.poi:poi:3.9' )
import static org.apache.poi.ss.usermodel.CellStyle.*
import static org.apache.poi.ss.usermodel.IndexedColors.*
import org.apache.poi.ss.usermodel.IndexedColors
import org.apache.poi.hssf.usermodel.HSSFWorkbook

new HSSFWorkbook().with { workbook ->
  // Create a RED font and a BLACK font
  def fonts = [ 'RED', 'BLACK' ].collectEntries { color ->
      [ color, createFont().with { font ->
                   font.color = IndexedColors."$color".index
                   font
               } ]
  }
  def styles = [ LIGHT_BLUE, LIGHT_GREEN, LIGHT_ORANGE ].collect { color ->
    createCellStyle().with { style ->
      fillForegroundColor = color.index
      fillPattern = SOLID_FOREGROUND

      // Set the font for this style
      if( color == LIGHT_GREEN ) {
          font = fonts.RED
      }
      else {
          font = fonts.BLACK
      }
      style
    }
  }
  createSheet( 'Output' ).with { sheet ->
    (0..4).each { rownum ->
      createRow( rownum ).with { row ->
        (0..4).each { colnum ->
          createCell( colnum ).with { cell ->
            setCellValue( "[$colnum,$rownum]" )
            cellStyle = styles[ ( ( rownum * 5 ) + colnum ) % styles.size() ]
          }
        }
      }
    }
    new File( '/tmp/test.xls' ).withOutputStream { os ->
      write( os )
    }
  }
}

A maybe easier to follow example would be:

@Grab( 'org.apache.poi:poi:3.9' )
import static org.apache.poi.ss.usermodel.CellStyle.*
import static org.apache.poi.ss.usermodel.IndexedColors.*
import org.apache.poi.ss.usermodel.IndexedColors
import org.apache.poi.hssf.usermodel.HSSFWorkbook

new HSSFWorkbook().with { workbook ->

    // Helper closure to create a font
    def fontMaker = { color ->
        createFont().with { font ->
            font.color = color.index
            font
        }
    }

    // Helper closure to create a style
    def styleMaker = { fg, font ->
        createCellStyle().with { style ->
            fillForegroundColor = fg.index
            fillPattern = SOLID_FOREGROUND
            style.font = font
            style
        }
    }

    // Make 2 fonts, one for ERROR, one for OK
    def fonts = [ ERROR : fontMaker( WHITE ),
                  OK    : fontMaker( BLACK ) ]

    // Make 2 styles, one for ERROR cells, one for OK
    def styles = [ ERROR: styleMaker( DARK_RED, fonts.ERROR ),
                   OK   : styleMaker( WHITE, fonts.OK ) ]

    createSheet( 'Output' ).with { sheet ->
        (0..4).each { rownum ->
            createRow( rownum ).with { row ->
                (0..4).each { colnum ->
                    createCell( colnum ).with { cell ->
                        setCellValue( "[$colnum,$rownum]" )

                        // Some logic
                        if( colnum == 2 && rownum == 2 ) {
                            // Mark cell with ERROR style
                            cellStyle = styles.ERROR
                        }
                        else {
                            cellStyle = styles.OK
                        }
                    }
                }
            }
        }
        new File( '/tmp/test.xls' ).withOutputStream { os ->
            write( os )
        }
    }
}

@Zornjac You have to create a Font, and apply it to the Style... See the updated example — tim_yates, Nov 21 '13 at 11:54
@Zornjac updated again with a (maybe) easier to follow example :-) Have fun! — tim_yates, Nov 21 '13 at 12:10

Is it the setFillBackgroundColor command

1 Answers1