Fonts in Excel using Apache POI
Working in Excel files is made easy by the Apache POI library. Even more easy is the using and working with various fonts in excel using Apache POI.
We saw how we can Write Excel using Apache POI in the previous post.
Now we will see how we can explore the various functions that POI has provided to play with the Fonts.
To set font to the excel, we need to follow the below steps
1. Create a new Font, using the workbook
1 | Font font = workbook.createFont(); |
2. Alter the Font as per your needs.
you can set height, font name, italic and even strike it out
1 2 3 4 | font.setFontHeightInPoints((short)24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); |
3. Create a CellStyle into which assign the font created.
1 2 | CellStyle style = workbook.createCellStyle(); style.setFont(font); |
4. Now use the CellStyle object and set to the cells that you want to apply the font.
Please see the complete code below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | package com.kscodes.sampleproject; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class WriteExcelUsingPOI { public static void main(String[] args) { // Create instance of the class and call the writeExcelFile() WriteExcelUsingPOI writeExcelUsingPOI = new WriteExcelUsingPOI(); writeExcelUsingPOI.writeExcelFile("C:\\kscodes\\EmployeeDetails.xlsx"); } public void writeExcelFile(String fileName) { XSSFWorkbook workbook = null; FileOutputStream fileOutputStream = null; try { int rowNum = 0; fileOutputStream = new FileOutputStream(new File(fileName)); // Create a Workbook workbook = new XSSFWorkbook(); // Create an Empty Sheet XSSFSheet sheet = workbook.createSheet("Employee Details"); // Create a new font and alter it. Font font = workbook.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName("Courier New"); font.setItalic(true); font.setStrikeout(true); CellStyle style = workbook.createCellStyle(); style.setFont(font); int cellNum = 0; // Create a Row and write the Row Header details Row row = sheet.createRow(rowNum++); Cell cell = row.createCell(cellNum); cell.setCellValue("Test Font for KSCodes"); cell.setCellStyle(style); workbook.write(fileOutputStream); System.out.println("Excel File created and written !!!!"); } catch (Exception e) { System.out.println("An Exception occured while writing Excel"); } finally { try { if (fileOutputStream != null) { fileOutputStream.close(); } if (workbook != null) { workbook.close(); } } catch (Exception e) { } } } } |
Output
Please Note
Their is a limit for creating CellStyles in a workbook. If you exceed the limit of 64000 CellStyles you may get the below exception
1 2 | java.lang.IllegalStateException: The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook |
To avoid this exception you can create your font and CellStyle one time and then reuse it.
Example:
If you have code like this
1 2 3 4 5 6 7 8 9 10 11 12 | //Wrong Way to Use CellStyle and Fonts for (int i = 0; i < 10000; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell((short) 0); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontHeightInPoints((short)24); font.setFontName("Courier New"); style.setFont(font); cell.setCellStyle(style); } |
You can easily change it to below code for better use of CellStyle and Fonts. This way you can write 10000 rows and cells with only 1 Font and CellStyle.
1 2 3 4 5 6 7 8 9 10 11 | //Correct Way to Use CellStyle and Fonts CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontHeightInPoints((short)24); font.setFontName("Courier New"); style.setFont(font); for (int i = 0; i < 10000; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell((short) 0); cell.setCellStyle(style); } |
