How to Create Different Types of Cells in a Spreadsheet using Java?

Apache POI is an API which was provided by Apache Foundation and is used to set the various type of values to the cell. The overloading function setCellValue() of Apache POI is used to set the value type of the cell. Maven is a powerful project management tool that is based on POM (project object model). It is used for project build, dependency, and documentation. It simplifies the build process like ANT. But it is too much advanced than ANT.

Algorithm: Steps To Create Different Types of Cells In A Spreadsheet 

• Step 1: Create an instance of the workbook
• Step 2: Create a spreadsheet in the above workbook.
• Step 3: Create rows using XSSFRow
• Step 4: Writing the content to the workbook by defining the object of type  FileOutputStream
• Step 5: Close the file

Procedure:

Step 1: Create a Maven project in eclipse and add Apache POI(used for setting value type of cells) dependencies to it or a Java project with Apache POI library installed.

Step 2: As in creating a Maven project, prerequisites are required to add the following Maven dependencies to pom.xml file, in order to use the Apache POI library. For these libraries search online for Maven Central Library and then search for Apache POI and get the dependencies or can also copy the following dependencies as shown below to the main java program.

XML

<dependencies>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.12</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

</dependencies>

Step 3: After adding dependencies create a new class. For creating a new class go to src/main/java/package right-click and create a new class and name it, here for illustration purpose file is names as GFG. . In this class we will first create an instance of the workbook and with that instance, we will create a spreadsheet and in that spreadsheet, we will define the value type of cells. Below is the code snippet is given for the GFG class(taken here) as discussed above.

Implementation: Code snippet

Example: To create different types of cells in a spreadsheet

Java

// Java Program to create
// different types of cells in a spreadsheet

// Importing Maven dependencies
// Importing Apache POI dependency files
// Importing java input/output file and
// Date class from java.util package
import java.io.File;
import java.io.FileOutputStream;
import java.util.Date;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

// Class having different types of cells
public class GFG {

    // Main driver method
    public static void main(String[] args) throws Exception
    {

        // Step 1: Create an instance of the workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        CreationHelper creationHelper
            = workbook.getCreationHelper();

        // Step 2: Create a spreadsheet in the above
        // workbook
        // and name it as CellTypesSheet
        XSSFSheet spreadsheet
            = workbook.createSheet("CellTypesSheet");

        // Step 3: Create rows using XSSFRow
        // We need to create row before creating cells,
        // row is a collection of cells
        XSSFRow noOfRows = spreadsheet.createRow((short)2);

        // Creating cells
        // Custom inputs in cells

        // Cell 1
        noOfRows.createCell(0).setCellValue("Cell Types");

        // Cell 2
        noOfRows.createCell(1).setCellValue("Cell Value");

        // Above two cells(Cell Types and Cell Values) are
        // created at row 3 in excel and similarly for rest
        // of the cells
        noOfRows = spreadsheet.createRow((short)3);

        // Cell 3
        // Setting a Blank type cell
        noOfRows.createCell(0).setCellValue(
            "Cell Type-BLANK");
        noOfRows.createCell(1);
        noOfRows = spreadsheet.createRow((short)4);

        // Cell 4
        // Setting a Boolean type cell
        noOfRows.createCell(0).setCellValue(
            "Cell Type-BOOLEAN");
        noOfRows.createCell(1).setCellValue(true);
        noOfRows = spreadsheet.createRow((short)5);

        // Cell 5
        // Setting a Error type cell
        noOfRows.createCell(0).setCellValue(
            "Cell Type-ERROR");
        noOfRows.createCell(1).setCellValue(
            XSSFCell.CELL_TYPE_ERROR);
        noOfRows = spreadsheet.createRow((short)6);

        // Cell 6
        // Setting a Date and Time type cell
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(
            creationHelper.createDataFormat().getFormat(
                "d/m/y h:mm"));
        noOfRows.createCell(0).setCellValue(
            "Cell Type-DATE-TIME");
        noOfRows.createCell(1).setCellValue(new Date());
        noOfRows.getCell(1).setCellStyle(cellStyle);
        noOfRows = spreadsheet.createRow((short)7);

        // Cell 7
        // Setting a Numeric type cell
        noOfRows.createCell(0).setCellValue(
            "Cell Type-Numeric");
        noOfRows.createCell(1).setCellValue(35);
        noOfRows = spreadsheet.createRow((short)8);

        // Cell 8
        // Setting a String type cell
        noOfRows.createCell(0).setCellValue(
            "Cell Type-String");
        noOfRows.createCell(1).setCellValue(
            "GeeksForGeeks");

        // Step 4: Writing the content to the workbook
        // by defining object of type  FileOutputStream
        FileOutputStream out = new FileOutputStream(
            new File("GfgTypesOfCells.xlsx"));
        workbook.write(out);

        // Step 5: Close the file
        out.close();

        // Display message to be printed for successful
        // execution of program
        System.out.println("GFG.xlsx Created Successfully");
    }
}

Output: A message will be printed in console as specified at last in the program that “GFG.xlsx Created Successfully” as the successful execution of the program responsible for changes in the spreadsheet below: