Formatting in excel is a neat trick in excel which is used to change the appearance of the data represented in the worksheet, formatting can be done in multiple ways such as we can format the data of the cells by using the styles. By default, all worksheet cells are formatted with the General number format. With the General format, anything you type into the cell is usually left as-is.
Example: If you type 11111.25 into a cell and then set a format as “0.0” it means after decimal it is only one digit, the cell contents are displayed as 11111.3. This type of formatting is used to get accurate data according to requirements. Let’s discuss the approach to how we can do this by using Java.
Approach:
- Import all the necessary .jar files like XSSF, XML.
- Create an instance of the workbook
- Create a spreadsheet in the above workbook.
- Create rows using XSSFRow
- Create a cell using XSSFCell.
- Setting cell values.
- Create style using XSSFCellStyle in which we are going to apply different styles.
- Set format data.
- Writing the content to the workbook by defining the object of type FileOutputStream
- Close the connection of the file.
Procedure:
- Create a Maven project in eclipse and add Apache POI(used for setting value type of cells) and also import all the necessary .jar files like HSSF, XML.
- Give the name to the workbook.
- Create a workbook using “new XSSFWorkbook()” in which we have to create the spreadsheet.
- Create a spreadsheet in the workbook using “workbook.createSheet(‘Sheet1’)” and also give the name to the sheet as “Sheet1”
- Create a row using XSSFRow. Rows are 0 based.
- Create a cell using XSSFCell.
- Create style using XSSFCellStyle in which we are going to apply different styles.
- Create a format using DataFormat “workbook.createDataFormat()” to apply a custom format in the cell.
- Set a value to a cell using cell.setCellValue();
- Apply format to a particular cell. Repeat this step as per the requirement to create a formatted cell using style.setDataFormat(format.getFormat(“”)); format type should be string type.
- Place the output file in the default location and also kept in the try-catch block using FileOutputStream().
- Write it to the workbook created in the initial step using workbook.write();
- Close the output file.
- Display message for console window when the program is successfully executed.
- Display error message for console window when the program is not successful this statement is kept in the catch block.
Example:
Java
// Java Program to Apply Different Data Formats to// a Cell in a Spreadsheet// Importing required classesimport java.io.FileNotFoundException;import java.io.FileOutputStream;import org.apache.poi.ss.usermodel.DataFormat;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;// Main classclass GFG { // Main driver method public static void main(String[] args) throws Exception { // Naming of WorkBook String excelfilename = "GeeksForGeeks.xlsx"; // Creating a WorkBook XSSFWorkbook workbook = new XSSFWorkbook(); // Create a Spread Sheet by creating an object of // XSSFSheet and also give name XSSFSheet spreadsheet = workbook.createSheet("Sheet1"); // Create a row and put some cells in it. Rows are 0 // based. XSSFRow row; // Creating a cell XSSFCell cell; // style XSSFCellStyle style; // Creating format to format style DataFormat format = workbook.createDataFormat(); int rowNum = 0; int colNum = 0; // Creating a row in Spread Sheet row = spreadsheet.createRow(rowNum++); // Creating a cell in row cell = row.createCell(colNum); // Setting a value cell.setCellValue(11111.25); style = workbook.createCellStyle(); // Setting a Format to a cell using style style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = spreadsheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = workbook.createCellStyle(); // Creating another format using style style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style); // Try block to check for exceptions try { // Place the output file in default location and // also kept in try catch block FileOutputStream outputfile = new FileOutputStream(excelfilename); // Writing to workbook workbook.write(outputfile); // Closing the output file // using close() method outputfile.close(); // Display message for console window when // program is successfully executed System.out.println(excelfilename + " is written successfully"); } // Catch block to handle exceptions catch (FileNotFoundException e) { // Display error message for console window when // program is not successfully executed System.out.println("ERROR!! " + e.getMessage()); } }} |
Output: On console window
A. When the program is successfully executed.
GeeksForGeeks.xlsx is written successfully.
B. When the program is not successfully executed.
ERROR!! GeeksForGeeks.xlsx (The process cannot access the file because it is being used by another process)
Output: Workbook(excel file)
