Java Remove Sheet from Existing Excel File using Apache POI

In this Java Apache POI tutorial we learn how to remove an Excel sheet from an existing Excel file in Java program using the Apache POI library.

Table of contents

Add Apache POI dependencies to Java project
Sample Excel File to Remove Sheet
How to Remove a Sheet from Existing Excel File in Java
How to use the 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>

Sample Excel File to Remove Sheet

For example, we have an Excel file with two sheets located at D:\SimpleSolution\Data\Data.xlsx as the screenshot below.

Java Remove Sheet from Existing Excel File using Apache POI

How to Remove a Sheet from Existing Excel File in Java

In Java, with a given Excel file we can follow these step using the Apache POI API to remove a Sheet at specified index.

Step 1: use the WorkbookFactory.create(InputStream inp) method to open an Excel Workbook using FileInputStream.
Step 2: use the Workbook.removeSheetAt(int index) method to remove a sheet at specified index.
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.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 remove a sheet at specified index from an existing Excel file
     * @param filePath file path of an existing Excel file
     * @param sheetIndex sheet index to remove
     */
    public void removeSheet(String filePath, int sheetIndex) {
        InputStream inputStream = null;
        Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            workbook = WorkbookFactory.create(inputStream);

            // Remove sheet at specified index
            workbook.removeSheetAt(sheetIndex);

            // 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 the ExcelFileService class

At this step we use the ExcelFileService class above in Java program to remove the first sheet of sample Excel file.

RemoveSheetFromExcelFileExample1.java

public class RemoveSheetFromExcelFileExample1 {
    public static void main(String... args) {
        // Existing Excel file to add remove sheet
        String filePath = "D:\\SimpleSolution\\Data\\Data.xlsx";
        // Excel sheet index to be removed
        int sheetIndexToRemove = 0;

        // Remove one sheet from Excel file
        ExcelFileService excelFileService = new ExcelFileService();
        excelFileService.removeSheet(filePath, sheetIndexToRemove);
    }
}