# How to Work with Excel Rows and Columns in Java

In daily development, scenarios such as report exporting, batch data formatting, and template modifications often require dynamically inserting or deleting rows and columns in Excel spreadsheets. This article will introduce how to manipulate rows and columns in Excel files using Java, including adding, deleting, grouping, and copying rows and columns, in both .xls and .xlsx formats, with complete runnable examples included.

## Environment Setup

This article uses **Spire.XLS for Java**, a specialized library for Excel file manipulation in Java. You can install it from Maven by adding the following command in your pom.xml file:

### Maven Dependency

Add this to your `pom.xml`:

```xml
<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <url>https://repo.e-iceblue.com/repository/maven-public/</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>14.12.0</version>
    </dependency>
</dependencies>
```

## 1\. Inserting Rows and Columns

The most basic operation is inserting rows and columns. Here's how to insert a single row at position 2:

```java
import com.spire.xls.*;

public class InsertRowsColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");
        
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        // Insert a row at position 2
        worksheet.insertRow(2);
        
        // Insert a column at position 2
        worksheet.insertColumn(2);
        
        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}
```

**Batch insertion** is just as easy—use the second parameter to specify how many to insert:

```java
// Insert 2 rows starting from row 5
worksheet.insertRow(5, 2);

// Insert 2 columns starting from column 5
worksheet.insertColumn(5, 2);
```

**Real-world use case**: I often use this when generating reports dynamically. For example, if I have 10 records, I insert 10 rows after the header.

## 2\. Deleting Rows and Columns

Deleting works similarly to inserting, but remember: **deleting shifts subsequent rows/columns forward**.

```java
import com.spire.xls.*;

public class DeleteRowsColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");
        
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        // Delete 4 rows starting from row 5 (deletes rows 5, 6, 7, 8)
        worksheet.deleteRow(5, 4);
        
        // Delete 2 columns starting from column 2 (deletes columns 2, 3)
        worksheet.deleteColumn(2, 2);
        
        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}
```

**Common pitfall**: When deleting multiple rows in a loop, always iterate **backwards**, otherwise you'll skip rows or delete the wrong ones due to index shifting.

## 3\. Deleting Blank Rows and Columns

This is super useful! Ever received an Excel export with tons of blank rows? Manually deleting them is a pain.

```java
import com.spire.xls.*;

public class DeleteBlankRows {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("messy_data.xlsx");
        
        Worksheet sheet = workbook.getWorksheets().get(0);
        
        // Delete blank rows (iterate backwards)
        for (int i = sheet.getRows().length - 1; i >= 0; i--) {
            if (sheet.getRows()[i].isBlank()) {
                sheet.deleteRow(i + 1);  // Note: row numbers start from 1
            }
        }
        
        // Delete blank columns (iterate backwards)
        for (int j = sheet.getColumns().length - 1; j >= 0; j--) {
            if (sheet.getColumns()[j].isBlank()) {
                sheet.deleteColumn(j + 1);  // Note: column numbers start from 1
            }
        }
        
        workbook.saveToFile("cleaned.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}
```

**Why iterate backwards?** Because when you delete a row, all subsequent row numbers shift. If you iterate forward, you might skip rows or delete the wrong ones.

**Use cases**: Cleaning up database exports, removing placeholder rows from templates, etc.

## 4\. Copying Rows and Columns

Sometimes you need to copy rows within the same sheet or across different sheets.

### Copying Within the Same Sheet

```java
import com.spire.xls.*;

public class CopyRows {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");
        
        Worksheet sheet = workbook.getWorksheets().get(0);
        
        // Copy row 1 to row 3
        // Parameters: source row, target row, copy values, copy formatting, copy formulas
        sheet.copy(sheet.getRows()[0], sheet.getRows()[2], true, true, true);
        
        workbook.saveToFile("result.xlsx", ExcelVersion.Version2010);
        workbook.dispose();
    }
}
```

### Copying Across Sheets

```java
Worksheet sheet1 = workbook.getWorksheets().get(0);
Worksheet sheet2 = workbook.getWorksheets().get(1);

// Copy row 1 from Sheet1 to row 2 in Sheet2
sheet1.copy(sheet1.getRows()[0], sheet2.getRows()[1], true, true, true);
```

