1package formula23import (4 "strconv"5 "strings"6)78// Worksheet bounds, matching Excel (A..XFD, 1..1048576). A reference9// past them isn't a reference: XFE1 reads as a name.10const (11 MaxCols = 1638412 MaxRows = 104857613)1415// Addr identifies a cell by zero-based column and row.16type Addr struct {17 Col, Row int18}1920// String returns the A1-style name of the cell.21func (a Addr) String() string {22 return ColName(a.Col) + strconv.Itoa(a.Row+1)23}2425// Valid reports whether a lies inside the worksheet.26func (a Addr) Valid() bool {27 return a.Col >= 0 && a.Col < MaxCols && a.Row >= 0 && a.Row < MaxRows28}2930// ColName converts a zero-based column index to letters: 0 -> A, 26 -> AA,31// 702 -> AAA.32func ColName(c int) string {33 if c < 26 {34 return string(rune('A' + c))35 }36 var b [8]byte37 i := len(b)38 for c++; c > 0; c = (c - 1) / 26 {39 i--40 b[i] = byte('A' + (c-1)%26)41 }42 return string(b[i:])43}4445// ParseCol converts column letters (case-insensitive) to a zero-based index.46func ParseCol(s string) (int, bool) {47 if len(s) == 0 || len(s) > 3 {48 return 0, false49 }50 c := 051 for _, r := range strings.ToUpper(s) {52 if r < 'A' || r > 'Z' {53 return 0, false54 }55 c = c*26 + int(r-'A'+1)56 }57 c--58 return c, c < MaxCols59}6061// ParseAddr parses an A1-style reference. Absolute markers ($A$1) are62// accepted and ignored.63func ParseAddr(s string) (Addr, bool) {64 a, _, ok := ParseRef(s)65 return a, ok66}6768// Abs records which parts of a reference are absolute ($A$1). Copying a69// formula shifts only the relative parts.70type Abs uint87172const (73 AbsCol Abs = 1 << iota74 AbsRow75)7677// ParseRef parses a reference written as A1, $A1, A$1 or $A$1.78func ParseRef(s string) (Addr, Abs, bool) {79 var abs Abs80 if rest, ok := strings.CutPrefix(s, "$"); ok {81 s, abs = rest, AbsCol82 }83 i := 084 for i < len(s) && isLetter(s[i]) {85 i++86 }87 col, ok := ParseCol(s[:i])88 if !ok {89 return Addr{}, 0, false90 }91 digits := s[i:]92 if rest, ok := strings.CutPrefix(digits, "$"); ok {93 digits, abs = rest, abs|AbsRow94 }95 if digits == "" || strings.ContainsFunc(digits, func(r rune) bool { return r < '0' || r > '9' }) {96 return Addr{}, 0, false97 }98 row, err := strconv.Atoi(digits)99 a := Addr{Col: col, Row: row - 1}100 return a, abs, err == nil && a.Valid()101}102103// RefString writes a with its absolute markers, e.g. $A1.104func RefString(a Addr, abs Abs) string {105 var b strings.Builder106 if abs&AbsCol != 0 {107 b.WriteByte('$')108 }109 b.WriteString(ColName(a.Col))110 if abs&AbsRow != 0 {111 b.WriteByte('$')112 }113 b.WriteString(strconv.Itoa(a.Row + 1))114 return b.String()115}116117// Rect is an inclusive rectangular range of cells.118type Rect struct {119 From, To Addr120}121122// NewRect returns the normalized rectangle spanning a and b.123func NewRect(a, b Addr) Rect {124 return Rect{125 From: Addr{Col: min(a.Col, b.Col), Row: min(a.Row, b.Row)},126 To: Addr{Col: max(a.Col, b.Col), Row: max(a.Row, b.Row)},127 }128}129130// Contains reports whether a lies inside r.131func (r Rect) Contains(a Addr) bool {132 return a.Col >= r.From.Col && a.Col <= r.To.Col &&133 a.Row >= r.From.Row && a.Row <= r.To.Row134}135136// AllRows reports whether r spans every row: whole columns, A:A.137func (r Rect) AllRows() bool { return r.From.Row == 0 && r.To.Row == MaxRows-1 }138139// AllCols reports whether r spans every column: whole rows, 1:1.140func (r Rect) AllCols() bool { return r.From.Col == 0 && r.To.Col == MaxCols-1 }141142// String returns the range as A1:B3, A1 for a single cell, or A:C and143// 2:5 for whole columns and rows.144func (r Rect) String() string {145 switch {146 case r.From == r.To:147 return r.From.String()148 case r.AllRows(), r.AllCols():149 return RangeString(r, [2]Abs{})150 }151 return r.From.String() + ":" + r.To.String()152}153154// ParseRange parses "A1", "A1:B3", 1-2-3 style "A1..B3", or whole155// columns "A:C" and rows "2:5".156func ParseRange(s string) (Rect, bool) {157 s = strings.TrimSpace(s)158 sep := strings.Index(s, "..")159 width := 2160 if sep < 0 {161 sep, width = strings.Index(s, ":"), 1162 }163 if sep < 0 {164 a, ok := ParseAddr(s)165 return Rect{From: a, To: a}, ok166 }167 if r, _, ok := ParseLines(s[:sep], s[sep+width:]); ok {168 return r, true169 }170 a, ok1 := ParseAddr(s[:sep])171 b, ok2 := ParseAddr(s[sep+width:])172 return NewRect(a, b), ok1 && ok2173}174175// ParseLines parses the ends of whole columns ("A", "$C") or whole rows176// ("2", "$5") as the range they span, with their absolute markers.177func ParseLines(from, to string) (Rect, [2]Abs, bool) {178 c0, abs0, ok0 := parseLine(from, false)179 c1, abs1, ok1 := parseLine(to, false)180 if ok0 && ok1 {181 r := NewRect(Addr{Col: c0}, Addr{Col: c1, Row: MaxRows - 1})182 return r, orderAbs(c0 > c1, abs0, abs1), true183 }184 r0, abs0, ok0 := parseLine(from, true)185 r1, abs1, ok1 := parseLine(to, true)186 if ok0 && ok1 {187 r := NewRect(Addr{Row: r0}, Addr{Col: MaxCols - 1, Row: r1})188 return r, orderAbs(r0 > r1, abs0, abs1), true189 }190 return Rect{}, [2]Abs{}, false191}192193func orderAbs(swap bool, a, b Abs) [2]Abs {194 if swap {195 return [2]Abs{b, a}196 }197 return [2]Abs{a, b}198}199200// parseLine parses a column's letters or a row's number, with an201// optional $.202func parseLine(s string, row bool) (int, Abs, bool) {203 rest, abs := strings.CutPrefix(s, "$")204 if !row {205 c, ok := ParseCol(rest)206 if abs {207 return c, AbsCol, ok208 }209 return c, 0, ok210 }211 if rest == "" || strings.ContainsFunc(rest, func(r rune) bool { return r < '0' || r > '9' }) {212 return 0, 0, false213 }214 n, err := strconv.Atoi(rest)215 if err != nil || n < 1 || n > MaxRows {216 return 0, 0, false217 }218 if abs {219 return n - 1, AbsRow, true220 }221 return n - 1, 0, true222}223224// RangeString writes a range as written in a formula, with its absolute225// markers: A1:B3, $A$1:B3, or whole columns (A:C) and rows (2:5).226func RangeString(r Rect, abs [2]Abs) string {227 switch {228 case r.AllRows():229 return lineString(ColName(r.From.Col), abs[0]&AbsCol) + ":" + lineString(ColName(r.To.Col), abs[1]&AbsCol)230 case r.AllCols():231 return lineString(strconv.Itoa(r.From.Row+1), abs[0]&AbsRow) + ":" + lineString(strconv.Itoa(r.To.Row+1), abs[1]&AbsRow)232 }233 return RefString(r.From, abs[0]) + ":" + RefString(r.To, abs[1])234}235236func lineString(s string, abs Abs) string {237 if abs != 0 {238 return "$" + s239 }240 return s241}242243// LooksLikeRef reports whether an upper-case name reads as a cell in A1 or244// R1C1 style, even beyond this sheet's edges, so names stay unambiguous in245// other spreadsheets too.246func LooksLikeRef(k string) bool {247 letters := strings.TrimLeft(k, "ABCDEFGHIJKLMNOPQRSTUVWXYZ")248 if letters != k && letters != "" && strings.Trim(letters, "0123456789") == "" {249 return true250 }251 rest, ok := strings.CutPrefix(k, "R")252 if !ok {253 rest, ok = k, strings.HasPrefix(k, "C")254 }255 if !ok {256 return false257 }258 rest = strings.TrimLeft(rest, "0123456789")259 rest, _ = strings.CutPrefix(rest, "C")260 return strings.Trim(rest, "0123456789") == ""261}262263func isLetter(c byte) bool {264 return (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z')265}266267func isDigit(c byte) bool {268 return c >= '0' && c <= '9'269}