Excel has an inconsistency in its fills. A solid fill defined for a cell needs
a foreground color:
<fill><file:///C:\Users\murphymdev\AppData\Local\Temp\Temp1_test%20conditional%20formatting.zip\xl\styles.xml>
<patternFill
patternType="solid"><file:///C:\Users\murphymdev\AppData\Local\Temp\Temp1_test%20conditional%20formatting.zip\xl\styles.xml>
<fgColor rgb="FFFFFF00"/>
<bgColor indexed="64"/>
</patternFill>
</fill>
<fill><file:///C:\Users\murphymdev\AppData\Local\Temp\Temp1_test%20conditional%20formatting.zip\xl\styles.xml>
<patternFill
patternType="solid"><file:///C:\Users\murphymdev\AppData\Local\Temp\Temp1_test%20conditional%20formatting.zip\xl\styles.xml>
<fgColor indexed="65"/>
<bgColor rgb="FFFFFF00"/>
</patternFill>
</fill>
This first fill shows in Excel as a cell with a yellow background, the second
shows in excel with a white background (neither have borders). In the excel
Format Cells dialog, the first happens by selecting Yellow as the background
color, and leaving Pattern Color and Style alone, the second fill shows No
Color for the background color with Yellow Pattern Color and blank Pattern
Style.
In the Conditional Formatting space, however, the resulting xml and behavior is
reversed for patternType='solid':
<dxf><file:///C:\Users\murphymdev\AppData\Local\Temp\Temp1_test%20conditional%20formatting.zip\xl\styles.xml>
<fill><file:///C:\Users\murphymdev\AppData\Local\Temp\Temp1_test%20conditional%20formatting.zip\xl\styles.xml>
<patternFill
patternType="solid"><file:///C:\Users\murphymdev\AppData\Local\Temp\Temp1_test%20conditional%20formatting.zip\xl\styles.xml>
<bgColor rgb="FFFFFF00"/>
</patternFill>
</fill>
</dxf>
This was arrived at in the Format Cells dialog by selecting Yellow as the
Background Color, and leaving Pattern Color and Style alone. Note that bgColor
is set here instead of Foreground color. With testing, it appears that in
almost all instances bgColor corresponds with Background Color in the dialog,
and fgColor corresponds with Pattern Color. The only exception is the initial
example. It should be documented somewhere that setFillBackgroundColor sets the
background color of the cell, and setForegroundColor sets the color of the
pattern except when the fill is applied to a CellStyle and the pattern is
SOLID_FOREGROUND. That being the case SOLID_FOREGROUND is somewhat a misnomer
when dealing with Conditional Formats as you need to set the background color
not the foreground color when you select pattern SOLID_FOREGROUND. I don't know
whether the solution is to add a new constant such as SOLID that exhibits the
appropriate bug or just to document the issue so that folks using it know to
use setForegroundColor when it applies to CellStyle and SOLID_FOREGROUND, and
setBackgroundColor at all other times.
