1package fileio23import (4 "bufio"5 "fmt"6 "strconv"7 "strings"89 "src.rest/cat/012/internal/formula"10 "src.rest/cat/012/internal/numfmt"11 "src.rest/cat/012/internal/sheet"12)1314// A sheet's rules go out as Excel's conditional formatting (single-color15// rules with a differential style, dxf, and color scales) and data16// validation, after the autoFilter as the schema orders them. The rules17// are tried in order and the first that applies wins, so each single-18// color rule stops the ones after it (stopIfTrue) and its priority is its19// place. Named colors are written in the colors of Sheets' palette.20//21// 012 Excel22// is empty, is not empty containsBlanks, notContainsBlanks23// text contains, starts ... containsText, notContainsText, beginsWith, endsWith24// text is exactly cellIs equal to the text25// date is, before, after timePeriod (today, tomorrow, yesterday) or an expression on INT(cell)26// comparisons, between cellIs27// custom formula expression28// color scale colorScale29// checkbox list validation of TRUE,FALSE30// dropdown, from a range list validation of the items, or of the range31// number, date, text length decimal, date, textLength validation32// custom formula custom validation3334// ruleRGB are the colors named rule colors are written in, for text, a35// fill and a color scale's points.36var ruleRGB = [sheet.NumColors][3]string{37 {},38 {"FFCC0000", "FFF4CCCC", "FFF8696B"}, // red39 {"FFBF9000", "FFFFF2CC", "FFFFEB84"}, // yellow40 {"FF38761D", "FFD9EAD3", "FF63BE7B"}, // green41 {"FF134F5C", "FFD0E0E3", "FF67C9D3"}, // cyan42 {"FF1155CC", "FFCFE2F3", "FF5A8AC6"}, // blue43 {"FF741B47", "FFEAD1DC", "FFC27BC0"}, // magenta44}4546// ruleNotes counts rules Excel has no equivalent for, keeping the first47// one's range as an example.48type ruleNotes struct {49 formats, validations valueCount50}5152// writeRules writes a sheet's conditional formats and data validation.53func (w *xlsxWriter) writeRules(bw *bufio.Writer, ws string, snap *Snapshot) {54 for i, f := range snap.CondFormats {55 rule, ok := w.cfRule(f, i+1)56 if !ok {57 w.rules.formats.add(w.multi, ws, f.Ranges[0].From)58 continue59 }60 fmt.Fprintf(bw, `<conditionalFormatting sqref="%s">%s</conditionalFormatting>`, sqref(f.Ranges), rule)61 }62 var dv strings.Builder63 n := 064 for _, v := range snap.Validations {65 x, ok := w.validation(v)66 if !ok {67 w.rules.validations.add(w.multi, ws, v.Ranges[0].From)68 continue69 }70 dv.WriteString(x)71 n++72 }73 if n > 0 {74 fmt.Fprintf(bw, `<dataValidations count="%d">%s</dataValidations>`, n, dv.String())75 }76}7778// sqref writes ranges as Excel's sqref: "A1:A9 C1:C9".79func sqref(rs []sheet.Rect) string {80 parts := make([]string, len(rs))81 for i, r := range rs {82 parts[i] = excelRect(r)83 }84 return strings.Join(parts, " ")85}8687// excelText quotes text as a formula's string.88func excelText(s string) string { return `"` + strings.ReplaceAll(s, `"`, `""`) + `"` }8990func excelNum(n float64) string { return strconv.FormatFloat(n, 'f', -1, 64) }9192// excelArg writes a rule's value as an Excel formula: a formula93// translated, a number (a date as Excel's serial), or text.94func (w *xlsxWriter) excelArg(arg string) (string, bool) {95 arg = strings.TrimSpace(arg)96 if sheet.IsFormulaEntry(arg) {97 return toExcelFormula(arg, w.renamed)98 }99 if n, f, ok := sheet.ParseValue(arg); ok {100 return excelNum(toExcelSerial(n, f)), true101 }102 return excelText(arg), true103}104105// cellIsOps are Excel's names of the comparisons.106var cellIsOps = map[sheet.RuleOp]string{sheet.RuleGreater: "greaterThan", sheet.RuleGreaterEq: "greaterThanOrEqual",107 sheet.RuleLess: "lessThan", sheet.RuleLessEq: "lessThanOrEqual", sheet.RuleEqual: "equal",108 sheet.RuleNotEqual: "notEqual", sheet.RuleBetween: "between", sheet.RuleNotBetween: "notBetween"}109110// textRules are the text tests Excel has a rule type for, with the111// formula each is written with, on the cell and the text.112var textRules = map[sheet.RuleOp]struct{ typ, op, formula string }{113 sheet.RuleContains: {"containsText", "containsText", `NOT(ISERROR(SEARCH(%[2]s,%[1]s)))`},114 sheet.RuleNotContains: {"notContainsText", "notContains", `ISERROR(SEARCH(%[2]s,%[1]s))`},115 sheet.RuleStartsWith: {"beginsWith", "beginsWith", `LEFT(%[1]s,LEN(%[2]s))=%[2]s`},116 sheet.RuleEndsWith: {"endsWith", "endsWith", `RIGHT(%[1]s,LEN(%[2]s))=%[2]s`},117}118119// cfRule writes rule f as a <cfRule> of priority p, or reports false120// when Excel can't take it.121func (w *xlsxWriter) cfRule(f sheet.CondFormat, p int) (string, bool) {122 switch {123 case f.IsBar():124 return dataBarXML(f, p), true125 case f.IsIcons():126 return iconSetXML(f, p)127 case f.IsScale():128 return colorScaleXML(f.Scale, p), true129 }130 cell := excelRect(sheet.Rect{From: f.Ranges[0].From, To: f.Ranges[0].From})131 head := fmt.Sprintf(`<cfRule type="%%s" dxfId="%d" priority="%d" stopIfTrue="1"`, w.styles.dxf(f.Style), p)132 formulas := func(fs ...string) string {133 var b strings.Builder134 for _, fx := range fs {135 b.WriteString("<formula>" + escapeXML(fx, false) + "</formula>")136 }137 return b.String()138 }139 arg := strings.TrimSpace(f.Args[0])140 switch op := f.Op; {141 case op == sheet.RuleEmpty:142 return fmt.Sprintf(head, "containsBlanks") + ">" + formulas("LEN(TRIM("+cell+"))=0") + "</cfRule>", true143 case op == sheet.RuleNotEmpty:144 return fmt.Sprintf(head, "notContainsBlanks") + ">" + formulas("LEN(TRIM("+cell+"))>0") + "</cfRule>", true145 case textRules[op].typ != "" && !sheet.IsFormulaEntry(arg):146 t := textRules[op]147 return fmt.Sprintf(head, t.typ) + fmt.Sprintf(` operator="%s" text="%s">`, t.op, escapeXML(arg, true)) +148 formulas(fmt.Sprintf(t.formula, cell, excelText(arg))) + "</cfRule>", true149 case op == sheet.RuleExactly && !sheet.IsFormulaEntry(arg):150 return fmt.Sprintf(head, "cellIs") + ` operator="equal">` + formulas(excelText(arg)) + "</cfRule>", true151 case (op == sheet.RuleDateIs || op == sheet.RuleDateBefore || op == sheet.RuleDateAfter) && sheet.IsPeriod(arg):152 return periodRule(head, cell, op, arg)153 case op == sheet.RuleDateIs || op == sheet.RuleDateBefore || op == sheet.RuleDateAfter:154 return w.dateRule(head, cell, op, arg)155 case op.Ranks():156 return rankRule(head, op, arg), true157 case op == sheet.RuleFormula:158 fx, ok := toExcelFormula(arg, w.renamed)159 return fmt.Sprintf(head, "expression") + ">" + formulas(fx) + "</cfRule>", ok160 case cellIsOps[op] != "":161 lo, ok1 := w.excelArg(f.Args[0])162 fs := []string{lo}163 ok2 := true164 if op.Args() == 2 {165 var hi string166 hi, ok2 = w.excelArg(f.Args[1])167 fs = append(fs, hi)168 }169 return fmt.Sprintf(head, "cellIs") + fmt.Sprintf(` operator="%s">`, cellIsOps[op]) + formulas(fs...) + "</cfRule>", ok1 && ok2170 }171 return "", false // a text test on a formula's value172}173174// dateRule writes a date test: a timePeriod for "Date is today" and the175// like, else an expression comparing the cell's day.176func (w *xlsxWriter) dateRule(head, cell string, op sheet.RuleOp, arg string) (string, bool) {177 days := map[string]string{"today": "TODAY()", "tomorrow": "TODAY()+1", "yesterday": "TODAY()-1"}178 word := strings.ToLower(arg)179 if op == sheet.RuleDateIs && days[word] != "" {180 return fmt.Sprintf(head, "timePeriod") + fmt.Sprintf(` timePeriod="%s"><formula>FLOOR(%s,1)=%s</formula></cfRule>`, word, cell, days[word]), true181 }182 day := days[word]183 if day == "" {184 n, _, ok := sheet.ParseValue(arg)185 if !ok {186 return "", false187 }188 y, m, d := numfmt.Civil(int64(n))189 day = fmt.Sprintf("DATE(%d,%d,%d)", y, m, d)190 }191 cmp := map[sheet.RuleOp]string{sheet.RuleDateIs: "=", sheet.RuleDateBefore: "<", sheet.RuleDateAfter: ">"}[op]192 return fmt.Sprintf(head, "expression") + ">" + "<formula>" + escapeXML("INT("+cell+")"+cmp+day, false) + "</formula></cfRule>", true193}194195// colorScaleXML writes a color scale of priority p.196func colorScaleXML(ps []sheet.ScalePoint, p int) string {197 var cfvo, colors strings.Builder198 for _, pt := range ps {199 fmt.Fprintf(&cfvo, `<cfvo type="%s"`, pt.Kind)200 if pt.Kind.TakesValue() {201 fmt.Fprintf(&cfvo, ` val="%s"`, escapeXML(strings.TrimSpace(pt.Value), true))202 }203 cfvo.WriteString("/>")204 fmt.Fprintf(&colors, `<color rgb="%s"/>`, ruleRGB[pt.Color][2])205 }206 return fmt.Sprintf(`<cfRule type="colorScale" priority="%d"><colorScale>%s%s</colorScale></cfRule>`, p, cfvo.String(), colors.String())207}208209// validTypes are Excel's names of the kinds that compare.210var validTypes = map[sheet.ValidKind]string{sheet.ValidNumber: "decimal", sheet.ValidDate: "date", sheet.ValidLength: "textLength"}211212// validation writes rule v as a <dataValidation>, or reports false when213// Excel can't take it: list items with commas or quotes, or longer than214// Excel's 255 characters.215func (w *xlsxWriter) validation(v sheet.Validation) (string, bool) {216 typ, op := "list", ""217 var fs []string218 ok := true219 switch v.Kind {220 case sheet.ValidList:221 list := strings.Join(v.Items, ",")222 ok = len(list) <= 255 && !strings.ContainsAny(strings.Join(v.Items, ""), `,"`)223 fs = []string{excelText(list)}224 case sheet.ValidCheckbox:225 fs = []string{`"TRUE,FALSE"`}226 if on, off, custom := v.CheckboxValues(); custom {227 // Excel has no checkbox of other values: a list of them.228 list := strings.Trim(on+","+off, ",")229 ok = !strings.ContainsAny(on+off, `,"`)230 fs = []string{excelText(list)}231 }232 case sheet.ValidRange:233 name, r, rok := v.SourceRange()234 ref, _ := strings.CutPrefix(excelRange("x", r), sheet.QuoteSheet("x")+"!")235 if name != "" {236 if to, renamed := w.renamed[formula.SheetKey(name)]; renamed {237 name = to238 }239 ref = excelRange(name, r)240 }241 fs, ok = []string{ref}, rok242 case sheet.ValidFormula:243 typ = "custom"244 fx, fok := toExcelFormula(v.Args[0], w.renamed)245 fs, ok = []string{fx}, fok246 default:247 typ, op = validTypes[v.Kind], cellIsOps[v.Op]248 if v.Kind == sheet.ValidDate && v.Op == sheet.RuleNone {249 op, fs = "greaterThan", []string{"0"} // any date250 break251 }252 for k := range v.Op.Args() {253 fx, aok := w.excelArg(v.Args[k])254 fs, ok = append(fs, fx), ok && aok255 }256 }257 if !ok {258 return "", false259 }260 var b strings.Builder261 fmt.Fprintf(&b, `<dataValidation type="%s"`, typ)262 if op != "" {263 fmt.Fprintf(&b, ` operator="%s"`, op)264 }265 if v.Kind.Dropdown() && v.Display == sheet.DropPlain {266 b.WriteString(` showDropDown="1"`) // Excel's name for hiding the arrow267 }268 style := "stop"269 if !v.Reject {270 style = "warning"271 }272 fmt.Fprintf(&b, ` allowBlank="1" showErrorMessage="1" errorStyle="%s"`, style)273 if h := strings.TrimSpace(v.Help); h != "" {274 fmt.Fprintf(&b, ` showInputMessage="1" prompt="%s" error="%s"`, escapeXML(h, true), escapeXML(h, true))275 }276 fmt.Fprintf(&b, ` sqref="%s">`, sqref(v.Ranges))277 for k, fx := range fs {278 fmt.Fprintf(&b, "<formula%d>%s</formula%d>", k+1, escapeXML(fx, false), k+1)279 }280 return b.String() + "</dataValidation>", true281}282283// rulesNotes says which rules were left out.284func (w *xlsxWriter) rulesNotes() []string {285 var out []string286 if n := w.rules.formats; n.n > 0 {287 out = append(out, fmt.Sprintf("%s with no Excel equivalent left out, e.g. %s",288 count(n.n, "conditional format", "conditional formats"), n.example))289 }290 if n := w.rules.validations; n.n > 0 {291 out = append(out, fmt.Sprintf("%s Excel can't hold left out, e.g. %s (list items with commas or quotes, or over 255 characters, or a formula with no Excel equivalent)",292 count(n.n, "data validation rule", "data validation rules"), n.example))293 }294 return out295}