If you need to add new data or delete existing
data in an Excel worksheet, you can insert or delete rows or columns in the worksheet.
Besides, you will have a cleaner spreadsheet without deleting data you might
need later by hiding rows and columns. In this article, I’ll show you how to
insert, hide and delete Excel rows and columns by using Free Spire.XLS for
Java.
ADD JAR TO PROJECT
Download the latest version of Free Spire.XLS forJava, unzip it and add Spire.Xls.jar located in the “lib” folder to your Java
project as a dependency.
Of course, if you are using maven, you need to add the
following code to your project’s pom.xml file.
<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>
Using the code
1. Insert Rows and Columns in Excel
By using the InsertRow and InsertColumn methods, we can insert one or several rows and
columns in Excel workbook. Below are the code snippets.
import com.spire.xls.*;
public class AddRowsAndColumns {
public static void main(String[] args) {
//Load the sample Excel file
Workbook workbook = new Workbook();
workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Insert a row into the worksheet.
worksheet.insertRow(2);
//Insert a column into the worksheet.
worksheet.insertColumn(2);
//Insert multiple rows into the worksheet.
worksheet.insertRow(5, 2);
//Insert multiple columns into the worksheet.
worksheet.insertColumn(5, 2);
//Save to file.
workbook.saveToFile("output/InsertRowsAndColumns.xlsx", ExcelVersion.Version2013);
}
}
Effective screenshot after inserting the empty rows and columns:
2.Hide Rows and Columns in Java
import com.spire.xls.*;
public class HideRowsAndColumns {
public static void main(String[] args) {
//Load the sample Excel file
Workbook workbook = new Workbook();
workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\InsertRowsAndColumns.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Hide the column of the worksheet.
worksheet.hideColumn(2);
//Hide the row of the worksheet
worksheet.hideRow(3);
//Save to file.
workbook.saveToFile("output/HideRowAndColumn.xlsx", ExcelVersion.Version2013);
}
}
Effective screenshot after hiding the second column and the third row:
3. Delete Rows and Columns in Excel
import com.spire.xls.*;
public class DeleteRowsAndColumns {
public static void main(String[] args) {
//Load the sample Excel file
Workbook workbook = new Workbook();
workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\InsertRowsAndColumns.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
//Delete a row from the worksheet.
worksheet.deleteRow(2);
//Delete multiple columns from the worksheet.
worksheet.deleteColumn(5, 2);
//Save to file.
workbook.saveToFile("output/DeleteRowAndColumn.xlsx", ExcelVersion.Version2013);
}
}
Effective screenshot after deleting the second row and the fifth and sixth columns:
