Open In App

How to Create Different Data Format of Cells in a Spreadsheet Using Java?

Improve
Improve
Like Article
Like
Save
Share
Report

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: 

  1. Import all the necessary .jar files like XSSF, XML.
  2. Create an instance of the workbook
  3. Create a spreadsheet in the above workbook.
  4. Create rows using XSSFRow
  5. Create a cell using XSSFCell.
  6. Setting cell values.
  7. Create style using XSSFCellStyle in which we are going to apply different styles.
  8. Set format data.
  9. Writing the content to the workbook by defining the object of type FileOutputStream
  10. Close the connection of the file.

Procedure:

  1. 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.
  2. Give the name to the workbook.
  3. Create a workbook using “new XSSFWorkbook()” in which we have to create the spreadsheet.
  4. Create a spreadsheet in the workbook using “workbook.createSheet(‘Sheet1’)” and also give the name to the sheet as “Sheet1”
  5. Create a row using XSSFRow. Rows are 0 based.
  6. Create a cell using XSSFCell.
  7. Create style using XSSFCellStyle in which we are going to apply different styles.
  8. Create a format using DataFormat “workbook.createDataFormat()” to apply a custom format in the cell.
  9. Set a value to a cell using cell.setCellValue();
  10. 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.
  11. Place the output file in the default location and also kept in the try-catch block using FileOutputStream().
  12. Write it to the workbook created in the initial step using workbook.write();
  13. Close the output file.
  14. Display message for console window when the program is successfully executed.
  15. 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 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)

 

 



Last Updated : 03 Mar, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads