Automatically Adjust Excel Row Height and Column Width Using Java

When working with Excel files, automatically adjusting row heights and column widths is a common requirement. Properly sized rows and columns ensure that all content is fully visible, preventing text from being cut off or displayed unclearly.

In this article, we’ll walk through how to use Java to automatically adjust row heights and column widths in Excel. We’ll cover both adjusting specific rows and columns as well as batch resizing multiple rows and columns at once.

Why You Should Automate Row Height and Column Width in Excel

Automatic adjustment of Excel rows and columns resizes cells based on their content. This ensures that all text and data are visible and properly formatted. This functionality is especially useful when dealing with complex reports, client information tables, or project schedules, where formatting accuracy is critical.

Some key benefits of automating row and column adjustment include:

• Improved efficiency: Automation saves the time and effort of manually resizing cells, particularly for large datasets.
• Consistent formatting: Programmatic adjustment guarantees the same layout across multiple files, avoiding mistakes from manual edits.
• Clear and readable content: Rows and columns automatically fit the content, preventing truncated text or misaligned data.

Environment Setup

Before we can adjust Excel files with Java, we need a library to handle Excel operations. In this guide, we’ll use Free Spire.XLS for Java, a free Java library for creating, reading, modifying, formatting, and converting Excel files.

Adding Free Spire.XLS to Your Project

You can download the JAR file from the official website or include it via Maven.

For Maven, add the following to your pom.xml:

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

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

Once the dependency is added, you can start coding.

Adjusting Specific Rows or Columns

Sometimes, you only need to resize a specific row or column. This can be achieved using the sheet.autoFitRow or sheet.autoFitColumn methods.

Example: Adjusting a Specific Row and Column

import com.spire.xls.*;

public class ExcelAutoFit {
    public static void main(String[] args) throws Exception {
        // Create a Workbook object and load the Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("example.xlsx");

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

        // Automatically adjust the height of row 3
        sheet.autoFitRow(3);

        // Automatically adjust the width of column 4
        sheet.autoFitColumn(4);

        // Save the updated file
        workbook.saveToFile("AutoFit_SpecificRowColumn.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Explanation:

• Creating the workbook: new Workbook() initializes a workbook, and loadFromFile loads the existing Excel file.
• Adjusting row/column:
  • autoFitRow(int rowIndex) adjusts the height of the specified row.
  • autoFitColumn(int columnIndex) adjusts the width of the specified column. > Note: Row and column indices start at 1.
• Saving changes: saveToFile writes the updated file to disk.

Adjusting Multiple Rows and Columns

If you want to resize multiple rows and columns at once, you can specify a range of cells. This is much more efficient than adjusting rows and columns individually.

Example: Auto-Fitting Multiple Rows and Columns

import com.spire.xls.*;

public class ExcelAutoFitMultiple {
    public static void main(String[] args) throws Exception {
        // Create a Workbook object and load the Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("example.xlsx");

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

        // Get a specific cell range
        CellRange range = sheet.getRange().get("A1:E14");

        // Automatically adjust the height of all rows in the range
        range.autoFitRows();

        // Automatically adjust the width of all columns in the range
        range.autoFitColumns();

        // Save the updated file
        workbook.saveToFile("AutoFit_MultipleRowsColumns.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Explanation:

• Getting a cell range: sheet.getRange().get("A1:E14") retrieves cells from A1 to E14. You can also use sheet.getAllocatedRange() to select all used cells automatically.
• Batch resizing:
  • autoFitRows() adjusts all rows in the range.
  • autoFitColumns() adjusts all columns in the range.

Tips for Better Results

• Wrap text: If a cell contains line breaks, enable text wrapping before auto-fitting:

  cell.getStyle().setWrapText(true);

• Merged cells: Auto-fitting may not work as expected for merged cells. You may need to manually adjust merged ranges.
• Performance: Auto-fitting very large worksheets can be slow. Focus on specific ranges rather than the entire sheet for better efficiency.

Advantages of Using Java for Auto-Fitting Excel

• Automation: Save time by letting code handle cell resizing.
• Precision: Ensure all content is fully visible, especially for long text or multi-line entries.
• Flexibility: Resize single rows/columns, multiple rows/columns, or custom ranges.
• Integration: Works seamlessly with Java projects, useful for generating reports or processing bulk Excel files.

Conclusion

Using Java to automatically adjust Excel row heights and column widths makes spreadsheet management easier, more efficient, and more accurate. Whether resizing a single row or column or processing entire ranges, this approach ensures clean, well-formatted Excel files while significantly reducing manual effort.