Aspose.Cells FOSS
Java
How to Work with Navigation in Java

How to Work with Navigation in Java

A workbook is a tree you move through in a predictable order: the WorksheetCollection holds sheets, each Worksheet exposes a Cells grid, and that grid can be walked by individual address, by row/column index, or by named range. This guide covers the navigation entry points Aspose.Cells FOSS for Java exposes at each level.

Step-by-Step Guide

Step 1: Install the Package 

<dependency>
  <groupId>org.aspose</groupId>
  <artifactId>aspose-cells-foss</artifactId>
  <version>26.7.0</version>
</dependency>

Step 2: Import Required Classes 

import org.aspose.cells_foss.CellArea;
import org.aspose.cells_foss.DefinedName;
import org.aspose.cells_foss.Workbook;
import org.aspose.cells_foss.Worksheet;

Step 3: Iterate Over Worksheets

WorksheetCollection implements the standard Java Iterable contract, so a basic loop visits every sheet in the workbook without tracking indices manually:

try (Workbook workbook = new Workbook("report.xlsx")) {
    for (Worksheet sheet : workbook.getWorksheets()) {
        System.out.println(sheet.getName());
    }
}

Step 4: Resolve a Single Cell by Address or Index

Cells.get(cellName) accepts an A1-style reference; Cells.get(row, column) accepts zero-based integer coordinates for the same cell:

Worksheet sheet = workbook.getWorksheets().get(0);
sheet.getCells().get("C3").putValue("by address");
sheet.getCells().get(2, 2).putValue("same cell, by index");

Step 5: Walk Rows and Columns

Cells.getRows() and Cells.getColumns() return the row/column metadata collections for the sheet — useful for sizing or visibility checks while scanning a range:

for (int r = 0; r < 5; r++) {
    double height = sheet.getCells().getRows().get(r).getHeight();
    System.out.println("row " + r + " height: " + height);
}

Step 6: Navigate to a Range with CellArea

CellArea represents a rectangular block of cells. Build one from explicit row/column bounds, or parse an A1-style range string with CellArea.createCellArea(startCellName, endCellName):

CellArea range = CellArea.createCellArea("A1", "C10");
System.out.println("rows: " + range.getTotalRows() + ", columns: " + range.getTotalColumns());

Step 7: Jump to a Named Range

Workbook.getDefinedNames() returns the workbook’s DefinedNameCollection. Each DefinedName carries the formula it points to, which can be a worksheet-qualified range reference:

int idx = workbook.getDefinedNames().add("SalesRange", "Report!$A$1:$C$10");
DefinedName salesRange = workbook.getDefinedNames().get(idx);
System.out.println(salesRange.getFormula());

Common Issues and Fixes

Cells.getRows().get(index) seems to create rows that were never set. Row and column metadata is sparse — reading an index that was never configured returns a default-valued entry rather than throwing. This is expected; it does not add a cell value, only row/column metadata.

Worksheet lookup by name returns the wrong sheet after a rename. WorksheetCollection.get(name) resolves by the sheet’s current name. If code renamed a sheet earlier with Worksheet.setName(...), look it up by the new name or keep its integer index instead.

CellArea.createCellArea(start, end) throws on a malformed range string. Both arguments must be valid A1-style cell references (for example “A1”, “C10”); passing a full range like “A1:C10” as a single argument is incorrect — use the two-argument overload with each endpoint separately.

Iterating WorksheetCollection skips a sheet that was just added. WorksheetCollection.add(...) appends to the end of the collection; if the iteration started before the add call, the loop continues against a collection view that was captured earlier. Start a fresh iteration after adding sheets.

Frequently Asked Questions

Can I get the total number of worksheets without iterating?

Yes. WorksheetCollection.getCount() returns the sheet count directly.

How do I find a worksheet’s position among its siblings?

Iterate workbook.getWorksheets() while tracking an incrementing counter, or use WorksheetCollection.get(name) combined with getActiveSheetIndex() if the sheet you need is the active one.

Is there a way to get all merged cell ranges on a sheet at once?

Yes. Cells.getMergedCells() returns the list of CellArea ranges that are currently merged on that worksheet.

Do defined names have to be workbook-scoped?

No. DefinedNameCollection.add(name, formula, localSheetIndex) scopes a name to a single worksheet instead of the whole workbook; omit the localSheetIndex argument for a workbook-level name.

See Also