Java Add Sheet to Existing Excel File using Apache POI

In this Java Apache POI tutorial we learn how to add a new Excel sheet to an existing Excel file in Java using the Apache POI library.

Table of contents

Add Apache POI dependencies to Java project
Example Excel File to Add New Sheet
How to add new sheet to existing Excel File in Java
How to use ExcelFileService class

Add Apache POI dependencies to Java project

If you use Gradle build project, add the following dependencies to the build.gradle file.

implementation group: 'org.apache.poi', name: 'poi', version: '5.2.2'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.2'

If you use Maven build project, add the following dependencies to the pom.xml file.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
</dependency>

Example Excel File to Add New Sheet

For example, we have an Excel with one sheet located at D:\SimpleSolution\Data\Data.xlsx as the following screenshot.

Java Add Sheet to Existing Excel File using Apache POI

How to add new sheet to existing Excel File in Java

In Java, with a given Excel file we can follow these steps using Apache POI API to add a new sheet.

Step 1: use the WorkbookFactory.create(InputStream inp) method to open Excel workbook using FileInputStream object and return Workbook object.
Step 2: use the Workbook.createSheet(String sheetname) method to add a new sheet and return Sheet object.
Step 3: use the Workbook.write(OutputStream stream) method to write the updated Excel file using FileOutputStream.

ExcelFileService.java

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;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.IOException;
import java.io.OutputStream;

public class ExcelFileService {

    /**
     * This method to add one sheet to an Excel file
     * @param filePath the Excel file to add sheet
     * @param newSheetName the new sheet name
     */
    public void addNewSheet(String filePath, String newSheetName) {
        InputStream inputStream = null;
        Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            workbook = WorkbookFactory.create(inputStream);
            // create a new sheet with given name
            Sheet sheet = workbook.createSheet(newSheetName);

            // Create a sample cell
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("Test New Cell");

            // Write updated Excel file
            outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
                workbook.close();
                outputStream.close();
            }catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }
}

How to use ExcelFileService class

At this step we use the above ExcelFileService class in Java program to add a new sheet to existing Excel file.

AddSheetsToExcelFileExample1.java

public class AddSheetsToExcelFileExample1 {
    public static void main(String... args) {
        // Existing Excel file to add more sheet
        String filePath = "D:\\SimpleSolution\\Data\\Data.xlsx";

        // Add one more sheet to Excel file
        ExcelFileService excelFileService = new ExcelFileService();
        excelFileService.addNewSheet(filePath, "Sample Sheet 2");
    }
}