Worksheets with a lot of complex and detailed information are difficult to read and analyze. To create a more compact and understandable spreadsheet view, you can organize data in groups and collapse the rows with similar content.
This article demonstrates how to programmatically group or ungroup rows and columns in Excel using Spire.XLS for Java.
Install Spire.XLS for Java
First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls</artifactId>
<version>14.4.1</version>
</dependency>
</dependencies>
Group Rows and Columns in Java
The following are the steps to group rows and columns using Spire.XLS for Java.
- Create a Workbook object.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Get the specific sheet using Workbook.getWorksheets().get() method.
- Group rows using Worksheet.groupByRows() method.
- Group columns using Worksheet.groupByColumns() method.
- Save the result to another Excel file using Workbook.saveToFile() method.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class GroupRowsAndColumns {
public static void main(String[] args) {
//Create a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel file
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Group rows
sheet.groupByRows(2, 5, false);
sheet.groupByRows(7, 10, false);
//Group columns
sheet.groupByColumns(5, 6, false);
//Save to another Excel file
workbook.saveToFile("GroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
}
}
Ungroup Rows and Columns in Java
The following are the steps to ungroup rows and columns using Spire.XLS for Java.
- Create a Workbook object.
- Load a sample Excel file using Workbook.loadFromFile() method.
- Get the specific sheet using Workbook.getWorksheets().get() method.
- Ungroup rows using Worksheet.ungroupByRows() method.
- Ungroup columns using Worksheet.ungroupByColumns() method.
- Save the result to another Excel file using Workbook.saveToFile() method.
- Java
import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class UngroupRowsAndColumns {
public static void main(String[] args) {
//Create a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel file
workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\ample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Ungroup rows
sheet.ungroupByRows(2, 5);
sheet.ungroupByRows(7, 10);
//Ungroup columns
sheet.ungroupByColumns(5, 6);
//Save to a different Excel file
workbook.saveToFile("UngroupRowsAndColumns.xlsx", ExcelVersion.Version2016);
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
