1package formula23import (4 "strings"5 "testing"6)78// Structured references parse into what they name and print back in9// 012's spelling; Excel's file form spells the formula's row #This Row.10func TestTableRefParse(t *testing.T) {11 cases := []struct {12 in string13 want TableRef14 prints string15 excel string16 }{17 {"Sales[Amount]", TableRef{Table: "Sales", From: "Amount"}, "Sales[Amount]", "Sales[Amount]"},18 {"sales[ Amount ]", TableRef{Table: "sales", From: "Amount"}, "sales[Amount]", "sales[Amount]"},19 {"Sales[]", TableRef{Table: "Sales"}, "Sales[]", "Sales[]"},20 {"Sales[#All]", TableRef{Table: "Sales", Items: ItemAll}, "Sales[#All]", "Sales[#All]"},21 {"Sales[#headers]", TableRef{Table: "Sales", Items: ItemHeaders}, "Sales[#Headers]", "Sales[#Headers]"},22 {"Sales[#This Row]", TableRef{Table: "Sales", Items: ItemThisRow}, "Sales[@]", "Sales[#This Row]"},23 {"Sales[@Amount]", TableRef{Table: "Sales", Items: ItemThisRow, From: "Amount"}, "Sales[@Amount]", "Sales[[#This Row],[Amount]]"},24 {"Sales[@[Unit Price]]", TableRef{Table: "Sales", Items: ItemThisRow, From: "Unit Price"}, "Sales[@[Unit Price]]", "Sales[[#This Row],[Unit Price]]"},25 {"Sales[[#This Row],[Amount]]", TableRef{Table: "Sales", Items: ItemThisRow, From: "Amount"}, "Sales[@Amount]", "Sales[[#This Row],[Amount]]"},26 {"Sales[[#Headers],[Amount]]", TableRef{Table: "Sales", Items: ItemHeaders, From: "Amount"}, "Sales[[#Headers],[Amount]]", "Sales[[#Headers],[Amount]]"},27 {"Sales[[#Headers];[#Data];[Amount]:[Units]]", TableRef{Table: "Sales", Items: ItemHeaders | ItemData, From: "Amount", To: "Units"}, "Sales[[#Headers],[#Data],[Amount]:[Units]]", "Sales[[#Headers],[#Data],[Amount]:[Units]]"},28 {"Sales[[Amount]:[Units]]", TableRef{Table: "Sales", From: "Amount", To: "Units"}, "Sales[[Amount]:[Units]]", "Sales[[Amount]:[Units]]"},29 {"Sales[[Total, EUR]]", TableRef{Table: "Sales", From: "Total, EUR"}, "Sales[[Total, EUR]]", "Sales[[Total, EUR]]"},30 {"Sales['#Items]", TableRef{Table: "Sales", From: "#Items"}, "Sales[['#Items]]", "Sales[['#Items]]"},31 {"Sales[[Q'[1']]]", TableRef{Table: "Sales", From: "Q[1]"}, "Sales[[Q'[1']]]", "Sales[[Q'[1']]]"},32 {"Sales[Größe]", TableRef{Table: "Sales", From: "Größe"}, "Sales[Größe]", "Sales[Größe]"},33 {"app[status]", TableRef{Table: "app", From: "status"}, "app[status]", "app[status]"},34 }35 for _, c := range cases {36 n, err := Parse("="+c.in, testFuncs)37 if err != nil {38 t.Errorf("%s: %v", c.in, err)39 continue40 }41 got, ok := n.(TableRef)42 if !ok || got != c.want {43 t.Errorf("%s = %#v, want %#v", c.in, n, c.want)44 continue45 }46 if p := Text(n); p != "="+c.prints {47 t.Errorf("%s prints %s, want %s", c.in, p, c.prints)48 }49 if back, err := Parse(Text(n), testFuncs); err != nil || back != n {50 t.Errorf("%s: printed %s parses as %#v, %v", c.in, Text(n), back, err)51 }52 if x := got.Excel(); x != c.excel {53 t.Errorf("%s in Excel's form is %s, want %s", c.in, x, c.excel)54 }55 }56}5758// Structured references take part in expressions like any reference.59func TestTableRefInFormulas(t *testing.T) {60 n, err := Parse("=SUM(Sales[Amount])*Sales[@Qty]+nu.app", testFuncs)61 if err != nil {62 t.Fatal(err)63 }64 var tables []string65 WalkTables(n, func(r TableRef) { tables = append(tables, r.String()) })66 if strings.Join(tables, " ") != "Sales[Amount] Sales[@Qty]" {67 t.Errorf("walked %v", tables)68 }69 out, changed := Rewrite(n, Rewriter{Table: func(r TableRef) Node {70 r.Table = "Revenue"71 return r72 }})73 if !changed || Text(out) != "=SUM(Revenue[Amount])*Revenue[@Qty]+nu.app" {74 t.Errorf("renamed to %s", Text(out))75 }76}7778func TestTableRefErrors(t *testing.T) {79 for in, msg := range map[string]string{80 "=Sales[Amount": "Missing ]",81 "=Sales[#Everything]": "Unknown table item #Everything",82 "=Sales[[#Nope],[A]]": "Unknown table item #Nope",83 "=Sales[[#This Row],[#Data]]": "#This Row can't be combined",84 "=Sales[[#Headers],[#Totals]]": "#Headers and #Totals need #Data",85 "=Sales[[A],[B]]": "Name one column",86 "=Sales[[A] [B]]": "Expected , between",87 "=Sales[[A]:B]": "Expected [column] after :",88 "=Sales[@[#Headers]]": "Only columns can follow @",89 "=Sales[[A]]x": "Unexpected",90 "=Sales[[A]]]": "Unexpected",91 "=SUM(Sales[[#Data],[A]:[B]]": "Expected , or )",92 "=Sales[Amount]:Sales[Units]+1": "Unexpected",93 } {94 _, err := Parse(in, testFuncs)95 if err == nil || !strings.Contains(err.Error(), msg) {96 t.Errorf("%s: %v, want %q", in, err, msg)97 }98 }99}100101// Separators between a reference's items follow the locale; names in102// brackets are left as they are.103func TestTableRefLocale(t *testing.T) {104 de := mustLocale(t, "de-DE")105 typed := "=SUMME(Sales[[#Headers];[Total, EUR]];1,5)"106 stored := Delocalize(typed, de)107 if stored != "=SUMME(Sales[[#Headers],[Total, EUR]],1.5)" {108 t.Errorf("stored %s", stored)109 }110 if back := Localize(stored, de); back != typed {111 t.Errorf("shown %s", back)112 }113}