How to change the specific text color in a same cell of excel sheet using apache poi?

Question

Does anyone know how to change the color of the particular text of a cell in excel. I am using apache poi and I could find out to change the text color of entire cell. But I want only a particular text.

Eg: Cell A1 has Hello World I want "Hello" to be in blue and "World" to be in green. How do I do this?

Epididymis

@BrianRogers Thanks for confirming one cell can have multiple colors. – Miseno

Answer 1

The key is using the HSSFRichTextString object to set the value of the cell. This object has an applyFont method which accepts a startingIndex, endingIndex and a Font. Thus, you can create fonts having the colors you want, then apply them to parts of the cell value using applyFont().

Here is some example code I cobbled together (completely untested):

// Set up a rudimentary worksheet with a cell in it
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(“sheet1”);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);

// Set up fonts
HSSFFont blueFont = workbook.createFont();
blueFont.setColor(HSSFColor.BLUE.index);

HSSFFont greenFont = workbook.createFont();
greenFont.setColor(HSSFColor.GREEN.index);

// create a cell style and assign the first font to it
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(blueFont);

// assign the style to the cell
cell.setCellStyle(style);

// override the parts of the text that you want to
// color differently by applying a different font.
HSSFRichTextString richString = new HSSFRichTextString("Hello, World!");
richString.applyFont(6, 13, greenFont);
cell.setCellValue(richString);

Garden

Interestingly, this doesn't work if you want to override the color of Hello. The exact same piece of code works if you modify World!The color of Hello is overridden alrite, but World loses its style. – Theadora

It's fixed if you apply the fonts explicitly - richString.applyFont( blueFont); richString.applyFont(2, 5, greenFont); – Theadora

For xlsx files there is an equivalent XSSFRichTextString which works the same way. – Rad

so u mean like XSSFRichTextString richString = new XSSFRichTextString("Hello, World!"); richString.applyFont(6, 13, greenFont); cell.setCellValue(richString); I have the same concern.But as of now i cant reach to my code but very curious to test it as soon as i can...hope this is working – Unheardof

Answer 2

At first create a style

//////////////////////Excel Header Style/////////////////////////   
        HSSFCellStyle headerlabelcs = wb.createCellStyle();
        headerlabelcs.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        headerlabelcs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerlabelcs.setBorderLeft((short)1);
        headerlabelcs.setBorderRight((short)1);

        HSSFFont headerlabelfont = wb.createFont();
        headerlabelfont.setFontHeightInPoints((short)12);
        headerlabelfont.setFontName("Calibri");
        headerlabelfont.setColor(HSSFColor.BLACK.index);
        headerlabelfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerlabelcs.setFont(headerlabelfont); 
                //////////////////////Excel Header Style/////////////////////////   

add then this line will be added in your code

sheet.getRow(rowIndex).getCell(0).setCellStyle(headerlabelcs);

Bordello

This solution doesn't do what the OP asked. He wants to set parts of the text in a single cell to two different colors. – Garden

Then based on the text change the style – Bordello