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 set the cell color to a user defined value using NPOI from F#?
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
1 answer
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.setFillForegroundColorand solid patternFillPatternType.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:
setCellColorreplaces 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 modifiedsetCellColorto 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#?.
0 comment threads
0 comment threads