1package sheet23import (4 "encoding/json"5 "fmt"67 "src.rest/cat/012/internal/formula"8)910// filePivot is a pivot table in the file (version 5): its definition, on11// one line after the cells of its sheet, never its results, which are12// computed again on loading:13//14// "pivot": {"source": "Sales!A1:D200", "rows": [{"column": "B"}],15// "values": [{"column": "D", "summarize": "sum"}], "rowTotals": true, "columnTotals": true}16type filePivot struct {17 Source string `json:"source"`18 Rows []filePivotGroup `json:"rows,omitempty"`19 Columns []filePivotGroup `json:"columns,omitempty"`20 Values []filePivotValue `json:"values,omitempty"`21 Filters []filePivotFilter `json:"filters,omitempty"`22 RowTotals bool `json:"rowTotals"`23 ColumnTotals bool `json:"columnTotals"`24}2526type filePivotGroup struct {27 Column string `json:"column"`28 Order string `json:"order,omitempty"` // "desc" for Z to A29 SortBy int `json:"sortBy,omitempty"` // by the total of this value, from 130}3132type filePivotValue struct {33 Column string `json:"column"`34 Summarize string `json:"summarize"`35 ShowAs string `json:"showAs,omitempty"`36 Name string `json:"name,omitempty"`37}3839type filePivotFilter struct {40 Column string `json:"column"`41 fileCriteria42}4344// hasPivots reports whether any sheet has a pivot table, which needs45// version 5.46func (w *Workbook) hasPivots() bool {47 for _, s := range w.sheets {48 if s.pivot.def != nil {49 return true50 }51 }52 return false53}5455func encodePivot(p *Pivot) ([]byte, error) {56 rng := p.Range.String()57 if p.Lost {58 rng = "#REF!"59 }60 fp := filePivot{Source: formula.QuoteSheet(p.Source) + "!" + rng, RowTotals: p.RowTotals, ColumnTotals: p.ColumnTotals}61 groups := func(gs []PivotGroup) []filePivotGroup {62 var out []filePivotGroup63 for _, g := range gs {64 fg := filePivotGroup{Column: ColName(g.Col), SortBy: g.SortBy}65 if g.Desc {66 fg.Order = "desc"67 }68 out = append(out, fg)69 }70 return out71 }72 fp.Rows, fp.Columns = groups(p.Rows), groups(p.Columns)73 for _, v := range p.Values {74 fp.Values = append(fp.Values, filePivotValue{Column: ColName(v.Col), Summarize: v.Summarize.String(), ShowAs: v.ShowAs.String(), Name: v.Name})75 }76 for _, f := range p.Filters {77 cr := f.Criteria78 fp.Filters = append(fp.Filters, filePivotFilter{Column: ColName(f.Col),79 fileCriteria: fileCriteria{Hidden: cr.Hidden, Condition: cr.Cond.Op.String(), Value: cr.Cond.Arg}})80 }81 return json.Marshal(fp)82}8384func decodePivot(fp filePivot) (*Pivot, error) {85 sheet, rest := SplitSheet(fp.Source)86 if sheet == "" {87 return nil, fmt.Errorf("pivot source %q names no sheet", fp.Source)88 }89 p := &Pivot{Source: sheet, RowTotals: fp.RowTotals, ColumnTotals: fp.ColumnTotals, Lost: rest == "#REF!"}90 if !p.Lost {91 r, ok := ParseRange(rest)92 if !ok {93 return nil, fmt.Errorf("invalid pivot source %q", fp.Source)94 }95 p.Range = r96 }97 var err error98 if p.Rows, err = decodeGroups(fp.Rows); err != nil {99 return nil, err100 }101 if p.Columns, err = decodeGroups(fp.Columns); err != nil {102 return nil, err103 }104 for _, fv := range fp.Values {105 v, err := decodeValue(fv)106 if err != nil {107 return nil, err108 }109 p.Values = append(p.Values, v)110 }111 for _, ff := range fp.Filters {112 col, ok := ParseCol(ff.Column)113 if !ok {114 return nil, fmt.Errorf("invalid pivot filter column %q", ff.Column)115 }116 op, ok := ParseCondOp(ff.Condition)117 if !ok {118 return nil, fmt.Errorf("unknown pivot filter condition %q", ff.Condition)119 }120 p.Filters = append(p.Filters, PivotFilter{Col: col, Criteria: Criteria{Hidden: ff.Hidden, Cond: Condition{Op: op, Arg: ff.Value}}})121 }122 return p, nil123}124125func decodeGroups(fgs []filePivotGroup) ([]PivotGroup, error) {126 var out []PivotGroup127 for _, fg := range fgs {128 col, ok := ParseCol(fg.Column)129 if !ok {130 return nil, fmt.Errorf("invalid pivot column %q", fg.Column)131 }132 out = append(out, PivotGroup{Col: col, Desc: fg.Order == "desc", SortBy: max(fg.SortBy, 0)})133 }134 return out, nil135}136137func decodeValue(fv filePivotValue) (PivotValue, error) {138 col, ok := ParseCol(fv.Column)139 if !ok {140 return PivotValue{}, fmt.Errorf("invalid pivot value column %q", fv.Column)141 }142 f, ok := ParseSummarize(fv.Summarize)143 if !ok {144 return PivotValue{}, fmt.Errorf("unknown pivot summary %q", fv.Summarize)145 }146 show, ok := ParseShowAs(fv.ShowAs)147 if !ok {148 return PivotValue{}, fmt.Errorf("unknown pivot show as %q", fv.ShowAs)149 }150 return PivotValue{Col: col, Summarize: f, ShowAs: show, Name: fv.Name}, nil151}