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.

Post History

50%

+0 −0

Q&A 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 ...

1 answer · posted 10mo ago by toraritte‭ · last activity 10mo ago by toraritte‭

Question f# excel NPOI

#2: Post edited by

toraritte‭ · 2024-01-11T16:08:13Z (10 months ago)

Copy Link

Raw

Markdown

The answer provided in the [How to set the cell color to a user defined value using NPOI from F#?](https://stackoverflow.com/questions/77701061/how-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
```

The answer provided in the [How to set the cell color to a user defined value using NPOI from F#?](https://software.codidact.com/posts/290555) 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: Initial revision by

toraritte‭ · 2024-01-11T16:06:00Z (10 months ago)

Copy Link

Raw

Markdown

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#?](https://stackoverflow.com/questions/77701061/how-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
```

f# excel NPOI

Communities

Post History