In this post, let us look at how to use Java and Apache POI to apply the custom font and numerous styles connected with it. A Java API is very useful to handle Microsoft Documents.
Apache POI library in Java:
ApachePOI is an abbreviation for Poor Obfuscation Implementation, a Java API for reading and writing Microsoft documents. It includes the Wordbook, Sheet, Row, and Cell classes and interfaces. Apache POI may be used to access files with the ‘xlsx’ extension as well as files with the ‘xlsx’ extension.
Classes Used:
XSSFWorkbook: XSSFWorkbook. It is a class that represents both high-level and low-level Excel file formats. It is part of the org. apache. xssf.
XSSFSheet:
public class XSSFSheet extends POIXMLDocumentPart implements Sheet
It is a High-level representation of a SpreadsheetML worksheet. Sheets are the central structures within a workbook and are where the majority of the spreadsheet work is done. The worksheet, which is displayed as a grid of cells, is the most used type of sheet. Text, numbers, dates, and formulas can all be entered into worksheet cells. Formatting cells is also possible.
FileOutputStream:
FileOutputStream(File file) class creates a file output stream to write to the file provided File object.
FileInputStream:
A FileInputStream reads bytes from a file in the file system. The files that are available are determined by the host environment. FileInputStream is intended for reading raw bytes streams such as image data. Consider using FileReader to read character streams.
Pre-requisites:
We required the following software on your computers to work with these.
- Check to see if your machine has Java; if not, obtain the most recent Java SDK version from here.
- Create a Maven project.
- Add apache poi dependency.
Apply Fonts to the Contents of a Cell Using Java
Here we use STS(Spring Tool Suite) to apply fonts to the contents of a cell in an excel sheet.
You can either use MyEclipse or STS(SpringToolSuite) for this. Here I am using STS.
STS:
Spring Tool Suite (STS) is a Java IDE designed specifically for the development of Spring-based enterprise applications. It is simpler, quicker, and more convenient. Most notably, it is built on the Eclipse IDE. STS is a free, open-source software platform powered by VMware. Spring Tools 4 represents the next generation of Spring tooling for your preferred development environment. It was largely redesigned from the bottom up to give world-class support for developing Spring-based enterprise apps, regardless of whether you choose Eclipse, Visual Studio Code, or Theia IDE.
Note:
If you are using MyEclipse then create a maven project.
STS directly imports all the classes from maven if we just add dependencies.
Step#1: Open STS(SpringToolSuite) and create a spring stater project say AddFontsAndStylesToCellData in a package com.sheetstips.
Step#2: Add the spring web dependency and click on Finish. Now the project is created successfully.
Step#3: Open the pom.xml file and add the following apache poi dependency from the maven repository.
https://mvnrepository.com/
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.12</version> </dependency> <dependency> <groupId>org.apache.poi</ groupId> <artifactId>poi-ooxml</ artifactId> <version>3.12</version> </dependency>
Step#4: Create a new class say AddFontToCellData in the src/main/java folder with the same package com.sheetstips.
Step#5: Write the below code in the above AddFontToCellData class
Approach:
- Import all the required libraries using the import keyword
- Import apache POI libaries using the import keyword
- Create a class say AddFontToCellData
- Inside the Main driver method, create a new XSSFWorkbook object using the new keyword
- Create a new worksheet in the above workbook object by passing the sheet name as an argument to the createSheet() function(Sheet1 by default)
- Store it in another variable
- Create a first row in the above worksheet using the createRow() function
- Created a new font object using the createFont() function
- Set the font properties using the setFontHeightInPoints(), setFontName(), setBold(), setItalic(), setColor() functions
- Set the above created font into style using the createCellStyle() function object.
- Create a cell with a custom row number value and set style to it.
- Give the cell value using the setCellValue() function by passing the cell
data as an argument to it - Create an object for the FileOutputStream by passing the output excel file path as an argument to it to place the output file in that location
- Write the data which is added in the above steps i.e write the outputFile to the workbook Object using the write() function
- Close the above-opened output file using the close() function.
- The Exit of the Program.
Below is the implementation:
package com.sheetstips; //Import all the required libraries using the import keyword import java.io.File; import java.io.FileOutputStream; //Import apache POI libaries using the import keyword import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; //Create a class say AddFontToCellData public class AddFontToCellData { // Main driver method public static void main(String[] args) throws Exception { // Create a new XSSFWorkbook object using the new keyword XSSFWorkbook workbookObj = new XSSFWorkbook(); // Create a new worksheet in the above workbook object by passing // the sheet name as an argument to the createSheet() function(Sheet1 by // default) // Store it in another variable XSSFSheet worksheet = workbookObj.createSheet("Font Styles"); // Create a first row in the above worksheet using the createRow() function XSSFRow excelrow = worksheet.createRow(1); // Created a new font object using the createFont() function XSSFFont font = workbookObj.createFont(); // Set the font properties using the setFontHeightInPoints(), setFontName(), // setBold(), setItalic(), setColor() functions font.setFontHeightInPoints((short) 25); font.setFontName("Times New Roman"); font.setBold(true); font.setItalic(true); font.setColor(HSSFColor.GREEN.index); // Set the above created font into style using the createCellStyle() function // object XSSFCellStyle cellStyling = workbookObj.createCellStyle(); cellStyling.setFont(font); // Create a cell with a custom row number value and set style to it. XSSFCell excelCell = excelrow.createCell(5); // Give the cell value using the setCellValue() function by passing the cell // data as an argument to it excelCell.setCellValue("SheetsTips"); excelCell.setCellStyle(cellStyling); // Create an object for the FileOutputStream by passing the output excel file // path as an argument to it to place the output file in that location FileOutputStream outputfile = new FileOutputStream(new File("AddingFontToCellData.xlsx")); // Write the data which is added in the above steps // i.e write the outputFile to the workbook Object using the write() function workbookObj.write(outputfile); // Close the above opened outputfile using the close() function outputfile.close(); } }
Step#6: Now run the code by Right-clicking on the project and click on run –> Run as spring boot App
Output: