1package fileio23import (4 "bufio"5 "fmt"6 "maps"7 "slices"8 "strconv"9 "strings"1011 "src.rest/cat/012/internal/sheet"12)1314// Frozen panes and filters in XLSX: written here, read in xlsxviewread.go.15//16// A filter is an <autoFilter> over its range, after the cells, with a17// <filterColumn> for each column with criteria (colId counts from the18// range's first column). Hidden values become the list of values shown19// (<filters>, blank="1" for blanks), as Excel keeps them; a condition20// alone becomes a custom filter where Excel has one (text conditions as21// wildcard patterns), else the values it lets through. The rows the22// filter hides are written hidden, since Excel doesn't filter again on23// opening.2425// excelRect writes r as Excel's A1:G4.26func excelRect(r sheet.Rect) string {27 a := func(x sheet.Addr) string { return excelColName(x.Col+1) + strconv.Itoa(x.Row+1) }28 if r.From == r.To {29 return a(r.From)30 }31 return a(r.From) + ":" + a(r.To)32}3334// writeSheetView writes the sheet's view: whether it is the tab shown,35// and its frozen panes.36func writeSheetView(bw *bufio.Writer, snap *Snapshot, active bool) {37 bw.WriteString(`<sheetViews><sheetView workbookViewId="0"`)38 if active {39 bw.WriteString(` tabSelected="1"`)40 }41 rows, cols := snap.FrozenRows, snap.FrozenCols42 if rows == 0 && cols == 0 {43 bw.WriteString(`/></sheetViews>`)44 return45 }46 bw.WriteString(`><pane`)47 if cols > 0 {48 fmt.Fprintf(bw, ` xSplit="%d"`, cols)49 }50 if rows > 0 {51 fmt.Fprintf(bw, ` ySplit="%d"`, rows)52 }53 pane := "bottomRight"54 switch {55 case cols == 0:56 pane = "bottomLeft"57 case rows == 0:58 pane = "topRight"59 }60 fmt.Fprintf(bw, ` topLeftCell="%s" activePane="%s" state="frozen"/><selection pane="%s"/></sheetView></sheetViews>`,61 excelColName(cols+1)+strconv.Itoa(rows+1), pane, pane)62}6364// writeAutoFilter writes the sheet's filter, if it has one.65func writeAutoFilter(bw *bufio.Writer, snap *Snapshot) {66 f := snap.Filter67 if f == nil {68 return69 }70 fmt.Fprintf(bw, `<autoFilter ref="%s"`, excelRect(f.Range))71 cols := slices.Sorted(maps.Keys(f.Cols))72 if len(cols) == 0 {73 bw.WriteString(`/>`)74 return75 }76 bw.WriteString(`>`)77 for _, c := range cols {78 if c < f.Range.From.Col || c > f.Range.To.Col || f.Cols[c].IsZero() {79 continue80 }81 fmt.Fprintf(bw, `<filterColumn colId="%d">%s</filterColumn>`, c-f.Range.From.Col, filterColumnXML(snap, c, f.Cols[c]))82 }83 bw.WriteString(`</autoFilter>`)84}8586// filterColumnXML is the criteria of column c as Excel keeps them.87func filterColumnXML(snap *Snapshot, c int, cr sheet.Criteria) string {88 if len(cr.Hidden) == 0 {89 if x, ok := customFilterXML(cr.Cond); ok {90 return x91 }92 }93 hidden := map[string]bool{}94 for _, h := range cr.Hidden {95 hidden[h] = true96 }97 shown, blanks := filterColumnValues(snap, c)98 var b strings.Builder99 b.WriteString(`<filters`)100 if blanks && !hidden[""] && cr.Cond.Matches(sheet.Value{}, "") {101 b.WriteString(` blank="1"`)102 }103 b.WriteString(`>`)104 for _, t := range slices.Sorted(maps.Keys(shown)) {105 if !hidden[t] && cr.Cond.Matches(shown[t], t) {106 fmt.Fprintf(&b, `<filter val="%s"/>`, escapeXML(t, true))107 }108 }109 b.WriteString(`</filters>`)110 return b.String()111}112113// filterColumnValues is the distinct values shown in column c of the114// filter's data rows, with a value for each, and whether any is blank.115func filterColumnValues(snap *Snapshot, c int) (map[string]sheet.Value, bool) {116 r := snap.Filter.Range117 last := min(r.To.Row, snap.Range.To.Row)118 shown := map[string]sheet.Value{}119 blanks := r.To.Row > last120 for row := r.From.Row + 1; row <= last; row++ {121 cell, ok := snap.Cells[sheet.Addr{Col: c, Row: row}]122 t := ""123 if ok {124 t = cell.Text()125 }126 if t == "" {127 blanks = true128 continue129 }130 if _, seen := shown[t]; !seen {131 shown[t] = cell.Value132 }133 }134 return shown, blanks135}136137// excelOps are the custom filter operators for 012's comparisons.138var excelOps = map[sheet.CondOp]string{139 sheet.CondGreater: "greaterThan", sheet.CondGreaterEq: "greaterThanOrEqual",140 sheet.CondLess: "lessThan", sheet.CondLessEq: "lessThanOrEqual",141 sheet.CondEqual: "equal", sheet.CondNotEqual: "notEqual",142}143144// customFilterXML is a condition as one of Excel's custom filters, or145// false when Excel has none like it.146func customFilterXML(c sheet.Condition) (string, bool) {147 custom := func(op, val string) (string, bool) {148 attr := ""149 if op != "equal" {150 attr = ` operator="` + op + `"`151 }152 return fmt.Sprintf(`<customFilters><customFilter%s val="%s"/></customFilters>`, attr, escapeXML(val, true)), true153 }154 arg := escapeWildcards(c.Arg)155 switch c.Op {156 case sheet.CondEmpty:157 return `<filters blank="1"/>`, true158 case sheet.CondNotEmpty:159 return custom("notEqual", " ")160 case sheet.CondContains:161 return custom("equal", "*"+arg+"*")162 case sheet.CondNotContains:163 return custom("notEqual", "*"+arg+"*")164 case sheet.CondStartsWith:165 return custom("equal", arg+"*")166 case sheet.CondEndsWith:167 return custom("equal", "*"+arg)168 case sheet.CondExactly:169 return custom("equal", arg)170 }171 op, ok := excelOps[c.Op]172 if !ok {173 return "", false174 }175 if n, f, isNum := sheet.ParseValue(strings.TrimSpace(c.Arg)); isNum {176 return custom(op, numInput(toExcelSerial(n, f)))177 }178 return custom(op, arg)179}180181// escapeWildcards escapes Excel's wildcards, so text matches as is.182func escapeWildcards(s string) string {183 return strings.NewReplacer("~", "~~", "*", "~*", "?", "~?").Replace(s)184}