Java Program to Create blank Excel Sheet

Java Program to Create blank Excel Sheet

by

In this article, let us see how to create a blank excel sheet using java. Here we make use of the Apache POI library in Java for this purpose.

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.

org.apache.poi.xssf.usermodel package:

To create and deal with blank Excel documents and files, Java includes the package ‘org.apache.poi.xssf.usermodel’. The class XSSFWorkbook in this package can be used to create and process blank spreadsheet workbooks. The class also includes functions for reading, writing, and working with new and existing sheets. The File and FIleOutputStream packages are also required to create a file and subsequently open a connection. It also makes it easier to modify the contents of the file in Java, such as appending, deleting, and so on.

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.

Java Program to Create blank Excel Sheet

Here we use STS(Spring Tool Suite) to create a blank 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 CreateBlankExcelFile 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 CreateBlankExcelClass in the src/main/java folder with the same package com.sheetstips.

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

Approach:

  • Import Excel interface using the import keyword
  • Import generic java libraries using the import keyword
  • Import File libraries using the import keyword
  • Import apache poi using the import keyword
  • Create a class say CreateBlankExcel
  • Inside the Main driver method, Create a new HSSFWorkbook object
  • Create a Spreadsheet by creating an object of XSSFSheet by passing the sheet name as an argument(Sheet1 is the default)
  • Give the output file path where the file must be saved and store it in a variable.
  • Create an object for the FileOutputStream by passing the above output excel file name as an argument to it to place the output file in that location
  • Write to workbook using the write() function
  • Close the output file using the close() function
  • Print some random text for acknowledgment.
  • The Exit of the Program

Below is the implementation:

// Import Excel interface using the import keyword
// Import generic java libraries using the import keyword
import java.io.File;
// Import File libraries using the import keyword
import java.io.FileOutputStream;
// Import apache poi using the import keyword
import org.apache.poi.xssf.usermodel.*;

// Create a class say CreateBlankExcel
public class CreateBlankExcel {

    // Main driver method
    public static void main(String[] args) throws Exception
    {
        // Create a new HSSFWorkbook object
        XSSFWorkbook workbook = new XSSFWorkbook();

        // Create a Spreadsheet by creating an object of
        // XSSFSheet by passing the sheet name as an agument(Sheet1 is the default)
        XSSFSheet spreadsheet
            = workbook.createSheet("Sheet1");

        // Give the output file path where the file must be saved and
        // store it in a variable
        String outputFilePath = "blankExcelFile.xlsx";
        
        // Create an object for the FileOutputStream by passing the above output excel file
        // name as an argument to it to place the output file in that location
        FileOutputStream outputfile
            = new FileOutputStream(outputFilePath);

        // Write to workbook using the write() function
        workbook.write(outputfile);

        // Close the output file using the close() function
        outputfile.close();

        // Print some random message for acknowledgment.
        System.out.println(
            "The blankExcelFile.xlsx file is created successfully!!!");
    }
}

Step#6: Now run the code by Right-clicking on the project and click on run –> Run as spring boot App

Output:

The blankExcelFile.xlsx file is created successfully!!!

creating blank excel sheet using apache poi

Step#7: Now when we refresh the project we can observe that a new excel file with the name blankExcelFile.xlsx is created which is the output file that we want.

Recommended Reading On: Java Program to Print Happy Birthday on Your Birthday

Leave a Comment