In this tutorial, you will learn how to apply multiple styles to excel cells using Apache POI. To apply multiple styles and text formatting to a cell, Apache POI provides RichTextString. The RichTextString is an interface used to create Rich text Unicode strings. These strings can have fonts applied to arbitrary parts of the string.
Table of Contents
How to apply different styles to a cell in Excel using POI?
If you want to apply multiple font styles to a single Excel cell content, then you can use the implementation class of the interface RichTextString. There are two known implementation classes HSSFRichTextString and XSSFRichTextString.
| Implementation class | Description |
|---|---|
| HSSFRichTextString | This class is especially for Excel 2003 or below (.xls file format). |
| XSSFRichTextString | This class is especially used for Excel 2007 or above (.xlsx file format). |
In this tutorial, we will see an example of using XSSFRichTextString class.
Apache POI XSSFRichTextString
Most of the strings in a workbook have formatting applied at the cell level, that is, the entire string in the cell has the same formatting applied. In these cases, the formatting for the cell is stored in the styles part, and the string for the cell can be shared across the workbook. The following code illustrates the example.
cell1.setCellValue(new XSSFRichTextString("Javacodepoint"));
cell2.setCellValue(new XSSFRichTextString("Javacodepoint"));
cell3.setCellValue(new XSSFRichTextString("Javacodepoint"));
In the above example, all three cells will use the same string cached on the workbook level.
Some strings in the workbook may have formatting applied at a level that is more granular than the cell level. For instance, specific characters within the string may be bolded, have coloring, italicizing, etc. In these cases, the formatting is stored along with the text in the string table and is treated as a unique entry in the workbook. The following code snippet illustrates this.
XSSFRichTextString s1 = new XSSFRichTextString("Javacodepoint");
s1.applyFont(boldArial);
cell1.setCellValue(s1);
XSSFRichTextString s2 = new XSSFRichTextString("Javacodepoint");
s2.applyFont(italicCourier);
cell2.setCellValue(s2);
Steps to apply Multiple Styles to Excel Cell POI:
- Create a cell using
createCell()method. eg- row.createCell(index); - Create multiple fonts (font1,font2, etc.) using
createFont()method. eg- Font font1= wb.createFont(); - Set the font styles (bold, italic, font name, colors, etc) to created Fonts.
- Create the RichTextString object using HSSFRichTextString or XSSFRichTextString class constructor.
- Apply the fonts to different-different parts of the rich text string using
applyFont()method. eg- richString.applyFont(0, 4, font1); - Finally, set the rich string to the cell using the
setCellValue()method. eg- cell.setCellValue(richString);
Apache POI Multiple Styles to Excel Cell Example
MultipleFontsInSingleCell.java
package com.javacodepoint.excel;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* Apply multiple fonts in a single cell Example
*/
public class MultipleFontsInSingleCell {
// main method
public static void main(String[] args) throws FileNotFoundException, IOException {
/*
* Creating a workbook for XLSX file using XSSFWorkbook, Use HSSFWorkbook for
* XLS File
*/
Workbook wb = new XSSFWorkbook();
// create a sheet
Sheet sheet = wb.createSheet();
// Create a cell and set the fonts
Cell cell = sheet.createRow(1).createCell(1);
// create two fonts dark blue and light blue
// dark blue font
Font darkBlueFont = wb.createFont();
darkBlueFont.setColor(IndexedColors.DARK_BLUE.getIndex());
darkBlueFont.setFontHeightInPoints((short) 26);
darkBlueFont.setFontName("Bauhaus 93");
// light blue font
Font lightBlueFont = wb.createFont();
lightBlueFont.setFontHeightInPoints((short) 26);
lightBlueFont.setFontName("Bauhaus 93");
lightBlueFont.setColor(IndexedColors.LIGHT_BLUE.getIndex());
// Create a RichTextString and apply the fonts
RichTextString richString = new XSSFRichTextString("JavaCodePoint");
richString.applyFont(0, 4, darkBlueFont);
richString.applyFont(4, 8, lightBlueFont);
richString.applyFont(8, 13, darkBlueFont);
// Finally, set the RichTextString to cell
cell.setCellValue(richString);
// create another cell
cell = sheet.createRow(3).createCell(1);
// create three fonts underline, bold, italic
Font underline = wb.createFont();
underline.setFontHeightInPoints((short) 20);
underline.setUnderline(XSSFFont.U_SINGLE);
Font bold = wb.createFont();
bold.setFontHeightInPoints((short) 20);
bold.setBold(true);
Font italic = wb.createFont();
italic.setFontHeightInPoints((short) 20);
italic.setItalic(true);
// Create another RichTextString and apply the underline, bold, italic fonts
richString = new XSSFRichTextString("Underline, Bold, Italic");
richString.applyFont(0, 9, underline);
richString.applyFont(11, 15, bold);
richString.applyFont(16, 23, italic);
// Finally, set the RichTextString to cell
cell.setCellValue(richString);
// Auto adjust the column width
sheet.autoSizeColumn(1);
// Write the output to a file
try (OutputStream fileOut = new FileOutputStream("multiple-fonts.xlsx")) {
wb.write(fileOut);
}
// close the workbook
wb.close();
// Print the confirmation message
System.out.println("An Excel file has been created successfully!");
}
}
OUTPUT:
Conclusion
In this tutorial, you have learned how to apply multiple styles within an excel cell using Apache POI.
References: RichTextString, XSSFRichTextString.
FAQ
How to set two styles for a single cell?
This can be done using RichTextString of the Apache POI library.
For example-
Font bold = wb.createFont();
bold.setBold(true);
Font italic = wb.createFont();
italic.setItalic(true);
RichTextString rt = new XSSFRichTextString(“Bold, Italic”);
rt.applyFont(0, 5, bold);
rt.applyFont(6, 12, italic);
Related Articles:
- Apache POI Excel Cell Color in Java.
- Apache POI Excel Cell Border in Java.
- Apache POI Excel Cell Alignment in Java.