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

Question

The answer provided in the How to set the cell color to a user defined value using NPOI from F#? thread solves the problem of how to set the right properties using F#, but it uses a new ICellStyle object and overwrites the existing style with it.

Re-writing the function (see below) to setting the existing cell's styling properties directly has a side effect of painting most of the cells in the sheet with the same color.

let changeCellColorDirectly (cell: ICell) (rgb: byte array) =
    let xssfCell = cell :?> NPOI.XSSF.UserModel.XSSFCell
    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"

I presume that same-styled cells share the same ICellStyle object, so I tried to solve this with ICellStyle's CloneStyleFrom method (see below), but the result was the same.

let changeCellColorByCloning (cell: ICell) (rgb: byte array) =
    let xssfCell: NPOI.XSSF.UserModel.XSSFCell = cell :?> NPOI.XSSF.UserModel.XSSFCell
    let newCellStyle: NPOI.SS.UserModel.ICellStyle = xssfCell.Sheet.Workbook.CreateCellStyle()
    newCellStyle.CloneStyleFrom(xssfCell.CellStyle)
    match newCellStyle 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

Answer 1

Solved this by writing my own clone function:

open System.Reflection

let cloneCellStyle (cell: NPOI.XSSF.UserModel.XSSFCell)  =
    let original = cell.CellStyle
    // printfn "ORIGINAL: %A" original.FontIndex
    let workbook = cell.Sheet.Workbook
    let copy = workbook.CreateCellStyle()
    let properties = original.GetType().GetProperties(BindingFlags.Public ||| BindingFlags.Instance)
    for prop in properties do
        // printfn "LOOP: %s --- %A" prop.Name (prop.GetValue(original))
        if prop.CanRead && prop.CanWrite then
            // printfn "IF: %s --- %A" prop.Name (prop.GetValue(original))
            let value = prop.GetValue(original)
            prop.SetValue(copy, value)

    // `FontIndex` can only be set by the `SetFont` method.
    // The line below solved  my style mismatch issues, but
    // more complicated styles may  reveal other issues; to
    // track down the culprit, the `printfn` statements can
    // be used  to show which  properties cannot be  set in
    // the `for` loop above.
    copy.SetFont <| original.GetFont(workbook)
    copy

let changeCellColor (cell: ICell) (rgb: byte array) =
    let xssfCell: NPOI.XSSF.UserModel.XSSFCell = cell :?> NPOI.XSSF.UserModel.XSSFCell
    let newCellStyle: NPOI.SS.UserModel.ICellStyle = cloneCellStyle xssfCell
    // newCellStyle.CloneStyleFrom(xssfCell.CellStyle)
    match newCellStyle with
    // 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

See the thread linked above on how to use.