1package fileio23import (4 "cmp"5 "fmt"6 "maps"7 "regexp"8 "slices"9 "strconv"10 "strings"1112 "src.rest/cat/012/internal/numfmt"13 "src.rest/cat/012/internal/sheet"14)1516// ruleSkips counts the rules an import left out, by why.17type ruleSkips struct {18 formats, validations map[string]int19}2021func (k *ruleSkips) skip(format bool, why string) {22 m := &k.validations23 if format {24 m = &k.formats25 }26 if *m == nil {27 *m = map[string]int{}28 }29 (*m)[why]++30}3132// notes says what was left out, e.g. "3 conditional formats left out:33// data bars (2), icon sets (1)".34func (k *ruleSkips) notes() []string {35 var out []string36 for _, part := range []struct {37 m map[string]int38 one, many string39 }{{k.formats, "conditional format", "conditional formats"}, {k.validations, "data validation rule", "data validation rules"}} {40 n := 041 var whys []string42 for _, why := range slices.Sorted(maps.Keys(part.m)) {43 n += part.m[why]44 whys = append(whys, fmt.Sprintf("%s (%d)", why, part.m[why]))45 }46 if n > 0 {47 out = append(out, count(n, part.one, part.many)+" left out: "+strings.Join(whys, ", "))48 }49 }50 return out51}5253// loadRules puts the rules read from a sheet on s, counting those left54// out.55func (bk *xlsxBook) loadRules(s *sheet.Sheet, r *xlsxSheetReader) {56 cfs := slices.Clone(r.cfs)57 slices.SortStableFunc(cfs, func(a, b xlsxCF) int { return cmp.Compare(a.priority, b.priority) })58 var fs []sheet.CondFormat59 for _, c := range cfs {60 f, why := bk.condFormat(c)61 if why != "" {62 bk.skips.skip(true, why)63 continue64 }65 fs = append(fs, f)66 }67 for range s.LoadCondFormats(fs) {68 bk.skips.skip(true, "without a text color, fill or text style 012 draws")69 }70 var vs []sheet.Validation71 for _, d := range r.dvs {72 v, why := validationOf(d)73 switch {74 case why == "any":75 case why != "":76 bk.skips.skip(false, why)77 default:78 vs = append(vs, v)79 }80 }81 for range s.LoadValidations(vs) {82 bk.skips.skip(false, "that 012 can't check")83 }84 for range r.extRules {85 bk.skips.skip(true, "in Excel 2010's extension (its own icon sets and the like)")86 }87}8889// sqrefRanges reads an sqref, "A1:A9 C1".90func sqrefRanges(ref string) ([]sheet.Rect, bool) {91 var out []sheet.Rect92 for _, f := range strings.Fields(ref) {93 r, ok := sheet.ParseRange(strings.ReplaceAll(f, "$", ""))94 if !ok {95 return nil, false96 }97 out = append(out, r)98 }99 return out, len(out) > 0100}101102// cfOps are Excel's comparisons by name.103var cfOps = func() map[string]sheet.RuleOp {104 m := map[string]sheet.RuleOp{}105 for op, name := range cellIsOps {106 m[name] = op107 }108 return m109}()110111// unsupported names the conditional formats 012 has no rule for.112var unsupported = map[string]string{"containsErrors": "errors", "notContainsErrors": "errors"}113114// condFormat is an Excel rule as 012's, or why it was left out.115func (bk *xlsxBook) condFormat(c xlsxCF) (sheet.CondFormat, string) {116 rs, ok := sqrefRanges(c.sqref)117 if !ok {118 return sheet.CondFormat{}, "ranges 012 can't read"119 }120 f := sheet.CondFormat{Ranges: rs}121 if c.dxf >= 0 && c.dxf < len(bk.styles.dxfs) {122 f.Style = bk.styles.dxfs[c.dxf]123 }124 first := func() string {125 if len(c.formulas) == 0 {126 return ""127 }128 return c.formulas[0]129 }130 switch c.typ {131 case "cellIs":132 return cellIsRule(f, c)133 case "containsText", "notContainsText", "beginsWith", "endsWith":134 f.Op = map[string]sheet.RuleOp{"containsText": sheet.RuleContains, "notContainsText": sheet.RuleNotContains,135 "beginsWith": sheet.RuleStartsWith, "endsWith": sheet.RuleEndsWith}[c.typ]136 f.Args[0] = c.text137 case "containsBlanks":138 f.Op = sheet.RuleEmpty139 case "notContainsBlanks":140 f.Op = sheet.RuleNotEmpty141 case "timePeriod":142 word, ok := periodWord(c.period)143 if !ok {144 return f, "dates in periods 012 doesn't know"145 }146 f.Op, f.Args[0] = sheet.RuleDateIs, word147 case "top10", "aboveAverage", "duplicateValues", "uniqueValues":148 return rankRuleOf(f, c)149 case "dataBar":150 return barRule(f, c)151 case "iconSet":152 return iconRule(f, c)153 case "expression":154 if op, day, ok := dateExpression(first()); ok {155 f.Op, f.Args[0] = op, day156 break157 }158 f.Op, f.Args[0] = sheet.RuleFormula, fromExcelFormula(first())159 case "colorScale":160 return colorScaleRule(f, c)161 default:162 if why, ok := unsupported[c.typ]; ok {163 return f, why164 }165 return f, c.typ + " rules"166 }167 return f, ""168}169170// cellIsRule reads a comparison: equal to text is "Text is exactly".171func cellIsRule(f sheet.CondFormat, c xlsxCF) (sheet.CondFormat, string) {172 op, ok := cfOps[c.op]173 if !ok || len(c.formulas) < op.Args() {174 return f, "comparisons 012 can't read"175 }176 f.Op = op177 for k := range op.Args() {178 f.Args[k] = fromExcelArg(c.formulas[k], false)179 }180 if text, quoted := unquoteExcel(c.formulas[0]); quoted && op == sheet.RuleEqual {181 f.Op, f.Args[0] = sheet.RuleExactly, text182 }183 return f, ""184}185186// colorScaleRule reads a color scale's points and colors.187func colorScaleRule(f sheet.CondFormat, c xlsxCF) (sheet.CondFormat, string) {188 if len(c.cfvo) < 2 || len(c.cfvo) != len(c.colors) {189 return f, "color scales 012 can't read"190 }191 for k, v := range c.cfvo {192 kind, ok := sheet.ParsePointKind(map[string]string{"min": "min", "max": "max", "num": "num",193 "percent": "percent", "percentile": "percentile"}[v[0]])194 if !ok {195 return f, "color scale points set by formulas"196 }197 f.Scale = append(f.Scale, sheet.ScalePoint{Kind: kind, Value: v[1], Color: c.colors[k].scaleColor()})198 }199 return f, ""200}201202// dateRE matches the expressions dates are written with: INT(A1)<DATE(2026,9,30)203// or INT(A1)=TODAY()+1.204var dateRE = regexp.MustCompile(`^INT\(\$?[A-Za-z]{1,3}\$?\d+\)([=<>])(?:DATE\((\d+),(\d+),(\d+)\)|TODAY\(\)([+-]1)?)$`)205206// dateExpression reads a date test written as xlsxrules.go writes one.207func dateExpression(fx string) (sheet.RuleOp, string, bool) {208 m := dateRE.FindStringSubmatch(strings.ReplaceAll(fx, " ", ""))209 if m == nil {210 return 0, "", false211 }212 op := map[string]sheet.RuleOp{"=": sheet.RuleDateIs, "<": sheet.RuleDateBefore, ">": sheet.RuleDateAfter}[m[1]]213 if m[2] != "" {214 y, _ := strconv.Atoi(m[2])215 mo, _ := strconv.Atoi(m[3])216 d, _ := strconv.Atoi(m[4])217 return op, fmt.Sprintf("%d-%02d-%02d", y, mo, d), true218 }219 return op, map[string]string{"": "today", "+1": "tomorrow", "-1": "yesterday"}[m[5]], true220}221222// unquoteExcel reads a formula that is only a string, "text".223func unquoteExcel(fx string) (string, bool) {224 fx = strings.TrimSpace(fx)225 if len(fx) < 2 || fx[0] != '"' || fx[len(fx)-1] != '"' || strings.Contains(strings.ReplaceAll(fx[1:len(fx)-1], `""`, ""), `"`) {226 return "", false227 }228 return strings.ReplaceAll(fx[1:len(fx)-1], `""`, `"`), true229}230231// fromExcelArg reads a rule's value: text, a number (a date as a date,232// for date rules) or a formula.233func fromExcelArg(fx string, date bool) string {234 if text, ok := unquoteExcel(fx); ok {235 return text236 }237 n, err := strconv.ParseFloat(strings.TrimSpace(fx), 64)238 switch {239 case err != nil:240 return fromExcelFormula(fx)241 case date:242 y, m, d := numfmt.Civil(int64(fromExcelSerial(n, sheet.Format{Kind: sheet.FmtDate})))243 return fmt.Sprintf("%d-%02d-%02d", y, m, d)244 }245 return strings.TrimSpace(fx)246}247248// validKinds are Excel's validation types as 012's kinds.249var validKinds = map[string]sheet.ValidKind{"whole": sheet.ValidNumber, "decimal": sheet.ValidNumber,250 "date": sheet.ValidDate, "textLength": sheet.ValidLength, "custom": sheet.ValidFormula}251252// validationOf is an Excel rule as 012's, or why it was left out ("any"253// for a rule that allows anything, which has nothing to keep).254func validationOf(d xlsxDV) (sheet.Validation, string) {255 rs, ok := sqrefRanges(d.sqref)256 if !ok {257 return sheet.Validation{}, "ranges 012 can't read"258 }259 v := sheet.Validation{Ranges: rs, Reject: d.showErr && d.style == "stop", Help: cmp.Or(d.prompt, d.err)}260 switch k, ok := validKinds[d.typ]; {261 case d.typ == "list":262 if d.hideArrow {263 v.Display = sheet.DropPlain264 }265 return listRule(v, d.formulas[0])266 case d.typ == "none" || d.typ == "":267 return v, "any"268 case !ok:269 return v, d.typ + " rules"270 case k == sheet.ValidFormula:271 v.Kind, v.Args[0] = k, fromExcelFormula(d.formulas[0])272 default:273 op, ok := cfOps[d.op]274 if !ok {275 return v, "comparisons 012 can't read"276 }277 v.Kind, v.Op = k, op278 if k == sheet.ValidDate && op == sheet.RuleGreater && strings.TrimSpace(d.formulas[0]) == "0" {279 v.Op = sheet.RuleNone // any date280 break281 }282 for i := range op.Args() {283 v.Args[i] = fromExcelArg(d.formulas[i], k == sheet.ValidDate)284 }285 }286 return v, ""287}288289// listRule reads a list: items in quotes, or a range; TRUE and FALSE290// alone are a checkbox.291func listRule(v sheet.Validation, fx string) (sheet.Validation, string) {292 if text, ok := unquoteExcel(fx); ok {293 for _, it := range strings.Split(text, ",") {294 if it = strings.TrimSpace(it); it != "" {295 v.Items = append(v.Items, it)296 }297 }298 v.Kind = sheet.ValidList299 if len(v.Items) == 2 && strings.EqualFold(v.Items[0], "TRUE") && strings.EqualFold(v.Items[1], "FALSE") {300 v.Kind, v.Items = sheet.ValidCheckbox, nil301 }302 return v, ""303 }304 v.Kind, v.Source = sheet.ValidRange, strings.ReplaceAll(strings.TrimPrefix(strings.TrimSpace(fx), "="), "$", "")305 if _, _, ok := v.SourceRange(); !ok {306 return v, "lists from named ranges or formulas"307 }308 return v, ""309}