This project is read-only.

Creating custom color palette entries: SetColorAtIndex

Mar 21, 2011 at 5:42 AM

Has anyone successfully set new custom colours in the Excel palette? It seems to accept the change, but does not save it into the workbook. Below is my function. The return Short data is stored in the font style or fill foreground. I have no problem setting one of the predefined colours or using HSSFPalette.FindSimilarColor.

Private Function GetXLColour(ByVal SystemColour As System.Drawing.Color) As Short
    Dim XlPalette As HSSFPalette = xlWorkbook.GetCustomPalette()
    Dim XlColour As NPOI.HSSF.Util.HSSFColor = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B)
    If IsNothing(XlColour) Then
        XlLastCustomColour += 1
        XlPalette.SetColorAtIndex(XlLastCustomColour, SystemColour.R, SystemColour.G, SystemColour.B)
        Return XlLastCustomColour
    Else
        Return XlColour.GetIndex()
    End If
End Function
example call: xlFont.Color = GetXLColour(TableCell.ForeColor)
example call: xlStyle.FillForegroundColor = GetXLColour(TableCell.BackColor)

I initially set XlLastCustomColour = 64 so to avoid the standard palette (custom palette indexes: 65 to 32766). Even if i overwrite the palette from index 0, nothing happens (it uses the predefined colours).

Mar 23, 2011 at 2:38 PM

I found a solution. SetColorAtIndex still doesn't work, but i found AddColour. The palette size it set to 56 (i think it should be 64), so i had to patch that to +1 so i can add a colour. Strangely, It only accepts a Byte index where it should be Int32. Anyway, at least i can add almost 200 colours now.

        Private Function GetXLColour(ByVal SystemColour As System.Drawing.Color) As Short
            'Lookup RGB from .NET system colour in Excel pallete - or create a new entry (get nearest if palette full). Return the XL palette index.
            Dim XlPalette As HSSFPalette = xlWorkbook.GetCustomPalette()
            Dim XlColour As NPOI.HSSF.Util.HSSFColor = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B)
            If IsNothing(XlColour) Then
                'Available colour palette entries: 65 to 32766 (0-64=standard palette; 64=auto, 32767=unspecified)
                If NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255 Then
                    If NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64 Then NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64
                    NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1
                    XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B)
                Else
                    XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B)
                End If
                Return XlColour.GetIndex()
            Else
                Return XlColour.GetIndex()
            End If
        End Function

Jun 21, 2011 at 9:27 PM

The first color that you add doesn't seem to work (returns the automatic color which is all black).  So if you are just worried about one color, like I happened to be, add a dummy color first and then add your color and it should work OK.  Unless I just did something incorrectly the first time.

Feb 15, 2012 at 8:02 PM
Edited Feb 15, 2012 at 8:25 PM

Thanks for the above suggestion

Coordinator
Feb 26, 2012 at 11:26 AM

Since xls only support 56+8 colors, you have to change the default pallette generated by NPOI.

Jan 14, 2014 at 12:54 PM
Good morning, I sorry if don't must write in this post, but when I try this sample, I obtain an error in the lines POI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64 and NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1 because STANDARD_PALETTE_SIZE is a constant.

What I must do for add a new color?

Thank you.

Javier.
Mar 31, 2014 at 9:37 AM
tonyqus wrote:
Since xls only support 56+8 colors, you have to change the default pallette generated by NPOI.
Hi! How to change the default pallete? please write the sample code