1package sheet23import (4 "bytes"5 "strings"6 "testing"7)89// salesSheet is a sheet with a table Sales over A1:C4: Region, Units and10// Amount, three rows of data.11func salesSheet(t *testing.T) *Sheet {12 t.Helper()13 s := sheetOf(t, map[string]string{14 "A1": "Region", "B1": "Units", "C1": "Amount",15 "A2": "North", "B2": "2", "C2": "10",16 "A3": "South", "B3": "3", "C3": "20",17 "A4": "East", "B4": "5", "C4": "30",18 })19 if err := s.CreateTable("Sales", rect("A1:C4")); err != nil {20 t.Fatal(err)21 }22 return s23}2425// entry is the formula or entry at a.26func entry(s *Sheet, a string) string {27 if c := s.Cell(at(a)); c != nil {28 return c.Input29 }30 return ""31}3233func TestTableStructuredReferences(t *testing.T) {34 s := salesSheet(t)35 for a, f := range map[string]string{36 "E1": "=SUM(Sales[Amount])",37 "E2": "=ROWS(Sales[#All])",38 "E3": "=Sales[[#Headers],[Units]]",39 "E4": "=COLUMNS(Sales)*10+ROWS(Sales)",40 "E5": "=SUM(Sales[[Units]:[Amount]])",41 "D2": "=Sales[@Units]*Sales[@Amount]",42 "E6": "=Sales[@Amount]",43 "E7": "=SUM(Sales[Nope])",44 "E8": "=SUM(Nope[Amount])",45 "E9": "=ROWS(Sales[#Data])+ROWS(sales[#headers])",46 } {47 if err := s.Set(at(a), f); err != nil {48 t.Fatalf("%s: %v", f, err)49 }50 }51 wantShown(t, s, map[string]string{52 "E1": "60", "E2": "4", "E3": "Units", "E4": "33", "E5": "70", "D2": "20",53 "E6": "#REF!", "E7": "#REF!", "E8": "#REF!", "E9": "4",54 })55 // Changing a cell of the table recalculates what reads it.56 s.Set(at("C2"), "100")57 wantShown(t, s, map[string]string{"E1": "150", "D2": "200"})58 if deps := s.Dependents(at("C3")); len(deps) == 0 {59 t.Error("no dependents traced through the table")60 }61}6263// Rows typed or pasted just below a table join it, in the same undo64// step as the entry.65func TestTableGrows(t *testing.T) {66 s := salesSheet(t)67 s.Set(at("E1"), "=SUM(Sales[Amount])")68 s.Set(at("C5"), "40")69 if tb, _ := s.TableAt(at("C5")); tb.Range != rect("A1:C5") {70 t.Fatalf("table %v", tb.Range)71 }72 wantShown(t, s, map[string]string{"E1": "100"})73 s.Set(at("A7"), "not next to it")74 if _, ok := s.TableAt(at("A7")); ok {75 t.Error("a row with a gap joined the table")76 }77 s.Undo()78 s.Undo()79 if tb, _ := s.TableAt(at("A1")); tb.Range != rect("A1:C4") {80 t.Errorf("undo left %v", tb.Range)81 }82 wantShown(t, s, map[string]string{"E1": "60"})83 clip := s.Copy(rect("A2:C3"))84 if _, err := s.Paste(clip, rect("A5"), false); err != nil {85 t.Fatal(err)86 }87 if tb, _ := s.TableAt(at("A1")); tb.Range != rect("A1:C6") {88 t.Errorf("paste below grew it to %v", tb.Range)89 }90 wantShown(t, s, map[string]string{"E1": "90"})91}9293// A header changed in place renames its column in the formulas that94// read it; blank and repeated headers get names of their own.95func TestTableHeaderRenames(t *testing.T) {96 s := salesSheet(t)97 s.Set(at("E1"), "=SUM(Sales[Amount])+Sales[[#Headers],[Units]:[Amount]]")98 s.Set(at("C1"), "Revenue")99 if got := entry(s, "E1"); got != "=SUM(Sales[Revenue])+Sales[[#Headers],[Units]:[Revenue]]" {100 t.Errorf("formula after renaming: %s", got)101 }102 s.Undo()103 if got := entry(s, "E1"); got != "=SUM(Sales[Amount])+Sales[[#Headers],[Units]:[Amount]]" {104 t.Errorf("formula after undo: %s", got)105 }106 s.Set(at("B1"), "")107 s.Set(at("A1"), "Amount")108 tb, _ := s.TableAt(at("A1"))109 if strings.Join(tb.Cols, ",") != "Amount2,Column2,Amount" || entry(s, "A1") != "Amount2" || entry(s, "B1") != "Column2" {110 t.Errorf("columns %v, A1 %q, B1 %q", tb.Cols, entry(s, "A1"), entry(s, "B1"))111 }112}113114// Columns inserted, deleted and moved keep formulas reading columns by115// name; a deleted column's references show #REF!.116func TestTableColumnsShift(t *testing.T) {117 s := salesSheet(t)118 s.Set(at("F1"), "=SUM(Sales[Amount])")119 s.Set(at("F2"), "=SUM(Sales[Units])")120 s.InsertCols(1, 1)121 tb, _ := s.TableAt(at("A1"))122 if tb.Range != rect("A1:D4") || strings.Join(tb.Cols, ",") != "Region,Column2,Units,Amount" {123 t.Fatalf("after insert: %v %v", tb.Range, tb.Cols)124 }125 wantShown(t, s, map[string]string{"G1": "60", "G2": "10", "B1": "Column2"})126 s.DeleteCols(2, 1)127 wantShown(t, s, map[string]string{"F1": "60", "F2": "#REF!"})128 if got := entry(s, "F2"); got != "=SUM(Sales[Units])" {129 t.Errorf("formula after delete: %s", got)130 }131 // The whole table moved: formulas follow it.132 if _, err := s.Move(rect("A1:C4"), at("A10")); err != nil {133 t.Fatal(err)134 }135 if tb, _ := s.TableAt(at("A10")); tb.Name != "Sales" || tb.Range != rect("A10:C13") {136 t.Errorf("after move: %v", tb)137 }138 wantShown(t, s, map[string]string{"F1": "60"})139 s.DeleteRows(9, 1)140 if s.HasTables() {141 t.Error("a table kept without its header row")142 }143 wantShown(t, s, map[string]string{"F1": "#REF!"})144}145146// Filling a formula down a table reads each row's own cells; copying147// keeps structured references as they are.148func TestTableFillAndCopy(t *testing.T) {149 s := salesSheet(t)150 if err := s.Book().ResizeTable("Sales", rect("A1:D4")); err != nil {151 t.Fatal(err)152 }153 s.Set(at("D1"), "Total")154 s.Set(at("D2"), "=Sales[@Units]*Sales[@Amount]")155 if _, err := s.FillDown(rect("D2:D4")); err != nil {156 t.Fatal(err)157 }158 wantShown(t, s, map[string]string{"D2": "20", "D3": "60", "D4": "150"})159 if got := entry(s, "D4"); got != "=Sales[@Units]*Sales[@Amount]" {160 t.Errorf("filled %s", got)161 }162 clip := s.Copy(rect("D2"))163 s.Paste(clip, rect("H9"), false)164 if got := entry(s, "H9"); got != "=Sales[@Units]*Sales[@Amount]" {165 t.Errorf("pasted %s", got)166 }167 wantShown(t, s, map[string]string{"H9": "#REF!"})168}169170func TestTableRenameAndRemove(t *testing.T) {171 s := salesSheet(t)172 w := s.Book()173 s.Set(at("E1"), "=SUM(Sales[Amount])+ROWS(Sales)")174 s.Set(at("D3"), "=Sales[@Amount]")175 if err := w.RenameTable("sales", "Revenue"); err != nil {176 t.Fatal(err)177 }178 if got := entry(s, "E1"); got != "=SUM(Revenue[Amount])+ROWS(Revenue)" {179 t.Errorf("after rename: %s", got)180 }181 if err := w.RemoveTable("Revenue"); err != nil {182 t.Fatal(err)183 }184 if got := entry(s, "E1"); got != "=SUM($C$2:$C$4)+ROWS($A$2:$C$4)" {185 t.Errorf("after remove: %s", got)186 }187 if got := entry(s, "D3"); got != "=$C3" {188 t.Errorf("this row after remove: %s", got)189 }190 wantShown(t, s, map[string]string{"E1": "63", "D3": "20"})191 s.Undo()192 if got := entry(s, "E1"); got != "=SUM(Revenue[Amount])+ROWS(Revenue)" || !s.HasTables() {193 t.Errorf("undo remove: %s", got)194 }195}196197func TestTableNames(t *testing.T) {198 s := salesSheet(t)199 w := s.Book()200 if err := s.CreateTable("Other", rect("B2:D6")); err != ErrTableOverlap {201 t.Errorf("overlap: %v", err)202 }203 for _, name := range []string{"Sales", "A1", "nu.x", "", "two words"} {204 if err := s.CreateTable(name, rect("H1:I3")); err == nil {205 t.Errorf("%q named a table", name)206 }207 }208 if err := w.DefineName("sales", s, rect("A1")); err == nil {209 t.Error("a named range took a table's name")210 }211 if w.NextTableName() != "Table1" {212 t.Errorf("next name %s", w.NextTableName())213 }214 if err := s.CreateTable("One", rect("H1:I1")); err != nil {215 t.Fatal(err)216 }217 if tb, _ := s.TableAt(at("H1")); tb.Range != rect("H1:I2") || strings.Join(tb.Cols, ",") != "Column1,Column2" {218 t.Errorf("a one-row table: %v %v", tb.Range, tb.Cols)219 }220}221222// A filter on a table's range grows with the table.223func TestTableFilterFollows(t *testing.T) {224 s := salesSheet(t)225 s.CreateFilter(rect("A1:C4"))226 s.Set(at("A5"), "West")227 if r, _ := s.FilterRange(); r != rect("A1:C5") {228 t.Errorf("filter %v", r)229 }230}231232func TestTableFileRoundTrip(t *testing.T) {233 s := salesSheet(t)234 w := s.Book()235 w.SetTableStyle("Sales", false, true)236 s.Set(at("E1"), "=SUM(Sales[Amount])")237 var b bytes.Buffer238 if err := s.Write(&b); err != nil {239 t.Fatal(err)240 }241 if !strings.Contains(b.String(), `"version": 6`) || !strings.Contains(b.String(), `"tables": [{"name":"Sales","range":"A1:C4","columns":["Region","Units","Amount"],"header":true}]`) {242 t.Fatalf("file:\n%s", b.String())243 }244 got, err := Read(&b)245 if err != nil {246 t.Fatal(err)247 }248 tb, ok := got.TableAt(at("B2"))249 if !ok || tb.Name != "Sales" || tb.Banded || !tb.Header {250 t.Errorf("read back %+v", tb)251 }252 wantShown(t, got, map[string]string{"E1": "60"})253}254255// A notebook cell's output sent to a sheet is a table by the region's256// name; nu.name stays the whole table.257func TestRegionIsTable(t *testing.T) {258 s := New()259 sent(t, s, "app", "B2")260 apply(t, s, LiveOp{Region: "app", Reset: true, Header: liveRow("status", "ms"), Rows: []LiveRow{liveRow("ok", "12"), liveRow("fail", "30")}})261 s.Set(at("A1"), "=SUM(app[ms])")262 s.Set(at("A2"), "=ROWS(app)")263 s.Set(at("A3"), "=ROWS(nu.app)")264 s.Set(at("A4"), "=COUNTIF(app[status],\"ok\")")265 wantShown(t, s, map[string]string{"A1": "42", "A2": "2", "A3": "3", "A4": "1"})266 apply(t, s, LiveOp{Region: "app", Reset: true, Header: liveRow("status", "ms"), Rows: []LiveRow{liveRow("ok", "1")}})267 wantShown(t, s, map[string]string{"A1": "1", "A2": "1"})268 if err := s.CreateTable("app", rect("H1:I3")); err == nil {269 t.Error("a table took a region's name")270 }271}272273// Tracing follows structured references: a formula's precedents name274// the table's cells as written, and a table's cells list the formulas275// reading them.276func TestTableTracing(t *testing.T) {277 s := salesSheet(t)278 s.Set(at("E1"), "=SUM(Sales[Amount])+ROWS(Sales)")279 s.Set(at("D3"), "=Sales[@Units]*2")280 links := s.PrecedentLinks(at("E1"))281 if len(links) != 2 || links[0].Kind != LinkTable || links[0].Name != "Sales[Amount]" || links[0].Range != rect("C2:C4") ||282 links[1].Name != "Sales" || links[1].Range != rect("A2:C4") {283 t.Errorf("precedents %+v", links)284 }285 deps, _ := s.DependentLinks(at("B3"), 0)286 if len(deps) != 2 {287 t.Errorf("dependents of B3 %+v", deps)288 }289 st := s.EvaluateSteps(at("D3"))290 if expr, value, ok := st.Next(); !ok || expr != "Sales[@Units]" || value != "3" || st.Result() != "6" {291 t.Errorf("first step %q = %q, result %q", expr, value, st.Result())292 }293}