1package sheet23import (4 "bytes"5 "reflect"6 "strings"7 "testing"8)910// sales is a small table: region, product, date, units, price.11func sales(t *testing.T) *Workbook {12 t.Helper()13 return bookOf(t, page{"Sales", map[string]string{14 "A1": "Region", "B1": "Product", "C1": "Date", "D1": "Units", "E1": "Price",15 "A2": "East", "B2": "Pens", "C2": "1/5/2026", "D2": "10", "E2": "$2.50",16 "A3": "West", "B3": "Ink", "C3": "1/5/2026", "D3": "4", "E3": "$9.00",17 "A4": "east", "B4": "Ink", "C4": "2/9/2026", "D4": "6", "E4": "$9.00",18 "A5": "North", "B5": "Pens", "C5": "2/9/2026", "D5": "", "E5": "$2.50",19 "A6": "West", "B6": "Pens", "C6": "1/5/2026", "D6": "3", "E6": "$2.75",20 }})21}2223// pivotGrid is what a pivot sheet shows, row by row, cells separated by24// |, trailing blanks dropped.25func pivotGrid(s *Sheet) []string {26 out, ok := s.PivotRange()27 if !ok {28 return nil29 }30 var rows []string31 for row := 0; row <= out.To.Row; row++ {32 var cells []string33 for col := 0; col <= out.To.Col; col++ {34 cells = append(cells, s.ShownText(Addr{Col: col, Row: row}))35 }36 rows = append(rows, strings.TrimRight(strings.Join(cells, "|"), "|"))37 }38 return rows39}4041func checkGrid(t *testing.T, s *Sheet, want ...string) {42 t.Helper()43 if got := pivotGrid(s); !reflect.DeepEqual(got, want) {44 t.Errorf("pivot shows\n %s\nwant\n %s\n(error %q)", strings.Join(got, "\n "), strings.Join(want, "\n "), s.PivotError())45 }46}4748func newPivot(t *testing.T, w *Workbook, p func(*Pivot)) *Sheet {49 t.Helper()50 src := w.Lookup("Sales")51 used, _ := src.UsedRange()52 pv := NewPivot(src, used)53 p(&pv)54 s, err := w.CreatePivot(src, used, "", pv)55 if err != nil {56 t.Fatal(err)57 }58 return s59}6061func TestPivotGroupsRows(t *testing.T) {62 w := sales(t)63 s := newPivot(t, w, func(p *Pivot) {64 p.Rows = []PivotGroup{{Col: 0}}65 p.Values = []PivotValue{{Col: 3, Summarize: SumBy}, {Col: 3, Summarize: CountABy}}66 })67 if s.Name() != "Pivot Table 1" || w.Index(s) != 1 {68 t.Errorf("sheet %q at %d, want Pivot Table 1 after Sales", s.Name(), w.Index(s))69 }70 // Text groups ignore case, keeping the first spelling, as Sheets.71 checkGrid(t, s,72 "Region|SUM of Units|COUNTA of Units",73 "East|16|2",74 "North|0|0",75 "West|7|2",76 "Grand Total|23|4")77 if c := s.Cell(at("A5")); !c.Style.Bold {78 t.Error("the Grand Total row isn't bold")79 }80 // A new pivot sheet's columns fit the results, until set by hand.81 if s.ColWidth(0) != 13 || s.ColWidth(2) != 17 {82 t.Errorf("widths %d %d, want 13 17", s.ColWidth(0), s.ColWidth(2))83 }84 s.SetColWidth(0, 20)85 w.Lookup("Sales").Set(at("A2"), "Eastern seaboard region")86 if s.ColWidth(0) != 20 {87 t.Errorf("width %d after setting it by hand", s.ColWidth(0))88 }89}9091func TestPivotFilterValues(t *testing.T) {92 w := sales(t)93 s := newPivot(t, w, func(p *Pivot) {94 p.Filters = []PivotFilter{95 {Col: 0, Criteria: Criteria{Hidden: []string{"West"}}},96 {Col: 1, Criteria: Criteria{Hidden: []string{"Ink"}}},97 }98 })99 p, _ := s.Pivot()100 // Region's values among the rows Product's filter lets through.101 got := w.PivotFilterValues(p, 0)102 want := []FilterValue{{"East", "East", 1, true}, {"North", "North", 1, true}, {"West", "West", 1, false}}103 if !reflect.DeepEqual(got, want) {104 t.Errorf("values %v, want %v", got, want)105 }106}107108func TestPivotColumnsAndSubtotals(t *testing.T) {109 w := sales(t)110 s := newPivot(t, w, func(p *Pivot) {111 p.Rows = []PivotGroup{{Col: 1}, {Col: 0}}112 p.Columns = []PivotGroup{{Col: 2}}113 p.Values = []PivotValue{{Col: 3, Summarize: SumBy}}114 })115 // Dates group by day and show as dates.116 checkGrid(t, s,117 "|Date|1/5/2026|2/9/2026|Grand Total",118 "Product|Region|SUM of Units|SUM of Units|SUM of Units",119 "Ink|east||6|6",120 "|West|4||4",121 "Ink Total||4|6|10",122 "Pens|East|10||10",123 "|North||0|0",124 "|West|3||3",125 "Pens Total||13|0|13",126 "Grand Total||17|6|23")127 p, _ := s.Pivot()128 p.RowTotals, p.ColumnTotals = false, false129 if err := s.SetPivot(p, ""); err != nil {130 t.Fatal(err)131 }132 checkGrid(t, s,133 "|Date|1/5/2026|2/9/2026",134 "Product|Region|SUM of Units|SUM of Units",135 "Ink|east||6",136 "|West|4",137 "Pens|East|10",138 "|North||0",139 "|West|3")140}141142func TestPivotSummaries(t *testing.T) {143 w := sales(t)144 var vals []PivotValue145 for _, f := range Summaries() {146 vals = append(vals, PivotValue{Col: 4, Summarize: f})147 }148 s := newPivot(t, w, func(p *Pivot) {149 p.Rows = []PivotGroup{{Col: 1}}150 p.Values = vals151 })152 // Sums, averages and extremes keep the column's currency format.153 checkGrid(t, s,154 "Product|SUM of Price|COUNTA of Price|COUNT of Price|COUNTUNIQUE of Price|AVERAGE of Price|MAX of Price|MIN of Price",155 "Ink|$18.00|2|2|1|$9.00|$9.00|$9.00",156 "Pens|$7.75|3|3|2|$2.58|$2.75|$2.50",157 "Grand Total|$25.75|5|5|3|$5.15|$9.00|$2.50")158}159160func TestPivotMixedKeysAndBlanks(t *testing.T) {161 w := bookOf(t, page{"Sales", map[string]string{162 "A1": "Key", "B1": "N",163 "A2": "1", "B2": "1",164 "A3": "'1", "B3": "2",165 "A4": "TRUE", "B4": "4",166 "A5": "", "B5": "8",167 "A6": "b", "B6": "16",168 "A7": "=1/0", "B7": "32",169 "A8": "B", "B8": "=1/0",170 }})171 s := newPivot(t, w, func(p *Pivot) {172 p.Rows = []PivotGroup{{Col: 0}}173 p.Values = []PivotValue{{Col: 1, Summarize: SumBy}, {Col: 1, Summarize: CountBy}}174 })175 // Numbers, then text, booleans and errors; blanks last. 1 and "1"176 // are different keys; an error in the values shows in their sums.177 checkGrid(t, s,178 "Key|SUM of N|COUNT of N",179 "1|1|1",180 "1|2|1",181 "b|#DIV/0!|1",182 "TRUE|4|1",183 "#DIV/0!|32|1",184 "|8|1",185 "Grand Total|#DIV/0!|6")186}187188func TestPivotSortByValueAndShowAs(t *testing.T) {189 w := sales(t)190 s := newPivot(t, w, func(p *Pivot) {191 p.Rows = []PivotGroup{{Col: 0, Desc: true, SortBy: 1}}192 p.Values = []PivotValue{{Col: 3, Summarize: SumBy}, {Col: 3, Summarize: SumBy, ShowAs: ShowPctTotal}}193 })194 checkGrid(t, s,195 "Region|SUM of Units|SUM of Units",196 "East|16|69.57%",197 "West|7|30.43%",198 "North|0|0.00%",199 "Grand Total|23|100.00%")200 p, _ := s.Pivot()201 p.Rows[0] = PivotGroup{Col: 0, Desc: true}202 s.SetPivot(p, "")203 checkGrid(t, s,204 "Region|SUM of Units|SUM of Units",205 "West|7|30.43%",206 "North|0|0.00%",207 "East|16|69.57%",208 "Grand Total|23|100.00%")209}210211func TestPivotFilters(t *testing.T) {212 w := sales(t)213 s := newPivot(t, w, func(p *Pivot) {214 p.Rows = []PivotGroup{{Col: 1}}215 p.Values = []PivotValue{{Col: 3, Summarize: SumBy}}216 p.Filters = []PivotFilter{217 {Col: 0, Criteria: Criteria{Hidden: []string{"North"}}},218 {Col: 3, Criteria: Criteria{Cond: Condition{Op: CondGreater, Arg: "3"}}},219 }220 })221 checkGrid(t, s,222 "Product|SUM of Units",223 "Ink|10",224 "Pens|10",225 "Grand Total|20")226}227228func TestPivotFrequency(t *testing.T) {229 w := sales(t)230 src := w.Lookup("Sales")231 r, _ := src.UsedRange()232 s, err := w.CreatePivot(src, r, "Frequency of Product", FrequencyPivot(src, r, 1))233 if err != nil {234 t.Fatal(err)235 }236 checkGrid(t, s,237 "Product|Count|Percent",238 "Pens|3|60.00%",239 "Ink|2|40.00%",240 "Grand Total|5|100.00%")241 // Blanks are counted too.242 src.Set(at("B6"), "")243 checkGrid(t, s,244 "Product|Count|Percent",245 "Ink|2|40.00%",246 "Pens|2|40.00%",247 "|1|20.00%",248 "Grand Total|5|100.00%")249}250251func TestPivotLiveUndoAndReads(t *testing.T) {252 w := sales(t)253 s := newPivot(t, w, func(p *Pivot) {254 p.Rows = []PivotGroup{{Col: 0}}255 p.Values = []PivotValue{{Col: 3, Summarize: SumBy}}256 })257 src := w.Lookup("Sales")258 // A formula elsewhere reads the pivot's result, and follows it.259 if err := src.Set(at("G1"), "='Pivot Table 1'!B5*2"); err != nil {260 t.Fatal(err)261 }262 if got := show(t, w, "Sales!G1"); got != "46" {263 t.Fatalf("G1 = %s, want 46", got)264 }265 src.Set(at("D2"), "100")266 checkGrid(t, s, "Region|SUM of Units", "East|106", "North|0", "West|7", "Grand Total|113")267 if got := show(t, w, "Sales!G1"); got != "226" {268 t.Errorf("G1 = %s after the edit, want 226", got)269 }270 // Rows inserted inside the source range grow it.271 if err := src.InsertRows(5, 1); err != nil {272 t.Fatal(err)273 }274 src.Set(at("A6"), "South")275 src.Set(at("D6"), "1")276 checkGrid(t, s, "Region|SUM of Units", "East|106", "North|0", "South|1", "West|7", "Grand Total|114")277278 for range 4 {279 w.Undo()280 }281 checkGrid(t, s, "Region|SUM of Units", "East|16", "North|0", "West|7", "Grand Total|23")282 if got := show(t, w, "Sales!G1"); got != "46" {283 t.Errorf("G1 = %s after undo, want 46", got)284 }285 // Undoing the pivot's creation removes its sheet; redo brings it back.286 w.Undo()287 w.Undo()288 if w.Lookup("Pivot Table 1") != nil {289 t.Fatal("undo left the pivot sheet")290 }291 w.Redo()292 checkGrid(t, w.Lookup("Pivot Table 1"), "Region|SUM of Units", "East|16", "North|0", "West|7", "Grand Total|23")293}294295func TestPivotEditsAreOneStep(t *testing.T) {296 w := sales(t)297 s := newPivot(t, w, func(p *Pivot) { p.Rows = []PivotGroup{{Col: 0}} })298 p, _ := s.Pivot()299 p.Values = []PivotValue{{Col: 3, Summarize: MaxBy}}300 if err := s.SetPivot(p, "add a value"); err != nil {301 t.Fatal(err)302 }303 checkGrid(t, s, "Region|MAX of Units", "East|10", "North|0", "West|4", "Grand Total|10")304 c, ok := w.Undo()305 if !ok || c.Label != "add a value" {306 t.Fatalf("undo %q, want add a value", c.Label)307 }308 checkGrid(t, s, "Region", "East", "North", "West")309}310311func TestPivotRefusesEdits(t *testing.T) {312 w := sales(t)313 s := newPivot(t, w, func(p *Pivot) {314 p.Rows = []PivotGroup{{Col: 0}}315 p.Values = []PivotValue{{Col: 3, Summarize: SumBy}}316 })317 if err := s.Set(at("B2"), "5"); err != ErrPivotEdit {318 t.Errorf("Set in the results: %v, want ErrPivotEdit", err)319 }320 if !s.InPivot(NewRect(at("C4"), at("B9"))) || s.InPivot(NewRect(at("C1"), at("D9"))) {321 t.Error("InPivot is wrong about the results' range B1:B5")322 }323 // Next to the results is fine; a result growing into it is an error324 // the pivot shows, as in Sheets.325 if err := s.Set(at("A6"), "note"); err != nil {326 t.Fatal(err)327 }328 src := w.Lookup("Sales")329 src.InsertRows(5, 1)330 src.Set(at("A6"), "South")331 if got := show(t, w, "'Pivot Table 1'!A1"); got != "#REF!" || !strings.Contains(s.ExplainError(at("A1")), "overwrite data in A6") {332 t.Errorf("A1 = %s (%q), want #REF! explaining the overlap", got, s.ExplainError(at("A1")))333 }334 if got := show(t, w, "'Pivot Table 1'!A6"); got != "note" {335 t.Errorf("A6 = %q, want the note kept", got)336 }337 // Clearing the note makes room again.338 if err := s.Set(at("A6"), ""); err != nil {339 t.Fatal(err)340 }341 checkGrid(t, s, "Region|SUM of Units", "East|16", "North|0", "South|0", "West|7", "Grand Total|23")342 w.Undo()343 // Copying results pastes their values.344 w.Undo()345 w.Undo()346 clip := s.Copy(NewRect(at("A2"), at("B2")))347 if _, err := src.Paste(clip, NewRect(at("H1"), at("H1")), false); err != nil {348 t.Fatal(err)349 }350 if got := show(t, w, "Sales!input:I1"); got != "16" || src.Cell(at("I1")).derived {351 t.Errorf("pasted %q, want the plain value 16", got)352 }353}354355func TestPivotFollowsSourceSheet(t *testing.T) {356 w := sales(t)357 s := newPivot(t, w, func(p *Pivot) {358 p.Rows = []PivotGroup{{Col: 1}}359 p.Values = []PivotValue{{Col: 3, Summarize: SumBy}}360 })361 src := w.Lookup("Sales")362 if err := w.RenameSheet(src, "Q1"); err != nil {363 t.Fatal(err)364 }365 if p, _ := s.Pivot(); p.Source != "Q1" {366 t.Errorf("source %q after renaming, want Q1", p.Source)367 }368 // A column inserted before the data moves the fields along.369 if err := src.InsertCols(0, 1); err != nil {370 t.Fatal(err)371 }372 p, _ := s.Pivot()373 if p.Range != NewRect(at("B1"), at("F6")) || p.Rows[0].Col != 2 || p.Values[0].Col != 4 {374 t.Errorf("after inserting a column: %+v", p)375 }376 checkGrid(t, s, "Product|SUM of Units", "Ink|10", "Pens|13", "Grand Total|23")377 // Deleting the value column drops the value.378 src.DeleteCols(4, 1)379 checkGrid(t, s, "Product", "Ink", "Pens")380 // Deleting the source sheet shows #REF!, and undo brings it back.381 if err := w.DeleteSheet(src); err != nil {382 t.Fatal(err)383 }384 if got := show(t, w, "'Pivot Table 1'!A1"); got != "#REF!" || !strings.Contains(s.PivotError(), "doesn't exist") {385 t.Errorf("A1 = %s (%q) without the source", got, s.PivotError())386 }387 w.Undo()388 checkGrid(t, s, "Product", "Ink", "Pens")389}390391func TestPivotRefusesLoops(t *testing.T) {392 w := sales(t)393 a := newPivot(t, w, func(p *Pivot) { p.Rows = []PivotGroup{{Col: 0}} })394 b, err := w.CreatePivot(a, NewRect(at("A1"), at("A4")), "", NewPivot(a, NewRect(at("A1"), at("A4"))))395 if err != nil {396 t.Fatal(err)397 }398 p, _ := a.Pivot()399 p.Source = b.Name()400 if err := a.SetPivot(p, ""); err != errPivotLoop {401 t.Errorf("a loop: %v", err)402 }403 p.Source = a.Name()404 if err := a.SetPivot(p, ""); err != errPivotSelf {405 t.Errorf("its own sheet: %v", err)406 }407}408409func TestPivotFileRoundTrip(t *testing.T) {410 w := sales(t)411 s := newPivot(t, w, func(p *Pivot) {412 p.Rows = []PivotGroup{{Col: 0, Desc: true, SortBy: 1}}413 p.Columns = []PivotGroup{{Col: 1}}414 p.Values = []PivotValue{{Col: 3, Summarize: AverageBy, ShowAs: ShowPctRow, Name: "Share"}}415 p.Filters = []PivotFilter{{Col: 2, Criteria: Criteria{Hidden: []string{"2/9/2026"}, Cond: Condition{Op: CondNotEmpty}}}}416 p.ColumnTotals = false417 })418 want := pivotGrid(s)419 var buf bytes.Buffer420 if err := w.Write(&buf); err != nil {421 t.Fatal(err)422 }423 text := buf.String()424 if !strings.Contains(text, `"version": 5`) || !strings.Contains(text, `"pivot": {"source":"Sales!A1:E6"`) {425 t.Fatalf("file:\n%s", text)426 }427 if strings.Contains(text, `"Grand Total"`) {428 t.Error("the pivot's results were saved")429 }430 w2, err := ReadBook(strings.NewReader(text))431 if err != nil {432 t.Fatal(err)433 }434 s2 := w2.Lookup("Pivot Table 1")435 p1, _ := s.Pivot()436 p2, _ := s2.Pivot()437 if !reflect.DeepEqual(p1, p2) {438 t.Errorf("read back\n%+v\nwant\n%+v", p2, p1)439 }440 if got := pivotGrid(s2); !reflect.DeepEqual(got, want) {441 t.Errorf("read back shows %q, want %q", got, want)442 }443 // Without a pivot, the file stays version 4.444 w2.DeleteSheet(s2)445 w2.AddSheet("", 5)446 buf.Reset()447 w2.Write(&buf)448 if !strings.Contains(buf.String(), `"version": 4`) {449 t.Errorf("without pivots: %.40s", buf.String())450 }451}452453// The example in docs/files/format.md reads.454func TestPivotFileExample(t *testing.T) {455 file := `{"version": 5, "sheets": [456 {"name": "Sales", "cells": {"A1": "Region", "B1": "Item", "C1": "Year", "D1": "Units", "A2": "East", "B2": "Pens", "C2": "2026", "D2": "3"}},457 {"name": "Pivot Table 1", "cells": {},458 "pivot": {"source":"Sales!A1:D200","rows":[{"column":"B"},{"column":"A","order":"desc","sortBy":1}],"columns":[{"column":"C"}],"values":[{"column":"D","summarize":"sum"},{"column":"D","summarize":"counta","showAs":"percent_of_total","name":"Share"}],"filters":[{"column":"A","hidden":["North"]}],"rowTotals":true,"columnTotals":false}}459 ]}`460 w, err := ReadBook(strings.NewReader(file))461 if err != nil {462 t.Fatal(err)463 }464 s := w.Lookup("Pivot Table 1")465 checkGrid(t, s,466 "|Year|2026",467 "Item|Region|SUM of Units|Share",468 "Pens|East|3|100.00%",469 "Pens Total||3|100.00%",470 "Grand Total||3|100.00%")471}472473func TestPivotDuplicate(t *testing.T) {474 w := sales(t)475 s := newPivot(t, w, func(p *Pivot) { p.Rows = []PivotGroup{{Col: 1}} })476 cp, err := w.DuplicateSheet(s)477 if err != nil {478 t.Fatal(err)479 }480 checkGrid(t, cp, "Product", "Ink", "Pens")481 w.Lookup("Sales").Set(at("B2"), "Paper")482 checkGrid(t, cp, "Product", "Ink", "Paper", "Pens")483}484485func TestPivotTelemetry(t *testing.T) {486 var got []PivotInfo487 OnPivot = func(_ any, i PivotInfo) { got = append(got, i) }488 defer func() { OnPivot = nil }()489 w := sales(t)490 newPivot(t, w, func(p *Pivot) {491 p.Rows = []PivotGroup{{Col: 0}, {Col: 1}}492 p.Values = []PivotValue{{Col: 3, Summarize: SumBy}}493 })494 if len(got) != 1 || got[0].Records != 5 || got[0].Groups != 8 || got[0].Failed {495 t.Errorf("reported %+v", got)496 }497}