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
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
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
Top comments (0)