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 Program to Apply Different Data Formats to // a Cell in a Spreadsheet // Importing required classes import 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 class class 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)