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?

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);