How to set the cell color to a user defined value using NPOI from F#?

Question

As far as I can tell (from F# at least), cell can only be colored using predefined NPOI.SS.UserModel.IndexedColors. From the available methods / properties,

val it: ICellStyle =
  NPOI.XSSF.UserModel.XSSFCellStyle
    { ...
     FillBackgroundColor = -494s;
     FillBackgroundColorColor = NPOI.XSSF.UserModel.XSSFColor;
     FillBackgroundXSSFColor = NPOI.XSSF.UserModel.XSSFColor;
     FillForegroundColor = 64s;
     FillForegroundColorColor = null;
     FillForegroundXSSFColor = null;
     FillPattern = NoFill;
    ...

only FillBackgroundColor, FillForegroundColor, and FillPattern can be set to an int16 value, which corresponds to the IndexedColors values, and the rest of the patterns are immutable. I can create a custom

new NPOI.XSSF.UserModel.XSSFColor([|255uy; 192uy; 150uy|])

color, but I simply couldn't figure out yet how to assign this either to an existing cell style or to a brand new one. All the articles I could find also only use IndexedColors (e.g., 1, 2, 3, ).

Am I missing something obvious or this is indeed not possible?

^{The meaning of the terms "foreground" and "background" in this context is also misleading, but that is inherited from Apache POI, and it is well documented online (again, here and here, for example).}

---

edit: Found two other solutions for C# (this and this), that do the obvious by setting FillBackgroundColor to with a custom XSSFColor, but, again, I couldn't figure out how to do this in F#:

> cs;;
val it: ICellStyle =
  NPOI.XSSF.UserModel.XSSFCellStyle
    {Alignment = General;
     BorderBottom = Thin;
     BorderDiagonal = None;
     BorderDiagonalColor = 8s;
     BorderDiagonalLineStyle = None;
     BorderLeft = Thin;
     BorderRight = Thin;
     BorderTop = Thin;
     BottomBorderColor = 64s;
     BottomBorderXSSFColor = NPOI.XSSF.UserModel.XSSFColor;
     DataFormat = 0s;
     DiagonalBorderXSSFColor = null;
     FillBackgroundColor = -494s;
     FillBackgroundColorColor = NPOI.XSSF.UserModel.XSSFColor;
     FillBackgroundXSSFColor = NPOI.XSSF.UserModel.XSSFColor;
     FillForegroundColor = 0s;
     FillForegroundColorColor = NPOI.XSSF.UserModel.XSSFColor;
     FillForegroundXSSFColor = NPOI.XSSF.UserModel.XSSFColor;
     FillPattern = NoFill;
     FontIndex = 3s;
     Indention = 0s;
     Index = 43s;
     IsHidden = false;
     IsLocked = false;
     LeftBorderColor = 64s;
     LeftBorderXSSFColor = NPOI.XSSF.UserModel.XSSFColor;
     RightBorderColor = 64s;
     RightBorderXSSFColor = NPOI.XSSF.UserModel.XSSFColor;
     Rotation = 0s;
     ShrinkToFit = false;
     TopBorderColor = 64s;
     TopBorderXSSFColor = NPOI.XSSF.UserModel.XSSFColor;
     VerticalAlignment = Bottom;
     WrapText = false;}

> c;;
val it: XSSFColor =
  NPOI.XSSF.UserModel.XSSFColor {ARGB = [|255uy; 255uy; 192uy; 150uy|];
                                 ARGBHex = "FFFFC096";
                                 HasAlpha = false;
                                 HasTint = false;
                                 Index = 0s;
                                 Indexed = 0s;
                                 IsAuto = false;
                                 IsIndexed = false;
                                 IsRGB = true;
                                 IsThemed = false;
                                 RGB = [|255uy; 192uy; 150uy|];
                                 RGBWithTint = [|255uy; 192uy; 150uy|];
                                 Theme = 0;
                                 Tint = 0.0;}

> cs.FillBackgroundColor;;   
val it: int16 = -494s

> cs.FillBackgroundColor <- c;;

  cs.FillBackgroundColor <- c;;
  --------------------------^

/Users/toraritte/dev/clones/dotNET/slate-excel-reports/stdin(332,27): error FS0001: This expression was expected to have type
    'int16'    
but here has type
    'XSSFColor'    

> cs.FillBackgroundColorColor;;
val it: IColor = NPOI.XSSF.UserModel.XSSFColor {ARGB = null;
                                                ARGBHex = null;
                                                HasAlpha = false;
                                                HasTint = false;
                                                Index = -494s;
                                                Indexed = -494s;
                                                IsAuto = false;
                                                IsIndexed = true;
                                                IsRGB = false;
                                                IsThemed = false;
                                                RGB = null;
                                                RGBWithTint = null;
                                                Theme = 0;
                                                Tint = 0.0;}

> cs.FillBackgroundColorColor <- c;;

  cs.FillBackgroundColorColor <- c;;
  ^^^^^^^^^^^^^^^^^^^^^^^^^^^

/Users/toraritte/dev/clones/dotNET/slate-excel-reports/stdin(334,1): error FS0810: Property 'FillBackgroundColorColor' cannot be set

Answer 1

I failed to notice that a cell's CellStyle property returns the generic ICellStyle, which has to be cast down to NPOI.XSSF.UserModel.XSSFCellStyle in order to gain access to the FillBackgroundXSSFColor and FillForegroundXSSFColor properties.

Also, here's a summary of which property does what, quoting from this answer:

As said in my linked answer: Cell interior uses pattern fills. The fill background color is the color behind the pattern.The fill foreground color is the color of the pattern. To fill the cell using a plain color, you need using fill foreground color CellStyle.setFillForegroundColor and solid pattern FillPatternType.SOLID_FOREGROUND.

In my case, I need NPOI.SS.UserModel.FillPattern and FillForegroundXSSFColor then. The full code:

let setCellColor (cell: ICell) (rgb: byte array) =
    let xssfCell = cell :?> NPOI.XSSF.UserModel.XSSFCell
    let newCellStyle: NPOI.SS.UserModel.ICellStyle = xssfCell.Sheet.Workbook.CreateCellStyle()
    match xssfCell.CellStyle with
    | :? NPOI.XSSF.UserModel.XSSFCellStyle as xssfCellStyle ->
        let color = new NPOI.XSSF.UserModel.XSSFColor(rgb)
        xssfCellStyle.FillForegroundXSSFColor <- color
        xssfCellStyle.FillPattern <- NPOI.SS.UserModel.FillPattern.SolidForeground
    | _ -> failwith "'newCellStyle' cannot be cast to XSSFCellStyle"
    xssfCell.CellStyle <- newCellStyle

To call with RGB colors:

let getCell (workbook: NPOI.XSSF.UserModel.XSSFWorkbook) (sheetNumber: int) (address: string) =
    let sheet: ISheet = workbook.GetSheetAt(sheetNumber)
    let cellAddress = new CellAddress (address)
    getCellByAddress sheet cellAddress

let rgb: byte array = [|255uy; 192uy; 150uy|]

setCellColor (getCell o 3 "a7") rgb;;

To call with hex color string:

let hexStringToRGB (hexString: string) =
    let isHexColor (color: string) =
        "^#([A-Fa-f0-9]{6}|[A-Fa-f0-9]{3})$"
        |> fun regexPattern -> new System.Text.RegularExpressions.Regex(regexPattern)
        |> fun regex -> regex.IsMatch(color)
    match (isHexColor hexString) with
    | false ->
        failwith "Invalid hex color string."
    | true ->
        let hexString = hexString.TrimStart('#')
        let r = System.Convert.ToByte(hexString.Substring(0, 2), 16)
        let g = System.Convert.ToByte(hexString.Substring(2, 2), 16)
        let b = System.Convert.ToByte(hexString.Substring(4, 2), 16)
        [|r; g; b|]

setCellColor (getCell o 3 "a7") (hexStringToRGB "#ffc096");;

NOTE: setCellColor replaces the input cell's style with a new one, so one has to take care of re-creating the previous style options if they are needed. When I modified setCellColor to modify the cell's own style object instead of replacing it, the result was that the entire worksheet got painted to the color used from that specific cell going forward. Not sure where I screwed up, but beware.

update See the solution in How to change the color of a cell to a user defined value while keeping its existing style using NPOI from F#?.