Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
How to change the color of a cell to a user defined value while keeping its existing style using NPOI from F#?
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
1 answer
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.
0 comment threads
0 comment threads