How to change specific text color in one cell of excel sheet using apache poi?

Question

Does anyone know how to change the color of a specific cell text in excel. I am using apache poi and I can learn how to change the text color of the whole cell. But I want only a specific text.

For example: Cell A1 has Hello World. I want "Hello" to be blue and "World" to green. How to do it?

Answer 1

The key uses the HSSFRichTextString object to set the value of the cell. This object has an applyFont method that accepts startIndex, endIndex and Font. That way, you can create fonts with the right colors, and then apply them to parts of the cell value with applyFont ().

Here is an example of the code I combined (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); 

Answer 2

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 to your code

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