Skip to main content

Addresses and Ranges

Each cell in a worksheet has a unique address which specifies the row and the column that include the cell.

Basic Concepts​

Rows​

Spreadsheet applications typically display ordinal row numbers, where 1 is the first row, 2 is the second row, etc. The numbering starts at 1.

SheetJS follows JavaScript counting conventions, where 0 is the first row, 1 is the second row, etc. The numbering starts at 0.

The following table lists some example row labels:

OrdinalRow LabelSheetJS
First10
Second21
26th2625
420th420419
7262nd72627261
1048576th10485761048575

Columns​

Spreadsheet applications typically use letters to represent columns.

The first column is A, the second column is B, and the 26th column is Z. After Z, the next column is AA and counting continues through AZ. After AZ, the count continues with BA. After ZZ, the count continues with AAA.

Some sample values, along with SheetJS column indices, are listed below:

OrdinalColumn LabelSheetJS
FirstA0
SecondB1
26thZ25
27thAA26
420thPD419
702ndZZ701
703rdAAA702
7262ndJSH7261
16384thXFD16383

Cell Addresses​

A1-Style​

A1-Style is the default address style in Lotus 1-2-3 and Excel.

A cell address is the concatenation of column label and row label.

For example, the cell in the third column and fourth row is C4, concatenating the third column label (C) and the fourth row label (4)

SheetJS Cell Address​

Cell address objects are stored as {c:C, r:R} where C and R are 0-indexed column and row numbers, respectively. For example, the cell address B5 is represented by the object {c:1, r:4}.

Cell Ranges​

A1-Style​

A cell range is represented as the top-left cell of the range, followed by :, followed by the bottom-right cell of the range. For example, the range "C2:D4" includes the 6 green cells in the following table:

ABCDE
1
2
3
4
5

A column range is represented by the left-most column, followed by :, followed by the right-most column. For example, the range C:D represents the third and fourth columns.

A row range is represented by the top-most row, followed by :, followed by the bottom-most column. For example, 2:4 represents the second/third/fourth rows.

SheetJS Range​

Cell range objects are stored as {s:S, e:E} where S is the first cell and E is the last cell in the range. The ranges are inclusive. For example, the range A3:B7 is represented by the object {s:{c:0, r:2}, e:{c:1, r:6}}.

Column and Row Ranges​

A column range (spanning every row) is represented with the starting row 0 and the ending row 1048575:

{ s: { c: 0, r: 0 }, e: { c: 0, r: 1048575 } } // A:A
{ s: { c: 1, r: 0 }, e: { c: 2, r: 1048575 } } // B:C

A row range (spanning every column) is represented with the starting col 0 and the ending col 16383:

{ s: { c: 0, r: 0 }, e: { c: 16383, r: 0 } } // 1:1
{ s: { c: 0, r: 1 }, e: { c: 16383, r: 2 } } // 2:3

Utilities​

Column Names​

Get the SheetJS index from an A1-Style column

var col_index = XLSX.utils.decode_col("D");

The argument is expected to be a string representing a column.

Get the A1-Style column string from a SheetJS index

var col_name = XLSX.utils.encode_col(3);

The argument is expected to be a SheetJS column (non-negative integer).

Row Names​

Get the SheetJS index from an A1-Style row

var row_index = XLSX.utils.decode_row("4");

The argument is expected to be a string representing a row.

Get the A1-Style row string from a SheetJS index

var row_name = XLSX.utils.encode_row(3);

The argument is expected to be a SheetJS column (non-negative integer).

Cell Addresses​

Generate a SheetJS cell address from an A1-Style address string

var address = XLSX.utils.decode_cell("A2");

The argument is expected to be a string representing a single cell address.

Generate an A1-Style address string from a SheetJS cell address

var a1_addr = XLSX.utils.encode_cell({r:1, c:0});

The argument is expected to be a SheetJS cell address

Cell Ranges​

Generate a SheetJS cell range from an A1-Style range string

var range = XLSX.utils.decode_range("A1:D3");

The argument is expected to be a string representing a range or a single cell address. The single cell address is interpreted as a single cell range, so XLSX.utils.decode_range("D3") is the same as XLSX.utils.decode_range("D3:D3")

Generate an A1-Style address string from a SheetJS cell address

var a1_range = XLSX.utils.encode_range({ s: { c: 0, r: 0 }, e: { c: 3, r: 2 } });

The argument is expected to be a SheetJS cell range.