Apache POI XSSFColor from hex code

Question

I want to set the foreground color of a cell to a given color in hex code. For example, when I try to set it to red:

style.setFillForegroundColor(new XSSFColor(Color.decode("#FF0000")).getIndexed());

No matter what Hex value I set in the parameter for the decode function, the getIndexed function will always return the black color.

Could it be that I might be doing something wrong? I think it's a bug but I'm not sure...

why do you need getIndexed()? For backward compatibility?? Try removing that. — gtiwari333, Jun 06 '12 at 10:47
If you apply the style to a cell, does it correctly show up as red in Excel? — Gagravarr, Jun 06 '12 at 13:16
I need getindexed because the method will only accept a short value. — Neets, Jun 06 '12 at 16:39

score 102 · Accepted Answer · answered Jun 07 '12 at 01:19

The good news is, if you are using XSSF, as opposed to HSSF, then the solution to your problem is fairly easy. You simply have to cast your style variable to XSSFCellStyle. If you do, then there is a version of setFillForegroundColor that takes an XSSFColor argument, so you need not call getIndexed(). Here is some example code:

XSSFCellStyle style = (XSSFCellStyle)cell.getCellStyle();
XSSFColor myColor = new XSSFColor(Color.RED);
style.setFillForegroundColor(myColor);

However, if you are using HSSF, then things are harder. HSSF uses a color palette, which is simply an array of colors. The short value that you pass into setFillForegroundColor is an index into the palette.

So the problem you have is converting an rgb value into a palette index. The solution you proposed, using getIndexed(), is logical, but, unfortuntately, it does work for XSSFColor the way you might suppose it should.

Fortunately, there is a solution. For the moment, let us assume you will be satisfied using one of the colors in the default palette, rather than using a custom color. In that case, you can use the HSSFPalette and HSSFColor classes to solve the problem. Here is some example code:

HSSFWorkbook hwb = new HSSFWorkbook();
HSSFPalette palette = hwb.getCustomPalette();
// get the color which most closely matches the color you want to use
HSSFColor myColor = palette.findSimilarColor(255, 0, 0);
// get the palette index of that color 
short palIndex = myColor.getIndex();
// code to get the style for the cell goes here
style.setFillForegroundColor(palIndex);

If you want to use custom colors not already in the default palette, then you have to add them to the palette. The javadoc for HSSFPalette defines the methods you can use for doing so.

I was using XSSF, so the first solution made the trick. Thanks a lot! — Neets, Jun 07 '12 at 08:51
This `palette.findSimilarColor(255, 0, 0);` saved me from madness. Thank you so much. Of course upvoted. — Alkis Kalogeris, Jan 02 '15 at 11:00
The above code did not work until i created the style from the workbook instead of getting that from the cell like @JonathanL answer below — isaac.hazan, Nov 26 '15 at 10:01

score 37 · Answer 2 · edited Sep 12 '18 at 14:37

37

For Apache POI prior to 4.0 you can simply do the following :

 XSSFColor grey = new XSSFColor(new java.awt.Color(192,192,192));
 cellStyle.setFillForegroundColor(grey);

Since POI 4.0 you have to provide the workbench IndexedColorMap:

 IndexedColorMap colorMap = workbook.getStylesSource().getIndexedColors();
 XSSFColor grey = new XSSFColor(new java.awt.Color(192,192,192), colorMap);
 cellStyle.setFillForegroundColor(grey);

edited Sep 12 '18 at 14:37

Robert

39,162
17
99
152

answered May 06 '14 at 09:36

TheByeByeMan

1,396
2
28
50

4

this works too and gives easy way of applying RGB color to a cell – Gleeb Dec 04 '14 at 15:19
Just note this might break your server because of dependencies to AWT classes and the server might be headless. Didn't check it yet. – Daniel Jan 28 '18 at 16:28
Thanks for the hint concerning POI 4.0! I am migrating from 3.17 to 4.0.0 and was wondering where to get that IndexedColorMap from because the constructor XSSFColor(Color) I was using has been removed. – Ulathar Sep 19 '18 at 12:36
Thx. It's the only example I found for poi 4.0 – Sergio Tx Sep 20 '18 at 11:45
4

Thanks for the POI 4.0 Example. That helped me a lot. The only change I propose is, that you cannot add a XSSFColor but you have to add the **.getIndex()**: `cellStyle.setFillForegroundColor(grey.getIndex())` – leole Oct 16 '18 at 06:05
4

This makes the cell black no matter what rgb values you use. – Tom Swifty Nov 15 '18 at 20:01
2

Hello, I am using poi 4.0.1 and my ide does not recognize getStyleSources() from workbook.getStylesSource().getIndexedColors(). What import do you need for that? I am importing the following, but no luck: import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.IndexedColorMap; import org.apache.poi.xssf.model.StylesTable; – Guillaume Jan 21 '19 at 12:40

score 18 · Answer 3 · edited Mar 02 '15 at 01:54

18

Use XSSFColor for XSSFWorkbook. XSSFColor can take byte[] rgb or a java.awt.Color. See examples below:

XSSFWorkbook wb = new XSSFWorkbook();
XSSFCellStyle cellStyle = wb.createCellStyle();
byte[] rgb = new byte[3];
rgb[0] = (byte) 242; // red
rgb[1] = (byte) 220; // green
rgb[2] = (byte) 219; // blue
XSSFColor myColor = new XSSFColor(rgb); // #f2dcdb
cellStyle.setFillForegroundColor(myColor);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

see GuenSeven's answer

XSSFColor myColor = new XSSFColor(new java.awt.Color(242, 220, 219)); // #f2dcdb
cellStyle.setFillForegroundColor(myColor);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

edited Mar 02 '15 at 01:54

sushain97

2,752
1
25
36

answered Dec 24 '14 at 00:19

Jonathan L

9,552
4
49
38

1

I am not able to use .setFillForegroundColor(myColor); what do i do? – meme May 05 '16 at 16:38
@meme, upgrade to poi version 3.0.7 or later, I use version 3.10 – Jonathan L May 05 '16 at 21:02

lher · Answer 4 · 2018-12-03T13:00:51.227

4

I didn't want to use AWTs Color, and since there is no constructor that solely accepts a byte array nowadays (I'm using version 3.17): There is a constructor public XSSFColor(byte[] rgb, IndexedColorMap colorMap), which did the trick for me:

byte[] byteColor = new byte[]{255,0,0};
XSSFColor color = new XSSFColor(byteColor, null);

edited Dec 03 '18 at 13:00

answered Mar 23 '18 at 14:06

lher

126
7

Arturo Franco Barrio · Answer 5 · 2020-03-24T14:02:25.020

1

You can use the following code to get a XSSFColor from a String hex color (FFEEDDCC or 112233 format). If you are using RGB, simply remove # character:

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFColor color = new XSSFColor(workbook.getStylesSource().getIndexedColors());
color.setARGBHex("#000000".substring(1));

edited Mar 24 '20 at 14:02

answered Mar 23 '20 at 11:40

Arturo Franco Barrio

119
1
3

score 0 · Answer 6 · answered Aug 20 '15 at 08:48

0

XSSFCellStyle accept color in method setFillForegroundColor for poi version above 3.07 ... so check your version first to avoid getting in the problem I was facing ... previous version needed short as argument.

answered Aug 20 '15 at 08:48

Fabrice Khedadi

41
1
4

Apache POI XSSFColor from hex code

6 Answers6

Linked