1package sheet23import (4 "testing"5)67// Inserting an imported workbook adds its sheets where asked, here after8// the first,9// renaming the ones whose names are taken (and the formulas that use10// them), brings its free named ranges, and undoes as one step.11func TestInsertBook(t *testing.T) {12 w := bookOf(t,13 page{"Sales", map[string]string{"A1": "1"}},14 page{"Notes", map[string]string{"A1": "=Sales!A1+Data!A1"}},15 )16 w.DefineName("Taken", w.Sheet(0), NewRect(at("A1"), at("A1")))17 w.ClearHistory()18 src := bookOf(t,19 page{"Sales", map[string]string{"A1": "10", "A2": "=Data!A1*2"}},20 page{"Data", map[string]string{"A1": "5", "A2": "=Sales!A1+1"}},21 )22 src.DefineName("Taken", src.Sheet(1), NewRect(at("A1"), at("A1")))23 src.DefineName("Fresh", src.Sheet(1), NewRect(at("A1"), at("A1")))24 src.hidePlain(src.Sheet(1))2526 res, err := w.InsertBook(src, 1, "import book.xlsx")27 if err != nil {28 t.Fatal(err)29 }30 if got := namesOf(w); got != "Sales,Sales 2,Data,Notes" {31 t.Fatalf("sheets %s", got)32 }33 if len(res.Sheets) != 2 || res.Renamed["Sales"] != "Sales 2" || res.Names != 1 {34 t.Errorf("result %+v", res)35 }36 for ref, want := range map[string]string{37 "Sales 2!A2": "10", // Data!A1*2 on the imported sheets38 "Data!A2": "11", // Sales!A1+1 now reads Sales 239 "Data!input:A2": "='Sales 2'!A1+1",40 "Notes!A1": "6", // Data exists now41 } {42 if got := show(t, w, ref); got != want {43 t.Errorf("%s = %s, want %s", ref, got, want)44 }45 }46 if !w.Lookup("Data").Hidden() {47 t.Error("Data lost its hidden flag")48 }49 if n, ok := w.LookupName("Fresh"); !ok || n.Sheet != w.Lookup("Data") {50 t.Errorf("Fresh: %v %+v", ok, n)51 }52 if n, _ := w.LookupName("Taken"); n.Sheet != w.Sheet(0) {53 t.Error("an imported name replaced Taken")54 }5556 c, ok := w.Undo()57 if !ok || c.Label != "import book.xlsx" || namesOf(w) != "Sales,Notes" {58 t.Fatalf("undo: %+v, sheets %s", c, namesOf(w))59 }60 if _, ok := w.LookupName("Fresh"); ok || show(t, w, "Notes!A1") != "#REF!" {61 t.Errorf("undo left Fresh %v, Notes!A1 %s", ok, show(t, w, "Notes!A1"))62 }63 w.Redo()64 if namesOf(w) != "Sales,Sales 2,Data,Notes" || show(t, w, "Notes!A1") != "6" {65 t.Errorf("redo: %s", namesOf(w))66 }67}6869// Replacing a sheet puts the imported one in its place, with its name,70// so formulas and named ranges reading it read the new data.71func TestReplaceSheet(t *testing.T) {72 w := bookOf(t,73 page{"Sales", map[string]string{"A1": "1", "B1": "old"}},74 page{"Notes", map[string]string{"A1": "=Sales!A1*2"}},75 )76 w.DefineName("First", w.Sheet(0), NewRect(at("A1"), at("A1")))77 w.Sheet(1).Set(at("A2"), "=First")78 w.ClearHistory()79 src := bookOf(t, page{"sales", map[string]string{"A1": "21"}})8081 old := w.Sheet(0)82 if _, err := w.ReplaceSheet(old, src.Sheet(0), "import sales.csv"); err != nil {83 t.Fatal(err)84 }85 if namesOf(w) != "Sales,Notes" || old.Live() || show(t, w, "Sales!B1") != "" {86 t.Fatalf("sheets %s", namesOf(w))87 }88 if show(t, w, "Notes!A1") != "42" || show(t, w, "Notes!A2") != "21" {89 t.Errorf("Notes!A1 %s, A2 %s", show(t, w, "Notes!A1"), show(t, w, "Notes!A2"))90 }91 c, _ := w.Undo()92 if c.Label != "import sales.csv" || w.Sheet(0) != old || show(t, w, "Notes!A1") != "2" || show(t, w, "Notes!A2") != "1" {93 t.Errorf("undo: %+v, Notes!A1 %s", c, show(t, w, "Notes!A1"))94 }95 w.Redo()96 if show(t, w, "Notes!A1") != "42" {97 t.Errorf("redo: %s", show(t, w, "Notes!A1"))98 }99}100101func namesOf(w *Workbook) string {102 var out string103 for i, s := range w.sheets {104 if i > 0 {105 out += ","106 }107 out += s.name108 }109 return out110}111112// hidePlain hides s outside the history, as a loader would.113func (w *Workbook) hidePlain(s *Sheet) { s.tabHidden = true }114115// Replacing a sheet keeps the charts that fit the new data: one that116// drew the whole table is re-pointed to the new table when it has as117// many columns and removed when it hasn't; one of part of a table keeps118// its range; one whose range is empty now is removed. Undo brings the119// removed ones back.120func TestReplaceSheetKeepsCharts(t *testing.T) {121 w := bookOf(t, page{"Sales", map[string]string{122 "A1": "Month", "B1": "Total", "A2": "Jan", "B2": "1", "A3": "Feb", "B3": "2",123 "E1": "x", "E2": "3", "H1": "9",124 }})125 old := w.Sheet(0)126 old.AddChart(Chart{Data: NewRect(at("A1"), at("B3")), Title: "Totals"})127 old.AddChart(Chart{Data: NewRect(at("E1"), at("E2"))})128 old.AddChart(Chart{Data: NewRect(at("H1"), at("H1"))})129 old.AddChart(Chart{Data: NewRect(at("B2"), at("B3")), Title: "Part"})130 w.ClearHistory()131 src := bookOf(t, page{"sales", map[string]string{132 "A1": "Month", "B1": "Total", "A2": "Jan", "B2": "4", "A3": "Feb", "B3": "5", "A4": "Mar", "B4": "6",133 "E1": "a", "F1": "b", "E2": "1", "F2": "2",134 }})135 fates, err := w.ReplaceSheet(old, src.Sheet(0), "import sales.csv")136 if err != nil {137 t.Fatal(err)138 }139 want := []ChartFate{140 {Name: "Totals", Was: NewRect(at("A1"), at("B3")), Now: NewRect(at("A1"), at("B4"))},141 {Name: "Chart 2", Was: NewRect(at("E1"), at("E2")), Now: NewRect(at("E1"), at("E2")), Removed: true}, // a column more142 {Name: "Chart 3", Was: NewRect(at("H1"), at("H1")), Now: NewRect(at("H1"), at("H1")), Removed: true}, // empty143 {Name: "Part", Was: NewRect(at("B2"), at("B3")), Now: NewRect(at("B2"), at("B3"))},144 }145 if len(fates) != len(want) {146 t.Fatalf("fates %+v", fates)147 }148 for i := range want {149 if fates[i] != want[i] {150 t.Errorf("chart %d: %+v, want %+v", i+1, fates[i], want[i])151 }152 }153 charts := w.Sheet(0).Charts()154 if len(charts) != 2 || charts[0].Data != want[0].Now || charts[0].Title != "Totals" || charts[1].Title != "Part" {155 t.Errorf("charts on the new sheet: %+v", charts)156 }157 w.Undo()158 if w.Sheet(0) != old || len(old.Charts()) != 4 || old.Charts()[0].Data != want[0].Was {159 t.Errorf("undo: charts %+v", old.Charts())160 }161}