Skip to main content

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:

<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:

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:

// 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.

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.

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

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

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:

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:

// 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:

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:

// 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:

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:

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:

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.

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.

Comments

Post a Comment

Popular posts from this blog

3 Ways to Generate Word Documents from Templates in Java

A template is a document with pre-applied formatting like styles, tabs, line spacing and so on. You can quickly generate a batch of documents with the same structure based on the template. In this article, I am going to show you the different ways to generate Word documents from templates programmatically in Java using Free Spire.Doc for Java library. Prerequisite First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use 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>...
Post a Comment
Read more

Insert and Extract OLE objects in Word in Java

You can use OLE (Object Linking and Embedding) to include content from other programs, such as another Word document, an Excel or PowerPoint document to an existing Word document. This article demonstrates how to insert and extract embedded OLE objects in a Word document in Java by using Free Spire.Doc for Java API.   Add dependencies First of all, you need to add needed dependencies for including Free Spire.Doc for Java into your Java project. There are two ways to do that. If you use 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>    ...
Post a Comment
Read more

Simple Java Code to Convert Excel to PDF in Java

This article demonstrates a simple solution to convert an Excel file to PDF in Java by using free Excel API – Free Spire.XLS for Java . The following examples illustrate two possibilities to convert Excel to PDF:      Convert the whole Excel file to PDF     Convert a particular Excel Worksheet to PDF Before start with coding, you need to Download Free Spire.XLS for Java package , unzip it and import Spire.Xls.jar file from the lib folder in your project as a denpendency. 1. Convert the whole Excel file to PDF Spire.XLS for Java provides saveToFile method in Workbook class that enables us to easily save a whole Excel file to PDF. import com.spire.xls.FileFormat; import com.spire.xls.Workbook; public class ExcelToPDF {     public static void main(String[] args){         //Create a Workbook         Workbook workbook = new Workbook();   ...
Post a Comment
Read more