**The three boolean parameters**:

*   First: whether to copy cell values
    
*   Second: whether to copy formatting (fonts, colors, etc.)
    
*   Third: whether to copy formulas
    

**Practical use**: I frequently use this to copy headers to multiple sheets or generate data rows from a template row.

## 5\. Hiding and Showing Rows/Columns

Sometimes you want to hide sensitive data without deleting it:

```java
import com.spire.xls.*;

public class HideRowsColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");
        
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        // Hide column 2
        worksheet.hideColumn(2);
        
        // Hide row 4
        worksheet.hideRow(4);
        
        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}
```

**To unhide**:

```java
// Show column 2
worksheet.showColumn(2);

// Show row 4
worksheet.showRow(4);
```

**Use cases**: Hiding intermediate calculations in financial reports, protecting sensitive information, etc.

## 6\. Setting Row Height and Column Width

Default dimensions might not fit your content. Here's how to customize them:

```java
import com.spire.xls.*;

public class SetHeightWidth {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");
        
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        // Set column 4 width to 30
        worksheet.setColumnWidth(4, 30);
        
        // Set row 4 height to 30
        worksheet.setRowHeight(4, 30);
        
        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}
```

**Setting default dimensions for all rows/columns**:

```java
// Set default row height for all rows
worksheet.setDefaultRowHeight(20);

// Set default column width for all columns
worksheet.setDefaultColumnWidth(15);
```

**Pro tip**: Chinese content usually needs wider columns than English. I typically preset different widths based on content type.

## 7\. Auto-Fitting Column Width

Not sure how wide to make columns? Let Excel figure it out:

```java
import com.spire.xls.*;

public class AutoFitColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");
        
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        // Auto-fit all columns in the used range
        worksheet.getAllocatedRange().autoFitColumns();
        
        // Or auto-fit specific columns
        // worksheet.getCellRange("A1:C10").autoFitColumns();
        
        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}
```

**Performance note**: Auto-fitting scans all cells, so it can be slow with large datasets. If performance matters, consider setting fixed widths instead.

## 8\. Grouping Rows and Columns (Outline Feature)

Excel's outline feature lets you collapse related rows/columns—perfect for hierarchical data:

```java
import com.spire.xls.*;

public class GroupRowsColumns {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");
        
        Worksheet sheet = workbook.getWorksheets().get(0);
        
        // Group rows 1 to 5 (can be collapsed)
        // Third parameter: whether to show summary below
        sheet.groupByRows(1, 5, false);
        
        // Group columns 1 to 3
        sheet.groupByColumns(1, 3, false);
        
        workbook.saveToFile("result.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}
```

**Real-world scenarios**: Grouping financial data by month, organizing project plans by phases, etc. Users can expand or collapse to view data at different granularity levels.

## 9\. Conditional Row Deletion

Sometimes you need to filter and delete rows based on content, like removing rows containing specific keywords:

```java
import com.spire.xls.*;

public class RemoveRowByKeyword {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.xlsx");
        
        Worksheet sheet = workbook.getWorksheets().get(0);
        
        String keyword = "Test";
        
        // Iterate backwards and delete rows containing the keyword
        for (int i = sheet.getRows().length - 1; i >= 0; i--) {
            boolean shouldDelete = false;
            
            // Check all cells in the row
            for (Object cell : sheet.getRows()[i]) {
                if (cell != null && cell.toString().contains(keyword)) {
                    shouldDelete = true;
                    break;
                }
            }
            
            if (shouldDelete) {
                sheet.deleteRow(i + 1);
            }
        }
        
        workbook.saveToFile("filtered.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
    }
}
```

**Use cases**: Cleaning test data, filtering invalid records, removing rows marked as obsolete, etc.

## 10\. Real-World Example: Dynamic Report Generation

Let's put everything together with a complete example: generating a formatted product report dynamically.

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

public class DynamicReport {
    
    // Product data class
    static class Product {
        String name;
        double price;
        int quantity;
        
