5.6 KiB
| sidebar_position | title |
|---|---|
| 1 | Addresses and Ranges |
export const g = {style: {backgroundColor:"green"}};
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:
| Ordinal | Row Label | SheetJS |
|---|---|---|
| First | 1 |
0 |
| Second | 2 |
1 |
| 26th | 26 |
25 |
| 420th | 420 |
419 |
| 7262nd | 7262 |
7261 |
| 1048576th | 1048576 |
1048575 |
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:
| Ordinal | Column Label | SheetJS |
|---|---|---|
| First | A |
0 |
| Second | B |
1 |
| 26th | Z |
25 |
| 27th | AA |
26 |
| 420th | PD |
419 |
| 702nd | ZZ |
701 |
| 703rd | AAA |
702 |
| 7262nd | JSH |
7261 |
| 16384th | XFD |
16383 |
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:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 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.