1package formula23import (4 "fmt"5 "strings"6)78type tokKind int910const (11 tokEOF tokKind = iota12 tokNum13 tokStr14 tokIdent // cell reference, TRUE/FALSE or a name15 tokFunc // identifier directly followed by "("16 tokOp // operators and punctuation17 tokRefErr // #REF!, left behind when a referenced cell is deleted18 tokSheet // a sheet name and its "!", as in Sheet2!A1 or 'My Sheet'!A1; text is the name19 tokTable // a structured reference, Sales[Amount]; text is as written20)2122type token struct {23 kind tokKind24 text string25 pos int26}2728// ParseError describes a formula that could not be parsed. Pos is the byte29// offset in the entry where the problem was found, used to place the edit30// cursor.31type ParseError struct {32 Pos int33 Msg string34 src string // the formula parsed, for LocalizeError35}3637func (e *ParseError) Error() string { return e.Msg }3839// Operators: two-character ones are matched first, then single characters.40// #AND#, #OR# and #NOT# are 1-2-3's logical operators.41var (42 twoCharOps = [...]string{"..", "<=", ">=", "<>"}43 hashOps = [...]string{"#AND#", "#OR#", "#NOT#"}44 oneCharOps = "+-*/^=<>&(),;:%{}"45 refErrorText = "#REF!"46)4748func isIdentStart(c byte) bool { return isLetter(c) || c == '_' || c == '$' }4950func isIdentPart(c byte) bool { return isIdentStart(c) || isDigit(c) || c == '.' }5152// lexer splits a formula (without its "=") into tokens.53type lexer struct {54 src string55 i int56 toks []token57}5859func lex(src string) ([]token, error) {60 // Most formulas have a token for every two or three bytes; growing61 // the slice as it fills cost more than lexing in deep formulas.62 lx := lexer{src: src, toks: make([]token, 0, len(src)/2+1)}63 for lx.i < len(src) {64 if err := lx.next(); err != nil {65 return nil, err66 }67 }68 return append(lx.toks, token{tokEOF, "", len(src)}), nil69}7071// emit adds a token that started at start and ends at the lexer's position.72func (lx *lexer) emit(kind tokKind, text string, start int) {73 lx.toks = append(lx.toks, token{kind, text, start})74}7576func (lx *lexer) next() error {77 c := lx.src[lx.i]78 switch {79 case c == ' ':80 lx.i++81 case isDigit(c) || c == '.' && lx.i+1 < len(lx.src) && isDigit(lx.src[lx.i+1]):82 lx.number()83 case c == '"':84 return lx.str()85 case c == '\'':86 return lx.quotedSheet()87 case c == '@' || isIdentStart(c):88 return lx.ident()89 case c == '#':90 return lx.hash()91 default:92 return lx.operator()93 }94 return nil95}9697// number reads 12, 1.5, .5 or 1.5E-3. A ".." ends it: A1..B3's range98// operator follows digits.99func (lx *lexer) number() {100 src, start := lx.src, lx.i101 for lx.i < len(src) && (isDigit(src[lx.i]) || src[lx.i] == '.' && !strings.HasPrefix(src[lx.i:], "..")) {102 lx.i++103 }104 lx.exponent()105 lx.emit(tokNum, src[start:lx.i], start)106}107108// exponent reads E3, E+3 or E-3 after a number's digits, if there is one.109func (lx *lexer) exponent() {110 src, j := lx.src, lx.i111 if j >= len(src) || src[j] != 'e' && src[j] != 'E' {112 return113 }114 j++115 if j < len(src) && (src[j] == '+' || src[j] == '-') {116 j++117 }118 if j >= len(src) || !isDigit(src[j]) {119 return120 }121 for j < len(src) && isDigit(src[j]) {122 j++123 }124 lx.i = j125}126127// str reads a string literal. Strings use "" to escape a quote, as in128// Sheets.129func (lx *lexer) str() error {130 src, start := lx.src, lx.i131 var escaped strings.Builder // the text so far, once it has an escape132 from := start + 1 // the start of the text not yet copied133 for i := from; i < len(src); i++ {134 if src[i] != '"' {135 continue136 }137 if i+1 < len(src) && src[i+1] == '"' {138 escaped.WriteString(src[from : i+1])139 i++140 from = i + 1141 continue142 }143 text := src[from:i]144 if escaped.Len() > 0 {145 escaped.WriteString(text)146 text = escaped.String()147 }148 lx.i = i + 1149 lx.emit(tokStr, text, start)150 return nil151 }152 return &ParseError{Pos: start, Msg: "Missing closing quote"}153}154155// quotedSheet reads a quoted sheet name and its "!", with ” for a156// quote: 'Q3 ”26'!A1.157func (lx *lexer) quotedSheet() error {158 start := lx.i159 name, end, ok := quotedName(lx.src, start)160 if !ok || end >= len(lx.src) || lx.src[end] != '!' {161 return &ParseError{Pos: start, Msg: "Expected ! after a quoted sheet name"}162 }163 lx.i = end + 1164 lx.emit(tokSheet, name, start)165 return nil166}167168// ident reads a reference, a name, a function name, a sheet name before169// "!", or a 1-2-3 style @function, which may omit its parentheses (@PI).170func (lx *lexer) ident() error {171 src, start := lx.src, lx.i172 at := src[start] == '@'173 if at {174 lx.i++175 }176 for lx.i < len(src) && isIdentPart(src[lx.i]) && !strings.HasPrefix(src[lx.i:], "..") {177 lx.i++178 }179 if !at && lx.i < len(src) && src[lx.i] == '!' {180 lx.emit(tokSheet, src[start:lx.i], start)181 lx.i++182 return nil183 }184 if !at && lx.i < len(src) && src[lx.i] == '[' {185 return lx.structured(start)186 }187 name := src[start:lx.i]188 if at {189 name = name[1:]190 }191 if name == "" {192 return &ParseError{Pos: start, Msg: "Missing function name after @"}193 }194 kind := tokIdent195 if at || lx.parenFollows() {196 kind = tokFunc197 }198 lx.emit(kind, strings.ToUpper(name), start)199 return nil200}201202// structured reads a structured reference: the table's name from203// start, and its brackets from the lexer's position.204func (lx *lexer) structured(start int) error {205 end := bracketEnd(lx.src, lx.i)206 if end < 0 {207 return &ParseError{Pos: lx.i, Msg: "Missing ] in a table reference"}208 }209 lx.i = end210 lx.emit(tokTable, lx.src[start:end], start)211 return nil212}213214// parenFollows reports whether "(" comes next, after any spaces.215func (lx *lexer) parenFollows() bool {216 j := lx.i217 for j < len(lx.src) && lx.src[j] == ' ' {218 j++219 }220 return j < len(lx.src) && lx.src[j] == '('221}222223// hash reads #REF! or one of 1-2-3's #AND#, #OR# and #NOT#.224func (lx *lexer) hash() error {225 start, rest := lx.i, lx.src[lx.i:]226 if len(rest) >= len(refErrorText) && strings.EqualFold(rest[:len(refErrorText)], refErrorText) {227 lx.i += len(refErrorText)228 lx.emit(tokRefErr, refErrorText, start)229 return nil230 }231 end := strings.IndexByte(rest[1:], '#')232 if end < 0 {233 return &ParseError{Pos: start, Msg: "Unexpected #"}234 }235 op := strings.ToUpper(rest[:end+2])236 for _, known := range hashOps {237 if op == known {238 lx.i += end + 2239 lx.emit(tokOp, known, start)240 return nil241 }242 }243 return &ParseError{Pos: start, Msg: "Unknown operator " + op}244}245246func (lx *lexer) operator() error {247 start, rest := lx.i, lx.src[lx.i:]248 for _, op := range twoCharOps {249 if strings.HasPrefix(rest, op) {250 lx.i += 2251 lx.emit(tokOp, op, start)252 return nil253 }254 }255 if strings.IndexByte(oneCharOps, rest[0]) >= 0 {256 lx.i++257 lx.emit(tokOp, rest[:1], start)258 return nil259 }260 return &ParseError{Pos: start, Msg: fmt.Sprintf("Unexpected %q", rest[0])}261}