1package formula23import (4 "strings"5)67// Structured references name a table's cells by its columns' names, as8// Excel's and Sheets' tables do: Sales[Amount] is the Amount column's9// data rows, Sales[#All] the whole table, header row included,10// Sales[@Amount] the Amount cell in the formula's own row, and11// Sales[[#Headers],[Amount]:[Units]] the headers of three columns. The12// parser only reads them; the engine finds the table and turns the13// reference into a range when it evaluates the formula, so tables that14// grow or columns that move are followed without rewriting formulas.1516// TableItems are the rows of a table a structured reference names.17type TableItems uint81819const (20 ItemHeaders TableItems = 1 << iota // the header row: [#Headers]21 ItemData // the data rows: [#Data], or nothing22 ItemTotals // a totals row: [#Totals]23 ItemThisRow // the formula's own row: [#This Row] or @24 // ItemAll is the whole table: [#All].25 ItemAll = ItemHeaders | ItemData | ItemTotals26)2728// itemWords are the item keywords, in the order they're printed.29var itemWords = []struct {30 item TableItems31 word string32}{{ItemAll, "#All"}, {ItemHeaders, "#Headers"}, {ItemData, "#Data"}, {ItemTotals, "#Totals"}, {ItemThisRow, "#This Row"}}3334// TableRef is a structured reference: Table[Column], Table[#All],35// Table[@Column], Table[[#Headers],[First]:[Last]].36type TableRef struct {37 Table string // the table's name as written38 // Items are the rows named; zero means the data rows, as when no39 // item is written.40 Items TableItems41 // From and To are the first and last column named, as written: ""42 // for every column, To "" for From alone.43 From, To string44}4546// Rows are the rows the reference names, with no item meaning the47// data rows.48func (t TableRef) Rows() TableItems {49 if t.Items == 0 {50 return ItemData51 }52 return t.Items53}5455// Cols are the first and last column the reference names, the same56// for one column, or "" for every column.57func (t TableRef) Cols() (string, string) {58 if t.To == "" {59 return t.From, t.From60 }61 return t.From, t.To62}6364// tableRef parses a structured reference token: the table's name and65// its bracketed part, written as src, which starts at pos.66func tableRef(src string, pos int) (TableRef, error) {67 i := strings.IndexByte(src, '[')68 t := TableRef{Table: src[:i]}69 inner := strings.TrimSpace(src[i+1 : len(src)-1])70 fail := func(msg string) (TableRef, error) {71 return TableRef{}, &ParseError{Pos: pos + i, Msg: msg}72 }73 switch {74 case inner == "":75 case inner[0] == '@':76 t.Items = ItemThisRow77 rest := strings.TrimSpace(inner[1:])78 if rest == "" {79 break80 }81 if rest[0] != '[' {82 t.From = unescapeColumn(rest)83 break84 }85 items, from, to, msg := specList(rest)86 if msg != "" || items != 0 {87 return fail(cmpOr(msg, "Only columns can follow @"))88 }89 t.From, t.To = from, to90 case inner[0] == '#':91 it, ok := itemWord(inner)92 if !ok {93 return fail("Unknown table item " + inner + ": use #All, #Data, #Headers, #Totals or #This Row")94 }95 t.Items = it96 case inner[0] == '[':97 items, from, to, msg := specList(inner)98 if msg != "" {99 return fail(msg)100 }101 t.Items, t.From, t.To = items, from, to102 default:103 t.From = unescapeColumn(inner)104 }105 if msg := t.check(); msg != "" {106 return fail(msg)107 }108 return t, nil109}110111func cmpOr(a, b string) string {112 if a != "" {113 return a114 }115 return b116}117118// check reports why the items can't go together, or "".119func (t TableRef) check() string {120 switch it := t.Items; {121 case it&ItemThisRow != 0 && it != ItemThisRow:122 return "#This Row can't be combined with other items"123 case it&ItemHeaders != 0 && it&ItemTotals != 0 && it&ItemData == 0:124 return "#Headers and #Totals need #Data between them"125 }126 if t.From == "" && t.To != "" {127 return "Expected a column before :"128 }129 return ""130}131132// itemWord reads an item keyword, ignoring case and spaces around it.133func itemWord(s string) (TableItems, bool) {134 s = strings.TrimSpace(s)135 for _, w := range itemWords {136 if strings.EqualFold(s, w.word) {137 return w.item, true138 }139 }140 return 0, false141}142143// specList reads a list of bracketed items, [#Headers],[Amount]:[Units],144// separated by , or ; and returns the items, the columns, and what's145// wrong with it, if anything.146func specList(s string) (items TableItems, from, to, msg string) {147 cols := 0148 for i := 0; ; {149 i = skipSpaces(s, i)150 text, next, ok := bracketed(s, i)151 if !ok {152 return 0, "", "", "Expected [ and ] around each item"153 }154 if strings.HasPrefix(strings.TrimSpace(text), "#") {155 it, ok := itemWord(text)156 if !ok {157 return 0, "", "", "Unknown table item " + strings.TrimSpace(text) + ": use #All, #Data, #Headers, #Totals or #This Row"158 }159 items |= it160 } else {161 if cols++; cols > 1 {162 return 0, "", "", "Name one column, or a range of them as [First]:[Last]"163 }164 from = unescapeColumn(text)165 if j := skipSpaces(s, next); j < len(s) && s[j] == ':' {166 var last string167 if last, next, ok = bracketed(s, skipSpaces(s, j+1)); !ok {168 return 0, "", "", "Expected [column] after :"169 }170 to = unescapeColumn(last)171 }172 }173 i = skipSpaces(s, next)174 if i >= len(s) {175 return items, from, to, ""176 }177 if s[i] != ',' && s[i] != ';' {178 return 0, "", "", "Expected , between a table's items"179 }180 i++181 }182}183184// bracketed reads [text] at s[i], with ' escaping the character after185// it, and returns the text as written and the index past the "]".186func bracketed(s string, i int) (string, int, bool) {187 if i >= len(s) || s[i] != '[' {188 return "", i, false189 }190 for j := i + 1; j < len(s); j++ {191 switch s[j] {192 case '\'':193 j++194 case '[':195 return "", j, false196 case ']':197 return s[i+1 : j], j + 1, true198 }199 }200 return "", len(s), false201}202203func skipSpaces(s string, i int) int {204 for i < len(s) && s[i] == ' ' {205 i++206 }207 return i208}209210// bracketEnd returns the index just past the "]" closing the "[" at211// src[i], or -1: brackets nest, and ' escapes the character after it.212func bracketEnd(src string, i int) int {213 depth := 0214 for j := i; j < len(src); j++ {215 switch src[j] {216 case '\'':217 j++218 case '[':219 depth++220 case ']':221 if depth--; depth == 0 {222 return j + 1223 }224 }225 }226 return -1227}228229// StructuredEnd returns the index just past the brackets of a230// structured reference whose "[" is at src[i], or -1 when they aren't231// closed.232func StructuredEnd(src string, i int) int { return bracketEnd(src, i) }233234// escaped are the characters a column's name escapes with ' in a235// structured reference.236const escaped = "[]#'"237238// unescapeColumn reads a column's name as written, trimmed, with its239// escapes removed.240func unescapeColumn(s string) string {241 s = strings.TrimSpace(s)242 if !strings.Contains(s, "'") {243 return s244 }245 var b strings.Builder246 for i := 0; i < len(s); i++ {247 if s[i] == '\'' && i+1 < len(s) {248 i++249 }250 b.WriteByte(s[i])251 }252 return b.String()253}254255// EscapeColumn writes a column's name as a structured reference holds256// it, with ' before each of [ ] # and '.257func EscapeColumn(name string) string {258 if !strings.ContainsAny(name, escaped) {259 return name260 }261 var b strings.Builder262 for i := 0; i < len(name); i++ {263 if strings.IndexByte(escaped, name[i]) >= 0 {264 b.WriteByte('\'')265 }266 b.WriteByte(name[i])267 }268 return b.String()269}270271// plainColumn reports whether a column's name can stand alone in the272// brackets, Sales[Unit Price], rather than in brackets of its own,273// Sales[[Total, EUR]]: nothing but letters, digits, _, spaces inside274// and characters beyond ASCII.275func plainColumn(name string) bool {276 if name == "" || name[0] == ' ' || name[len(name)-1] == ' ' {277 return false278 }279 for i := 0; i < len(name); i++ {280 if c := name[i]; !isLetter(c) && !isDigit(c) && c != '_' && c != ' ' && c < 0x80 {281 return false282 }283 }284 return true285}286287// String writes the reference as 012 prints it: Sales[Amount],288// Sales[@Amount], Sales[[#Headers],[Amount]].289func (t TableRef) String() string { return t.write(false) }290291// Excel writes the reference as Excel's files hold it, where the292// formula's own row is [#This Row] rather than @.293func (t TableRef) Excel() string { return t.write(true) }294295func (t TableRef) write(excel bool) string {296 var b strings.Builder297 b.WriteString(t.Table)298 b.WriteByte('[')299 cols := t.columns()300 switch {301 case t.Items == ItemThisRow && !excel:302 b.WriteByte('@')303 if cols != "" && t.To == "" && plainColumn(t.From) && !strings.Contains(t.From, " ") {304 b.WriteString(t.From)305 } else {306 b.WriteString(cols)307 }308 case t.Items == 0 && cols == "":309 case t.Items == 0 && t.To == "" && plainColumn(t.From):310 b.WriteString(t.From)311 case t.Items == 0:312 b.WriteString(cols)313 case cols == "" && t.single():314 b.WriteString(t.words()[0])315 default:316 for i, w := range t.words() {317 if i > 0 {318 b.WriteByte(',')319 }320 b.WriteString("[" + w + "]")321 }322 if cols != "" {323 b.WriteString("," + cols)324 }325 }326 b.WriteByte(']')327 return b.String()328}329330// columns writes the columns named in brackets, [From] or331// [From]:[To], or "" for every column.332func (t TableRef) columns() string {333 if t.From == "" {334 return ""335 }336 s := "[" + EscapeColumn(t.From) + "]"337 if t.To != "" {338 s += ":[" + EscapeColumn(t.To) + "]"339 }340 return s341}342343// single reports whether the reference names one item keyword.344func (t TableRef) single() bool { return len(t.words()) == 1 }345346// words are the item keywords the reference names.347func (t TableRef) words() []string {348 var out []string349 left := t.Items350 for _, w := range itemWords {351 if left&w.item == w.item && w.item != 0 {352 out = append(out, w.word)353 left &^= w.item354 }355 }356 return out357}358359// ExcelTableRef writes a structured reference, the table's name and360// its brackets as written in src, in Excel's file syntax, or reports361// false when it doesn't parse.362func ExcelTableRef(src string) (string, bool) {363 t, err := tableRef(src, 0)364 if err != nil {365 return "", false366 }367 return t.Excel(), true368}369370// WalkTables calls fn for every structured reference in n.371func WalkTables(n Node, fn func(TableRef)) {372 switch n := n.(type) {373 case TableRef:374 fn(n)375 case Unary, Binary, Call, Array, Invoke:376 EachChild(n, func(k Node) { WalkTables(k, fn) })377 }378}