POI setting Cell Background to a Custom Color

Question

I want to set custom color to a cell s background  I use HSSFWorkbook  can t use anything else    HSSFPalette palette   aWorkBook getCustomPalette                 Color col   new Color backgroundColor                        HSSFColor myColor    palette addColor  byte  10   byte  11   byte  12      I get this error  java lang RuntimeException  Could not find free color index

User · Answer

See http   poi apache org spreadsheet quick-guide html CustomColors   Custom colors  HSSF   HSSFWorkbook wb   new HSSFWorkbook    HSSFSheet sheet   wb createSheet    HSSFRow row   sheet createRow  short  0   HSSFCell cell   row createCell  short  0   cell setCellValue  Default Palette       apply some colors from the standard palette     as in the previous examples    we ll use red text on a lime background  HSSFCellStyle style   wb createCellStyle    style setFillForegroundColor HSSFColor LIME index   style setFillPattern HSSFCellStyle SOLID FOREGROUND    HSSFFont font   wb createFont    font setColor HSSFColor RED index   style setFont font    cell setCellStyle style      save with the default palette FileOutputStream out   new FileOutputStream  default palette xls    wb write out   out close       now  let s replace RED and LIME in the palette    with a more attractive combination     lovingly borrowed from freebsd org   cell setCellValue  Modified Palette       creating a custom palette for the workbook HSSFPalette palette   wb getCustomPalette       replacing the standard red with freebsd org red palette setColorAtIndex HSSFColor RED index           byte  153     RGB red  0-255           byte  0       RGB green          byte  0       RGB blue      replacing lime with freebsd org gold palette setColorAtIndex HSSFColor LIME index   byte  255   byte  204   byte  102      save with the modified palette    note that wherever we have previously used RED or LIME  the    new colors magically appear out   new FileOutputStream  modified palette xls    wb write out   out close      XSSF   XSSFWorkbook wb   new XSSFWorkbook    XSSFSheet sheet   wb createSheet    XSSFRow row   sheet createRow 0   XSSFCell cell   row createCell  0   cell setCellValue  custom XSSF colors     XSSFCellStyle style1   wb createCellStyle    style1 setFillForegroundColor new XSSFColor new java awt Color 128  0  128     style1 setFillPattern CellStyle SOLID FOREGROUND

User · Answer

You get this error because pallete is full  What you need to do is override preset color  Here is an example of function I m using   public HSSFColor setColor HSSFWorkbook workbook  byte r byte g  byte b       HSSFPalette palette   workbook getCustomPalette        HSSFColor hssfColor   null      try           hssfColor  palette findColor r  g  b            if  hssfColor    null                palette setColorAtIndex HSSFColor LAVENDER index  r  g b               hssfColor   palette getColor HSSFColor LAVENDER index                   catch  Exception e            logger error e              return hssfColor      And later use it for background color   HSSFColor lightGray    setColor workbook  byte  0xE0   byte 0xE0  byte  0xE0   style2 setFillForegroundColor lightGray getIndex     style2 setFillPattern CellStyle SOLID FOREGROUND

User · Answer

Slot free in NPOI excel indexedcolors from 57               Color selColor           var wb   new HSSFWorkbook             var sheet   wb CreateSheet  NPOI            var style   wb CreateCellStyle            var font   wb CreateFont            var palette   wb GetCustomPalette             short indexColor   57           palette SetColorAtIndex indexColor   byte selColor R   byte selColor G   byte selColor B            font Color   palette GetColor indexColor  Indexed

User · Answer

Don t forget to call this   style setFillPattern CellStyle Align Fill     Parameter may differ according to your need  Maybe CellStyle FINE DOTS or so

User · Answer

You can set custom color using this- check out this - click hear XSSFWorkbook workbook   new XSSFWorkbook     IndexedColorMap colorMap   workbook getStylesSource   getIndexedColors    Font tableHeadOneFontStyle   workbook createFont            tableHeadOneFontStyle setBold  true            tableHeadOneFontStyle setColor  IndexedColors BLACK getIndex       XSSFCellStyle tableHeaderOneColOneStyle   workbook createCellStyle            tableHeaderOneColOneStyle setFont  tableHeadOneFontStyle            tableHeaderOneColOneStyle                  setFillForegroundColor  new XSSFColor  new java awt Color  255  231  153    colorMap              tableHeaderOneColOneStyle setFillPattern  FillPatternType SOLID FOREGROUND            tableHeaderOneColOneStyle   setLeftRightBorderColor  tableHeaderOneColOneStyle            tableHeaderOneColOneStyle   alignCenter  tableHeaderOneColOneStyle

User · Answer

As pointed in Vlad s answer  you are running out of free color slots  One way to get around that would be to cache the colors  whenever you try a RGB combination  the routine should first check if the combination is in the cache  if it is in the cache  then it should use that one instead of creating a new one from scratch  new colors would then only be created if they re not yet in cache   Here s the implementation I use  it uses XSSF plus Guava s LoadingCache and is geared towards generationg XSSF colors from CSS rgb r  g  b  declarations  but it should be relatively trivial to adapt it to HSSF       private final LoadingCache lt String  XSSFColor gt  colorsFromCSS   CacheBuilder newBuilder                build new CacheLoader lt String  XSSFColor gt                       private final Pattern RGB   Pattern compile  rgb     s    d    s     s    d    s    s    d    s                          Override                 public XSSFColor load String style  throws Exception                       Matcher mat   RGB matcher style                       if   mat find                              throw new IllegalStateException  Couldn t read CSS color      style                                                                    return new XSSFColor new java awt Color                              Integer parseInt mat group 1                                 Integer parseInt mat group 2                                 Integer parseInt mat group 3                                           Perhaps someone else could post a HSSF equivalent

[java] POI setting Cell Background to a Custom Color

Examples related to java

Examples related to apache-poi

Examples related to excel-2003