        Product(String name, double price, int quantity) {
            this.name = name;
            this.price = price;
            this.quantity = quantity;
        }
    }
    
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.getWorksheets().get(0);
        
        // Prepare data
        List<Product> products = new ArrayList<>();
        products.add(new Product("Product A", 99.9, 100));
        products.add(new Product("Product B", 199.9, 50));
        products.add(new Product("Product C", 299.9, 30));
        
        // 1. Write headers
        String[] headers = {"Product Name", "Unit Price", "Quantity", "Total"};
        for (int i = 0; i < headers.length; i++) {
            sheet.getCellRange(1, i + 1).setValue(headers[i]);
        }
        
        // Format headers
        sheet.getCellRange(1, 1, 1, headers.length).getStyle().getFont().isBold(true);
        sheet.getCellRange(1, 1, 1, headers.length).getStyle()
            .setKnownColor(ExcelColors.LightBlue);
        
        // 2. Dynamically insert data rows
        for (int i = 0; i < products.size(); i++) {
            int row = i + 2;  // Start from row 2 (row 1 is header)
            Product p = products.get(i);
            
            sheet.getCellRange(row, 1).setValue(p.name);
            sheet.getCellRange(row, 2).setNumberValue(p.price);
            sheet.getCellRange(row, 3).setNumberValue(p.quantity);
            
            // Total = Price * Quantity (using formula)
            sheet.getCellRange(row, 4).setFormula("=B" + row + "*C" + row);
            sheet.getCellRange(row, 4).getStyle().setNumberFormat("$#,##0.00");
        }
        
        // 3. Add summary row
        int summaryRow = products.size() + 2;
        sheet.getCellRange(summaryRow, 1).setValue("Total");
        sheet.getCellRange(summaryRow, 1).getStyle().getFont().isBold(true);
        
        // Summary formulas
        sheet.getCellRange(summaryRow, 3).setFormula("=SUM(C2:C" + (summaryRow - 1) + ")");
        sheet.getCellRange(summaryRow, 4).setFormula("=SUM(D2:D" + (summaryRow - 1) + ")");
        sheet.getCellRange(summaryRow, 4).getStyle().setNumberFormat("$#,##0.00");
        
        // 4. Set column widths
        sheet.setColumnWidth(1, 20);  // Product Name
        sheet.setColumnWidth(2, 15);  // Unit Price
        sheet.setColumnWidth(3, 15);  // Quantity
        sheet.setColumnWidth(4, 20);  // Total
        
        // 5. Auto-fit (optional)
        // sheet.getAllocatedRange().autoFitColumns();
        
        // 6. Add borders
        sheet.getCellRange(1, 1, summaryRow, 4).getBorders()
            .setLineStyle(LineStyleType.Thin);
        
        workbook.saveToFile("product_report.xlsx", ExcelVersion.Version2013);
        workbook.dispose();
        
        System.out.println("Report generated successfully!");
    }
}
```

This example demonstrates:

*   **Dynamic row insertion** based on data volume
    
*   **Using formulas** for automatic calculations
    
*   **Formatting** (bold, background color, number format)
    
*   **Column width adjustment**
    
*   **Adding borders**
    

## Summary

Here are the key takeaways:

1.  **Insert rows/columns** using `insertRow()` and `insertColumn()`, supports batch insertion
    
2.  **Delete rows/columns** using `deleteRow()` and `deleteColumn()`, iterate backwards when deleting in loops
    
3.  **Delete blanks** using `isBlank()` check—great for cleaning dirty data
    
4.  **Copy rows/columns** using `copy()` method, control what to copy (values, formatting, formulas)
    
5.  **Hide/show** using `hideRow/Column()` and `showRow/Column()`
    
6.  **Set dimensions** using `setRowHeight()` and `setColumnWidth()`
    
7.  **Auto-fit** using `autoFitColumns()`, but watch performance with large datasets
    
8.  **Group rows/columns** using `groupByRows/Columns()`, perfect for hierarchical data
    

In practice, the most important thing is understanding your business requirements. Not every scenario needs complex operations—sometimes simple insert and delete are all you need.

Hope these tips help you work more efficiently with Excel in Java! Feel free to share your own tricks in the comments.