Skip to content

linexcel.refs

linexcel.refs

Excel reference utilities: A1 ↔ (row, col), R1C1, ranges.

All conversions are independent of the computation engine so they remain unit-testable.

Rect dataclass

Cell rectangle (inclusive bounds), with optional sheet.

Source code in src/linexcel/refs.py 
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
@dataclass(frozen=True)
class Rect:
    """Cell rectangle (inclusive bounds), with optional sheet."""

    sheet: str | None
    r1: int
    c1: int
    r2: int
    c2: int

    @property
    def ncells(self) -> int:
        """Number of cells in the rectangle.

        >>> Rect(None, 1, 1, 3, 2).ncells
        6
        """
        return (self.r2 - self.r1 + 1) * (self.c2 - self.c1 + 1)

    def clipped(self, max_row: int, max_col: int) -> Rect | None:
        """Clip the range to the used dimensions of the sheet."""
        r2 = min(self.r2, max(max_row, 1))
        c2 = min(self.c2, max(max_col, 1))
        if r2 < self.r1 or c2 < self.c1:
            return None
        return Rect(self.sheet, self.r1, self.c1, r2, c2)

    def intersects(self, other: Rect) -> bool:
        """Check if two rectangles overlap.

        >>> Rect(None, 1, 1, 3, 3).intersects(Rect(None, 2, 2, 5, 5))
        True
        >>> Rect(None, 1, 1, 2, 2).intersects(Rect(None, 4, 4, 5, 5))
        False
        """
        return not (
            self.r2 < other.r1
            or other.r2 < self.r1
            or self.c2 < other.c1
            or other.c2 < self.c1
        )

    def to_a1(self) -> str:
        """Convert to A1 notation string.

        >>> Rect(None, 1, 1, 1, 1).to_a1()
        'A1'
        >>> Rect(None, 1, 1, 3, 3).to_a1()
        'A1:C3'
        >>> Rect("Sheet1", 1, 1, 1, 1).to_a1()
        'Sheet1!A1'
        """
        start = f"{num_to_col(self.c1)}{self.r1}"
        end = f"{num_to_col(self.c2)}{self.r2}"
        addr = start if start == end else f"{start}:{end}"
        return f"{quote_sheet(self.sheet)}!{addr}" if self.sheet else addr

ncells property 

ncells: int

Number of cells in the rectangle.

Rect(None, 1, 1, 3, 2).ncells 6

clipped 

clipped(max_row: int, max_col: int) -> Rect | None

Clip the range to the used dimensions of the sheet.

Source code in src/linexcel/refs.py 
82
83
84
85
86
87
88
def clipped(self, max_row: int, max_col: int) -> Rect | None:
    """Clip the range to the used dimensions of the sheet."""
    r2 = min(self.r2, max(max_row, 1))
    c2 = min(self.c2, max(max_col, 1))
    if r2 < self.r1 or c2 < self.c1:
        return None
    return Rect(self.sheet, self.r1, self.c1, r2, c2)

intersects 

intersects(other: Rect) -> bool

Check if two rectangles overlap.

Rect(None, 1, 1, 3, 3).intersects(Rect(None, 2, 2, 5, 5)) True Rect(None, 1, 1, 2, 2).intersects(Rect(None, 4, 4, 5, 5)) False

Source code in src/linexcel/refs.py 
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
def intersects(self, other: Rect) -> bool:
    """Check if two rectangles overlap.

    >>> Rect(None, 1, 1, 3, 3).intersects(Rect(None, 2, 2, 5, 5))
    True
    >>> Rect(None, 1, 1, 2, 2).intersects(Rect(None, 4, 4, 5, 5))
    False
    """
    return not (
        self.r2 < other.r1
        or other.r2 < self.r1
        or self.c2 < other.c1
        or other.c2 < self.c1
    )

to_a1 

to_a1() -> str

Convert to A1 notation string.

Rect(None, 1, 1, 1, 1).to_a1() 'A1' Rect(None, 1, 1, 3, 3).to_a1() 'A1:C3' Rect("Sheet1", 1, 1, 1, 1).to_a1() 'Sheet1!A1'

