DEV Community

loading...

How to Delete Excel Rows and Columns in Java

eiceblue profile image E-iceblue Product Family ・3 min read

You may have encountered the situations where you need to remove the blank rows/columns from a worksheet, or delete rows/columns containing specific keywords. In this article, I will show you how to delete a specified row/column and how to batch delete some peculiar rows/columns by using Free Spire.XLS for Java.

Add Spire.Xls.jar as dependency

Method 1: Download Free Spire.XLS for Java pack, unzip it and you’ll get Spire.Doc.jar file from the “lib” folder. Import the jar file in your project as a denpendency.

Method 2: If you are creating a Maven project, you can easily add the jar dependency by adding the following configurations to the pom.xml.

<repositories>
        <repository>
            <id>com.e-iceblue</id>
            <name>e-iceblue</name>
            <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>
        </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId> e-iceblue </groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>

Example 1. Delete specified rows and columns

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class DeleteSpecifiedRowsAndColumns {

    public static void main(String[] args) {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\test1.xlsx");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Delete specific row by index
        sheet.deleteRow(4);

        //Delete specific column by index
        sheet.deleteColumn(5);

        //Save the file
        wb.saveToFile("DeleteSpecificRowsAndColumns.xlsx", ExcelVersion.Version2016);
    }
}

Output
Alt Text

Example 2. Delete blank rows and columns

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

public class DeleteBlankRowsAndColumns {

    public static void main(String[] args) {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\test2.xlsx");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Loop through the rows
        for (int i = sheet.getLastRow(); i >= 1; i--)
        {
            //Detect if a row is blank
            if (sheet.getRows()[i-1].isBlank())
            {
                //Remove blank row
                sheet.deleteRow(i);
            }
        }

        //Loop through the columns
        for (int j = sheet.getLastColumn(); j >= 1; j--)
        {
            //Detect if a column is blank
            if (sheet.getColumns()[j-1].isBlank())
            {
                //Remove blank column
                sheet.deleteColumn(j);
            }
        }

        //Save the file
        wb.saveToFile("DeleteBlankRowsAndColumns.xlsx", ExcelVersion.Version2016);
    }
}

Output
Alt Text

Example 3. Delete rows containing keywords

import com.spire.xls.*;
import java.util.ArrayList;
import java.util.List;

public class DeleteRowsByKeyword {

    public static void main(String[] args) {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("C:\\Users\\Administrator\\Desktop\\test3.xlsx");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Call getRowList method to get the rows containing the keyword
        List<Integer> list = getRowList(sheet,"Hello World");

        //Loop through the list
        for (int i = 0; i < list.size(); i++) {

            //Delete the specified row
            sheet.deleteRow(list.get(i));
        }

        //Save the file
        wb.saveToFile("DeleteRowByKeywords.xlsx",ExcelVersion.Version2016);
    }

    //Create getRowList method to get the numbers of the rows containing keyword
    static List<Integer> getRowList(Worksheet sheet, String keyword) {

        int rowNum;
        List<Integer> rowList = new ArrayList<>();
        for (int i = sheet.getLastRow(); i >= 1; i--) {
            rowNum = i;
            for (int j = sheet.getLastColumn(); j >= 1; j--) {

                if (sheet.get(i, j).getText().contains(keyword)) {

                    rowList.add(rowNum);
                    break;
                }
            }
        }
        return rowList;
    }
}

Output
Alt Text

Discussion (0)

pic
Editor guide