Introduction

In this tutorial I am going to show you how to remove value from a row or remove a row completely from the excel file using apache poi library. The Apache POI is used with Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2).

You can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is the Java Excel solution (for Excel 97-2008).

Prerequisites

Java 8+, maven 3.6.3 – 3.8.5, gradle 6.5.1, Apache POI 3.15 or 4.1.1 or 4.1.2/5.2.2

Project Setup

You need to create a maven or gradle project in your favorite IDE or tool. The name of the project is apache-poi-excel-remove-row-value.

Here I will add apache poi API as a dependency for working with Microsoft word document or even you can work with open source word document.

If you are creating gradle based project then you can use below build.gradle script:

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:4.1.2'
    
    //required only for jdk 9 or above
    implementation('com.fasterxml.jackson.core:jackson-databind:2.11.2')
}

If you are creating maven based project then you can use below pom.xml file:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.roytuts</groupId>
	<artifactId>apache-poi-excel-remove-row-value</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>at least 1.8</java.version>
		<poi.version>3.15 or 4.1.1 or 4.1.2 or 5.2.2</poi.version>
	</properties>
	
	<dependencies>
		<!-- apache poi for xlsx, docx etc reading/writing -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>${poi.version}</version>
		</dependency>
		
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.11.2</version>
		</dependency>
	</dependencies>
	
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
                <configuration>
					<source>${java.version}</source>
					<target>${java.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Remove Value or Remove Row

In the following code example I am showing how to remove value from a row or remove a row completely.

First I am creating five rows having each row two columns. Then I am removing the third row using the following line of code. In this case the value will be removed from the row but the row will be there as a blank or empty row.

sheet.removeRow(sheet.getRow(2));

If you want to completely remove a row rather keeping it as an empty or blank, for example, if you want to remove third row then you can use sheet.shiftRows(3, totalRows, -1);

package com.roytuts.apache.poi.excel.remove.row.value;

import java.io.FileNotFoundException;
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;

public class ExcelRowValueRemoverApp {

	public static void main(String[] args) {
		final String fileName = "workbook-remove-value.xlsx";//"workbook-remove-row.xlsx";
		removeRowValueFromExcel(fileName);
	}

	public static void removeRowValueFromExcel(final String fileName) {
		// get the file extension
		String fileExt = PoiUtils.getFileExtension(fileName);
		Workbook workbook = null;

		// based on file extension create Workbook object
		if (".xls".equalsIgnoreCase(fileExt)) {
			workbook = PoiUtils.getHSSFWorkbook();
		} else if (".xlsx".equalsIgnoreCase(fileExt)) {
			workbook = PoiUtils.getXSSFWorkbook();
		}

		// create Sheet object
		// sheet name must not exceed 31 characters
		// the name must not contain 0x0000, 0x0003, colon(:), backslash(\),
		// asterisk(*), question mark(?), forward slash(/), opening square
		// bracket([), closing square bracket(])
		Sheet sheet = workbook.createSheet("my_sheet");

		// set column width for two columns
		sheet.setColumnWidth(0, 9000);
		sheet.setColumnWidth(1, 9000);

		// Create five rows and two columns
		for (int i = 0; i < 5; i++) {
			Row row = sheet.createRow((short) i);
			for (int j = 0; j < 2; j++) {
				row.createCell(j).setCellValue("row : " + i + ", column : " + j);
			}
		}

		// total no. of rows
		int totalRows = sheet.getLastRowNum();
		System.out.println("Total no of rows : " + totalRows);

		// remove values from third row but keep third row blank
		if (sheet.getRow(2) != null) {
			sheet.removeRow(sheet.getRow(2));
		}

		// remove third row completely - 2 for third row and +1; 2+1=3
		// sheet.shiftRows(3, totalRows, -1);

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(fileName);
			workbook.write(fileOut);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				fileOut.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

}

Testing Remove Value or Row

Executing the above code for two conditions will give you the following outputs.

When you remove value from the row and keep it as a blank or empty row.

When you remove a row completely, you will see the following output:

Source Code

Download