11

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?

Andrew Thompson
  • 168,117
  • 40
  • 217
  • 433
user001
  • 991
  • 6
  • 16
  • 34

2 Answers2

16

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);
Brian Rogers
  • 125,747
  • 31
  • 299
  • 300
  • 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. – Achow Dec 22 '14 at 02:12
  • It's fixed if you apply the fonts explicitly - richString.applyFont( blueFont); richString.applyFont(2, 5, greenFont); – Achow Dec 22 '14 at 02:20
  • 1
    For xlsx files there is an equivalent [XSSFRichTextString](https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRichTextString.html) which works the same way. – jamsandwich Aug 11 '16 at 05:41
  • 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 – Dobidoo Aug 20 '18 at 12:00
-1

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);
Biswajit
  • 2,434
  • 2
  • 28
  • 35