How to Permanently Delete Rows in Excel Using Java?

Introduction If you're working with Excel files in Java and need to permanently delete rows based on specific criteria, you're in the right place. This article will guide you through the process of identifying the last column's cell values in an Excel sheet and deleting any rows that contain 'Not Valid' or 'Not Applicable'. Whether you’re using Apache POI for reading and writing Excel files, navigating through rows, or cleaning up your datasets, we'll cover essential code snippets and explanations. Understanding the Problem When handling Excel spreadsheets, it’s common to encounter rows that don’t meet specific data validation rules. In your case, you need to check the last cell of each row for the values 'Not Valid' or 'Not Applicable'. However, if you use methods like sheet.removeRow(row) or row.setZeroHeight(true), you might only be hiding the rows or setting their height to zero instead of deleting them entirely. To fulfill your requirement of permanent deletion, we’ll need to adjust our approach. Step-by-Step Solution Here’s a revised version of your code that properly removes the rows from the Excel sheet: 1. Setup Dependencies First, ensure that you have Apache POI in your project to manipulate Excel files. If you're using Maven, include the following dependencies in your pom.xml: org.apache.poi poi 5.2.3 org.apache.poi poi-ooxml 5.2.3 2. Java Code to Delete Rows Here’s the complete Java code snippet that addresses your requirement: import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; 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; public class Column { public static void main(String[] args) { try { Workbook wb = WorkbookFactory.create(new FileInputStream("C:/Users/Excel1.xlsx")); Sheet sheet = wb.getSheet("Retail-All"); int lastRowNum = sheet.getLastRowNum(); // Loop backwards to avoid indexing issues when deleting rows for (int i = lastRowNum; i >= 0; i--) { Row row = sheet.getRow(i); if (row != null && row.getLastCellNum() > 0) { String lastCellValue = row.getCell(row.getLastCellNum() - 1).toString(); if (lastCellValue.equalsIgnoreCase("Not Valid") || lastCellValue.equalsIgnoreCase("Not Applicable")) { sheet.removeRow(row); // Optionally shift rows up if you want to fill the gap // sheet.shiftRows(i + 1, lastRowNum, -1); } } } // Write changes to a new file FileOutputStream fileOut = new FileOutputStream("C:/Users/shiftRows.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); } catch (Exception e) { e.printStackTrace(); } } } 3. Key Points in the Code Looping Backwards: When deleting rows in a loop, always iterate from the bottom of the sheet to the top. This approach prevents skipping rows when one is deleted. Cell Value Checking: Ensure that the relevant cell is not null and has content by using row.getLastCellNum() > 0. This ensures you're working with valid rows. Removing Rows: Use sheet.removeRow(row) to permanently delete the entire row from the Excel sheet. Unlike setting row height, this action removes the row from the file. Frequently Asked Questions (FAQ) What happens to the rows once they are removed? Once you remove a row using sheet.removeRow(row), it is permanently deleted from the sheet. If you save the file, there won't be any record of the deleted rows. Can I undo the deletion? No, once the rows are deleted and the workbook is saved, the deletion cannot be undone. Always keep a backup of your Excel files before performing batch deletions. How can I verify if the rows have been deleted? You can open the saved file (shiftRows.xlsx) and check if the rows containing 'Not Valid' or 'Not Applicable' have been removed. Conclusion Manipulating Excel files to delete unwanted rows might seem daunting at first, but with Apache POI, it becomes manageable. This guide provided a detailed explanation and example code to help you permanently delete rows based on the criteria specified. Remember to test your implementation on sample data, and always keep backups for safety!

May 11, 2025 - 04:26

How to Permanently Delete Rows in Excel Using Java?

Introduction

If you're working with Excel files in Java and need to permanently delete rows based on specific criteria, you're in the right place. This article will guide you through the process of identifying the last column's cell values in an Excel sheet and deleting any rows that contain 'Not Valid' or 'Not Applicable'. Whether you’re using Apache POI for reading and writing Excel files, navigating through rows, or cleaning up your datasets, we'll cover essential code snippets and explanations.

Understanding the Problem

When handling Excel spreadsheets, it’s common to encounter rows that don’t meet specific data validation rules. In your case, you need to check the last cell of each row for the values 'Not Valid' or 'Not Applicable'. However, if you use methods like sheet.removeRow(row) or row.setZeroHeight(true), you might only be hiding the rows or setting their height to zero instead of deleting them entirely. To fulfill your requirement of permanent deletion, we’ll need to adjust our approach.

Step-by-Step Solution

Here’s a revised version of your code that properly removes the rows from the Excel sheet:

1. Setup Dependencies

First, ensure that you have Apache POI in your project to manipulate Excel files. If you're using Maven, include the following dependencies in your pom.xml:


    org.apache.poi
    poi
    5.2.3


    org.apache.poi
    poi-ooxml
    5.2.3

2. Java Code to Delete Rows

Here’s the complete Java code snippet that addresses your requirement:

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

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;

public class Column {
    public static void main(String[] args) {
        try {
            Workbook wb = WorkbookFactory.create(new FileInputStream("C:/Users/Excel1.xlsx"));
            Sheet sheet = wb.getSheet("Retail-All");

            int lastRowNum = sheet.getLastRowNum();

            // Loop backwards to avoid indexing issues when deleting rows
            for (int i = lastRowNum; i >= 0; i--) {
                Row row = sheet.getRow(i);
                if (row != null && row.getLastCellNum() > 0) {
                    String lastCellValue = row.getCell(row.getLastCellNum() - 1).toString();
                    if (lastCellValue.equalsIgnoreCase("Not Valid") || lastCellValue.equalsIgnoreCase("Not Applicable")) {
                        sheet.removeRow(row);
                        // Optionally shift rows up if you want to fill the gap
                        // sheet.shiftRows(i + 1, lastRowNum, -1);
                    }
                }
            }
            // Write changes to a new file
            FileOutputStream fileOut = new FileOutputStream("C:/Users/shiftRows.xlsx");
            wb.write(fileOut);
            fileOut.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3. Key Points in the Code

Looping Backwards: When deleting rows in a loop, always iterate from the bottom of the sheet to the top. This approach prevents skipping rows when one is deleted.
Cell Value Checking: Ensure that the relevant cell is not null and has content by using row.getLastCellNum() > 0. This ensures you're working with valid rows.
Removing Rows: Use sheet.removeRow(row) to permanently delete the entire row from the Excel sheet. Unlike setting row height, this action removes the row from the file.

Frequently Asked Questions (FAQ)

What happens to the rows once they are removed?

Once you remove a row using sheet.removeRow(row), it is permanently deleted from the sheet. If you save the file, there won't be any record of the deleted rows.

Can I undo the deletion?

No, once the rows are deleted and the workbook is saved, the deletion cannot be undone. Always keep a backup of your Excel files before performing batch deletions.

How can I verify if the rows have been deleted?

You can open the saved file (shiftRows.xlsx) and check if the rows containing 'Not Valid' or 'Not Applicable' have been removed.

Conclusion

Manipulating Excel files to delete unwanted rows might seem daunting at first, but with Apache POI, it becomes manageable. This guide provided a detailed explanation and example code to help you permanently delete rows based on the criteria specified. Remember to test your implementation on sample data, and always keep backups for safety!