In data processing workflows, Excel files frequently accumulate unnecessary empty rows and columns due to data modification or extraction. This guide demonstrates how to utilize the Free Spire.XLS for Java library to identify and remove these blank elements programmatically, ensuring cleaner datasets for analysis.

Dependency Configuration

To integrate the library into your project, you can manually add the JAR file or configure the Maven repository. Below is the required configuration for your pom.xml file:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <url>http://repo.e-iceblue.cn/repository/maven-public/</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls.free</artifactId>
        <version>2.2.0</version>
    </dependency>
</dependencies>

Java Implementation

The following implemantation illustrates the logic for loading a workbook, accessing the target worksheet, and iterating through the cells to remove empty structures. It is crucial to iterate backward (from the last row/column to the first) when deleting items; this prevents index shifting errors that often occur when removing elements sequentially from the beginning of a collection.

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

public class ExcelDataCleaner {

    public static void main(String[] args) {
        // Define input and output file paths
        String inputFile = "SampleData.xlsx";
        String outputFile = "CleanedData.xlsx";

        // Initialize the workbook and load the source file
        Workbook workbook = new Workbook();
        workbook.loadFromFile(inputFile);

        // Access the first worksheet in the workbook
        Worksheet currentSheet = workbook.getWorksheets().get(0);

        // Execute cleanup operations
        eliminateEmptyRows(currentSheet);
        eliminateEmptyColumns(currentSheet);

        // Save the modified workbook
        workbook.saveToFile(outputFile, ExcelVersion.Version2016);
    }

    private static void eliminateEmptyRows(Worksheet sheet) {
        // Iterate from the last row index down to the first
        int rowCount = sheet.getLastRow();
        for (int i = rowCount; i >= 1; i--) {
            // Check if the row is entirely blank
            if (sheet.getRows()[i - 1].isBlank()) {
                sheet.deleteRow(i);
            }
        }
    }

    private static void eliminateEmptyColumns(Worksheet sheet) {
        // Iterate from the last column index down to the first
        int colCount = sheet.getLastColumn();
        for (int j = colCount; j >= 1; j--) {
            // Check if the column is entirely blank
            if (sheet.getColumns()[j - 1].isBlank()) {
                sheet.deleteColumn(j);
            }
        }
    }
}