Source code in src/linexcel/refs.py 
105
106
107
108
109
110
111
112
113
114
115
116
117
118
def to_a1(self) -> str:
    """Convert to A1 notation string.

    >>> Rect(None, 1, 1, 1, 1).to_a1()
    'A1'
    >>> Rect(None, 1, 1, 3, 3).to_a1()
    'A1:C3'
    >>> Rect("Sheet1", 1, 1, 1, 1).to_a1()
    'Sheet1!A1'
    """
    start = f"{num_to_col(self.c1)}{self.r1}"
    end = f"{num_to_col(self.c2)}{self.r2}"
    addr = start if start == end else f"{start}:{end}"
    return f"{quote_sheet(self.sheet)}!{addr}" if self.sheet else addr

RefDetail dataclass

Parsed reference with $ anchors (for group stretching).

Source code in src/linexcel/refs.py 
230
231
232
233
234
235
236
237
238
@dataclass(frozen=True)
class RefDetail:
    """Parsed reference with $ anchors (for group stretching)."""

    rect: Rect
    row1_abs: bool
    col1_abs: bool
    row2_abs: bool
    col2_abs: bool

col_to_num 

col_to_num(col: str) -> int

Convert a column letter (A, B, ..., XFD) to a 1-indexed number.

col_to_num("A") 1 col_to_num("Z") 26 col_to_num("AA") 27 col_to_num("XFD") 16384

Source code in src/linexcel/refs.py 
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
def col_to_num(col: str) -> int:
    """Convert a column letter (A, B, ..., XFD) to a 1-indexed number.

    >>> col_to_num("A")
    1
    >>> col_to_num("Z")
    26
    >>> col_to_num("AA")
    27
    >>> col_to_num("XFD")
    16384
    """
    n = 0
    for ch in col.upper():
        n = n * 26 + (ord(ch) - 64)
    return n

num_to_col 

num_to_col(n: int) -> str

Convert a 1-indexed column number to letters.

num_to_col(1) 'A' num_to_col(26) 'Z' num_to_col(27) 'AA' num_to_col(16384) 'XFD'

Source code in src/linexcel/refs.py 
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
def num_to_col(n: int) -> str:
    """Convert a 1-indexed column number to letters.

    >>> num_to_col(1)
    'A'
    >>> num_to_col(26)
    'Z'
    >>> num_to_col(27)
    'AA'
    >>> num_to_col(16384)
    'XFD'
    """
    out = ""
    while n > 0:
        n, rem = divmod(n - 1, 26)
        out = chr(65 + rem) + out
    return out

quote_sheet 

quote_sheet(sheet: str) -> str

Quote a sheet name if necessary for inclusion in a formula.

quote_sheet("Sheet1") 'Sheet1' quote_sheet("Sheet 1") "'Sheet 1'" quote_sheet("L'été") "'L''été'"

Source code in src/linexcel/refs.py 
121
122
123
124
125
126
127
128
129
130
131
132
133
def quote_sheet(sheet: str) -> str:
    """Quote a sheet name if necessary for inclusion in a formula.

    >>> quote_sheet("Sheet1")
    'Sheet1'
    >>> quote_sheet("Sheet 1")
    "'Sheet 1'"
    >>> quote_sheet("L'été")
    "'L''été'"
    """
    if re.fullmatch(r"[A-Za-z_][A-Za-z0-9_.]*", sheet):
        return sheet
    return "'" + sheet.replace("'", "''") + "'"

split_sheet_prefix 

split_sheet_prefix(ref: str) -> tuple[str | None, str]

Split the sheet prefix from a reference ('Sheet 1'!A1 → (Sheet 1, A1)).

split_sheet_prefix("Sheet1!A1") ('Sheet1', 'A1') split_sheet_prefix("'My Sheet'!B2:C3") ('My Sheet', 'B2:C3') split_sheet_prefix("A1") (None, 'A1')

Source code in src/linexcel/refs.py 
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
def split_sheet_prefix(ref: str) -> tuple[str | None, str]:
    """Split the sheet prefix from a reference ('Sheet 1'!A1 → (Sheet 1, A1)).

    >>> split_sheet_prefix("Sheet1!A1")
    ('Sheet1', 'A1')
    >>> split_sheet_prefix("'My Sheet'!B2:C3")
    ('My Sheet', 'B2:C3')
    >>> split_sheet_prefix("A1")
    (None, 'A1')
    """
    m = _SHEET_PREFIX_RE.match(ref)
    if not m:
        return None, ref
    if m.group("q"):
        sheet = m.group("q")[1:-1].replace("''", "'")
    else:
        sheet = m.group("p")
    return sheet, ref[m.end() :]

