1package fileio23import (4 "encoding/xml"5 "fmt"6 "io"7 "strconv"8 "strings"910 "src.rest/cat/012/internal/sheet"11)1213// Reading frozen panes and filters; see xlsxview.go for how they are14// written.1516// readPane reads a <pane> of the sheet's first view: frozen panes17// (state frozen or frozenSplit) freeze xSplit columns and ySplit rows.18// A split that isn't frozen is left out.19func (r *xlsxSheetReader) readPane(se xml.StartElement) {20 if r.paneRead {21 return22 }23 r.paneRead = true24 if st, _ := attr(se, "state"); st != "frozen" && st != "frozenSplit" {25 return26 }27 split := func(name string) int {28 f, err := strconv.ParseFloat(attrOr(se, name, "0"), 64)29 if err != nil || !(f > 0) {30 return 031 }32 return int(min(f, sheet.MaxFrozen))33 }34 r.frozenCols, r.frozenRows = split("xSplit"), split("ySplit")35}3637// xlsxAutoFilter is a worksheet's <autoFilter>: its range and the38// criteria of its columns.39type xlsxAutoFilter struct {40 ref string41 cols []xlsxFilterColumn42}4344// xlsxFilterColumn is a <filterColumn>.45type xlsxFilterColumn struct {46 col int // colId, from the range's first column47 filters bool // a <filters> element: the values shown48 values []string // its <filter val>s49 blank bool // blanks are shown50 dates bool // it has <dateGroupItem>s51 custom []xlsxCustomFilter52 other string // a kind of criteria 012 has no equivalent for53}5455// xlsxCustomFilter is a <customFilter>: an operator and a value.56type xlsxCustomFilter struct{ op, val string }5758// maxFilterValues caps the values one filter column lists.59const maxFilterValues = excelRows6061// readTail reads what follows the rows, returning the sheet's62// autoFilter or nil, and keeping its rules in the reader.63func (r *xlsxSheetReader) readTail() (*xlsxAutoFilter, error) {64 var af *xlsxAutoFilter65 for {66 t, err := r.x.next()67 if err == io.EOF {68 return af, nil69 }70 if err != nil {71 return nil, err72 }73 se, ok := t.(xml.StartElement)74 if !ok {75 continue76 }77 rule, err := r.readRuleElement(se)78 switch {79 case err != nil:80 return nil, err81 case se.Name.Local == "mergeCell" && r.x.depth == 3:82 r.readMerge(se)83 case rule || r.x.depth != 2:84 case se.Name.Local == "sheetProtection":85 v, _ := attr(se, "sheet")86 r.protected = v == "1" || v == "true"87 case se.Name.Local == "autoFilter" && af == nil:88 if af, err = r.readAutoFilter(se); err != nil {89 return nil, err90 }91 }92 }93}9495// readAutoFilter reads an <autoFilter>, just started.96func (r *xlsxSheetReader) readAutoFilter(se xml.StartElement) (*xlsxAutoFilter, error) {97 return readAutoFilter(r.x, se)98}99100// readAutoFilter reads an <autoFilter> of a worksheet or a table, just101// started in x.102func readAutoFilter(x *xmlStream, se xml.StartElement) (*xlsxAutoFilter, error) {103 af := &xlsxAutoFilter{}104 af.ref, _ = attr(se, "ref")105 for depth := x.depth; ; {106 t, err := x.next()107 if err != nil {108 return nil, eofAsUnexpected(err)109 }110 if _, ok := t.(xml.EndElement); ok && x.depth < depth {111 return af, nil112 }113 se, ok := t.(xml.StartElement)114 if !ok {115 continue116 }117 if se.Name.Local == "filterColumn" && x.depth == depth+1 {118 af.cols = append(af.cols, xlsxFilterColumn{col: intAttr(se, "colId", -1)})119 } else if n := len(af.cols); n > 0 {120 af.cols[n-1].add(se)121 }122 }123}124125// add reads an element inside the column's <filterColumn>.126func (fc *xlsxFilterColumn) add(se xml.StartElement) {127 switch se.Name.Local {128 case "filters":129 fc.filters, fc.blank = true, boolAttr(se, "blank", false)130 case "filter":131 if len(fc.values) < maxFilterValues {132 fc.values = append(fc.values, attrOr(se, "val", ""))133 }134 case "dateGroupItem":135 fc.dates = true136 case "customFilter":137 fc.custom = append(fc.custom, xlsxCustomFilter{op: attrOr(se, "operator", "equal"), val: attrOr(se, "val", "")})138 case "top10":139 fc.other = "top 10"140 case "dynamicFilter":141 fc.other = "a dynamic filter"142 case "colorFilter":143 fc.other = "by color"144 case "iconFilter":145 fc.other = "by icon"146 }147}148149// applyFilters puts each sheet's autoFilter (filters[i] on sheet i) on150// book, whose values are computed, and returns the notes on what was151// left out.152func applyFilters(book *sheet.Workbook, filters []*xlsxAutoFilter) []string {153 var fn filterNotes154 for i, af := range filters {155 if af != nil {156 fn.apply(book.Sheet(i), af)157 }158 }159 return fn.notes()160}161162// filterNotes counts the filter criteria left out across a workbook,163// keeping the first as an example.164type filterNotes struct {165 n int166 example string167}168169// apply puts filter af on s, whose values are computed: a filter over170// its range, with the criteria of each column that 012 can apply.171func (fn *filterNotes) apply(s *sheet.Sheet, af *xlsxAutoFilter) {172 r, ok := sheet.ParseRange(strings.ReplaceAll(af.ref, "$", ""))173 if !ok {174 fn.leftOut(s, "", "its range "+strconv.Quote(af.ref))175 return176 }177 s.LoadFilter(&sheet.Filter{Range: r}) // no criteria yet, so every column lists all its values178 f := &sheet.Filter{Range: r, Cols: map[int]sheet.Criteria{}}179 for _, fc := range af.cols {180 c := r.From.Col + fc.col181 if fc.col < 0 || c > r.To.Col {182 continue183 }184 cr, why := fc.criteria(s, c)185 if why != "" {186 fn.leftOut(s, sheet.ColName(c), why)187 continue188 }189 if !cr.IsZero() {190 f.Cols[c] = cr191 }192 }193 s.LoadFilter(f)194}195196func (fn *filterNotes) leftOut(s *sheet.Sheet, col, why string) {197 fn.n++198 if fn.example == "" {199 where := "on " + sheet.QuoteSheet(s.Name())200 if col != "" {201 where = "column " + col + " " + where202 }203 fn.example = where + " (" + why + ")"204 }205}206207// notes is what the notes say of the criteria left out.208func (fn *filterNotes) notes() []string {209 if fn.n == 0 {210 return nil211 }212 return []string{fmt.Sprintf("%s of a filter 012 can't apply left out, e.g. %s",213 count(fn.n, "criterion", "criteria"), fn.example)}214}215216// criteria is the column's criteria in 012's terms, for column c of s,217// or why they can't be.218func (fc *xlsxFilterColumn) criteria(s *sheet.Sheet, c int) (sheet.Criteria, string) {219 switch {220 case fc.other != "":221 return sheet.Criteria{}, fc.other222 case fc.dates:223 return sheet.Criteria{}, "dates by year or month"224 case len(fc.custom) > 1:225 return sheet.Criteria{}, "two conditions"226 case len(fc.custom) == 1:227 cond, ok := conditionOf(fc.custom[0])228 if !ok {229 return sheet.Criteria{}, "the condition " + fc.custom[0].op + " " + strconv.Quote(fc.custom[0].val)230 }231 return sheet.Criteria{Cond: cond}, ""232 case !fc.filters:233 return sheet.Criteria{}, ""234 case len(fc.values) == 0 && fc.blank:235 return sheet.Criteria{Cond: sheet.Condition{Op: sheet.CondEmpty}}, ""236 }237 // Excel lists the values shown, matching them as displayed and238 // ignoring case; 012 keeps those hidden.239 shown := make(map[string]bool, len(fc.values))240 for _, v := range fc.values {241 shown[strings.ToLower(v)] = true242 }243 var cr sheet.Criteria244 for _, v := range s.FilterValues(c) {245 if v.Text == "" && !fc.blank || v.Text != "" && !shown[strings.ToLower(v.Text)] {246 cr.Hidden = append(cr.Hidden, v.Text)247 }248 }249 return cr, ""250}251252// excelConds are 012's comparisons by custom filter operator.253var excelConds = map[string]sheet.CondOp{254 "greaterThan": sheet.CondGreater, "greaterThanOrEqual": sheet.CondGreaterEq,255 "lessThan": sheet.CondLess, "lessThanOrEqual": sheet.CondLessEq,256}257258// conditionOf is a custom filter as a condition: comparisons as they259// are; equal and notEqual with wildcards only at the ends as contains,260// starts with and ends with; notEqual to a space as not empty.261func conditionOf(cf xlsxCustomFilter) (sheet.Condition, bool) {262 if op, ok := excelConds[cf.op]; ok {263 return sheet.Condition{Op: op, Arg: cf.val}, true264 }265 lead, core, trail, ok := wildcardShape(cf.val)266 if !ok {267 return sheet.Condition{}, false268 }269 switch op := cf.op; {270 case op == "notEqual" && cf.val == " ":271 return sheet.Condition{Op: sheet.CondNotEmpty}, true272 case op == "equal" && lead && trail:273 return sheet.Condition{Op: sheet.CondContains, Arg: core}, true274 case op == "equal" && lead:275 return sheet.Condition{Op: sheet.CondEndsWith, Arg: core}, true276 case op == "equal" && trail:277 return sheet.Condition{Op: sheet.CondStartsWith, Arg: core}, true278 case op == "equal":279 if _, _, isNum := sheet.ParseValue(strings.TrimSpace(core)); isNum {280 return sheet.Condition{Op: sheet.CondEqual, Arg: core}, true281 }282 return sheet.Condition{Op: sheet.CondExactly, Arg: core}, true283 case op == "notEqual" && lead && trail:284 return sheet.Condition{Op: sheet.CondNotContains, Arg: core}, true285 case op == "notEqual" && !lead && !trail:286 return sheet.Condition{Op: sheet.CondNotEqual, Arg: core}, true287 }288 return sheet.Condition{}, false289}290291// wildcardShape splits a filter pattern into whether it starts and ends292// with *, and the text between, unescaped (~ escapes the next character);293// false when it has other wildcards.294func wildcardShape(p string) (lead bool, core string, trail bool, ok bool) {295 var b strings.Builder296 rs := []rune(p)297 for i := 0; i < len(rs); i++ {298 switch r := rs[i]; {299 case r == '~' && i+1 < len(rs):300 i++301 b.WriteRune(rs[i])302 case r == '*' && i == 0:303 lead = true304 case r == '*' && i == len(rs)-1:305 trail = true306 case r == '*' || r == '?':307 return false, "", false, false308 default:309 b.WriteRune(r)310 }311 }312 return lead, b.String(), trail, true313}