Last Updated On
In this tutorial, we will change the font style in Excel using Apache POI.
What is Apache POI?
Apache POI, where POI stands for (Poor Obfuscation Implementation) is the Java API for Microsoft Documents. It offers a collection of Java libraries. These libraries help us to read, write, and manipulate different Microsoft files such as Excel sheets, PowerPoint, and Word files.
Add the below mentioned dependencies to the project. The latest dependencies can be downloaded from Maven.
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.3.0</version>
</dependency>
<!-- POI XML -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.3.0</version>
</dependency>
Every system comes bundled with a huge collection of fonts such as Arial, Impact, Times New Roman, etc. The collection can also be updated with new fonts, if required. Similarly there are various styles in which a font can be displayed, for example, bold, italic, underline, strike through, etc.
Apache POI provides methods to handle font in excel sheet. We can create font, set color, set size etc. The Font is an interface which provides methods to handle the font.
Font font = workbook.createFont();
Implementation
Step 1 – Create a blank workbook.
XSSFWorkbook workbook = newXSSFWorkbook();
Step 2 – Create a blank sheet and assign name to the sheet – Example_Sheet.
XSSFSheet sheet = workbook.createSheet("Example_Sheet");
Step 3 – Create a Row. A spreadsheet consists of rows and cells. It has a grid layout. I have created object called Header of XSSFRow.
Row row = sheet.createRow(0);
Step 4 – The syntax below creates new cell within the row at index 0. It sets a string value for the cell.
Cell cell = row.createCell(0);
cell.setCellValue("Happy Days");
Step 5 – Creates a new font and customizes it with the following properties:
- Size: 24 points
- Name: Arial
- Style: Bold and Italic
- Color: Dark Red, using IndexedColors
- Underline: Single underline
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName("Arial");
font.setBold(true);
font.setItalic(true);
font.setColor(IndexedColors.DARK_RED.getIndex());
font.setUnderline(Font.U_SINGLE);
Step 6 – Creates a new cell style
Assigns the custom font to this style using style.setFont(font). Applies the style to the previously created cell using cell.setCellStyle(style).
CellStyle style = workbook.createCellStyle();
style.setFont(font);
cell.setCellStyle(style);
Step 7– Write the workbook to a File and close the workbook. Catches any IOException errors that might occur during the file creation or writing process.
try {
FileOutputStream outputStream = new FileOutputStream("src/test/StyledCell.xlsx");
workbook.write(outputStream);
outputStream.close();
System.out.println("StyledCell.xlsx Workbook is successfully created");
} catch (IOException e) {
e.printStackTrace();
} finally {
workbook.close();
}
Below is the sample code for changing the font style in the excel in Java.
package com.example.Excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ApplyFont_Excel {
public static void main(String[] args) throws IOException {
// create blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Example_Sheet");
//Create a row
Row row = sheet.createRow(0);
//Create a cell
Cell cell = row.createCell(0);
//Set the value of the cell
cell.setCellValue("Happy Days");
//Create a new font
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName("Arial");
font.setBold(true);
font.setItalic(true);
font.setColor(IndexedColors.DARK_RED.getIndex());
font.setUnderline(Font.U_SINGLE);
//Create a cell style and set the font
CellStyle style = workbook.createCellStyle();
style.setFont(font);
//Apply the style to the cell
cell.setCellStyle(style);
//Write the output to the file
try {
FileOutputStream outputStream = new FileOutputStream("src/test/StyledCell.xlsx");
workbook.write(outputStream);
outputStream.close();
System.out.println("StyledCell.xlsx Workbook is successfully created");
} catch (IOException e) {
e.printStackTrace();
} finally {
workbook.close();
}
}
}
The output of the above program is
StyledCell.xlsx excel is created in src/test folder.
Open the excel and it looks like the below shown image:
To apply different font styles to different rows in an Excel sheet using Apache POI, you can define separate CellStyle objects, each associated with a unique Font, and apply these styles to the desired cells.
package com.example.Excel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ApplyFontMultipleRows_Excel {
public static void main(String[] args) throws IOException {
// create blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Example_Sheet");
//Create a new font1
Font font1 = workbook.createFont();
font1.setFontHeightInPoints((short) 12);
font1.setFontName("Arial");
font1.setBold(true);
font1.setItalic(true);
font1.setColor(IndexedColors.DARK_RED.getIndex());
font1.setUnderline(Font.U_SINGLE);
//Create a new font2
Font font2 = workbook.createFont();
font2.setFontHeightInPoints((short) 14);
font2.setFontName("Times New Roman");
font2.setBold(true);
font2.setItalic(true);
font2.setColor(IndexedColors.BLUE.getIndex());
//Create a new font3
Font font3 = workbook.createFont();
font3.setFontHeightInPoints((short) 16);
font3.setFontName("Courier New");
font3.setBold(true);
font3.setColor(IndexedColors.GREEN.getIndex());
//Create a cell style and set the font
CellStyle style1 = workbook.createCellStyle();
style1.setFont(font1);
CellStyle style2 = workbook.createCellStyle();
style2.setFont(font2);
CellStyle style3 = workbook.createCellStyle();
style3.setFont(font3);
// Apply styles to rows
Row row1 = sheet.createRow(0);
Cell cell1 = row1.createCell(0);
cell1.setCellValue("Underlined Italic Bold Arial");
cell1.setCellStyle(style1);
Row row2 = sheet.createRow(1);
Cell cell2 = row2.createCell(0);
cell2.setCellValue("Bold Italic Times New Roman");
cell2.setCellStyle(style2);
Row row3 = sheet.createRow(2);
Cell cell3 = row3.createCell(0);
cell3.setCellValue("Courier New");
cell3.setCellStyle(style3);
//Write the output to the file
try {
FileOutputStream outputStream = new FileOutputStream("src/test/FontStyle.xlsx");
workbook.write(outputStream);
outputStream.close();
System.out.println("Workbook FontStyle.xlsx is created");
} catch (IOException e) {
e.printStackTrace();
} finally {
workbook.close();
}
}
}
The output of the above program is
Open the excel and it looks like the below shown image:
In this example:
1. We created three different “Font“ objects with different styles (Underlined Italic Bold Arial, Times New Roman Bold Italic, and Courier New Italic).
2. We created three corresponding “CellStyle” objects and set the respective fonts.
3. We applied these styles to specific cells in different rows.
Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!! Cheers!!
QA Automation Expert 