1package sheet23import (4 "bytes"5 "strconv"6 "strings"7 "testing"8)910// page is one sheet of a test workbook: its name and cell inputs.11type page struct {12 name string13 cells map[string]string14}1516// bookOf builds a workbook from pages, without undo history.17func bookOf(t *testing.T, pages ...page) *Workbook {18 t.Helper()19 w := emptyBook()20 for _, p := range pages {21 w.insert(w.newSheet(p.name), len(w.sheets))22 }23 for i, p := range pages {24 for a, in := range p.cells {25 if err := w.sheets[i].put(at(a), in); err != nil {26 t.Fatalf("%s!%s: %v", p.name, a, err)27 }28 }29 }30 w.RecalcAll()31 return w32}3334// show is what a cell shows, for comparing in tests: its value, or its35// input with "input:" in front of the address.36func show(t *testing.T, w *Workbook, ref string) string {37 t.Helper()38 sheet, rest := SplitSheet(ref)39 s := w.Lookup(sheet)40 if s == nil {41 t.Fatalf("no sheet %q", sheet)42 }43 if in, ok := strings.CutPrefix(rest, "input:"); ok {44 if c := s.Cell(at(in)); c != nil {45 return c.Input46 }47 return ""48 }49 return s.Value(at(rest)).String()50}5152func expect(t *testing.T, w *Workbook, want map[string]string) {53 t.Helper()54 for ref, v := range want {55 if got := show(t, w, ref); got != v {56 t.Errorf("%s = %q, want %q", ref, got, v)57 }58 }59}6061func TestSheetNames(t *testing.T) {62 w := NewBook()63 tests := []struct{ name, err string }{64 {"Budget", ""},65 {"Sheet1", "There's already a sheet named Sheet1"},66 {"sheet1", "There's already a sheet named Sheet1"},67 {"a/b", `A sheet name can't contain : \ / ? * [ or ]`},68 {"'quoted", "A sheet name can't start or end with '"},69 {strings.Repeat("x", 32), "A sheet name can be at most 31 characters"},70 }71 for _, tt := range tests {72 _, err := w.AddSheet(tt.name, w.Len())73 if got := errText(err); got != tt.err {74 t.Errorf("AddSheet(%q): %q, want %q", tt.name, got, tt.err)75 }76 }77 if s, _ := w.AddSheet("", w.Len()); s.Name() != "Sheet3" {78 t.Errorf("next default name %s, want Sheet3", s.Name())79 }80}8182func errText(err error) string {83 if err == nil {84 return ""85 }86 return err.Error()87}8889func TestCrossSheetValues(t *testing.T) {90 w := bookOf(t,91 page{"Sheet1", map[string]string{92 "A1": "=Data!A1*2", "A2": "=SUM('Q3 plan'!A1:A3)", "A3": "=Nowhere!A1",93 "A4": "=Sales", "A5": "=Data!A1+A1", "A6": "=VLOOKUP(2, 'Q3 plan'!A1:B3, 2, FALSE)",94 }},95 page{"Data", map[string]string{"A1": "21", "B1": "=Sheet1!A1+1"}},96 page{"Q3 plan", map[string]string{"A1": "1", "A2": "2", "A3": "3", "B2": "two"}},97 )98 if err := w.DefineName("Sales", w.Lookup("Q3 plan"), NewRect(at("A1"), at("A3"))); err != nil {99 t.Fatal(err)100 }101 w.Sheet(0).Set(at("A4"), "=SUM(Sales)")102 expect(t, w, map[string]string{103 "Sheet1!A1": "42", "Sheet1!A2": "6", "Sheet1!A3": "#REF!", "Sheet1!A4": "6",104 "Sheet1!A5": "63", "Sheet1!A6": "two", "Data!B1": "43",105 })106 if got := w.Sheet(0).ExplainError(at("A3")); got != "Unresolved sheet name Nowhere" {107 t.Errorf("explain A3: %q", got)108 }109 // Changing a cell on one sheet recalculates the others, in order.110 w.Lookup("Data").Set(at("A1"), "5")111 w.Lookup("Q3 plan").Set(at("A3"), "30")112 expect(t, w, map[string]string{"Sheet1!A1": "10", "Data!B1": "11", "Sheet1!A2": "33", "Sheet1!A4": "33", "Sheet1!A5": "15"})113 // A cycle across sheets is circular.114 w.Lookup("Data").Set(at("A1"), "=Sheet1!A1")115 if !w.Circular || show(t, w, "Sheet1!A1") != "#REF!" {116 t.Errorf("circular %v, A1 %s", w.Circular, show(t, w, "Sheet1!A1"))117 }118 if got := w.Sheet(0).ExplainError(at("A1")); got != "Circular reference: A1 → Data!A1 → A1" {119 t.Errorf("explain cycle: %q", got)120 }121}122123func TestRenameSheetRewritesFormulas(t *testing.T) {124 w := bookOf(t,125 page{"Sheet1", map[string]string{"A1": "=Data!A1+data!A2", "A2": "=SUM(Data!A1:A2)", "A3": "=Other!A1"}},126 page{"Data", map[string]string{"A1": "1", "A2": "2", "A3": "=Data!A1*10"}},127 )128 data := w.Lookup("Data")129 if err := w.RenameSheet(data, "Q3 plan"); err != nil {130 t.Fatal(err)131 }132 expect(t, w, map[string]string{133 "Sheet1!input:A1": "='Q3 plan'!A1+'Q3 plan'!A2", "Sheet1!input:A2": "=SUM('Q3 plan'!A1:A2)",134 "Sheet1!input:A3": "=Other!A1", "Q3 plan!input:A3": "='Q3 plan'!A1*10",135 "Sheet1!A1": "3", "Q3 plan!A3": "10",136 })137 // Renaming to a name formulas already use makes them resolve.138 if err := w.RenameSheet(data, "Other"); err != nil {139 t.Fatal(err)140 }141 expect(t, w, map[string]string{"Sheet1!A3": "1", "Sheet1!input:A1": "=Other!A1+Other!A2"})142 w.Undo()143 w.Undo()144 expect(t, w, map[string]string{"Data!A3": "10", "Sheet1!input:A1": "=Data!A1+data!A2", "Sheet1!A3": "#REF!"})145 if data.Name() != "Data" {146 t.Errorf("undo left the name %s", data.Name())147 }148}149150func TestDeleteSheet(t *testing.T) {151 w := bookOf(t,152 page{"Sheet1", map[string]string{"A1": "=Data!A1*2", "A2": "=Total"}},153 page{"Data", map[string]string{"A1": "21"}},154 )155 data := w.Lookup("Data")156 w.DefineName("Total", data, NewRect(at("A1"), at("A1")))157 if err := w.DeleteSheet(data); err != nil {158 t.Fatal(err)159 }160 expect(t, w, map[string]string{"Sheet1!A1": "#REF!", "Sheet1!A2": "#REF!", "Sheet1!input:A1": "=Data!A1*2"})161 if n, _ := w.LookupName("Total"); n.Ref() != "#REF!" {162 t.Errorf("name on a deleted sheet: %s", n.Ref())163 }164 if err := w.DeleteSheet(w.Sheet(0)); errText(err) != "A spreadsheet needs at least one sheet" {165 t.Errorf("deleting the last sheet: %v", err)166 }167 // Changes to the deleted sheet's old cells don't reach anyone.168 c, ok := w.Undo()169 if !ok || c.Label != "delete sheet Data" || c.Sheet != data || w.Len() != 2 {170 t.Fatalf("undo: %+v %v, %d sheets", c, ok, w.Len())171 }172 expect(t, w, map[string]string{"Sheet1!A1": "42", "Sheet1!A2": "21"})173 w.Redo()174 // A new sheet with the name brings the formulas back.175 s, _ := w.AddSheet("data", 1)176 s.Set(at("A1"), "4")177 expect(t, w, map[string]string{"Sheet1!A1": "8"})178}179180func TestStructureOnOneSheetLeavesOthers(t *testing.T) {181 w := bookOf(t,182 page{"Sheet1", map[string]string{"A1": "1", "A2": "=Data!A2", "A3": "=A1", "A4": "=SUM(Data!A1:A3)"}},183 page{"Data", map[string]string{"A1": "10", "A2": "20", "A3": "30", "B1": "=Sheet1!A1", "B2": "=A2"}},184 )185 one, data := w.Sheet(0), w.Lookup("Data")186 // Rows inserted on Data move references to Data, wherever they are.187 if err := data.InsertRows(0, 1); err != nil {188 t.Fatal(err)189 }190 expect(t, w, map[string]string{191 "Sheet1!input:A2": "=Data!A3", "Sheet1!input:A4": "=SUM(Data!A2:A4)", "Sheet1!A2": "20",192 "Data!input:B2": "=Sheet1!A1", "Data!input:B3": "=A3",193 })194 // Rows inserted on Sheet1 leave its references to Data alone.195 if err := one.InsertRows(0, 2); err != nil {196 t.Fatal(err)197 }198 expect(t, w, map[string]string{199 "Sheet1!input:A4": "=Data!A3", "Sheet1!input:A5": "=A3", "Data!input:B2": "=Sheet1!A3", "Sheet1!A4": "20",200 })201 // Deleting referenced rows on Data leaves #REF! in Sheet1.202 data.DeleteRows(2, 1)203 expect(t, w, map[string]string{"Sheet1!input:A4": "=#REF!", "Sheet1!input:A6": "=SUM(Data!A2:A3)", "Sheet1!A6": "40"})204 w.Undo()205 expect(t, w, map[string]string{"Sheet1!input:A4": "=Data!A3", "Sheet1!A6": "60"})206}207208func TestCopyBetweenSheets(t *testing.T) {209 w := bookOf(t,210 page{"Sheet1", map[string]string{"A1": "5", "B1": "=A1*2", "C1": "=Data!A1+$A$1"}},211 page{"Data", map[string]string{"A1": "100", "A2": "7"}},212 )213 one, data := w.Sheet(0), w.Lookup("Data")214 clip := one.Copy(NewRect(at("B1"), at("C1")))215 if _, err := data.Paste(clip, NewRect(at("B2"), at("B2")), false); err != nil {216 t.Fatal(err)217 }218 // References without a sheet now read Data; relative parts shifted.219 expect(t, w, map[string]string{"Data!input:B2": "=A2*2", "Data!B2": "14", "Data!input:C2": "=Data!A2+$A$1", "Data!C2": "107"})220}221222func TestMoveBetweenSheets(t *testing.T) {223 w := bookOf(t,224 page{"Sheet1", map[string]string{"A1": "5", "A2": "=A1*2", "A3": "=B9", "B1": "=A2+1", "C1": "=Data!Z1"}},225 page{"Data", map[string]string{"B1": "old", "Z1": "=Sheet1!A2", "Z2": "=B1"}},226 page{"Other", map[string]string{"A1": "=Sheet1!A1+Sheet1!A2"}},227 )228 one, data := w.Sheet(0), w.Lookup("Data")229 d, err := one.MoveTo(data, NewRect(at("A1"), at("A3")), at("B1"))230 if err != nil || d != NewRect(at("B1"), at("B3")) {231 t.Fatalf("MoveTo: %v %v", d, err)232 }233 expect(t, w, map[string]string{234 // The moved formulas read what they read, naming Sheet1 for B9.235 "Data!input:B2": "=B1*2", "Data!input:B3": "=Sheet1!B9", "Data!B2": "10",236 // Formulas elsewhere follow the cells to Data.237 "Sheet1!input:B1": "=Data!B2+1", "Sheet1!B1": "11", "Data!input:Z1": "=B2",238 "Other!input:A1": "=Data!B1+Data!B2", "Other!A1": "15",239 // Z2 read the cell the move overwrote.240 "Data!input:Z2": "=#REF!", "Sheet1!input:A1": "",241 })242 c, _ := w.Undo()243 if c.Sheet != data {244 t.Errorf("undo shows %v", c.Sheet)245 }246 expect(t, w, map[string]string{"Sheet1!input:A2": "=A1*2", "Data!B1": "old", "Other!A1": "15", "Data!input:Z2": "=B1"})247}248249func TestSheetUndo(t *testing.T) {250 w := NewBook()251 one := w.Sheet(0)252 one.Set(at("A1"), "=Two!A1")253 two, _ := w.AddSheet("Two", 1)254 two.Set(at("A1"), "7")255 dup, _ := w.DuplicateSheet(two)256 w.MoveSheet(dup, 0)257 w.RenameSheet(two, "Deux")258 order := func() string {259 var names []string260 for _, s := range w.Sheets() {261 names = append(names, s.Name())262 }263 return strings.Join(names, ",")264 }265 if got := order(); got != "Copy of Two,Sheet1,Deux" || show(t, w, "Sheet1!A1") != "7" {266 t.Fatalf("order %s, A1 %s", got, show(t, w, "Sheet1!A1"))267 }268 steps := []string{"Copy of Two,Sheet1,Two", "Sheet1,Two,Copy of Two", "Sheet1,Two", "Sheet1,Two", "Sheet1"}269 for i, want := range steps {270 w.Undo()271 if got := order(); got != want {272 t.Errorf("undo %d: %s, want %s", i+1, got, want)273 }274 }275 if show(t, w, "Sheet1!A1") != "#REF!" {276 t.Errorf("A1 %s after undoing the sheet", show(t, w, "Sheet1!A1"))277 }278 for range steps {279 w.Redo()280 }281 if got := order(); got != "Copy of Two,Sheet1,Deux" || show(t, w, "Sheet1!A1") != "7" || show(t, w, "Copy of Two!A1") != "7" {282 t.Errorf("redo: %s, A1 %s", order(), show(t, w, "Sheet1!A1"))283 }284}285286func TestDuplicateIsIndependent(t *testing.T) {287 w := bookOf(t, page{"Sheet1", map[string]string{"A1": "2", "A2": "=A1*3"}})288 one := w.Sheet(0)289 one.SetFrozen(1, 0)290 cp, err := w.DuplicateSheet(one)291 if err != nil || cp.Name() != "Copy of Sheet1" || w.Index(cp) != 1 {292 t.Fatalf("duplicate %v %v", cp, err)293 }294 cp.Set(at("A1"), "10")295 if r, _ := cp.Frozen(); r != 1 {296 t.Error("frozen rows not copied")297 }298 expect(t, w, map[string]string{"Sheet1!A2": "6", "Copy of Sheet1!A2": "30"})299 if again, _ := w.DuplicateSheet(one); again.Name() != "Copy of Sheet1 2" {300 t.Errorf("second copy named %s", again.Name())301 }302}303304func TestWorkbookFileRoundTrip(t *testing.T) {305 w := bookOf(t,306 page{"Budget", map[string]string{"A1": "Rent", "B1": "1450", "B2": "='Q3 plan'!A1"}},307 page{"Q3 plan", map[string]string{"A1": "=Budget!B1*2"}},308 )309 w.Sheet(1).SetColWidth(0, 14)310 w.Sheet(1).SetFrozen(1, 0)311 w.DefineName("Rent", w.Sheet(0), NewRect(at("B1"), at("B1")))312 w.SetActive(w.Sheet(1))313 var b bytes.Buffer314 if err := w.Write(&b); err != nil {315 t.Fatal(err)316 }317 out := b.String()318 for _, want := range []string{`"version": 4,`, `"names": {"Rent": "Budget!B1"},`, `"active": 1,`,319 ` "name": "Q3 plan",` + "\n" + ` "widths": {"A": 14},`, ` "B2": "='Q3 plan'!A1"`} {320 if !strings.Contains(out, want) {321 t.Errorf("missing %s in\n%s", want, out)322 }323 }324 r, err := ReadBook(strings.NewReader(out))325 if err != nil {326 t.Fatal(err)327 }328 if r.Len() != 2 || r.Active() != 1 || nameRefs(r.Sheet(0)) != "Rent=Budget!B1 " {329 t.Fatalf("read %d sheets, active %d, names %s", r.Len(), r.Active(), nameRefs(r.Sheet(0)))330 }331 expect(t, r, map[string]string{"Budget!B2": "2900", "Q3 plan!A1": "2900"})332 if rows, _ := r.Sheet(1).Frozen(); rows != 1 || r.Sheet(1).ColWidth(0) != 14 || r.CanUndo() {333 t.Error("sheet settings not read back, or loading is undoable")334 }335 var again bytes.Buffer336 r.Write(&again)337 if again.String() != out {338 t.Errorf("second write differs:\n%s", again.String())339 }340}341342// A workbook of one sheet keeps writing the single-sheet format, so older343// builds open it; a renamed sheet adds a field they ignore.344func TestSingleSheetFileStaysCompatible(t *testing.T) {345 w := bookOf(t, page{"Sheet1", map[string]string{"A1": "1"}})346 write := func() string {347 var b bytes.Buffer348 w.Write(&b)349 return b.String()350 }351 if got := write(); got != "{\n \"version\": 2,\n \"cells\": {\n \"A1\": \"1\"\n }\n}\n" {352 t.Errorf("plain sheet:\n%s", got)353 }354 w.RenameSheet(w.Sheet(0), "Budget")355 out := write()356 if !strings.HasPrefix(out, "{\n \"version\": 2,\n \"name\": \"Budget\",\n") {357 t.Errorf("renamed sheet:\n%s", out)358 }359 r, err := Read(strings.NewReader(out))360 if err != nil || r.Name() != "Budget" {361 t.Errorf("read back %v %v", r, err)362 }363 // A formula naming a sheet, even an unknown one, needs version 4.364 w.Sheet(0).Set(at("A2"), "=Other!A1")365 if out := write(); !strings.Contains(out, `"version": 4`) {366 t.Errorf("cross-sheet formula:\n%s", out)367 }368 for _, bad := range []string{369 `{"version": 4, "sheets": []}`,370 `{"version": 4, "sheets": [{"name": "A", "cells": {}}, {"name": "a", "cells": {}}]}`,371 `{"version": 4, "sheets": [{"name": "x/y", "cells": {}}]}`,372 `{"version": 4, "names": {"N": "Missing!A1"}, "sheets": [{"name": "A", "cells": {}}]}`,373 `{"version": 5, "cells": {}}`,374 } {375 if _, err := ReadBook(strings.NewReader(bad)); err == nil {376 t.Errorf("read %s without error", bad)377 }378 }379}380381// Decimal arithmetic is a workbook setting: one undo step for every382// sheet, saved once at the top of the file.383func TestDecimalIsWorkbookWide(t *testing.T) {384 w := bookOf(t,385 page{"Sheet1", map[string]string{"A1": "=0.1+0.2"}},386 page{"Data", map[string]string{"A1": "=Sheet1!A1-0.3=0"}},387 )388 w.SetDecimal(true)389 expect(t, w, map[string]string{"Sheet1!A1": "0.3", "Data!A1": "TRUE"})390 var b bytes.Buffer391 w.Write(&b)392 if out := b.String(); !strings.Contains(out, "{\n \"version\": 4,\n \"arithmetic\": \"decimal\",\n") {393 t.Errorf("file:\n%s", out)394 }395 r, err := ReadBook(&b)396 if err != nil || !r.Decimal() {397 t.Fatalf("read back: decimal %v, %v", r.Decimal(), err)398 }399 c, _ := w.Undo()400 if w.Decimal() || c.Label != "turn off decimal arithmetic" && c.Label != "turn on decimal arithmetic" {401 t.Errorf("undo: %v %q", w.Decimal(), c.Label)402 }403 expect(t, w, map[string]string{"Data!A1": "FALSE"})404 // A version 3 file keeps the setting at the top, as before.405 s, err := Read(strings.NewReader(`{"version": 3, "arithmetic": "decimal", "cells": {"A1": "=0.1+0.2"}}`))406 if err != nil || !s.Decimal() || s.Value(Addr{}).String() != "0.3" {407 t.Errorf("version 3: %v %v", s, err)408 }409}410411// BenchmarkCrossSheetEdit edits a cell that thousands of formulas on412// another sheet read, the case the cross-sheet index scans for.413func BenchmarkCrossSheetEdit(b *testing.B) {414 w := NewBook()415 one := w.Sheet(0)416 data, _ := w.AddSheet("Data", 1)417 for i := range 5000 {418 a := Addr{Row: i}419 data.put(a, strconv.Itoa(i))420 one.put(a, "=Data!A"+strconv.Itoa(i+1)+"*2+SUM(Data!A1:A10)")421 }422 w.RecalcAll()423 b.ResetTimer()424 for i := range b.N {425 data.Set(Addr{}, strconv.Itoa(i))426 }427}428429// BenchmarkSameSheetEdit is BenchmarkCrossSheetEdit on one sheet.430func BenchmarkSameSheetEdit(b *testing.B) {431 s := New()432 for i := range 5000 {433 s.put(Addr{Row: i}, strconv.Itoa(i))434 s.put(Addr{Col: 1, Row: i}, "=A"+strconv.Itoa(i+1)+"*2+SUM(A1:A10)")435 }436 s.RecalcAll()437 b.ResetTimer()438 for i := range b.N {439 s.Set(Addr{}, strconv.Itoa(i))440 }441}442443func TestTraceAcrossSheets(t *testing.T) {444 w := bookOf(t,445 page{"Sheet1", map[string]string{"A1": "1", "B1": "=A1+Data!A1+SUM('Q3 plan'!A1:A2)+Gone!A1", "B2": "=Plan"}},446 page{"Data", map[string]string{"A1": "2", "B1": "=Sheet1!A1", "B2": "=A1"}},447 page{"Q3 plan", map[string]string{"A1": "=Sheet1!A1*2"}},448 )449 one := w.Sheet(0)450 w.DefineName("Plan", w.Sheet(2), NewRect(at("A1"), at("A1")))451 if got, want := targets(one, one.Precedents(at("B1"))), "A1 Data!A1 'Q3 plan'!A1:A2"; got != want {452 t.Errorf("precedents %q, want %q", got, want)453 }454 if got, want := targets(one, one.Precedents(at("B2"))), "'Q3 plan'!A1"; got != want {455 t.Errorf("precedents through a name %q, want %q", got, want)456 }457 if got, want := targets(one, one.Dependents(at("A1"))), "B1 Data!B1 'Q3 plan'!A1"; got != want {458 t.Errorf("dependents %q, want %q", got, want)459 }460 plan := w.Sheet(2)461 if got, want := targets(plan, plan.Dependents(at("A1"))), "Sheet1!B1 Sheet1!B2"; got != want {462 t.Errorf("dependents of Q3 plan!A1 %q, want %q", got, want)463 }464}