Cells and Ranges

Two ways to reach data: Cell for one cell with all of its attachments, and Range for a rectangular block. Which you pick has real performance consequences — see Bulk access.

Coordinates

Row and column indices are 0-based throughout the API: cell(0, 0) is A1. Formula strings, named ranges, and range(String) use ordinary 1-based A1 notation, because that is what the file format and the user see.

Anywhere the API takes a reference — sheet.range(String), formula text, addName, a chart’s series reference, a pivot table’s source — it is the 1-based A1 form. Anywhere it takes int row, int col, it is 0-based.

sheet.range("A2:D50");        // identical to sheet.range(1, 0, 49, 3)
wb.range("Summary!A1");       // a sheet-qualified reference

CellValue

CellValue is a sealed interface — a value is exactly one of six things:

CellValue.number(42.0);
CellValue.text("hello");
CellValue.bool(true);
CellValue.blank();
CellValue.error(2);                     // #DIV/0!
CellValue.array(new CellValue[][] {...});

Because it is sealed, you can handle each variant explicitly:

String describe(CellValue v) {
    if (v instanceof CellValue.Number n) return "number " + n.value();
    if (v instanceof CellValue.Text t)   return "text " + t.value();
    if (v instanceof CellValue.Bool b)   return "bool " + b.value();
    if (v instanceof CellValue.Error e)  return "error " + e.code();
    if (v instanceof CellValue.Array a) {
        return a.rowCount() + "×" + a.colCount() + " array";
    }
    return "blank";
}

Read a raw value through the matching variant accessor, such as CellValue.Number.value() or CellValue.Text.value(). For an error, CellValue.Error.display() returns its Excel error string. In contrast, Cell.formattedText() applies the cell’s number format, which is what a spreadsheet application shows.

Dates

Spreadsheets store dates as serial numbers, so the API converts explicitly:

cell.setDate(LocalDate.of(2026, 3, 1));
cell.setDateTime(LocalDateTime.now());

LocalDate d = cell.dateValue();
LocalDateTime dt = cell.dateTimeValue();

Which epoch a serial number refers to depends on the workbook’s date1904 setting — see Workbook and Sheet.

Cell

A Cell is the single-cell view, and it is where everything attached to one cell lives: value, formula, style, comment, threaded comments, rich text, hyperlink.

Cell cell = sheet.cell(4, 2);

cell.setValue(CellValue.number(1499.0));
cell.setFormula("SUM(B2:B50)");
cell.formula();                   // the formula text, or null
cell.formattedText();
cell.clear();

cell.setStyle(style);
cell.setRichText(richText);
cell.setHyperlink("https://keikai.io");
cell.setComment("Finance", "Adjusted after audit");

Localized formulas

For a UI that shows function names and argument separators in the user’s language:

cell.setFormulaLocalized("SUMME(B2:B50)", "de-DE");
String shown = cell.formulaLocalized("de-DE");

The stored formula is always canonical English; localization is a presentation concern applied on the way in and out.

Threaded comments

Modern Excel comments are threads with replies and a resolved flag, distinct from legacy notes:

String id = cell.addThreadedComment("alice", "Is this final?");
cell.addThreadedReply(id, "bob", "Yes, signed off.");
cell.setThreadedCommentResolved(id, true);

for (ThreadedComment tc : cell.threadedComments()) { ... }

setComment(author, text) writes a legacy note instead. The two coexist in the file format; do not expect one API to see the other’s content.

Range

Range r = sheet.range(1, 0, 49, 3);     // rows 1–49, cols 0–3
Range same = sheet.range("A2:D50");

r.rowCount(); r.colCount();
r.firstRow(); r.lastColumn();

Bulk access and performance

Every Cell call crosses the JNI boundary. Every Range call crosses it once for the whole block. For anything beyond a handful of cells, this is the difference that matters:

// One native call for 49 × 4 cells
CellValue[][] values = r.values();
r.setValues(values);

// Faster still when everything is numeric — no boxing, no CellValue
double[] nums = r.numbers();
r.setNumbers(nums);

String[][] formulas = r.formulas();
r.setFormulas(formulas);

numbers() / setNumbers(double[]) are row-major and flat, and the right choice for loading a matrix out of a database. numbers() returns NaN for every cell that is not a number (blank, text, boolean, error). Like setValues, they keep each cell’s style, comment, and hyperlink.

0.1.0 builds: setNumbers (and Sheet.setNumber) replaced the cells completely, dropping style, comment, and hyperlink; update formatted cells with setValues on those builds.

Formulas over a range

r.setFormula("A2*1.05");      // writes this exact formula to every cell
r.setArrayFormula("MMULT(A1:C3, E1:G3)");   // one legacy CSE array formula
r.spillRange();               // the area a dynamic-array formula actually spilled into

The distinction matters: setFormula writes the same formula text to every cell. To shift relative references as Excel’s fill handle does, use autoFill(destination) from a source range, copy the source with copyTo(sheet, row, column), or write each cell’s own formula with setFormulas; all three keep $-anchored references fixed. (0.1.0 builds: autoFill copied formula text without shifting it — use copyTo or setFormulas there.) setArrayFormula writes a single formula occupying the whole range. Modern dynamic arrays spill on their own — see Formulas.

Operations

r.sort(SortKey.asc(0), SortKey.desc(2));
r.sort(0, true);                       // one column, ascending
r.sortLeftToRight(SortKey.asc(0));     // sort columns instead of rows
r.sort(SortKey.customOrder(1, "High", "Medium", "Low"));

sheet.range("A2:D2").autoFill(sheet.range("A2:D20"));   // extend row 2 down
r.removeDuplicates(new int[] {0, 1}, true);

r.copyTo(otherSheet, 0, 0);
r.copyTo(otherSheet, 0, 0, "values");  // paste-special

r.merge();
r.unmerge();
r.autoMerge(true);

autoFill follows Excel’s fill handle: the destination must be on the same sheet, contain the source range, and extend it along one axis. It continues numeric series, repeats text, and shifts relative references in formulas.

copyTo with a paste type covers paste-special: values only, formats only, formulas, and so on. Without it, everything is copied.

Attaching content

r.setStyle(style);
r.applyNamedStyle("Currency");
r.setValidation(validation);
r.addConditionalFormat(format);
r.createTable("Sales", "Region", "Q1", "Q2");

See Styles and Content Objects.

Importing Java objects

Sheet.importData writes a list of POJOs or records as rows, reading properties by name:

record Sale(String region, double q1, double q2) {}

List<Sale> sales = List.of(new Sale("APAC", 1200, 1450), new Sale("EMEA", 980, 1100));

sheet.importData(0, 0, sales, ImportOptions.builder()
        .columns("region", "q1", "q2")
        .headers(true)
        .columnLabels("Region", "Q1", "Q2")
        .headerStyle(boldStyle)
        .build());

Returns the number of rows written, including the header row. Without columns(...), a record’s components are written in declaration order, but a JavaBean’s getX/isX properties are written in alphabetical order — pin the column list explicitly whenever the order matters or the class may gain fields later.

For filling an existing template rather than writing a fresh block, see Template Markers.

Get code at GitHub

Twitter Facebook LinkedIn