How to Create a Cell at Specific Position in Excel file using Java

How to Create a Cell at Specific Position in Excel file using Java

by

In this article, let us look at how to create a cell at a specific position in an excel file using Java.

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.

Apache POI is a free and open-source Java library for creating and manipulating Microsoft Office file formats. The following file formats should be able to be created, modified, and displayed/read using POI. For example, because java does not include built-in support for interacting with Excel files, we must search for open-source APIs to complete the job.

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.

Creating Cell at a Specific Position in Excel file using Java

Here we use STS(Spring Tool Suite) to create a cell at a specific position in an excel file.

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 CreateCellExcelFile 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.

https://mvnrepository.com/artifact/org.apache.poi/poi

<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 CreateCellAtSpecificPosition in the src/main/java folder with the same package com.sheetstips.

Step#5: Write the below code in the above CreateCellAtSpecificPosition class

Approach:

  • Import all the required classes using the import keyword.
  • Create a class say CreateCellAtSpecificPosition.
  • Inside the Main driver method, Create a workbook object using the new keyword and store it in a variable.
  • Create an object for the FileOutputStream by passing the output excel file name as an argument to it
  • The output stream accepts the output bytes and sends them to sink
  • Create a new sheet in the above workbook using createSheet() function provided by Apache POI by passing the sheetname as an argument to it and store it in another variable.
  • Create a row at specific position using the createRow() function by passing the specific row number as an argument to it.
  • Create a cell at specific position using the createCell() function by passing the specific cell number as an argument to it.
  • Pass some random cell value to the setCellValue() function to store the given cell value at the specified cell position.
  • Get the index value of row of the given cell using the getRowIndex() function.
  • Get the index value of column of the given cell using the getColumnIndex() function.
  • Write the data to the above Workbook at a specified cell location using the write() function
  • Print the row and column index of the created cell at the specified position.
  • The Exit of the Program.

Below is the implementation:

// Import all the required classes using the import keyword
import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

// Create a class say CreateCellAtSpecificPosition
public class CreateCellAtSpecificPosition {

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

        // Create a workbook object using the new keyword and store it in a variable
        Workbook workbookObj = new HSSFWorkbook();

        // Create an object for the FileOutputStream by passing the output excel file
        // name as an argument to it
        // The output stream accepts the output bytes and sends them to sink
        OutputStream outputExcelFile = new FileOutputStream("createCellExcel.xlsx");

        // Create a new sheet in the above workbook using createSheet() function
        // provided by Apache POI by passing the sheetname as an argument to it 
        // and store it in another variable
        Sheet sheetname = workbookObj.createSheet("Websites");

        // Create a row at specific position using the createRow() function by 
        // passing the specific row number as an argument to it
        Row row = sheetname.createRow(1);

        // Create a cell at specific position using the createCell() function by 
        // passing the specific cell number as an argument to it
        Cell cell = row.createCell(1);

        // Pass some random cell value to the setCellValue() function to store the given 
        // cell value at the specified cell position
        cell.setCellValue("SheetsTips");

        // Get the index value of row of the given cell using the getRowIndex() function
        int rowIndex = cell.getRowIndex();
        // Get the index value of column of the given cell using the getColumnIndex() function
        int columnIndex = cell.getColumnIndex();

        // Write the data to the above Workbook at a specified cell location
        // using the write() function
        workbookObj.write(outputExcelFile);

        // Print the row and column index of created cell at the specified position
        System.out.println("The new cell is created at the postion"
                        + "(" + rowIndex + ","
                        + columnIndex + ")");
    }
}

Output:

Creating a cell at the specific position

Leave a Comment