parse_ref 

parse_ref(ref: str, default_sheet: str | None = None) -> Rect | None

Parse an A1 reference (cell, range, whole columns or rows).

Returns None if the string is not a valid A1 reference (defined name, structured table reference, ...).

parse_ref("A1") Rect(sheet=None, r1=1, c1=1, r2=1, c2=1) parse_ref("B3:D5") Rect(sheet=None, r1=3, c1=2, r2=5, c2=4) parse_ref("Sheet1!$C$2") Rect(sheet='Sheet1', r1=2, c1=3, r2=2, c2=3) parse_ref("A:A") is None False parse_ref("not_a_ref") is None True

Source code in src/linexcel/refs.py 
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
def parse_ref(ref: str, default_sheet: str | None = None) -> Rect | None:
    """Parse an A1 reference (cell, range, whole columns or rows).

    Returns ``None`` if the string is not a valid A1 reference
    (defined name, structured table reference, ...).

    >>> parse_ref("A1")
    Rect(sheet=None, r1=1, c1=1, r2=1, c2=1)
    >>> parse_ref("B3:D5")
    Rect(sheet=None, r1=3, c1=2, r2=5, c2=4)
    >>> parse_ref("Sheet1!$C$2")
    Rect(sheet='Sheet1', r1=2, c1=3, r2=2, c2=3)
    >>> parse_ref("A:A") is None
    False
    >>> parse_ref("not_a_ref") is None
    True
    """
    sheet, body = split_sheet_prefix(ref)
    if sheet is None:
        sheet = default_sheet
    elif ":" in sheet:
        # 3D reference (Sheet1:Sheet3!A1): out of scope for cell graph.
        return None
    body = body.strip()
    if ":" in body:
        left, _, right = body.partition(":")
        p1 = _parse_endpoint(left)
        p2 = _parse_endpoint(right)
        if p1 is None or p2 is None:
            return None
        (r1, c1), (r2, c2) = p1, p2
        rect = Rect(sheet, min(r1, r2), min(c1, c2), max(r1, r2), max(c1, c2))
        return normalize_whole_ranges(rect)
    m = _CELL_RE.match(body)
    if not m:
        return None
    row = int(m.group(4))
    col = col_to_num(m.group(2))
    if row < 1 or row > MAX_ROW or col > MAX_COL:
        return None
    return Rect(sheet, row, col, row, col)

normalize_whole_ranges 

normalize_whole_ranges(rect: Rect) -> Rect

Replace -1 markers (whole column/row) with max bounds.

Source code in src/linexcel/refs.py 
221
222
223
224
225
226
227
def normalize_whole_ranges(rect: Rect) -> Rect:
    """Replace -1 markers (whole column/row) with max bounds."""
    r1 = 1 if rect.r1 == -1 else rect.r1
    r2 = MAX_ROW if rect.r2 == -1 else rect.r2
    c1 = 1 if rect.c1 == -1 else rect.c1
    c2 = MAX_COL if rect.c2 == -1 else rect.c2
    return Rect(rect.sheet, r1, c1, r2, c2)

parse_ref_detailed 

parse_ref_detailed(ref: str, default_sheet: str | None = None) -> RefDetail | None

Like :func:parse_ref, but preserves absolute anchors on each bound.

Source code in src/linexcel/refs.py 
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
def parse_ref_detailed(ref: str, default_sheet: str | None = None) -> RefDetail | None:
    """Like :func:`parse_ref`, but preserves absolute anchors on each bound."""
    sheet, body = split_sheet_prefix(ref)
    if sheet is None:
        sheet = default_sheet
    elif ":" in sheet:
        return None
    body = body.strip()
    parts = body.split(":")
    if len(parts) > 2:
        return None
    ends = []
    for part in parts:
        e = _parse_endpoint_detailed(part.strip())
        if e is None:
            return None
        ends.append(e)
    if len(ends) == 1:
        (row, col, row_abs, col_abs) = ends[0]
        if row == -1 or col == -1:
            return None  # a whole column/row alone is not a cell
        rect = Rect(sheet, row, col, row, col)
        return RefDetail(rect, row_abs, col_abs, row_abs, col_abs)
    (r1, c1, r1a, c1a), (r2, c2, r2a, c2a) = ends
    if (r1, c1) > (r2, c2):
        (r1, c1, r1a, c1a), (r2, c2, r2a, c2a) = (r2, c2, r2a, c2a), (r1, c1, r1a, c1a)
    # Whole-axis refs (A:A, 1:1) are frozen: they don't stretch.
    if r1 == -1:
        r1, r2, r1a, r2a = 1, MAX_ROW, True, True
    if c1 == -1:
        c1, c2, c1a, c2a = 1, MAX_COL, True, True
    return RefDetail(Rect(sheet, r1, c1, r2, c2), r1a, c1a, r2a, c2a)

