How to Create a new Workbook POI Example

Microsoft Excel is a widely used spreadsheet application for creating, managing, and analyzing data. In Java, Apache POI (Poor Obfuscation Implementation) provides a powerful library for working with Microsoft Office documents, including Excel. In this article, we’ll explore how to create a new workbook in Excel in Java using Apache POI with a step-by-step example.

Apache POI Overview

Apache POI is a set of Java APIs for reading and writing Microsoft Office files, including Excel. It supports both the older .xls format and the newer .xlsx format. In this example, we’ll focus on creating a new Excel workbook in the .xlsx format.

Setting Up the Project

Before we start, make sure you have Apache POI added to your project. You can include the necessary dependencies in your build tool (e.g., Maven or Gradle). For Maven, add the following dependency:

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

For Gradle, add the following to your dependencies:

implementation 'org.apache.poi:poi:5.1.0'
implementation 'org.apache.poi:poi-ooxml:5.1.0'

These lines specify the dependencies for the poi and poi-ooxml artifacts with version 5.1.0. Make sure to sync your Gradle project after adding these dependencies to download and include the Apache POI library in your project.

How to Create an Excel XLSX File in Java?

In this example, we’ll explore how to use Apache POI XSSF to create XLSX files in our Java application. We assume you’ve already installed and set up the Apache POI API in your Java application. If you haven’t done that yet, you can check out the Download and Install Apache POI in the Eclipse guide and follow the steps below.

Steps to Create Excel Workbook using Apache POI

Creating an Excel Workbook with Apache POI API in Java is a straightforward process. You can accomplish it by following these steps:

1. Create a workbook using the XSSFWorkbook class
2. Close the output stream.

Below is the Java code for creating an Excel Workbook using Apache POI.

XSSFWorkbook wb = new XSSFWorkbook();
...
try (FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
    wb.write(fileOut);
}

Creating an Excel Workbook Complete Example

A complete example of creating a workbook using XSSFWorkbook is shown below.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWorkbookExample {

    public static void main(String[] args) {
        // Create a new Excel workbook
        try (Workbook workbook = new XSSFWorkbook()) {
            // Create a sheet in the workbook
            Sheet sheet = workbook.createSheet("ExampleSheet");

            // Create a row in the sheet
            Row row = sheet.createRow(0);

            // Create cells in the row and set values
            Cell cell1 = row.createCell(0);
            cell1.setCellValue("Name");

            Cell cell2 = row.createCell(1);
            cell2.setCellValue("Age");

            // Create another row
            Row row2 = sheet.createRow(1);

            // Set values in the second row
            row2.createCell(0).setCellValue("John Doe");
            row2.createCell(1).setCellValue(25);

            // Write the workbook content to a file
            try (FileOutputStream fileOut = new FileOutputStream("workbook_example.xlsx")) {
                workbook.write(fileOut);
                System.out.println("Excel workbook created successfully!");
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Code Explanation:

• We import the necessary classes from the Apache POI library.
• We create a new instance of XSSFWorkbook, representing an Excel workbook in the .xlsx format.
• We create a sheet within the workbook using createSheet("ExampleSheet").
• We create a row in the sheet using createRow(0).
• We create cells within the row and set values.
• We repeat the process for additional rows and cells.
• Finally, we write the workbook content into a file using FileOutputStream.

Running the Example

When you run the example, a file named “workbook_example.xlsx” will be created in the project directory. Open the file using Microsoft Excel or any compatible spreadsheet software to see the generated workbook.

Conclusion

Creating a new workbook in Excel in Java using Apache POI is a straightforward process. You can further customize your workbook by adding formatting, formulas, and additional sheets to suit your specific requirements.

Read also: Apache POI Multiple Styles to Excel Cell

Related Articles:

• Apache POI – Read and Write Excel files in java
• How to create password-protected Excel in java?
• How to Read password-protected Excel in java?
• How to write data to an existing Excel file in java?