stretch_ref 

stretch_ref(detail: RefDetail, rep_row: int, rep_col: int, rows_span: tuple[int, int], cols_span: tuple[int, int]) -> Rect

Stretch a representative cell's reference to an entire stretched group.

rows_span/cols_span are the (min, max) bounds of the group's member cells. Relative bounds follow the displacement, anchored bounds ($) stay fixed.

Source code in src/linexcel/refs.py 
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
def stretch_ref(
    detail: RefDetail,
    rep_row: int,
    rep_col: int,
    rows_span: tuple[int, int],
    cols_span: tuple[int, int],
) -> Rect:
    """Stretch a representative cell's reference to an entire stretched group.

    ``rows_span``/``cols_span`` are the (min, max) bounds of the group's
    member cells. Relative bounds follow the displacement, anchored bounds
    ($) stay fixed.
    """
    rect = detail.rect
    r1 = rect.r1 if detail.row1_abs else rect.r1 + (rows_span[0] - rep_row)
    r2 = rect.r2 if detail.row2_abs else rect.r2 + (rows_span[1] - rep_row)
    c1 = rect.c1 if detail.col1_abs else rect.c1 + (cols_span[0] - rep_col)
    c2 = rect.c2 if detail.col2_abs else rect.c2 + (cols_span[1] - rep_col)
    r1, r2 = max(1, min(r1, r2)), min(MAX_ROW, max(r1, r2))
    c1, c2 = max(1, min(c1, c2)), min(MAX_COL, max(c1, c2))
    return Rect(rect.sheet, r1, c1, r2, c2)

cell_to_r1c1 

cell_to_r1c1(cell: str, base_row: int, base_col: int) -> str | None

Convert an A1 cell reference to R1C1 relative to (base_row, base_col).

Source code in src/linexcel/refs.py 
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
def cell_to_r1c1(cell: str, base_row: int, base_col: int) -> str | None:
    """Convert an A1 cell reference to R1C1 relative to (base_row, base_col)."""
    m = _CELL_RE.match(cell)
    if m:
        abs_col, col, abs_row, row = (
            m.group(1) == "$",
            col_to_num(m.group(2)),
            m.group(3) == "$",
            int(m.group(4)),
        )
        r = f"R{row}" if abs_row else _rel("R", row - base_row)
        c = f"C{col}" if abs_col else _rel("C", col - base_col)
        return r + c
    m = _COL_RE.match(cell)
    if m:
        abs_col, col = m.group(1) == "$", col_to_num(m.group(2))
        return f"C{col}" if abs_col else _rel("C", col - base_col)
    m = _ROW_RE.match(cell)
    if m:
        abs_row, row = m.group(1) == "$", int(m.group(2))
        return f"R{row}" if abs_row else _rel("R", row - base_row)
    return None

ref_to_r1c1 

ref_to_r1c1(ref: str, base_row: int, base_col: int) -> str | None

Convert a full reference (with sheet and ':') to R1C1 form.

Source code in src/linexcel/refs.py 
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
def ref_to_r1c1(ref: str, base_row: int, base_col: int) -> str | None:
    """Convert a full reference (with sheet and ':') to R1C1 form."""
    sheet, body = split_sheet_prefix(ref)
    parts = body.split(":")
    if len(parts) > 2:
        return None
    converted = []
    for part in parts:
        c = cell_to_r1c1(part.strip(), base_row, base_col)
        if c is None:
            return None
        converted.append(c)
    out = ":".join(converted)
    if sheet is not None:
        out = f"{quote_sheet(sheet)}!{out}"
    return out