1package fileio23import (4 "fmt"5 "strconv"6 "strings"78 "src.rest/cat/012/internal/sheet"9)1011// The rules Excel 2007 has a type of its own for, both ways:12//13// 012 Excel14// top, bottom (percent) values top10 with rank, percent and bottom15// above, below average aboveAverage (aboveAverage="0" for below)16// duplicate, unique values duplicateValues, uniqueValues17// date is in a period timePeriod where Excel has the period18// (weeks, months, the past week), else an19// expression on INT(cell)20// data bar dataBar: two cfvo and a color21// icon set iconSet: 3Arrows, 5Quarters, 4Rating ...22//23// Coming in, traffic lights, signs and quarters read as circles, flags24// and the other symbols as symbols, and gray arrows as arrows. Rules25// only Excel 2010's extension holds (its own icon sets, data bars'26// extra settings) are left out, counted in the import's note, but for27// the extension's copy of a data bar the main part also holds.2829// rankRule writes a top, bottom, average or duplicates rule.30func rankRule(head string, op sheet.RuleOp, arg string) string {31 switch op {32 case sheet.RuleAboveAverage:33 return fmt.Sprintf(head, "aboveAverage") + "/>"34 case sheet.RuleBelowAverage:35 return fmt.Sprintf(head, "aboveAverage") + ` aboveAverage="0"/>`36 case sheet.RuleDuplicate:37 return fmt.Sprintf(head, "duplicateValues") + "/>"38 case sheet.RuleUnique:39 return fmt.Sprintf(head, "uniqueValues") + "/>"40 }41 attrs := fmt.Sprintf(` rank="%s"`, strings.TrimSpace(arg))42 if op == sheet.RuleTopPercent || op == sheet.RuleBottomPercent {43 attrs += ` percent="1"`44 }45 if op == sheet.RuleBottom || op == sheet.RuleBottomPercent {46 attrs += ` bottom="1"`47 }48 return fmt.Sprintf(head, "top10") + attrs + "/>"49}5051// excelPeriods are the periods Excel's timePeriod names, with 012's52// words for them.53var excelPeriods = map[string]string{"today": "today", "yesterday": "yesterday", "tomorrow": "tomorrow",54 "past week": "last7Days", "this week": "thisWeek", "last week": "lastWeek", "next week": "nextWeek",55 "this month": "thisMonth", "last month": "lastMonth", "next month": "nextMonth"}5657// periodBounds are Excel formulas for the first and last days of a58// period, and false for a word that isn't one.59func periodBounds(word string) (lo, hi string, ok bool) {60 word = strings.Join(strings.Fields(strings.ToLower(word)), " ")61 word = strings.TrimPrefix(strings.TrimPrefix(word, "in the "), "the ")62 switch word {63 case "today":64 return "TODAY()", "TODAY()", true65 case "tomorrow":66 return "TODAY()+1", "TODAY()+1", true67 case "yesterday":68 return "TODAY()-1", "TODAY()-1", true69 case "past week", "last 7 days":70 return "TODAY()-6", "TODAY()", true71 case "past month":72 return "EDATE(TODAY(),-1)", "TODAY()", true73 case "past year":74 return "EDATE(TODAY(),-12)", "TODAY()", true75 }76 which, unit, _ := strings.Cut(word, " ")77 step, known := map[string]int{"this": 0, "last": -1, "next": 1}[which]78 if !known {79 return "", "", false80 }81 k := func(n int) string {82 if n == 0 {83 return ""84 }85 return fmt.Sprintf("%+d", n)86 }87 switch unit {88 case "week":89 lo = "TODAY()-WEEKDAY(TODAY())+1" + k(7*step)90 return lo, lo + "+6", true91 case "month":92 return "DATE(YEAR(TODAY()),MONTH(TODAY())" + k(step) + ",1)", "DATE(YEAR(TODAY()),MONTH(TODAY())" + k(step+1) + ",0)", true93 case "year":94 return "DATE(YEAR(TODAY())" + k(step) + ",1,1)", "DATE(YEAR(TODAY())" + k(step) + ",12,31)", true95 }96 return "", "", false97}9899// periodRule writes a date rule on a period: a timePeriod where Excel100// has one for "Date is", else an expression.101func periodRule(head, cell string, op sheet.RuleOp, word string) (string, bool) {102 lo, hi, ok := periodBounds(word)103 if !ok {104 return "", false105 }106 day := "INT(" + cell + ")"107 var fx string108 switch op {109 case sheet.RuleDateBefore:110 fx = day + "<" + lo111 case sheet.RuleDateAfter:112 fx = day + ">" + hi113 default:114 fx = "AND(" + day + ">=" + lo + "," + day + "<=" + hi + ")"115 if p := excelPeriods[strings.Join(strings.Fields(strings.ToLower(word)), " ")]; p != "" {116 return fmt.Sprintf(head, "timePeriod") + fmt.Sprintf(` timePeriod="%s"><formula>%s</formula></cfRule>`, p, escapeXML(fx, false)), true117 }118 }119 return fmt.Sprintf(head, "expression") + "><formula>" + escapeXML(fx, false) + "</formula></cfRule>", true120}121122// cfvoXML writes a point of a data bar or icon set.123func cfvoXML(p sheet.ScalePoint) string {124 s := fmt.Sprintf(`<cfvo type="%s"`, p.Kind)125 if p.Kind.TakesValue() {126 s += fmt.Sprintf(` val="%s"`, escapeXML(strings.TrimSpace(p.Value), true))127 }128 return s + "/>"129}130131// dataBarXML writes a data bar of priority p.132func dataBarXML(f sheet.CondFormat, p int) string {133 var b strings.Builder134 fmt.Fprintf(&b, `<cfRule type="dataBar" priority="%d"><dataBar`, p)135 if f.BarOnly {136 b.WriteString(` showValue="0"`)137 }138 b.WriteString(">")139 for _, pt := range f.Scale {140 b.WriteString(cfvoXML(pt))141 }142 fmt.Fprintf(&b, `<color rgb="%s"/></dataBar></cfRule>`, ruleRGB[f.Bar][2])143 return b.String()144}145146// excelIconSets are Excel's icon sets for 012's, by size.147var excelIconSets = map[sheet.IconSet]map[int]string{148 sheet.IconsArrows: {3: "3Arrows", 4: "4Arrows", 5: "5Arrows"},149 sheet.IconsCircles: {3: "3TrafficLights1", 4: "4TrafficLights", 5: "5Quarters"},150 sheet.IconsSymbols: {3: "3Symbols"},151 sheet.IconsRating: {4: "4Rating", 5: "5Rating"},152}153154// iconSetOf is 012's set for an Excel icon set, by its name.155func iconSetOf(name string) (sheet.IconSet, int, bool) {156 if name == "" {157 name = "3TrafficLights1" // Excel's default158 }159 n, err := strconv.Atoi(name[:1])160 if err != nil {161 return 0, 0, false162 }163 rest := name[1:]164 switch {165 case strings.HasPrefix(rest, "Arrows"):166 return sheet.IconsArrows, n, true167 case strings.HasPrefix(rest, "TrafficLights"), rest == "Signs", rest == "Quarters", rest == "RedToBlack":168 return sheet.IconsCircles, n, true169 case strings.HasPrefix(rest, "Symbols"), rest == "Flags":170 return sheet.IconsSymbols, n, n == 3171 case rest == "Rating":172 return sheet.IconsRating, n, true173 }174 return 0, 0, false175}176177// iconSetXML writes an icon set of priority p: a first point at the178// lowest value, then the rule's thresholds.179func iconSetXML(f sheet.CondFormat, p int) (string, bool) {180 name := excelIconSets[f.Icons][len(f.Scale)+1]181 if name == "" {182 return "", false183 }184 var b strings.Builder185 fmt.Fprintf(&b, `<cfRule type="iconSet" priority="%d"><iconSet iconSet="%s"`, p, name)186 if f.Reverse {187 b.WriteString(` reverse="1"`)188 }189 if f.BarOnly {190 b.WriteString(` showValue="0"`)191 }192 b.WriteString(`><cfvo type="percent" val="0"/>`)193 for _, pt := range f.Scale {194 b.WriteString(cfvoXML(pt))195 }196 return b.String() + "</iconSet></cfRule>", true197}198199// pointOf reads a data bar's or icon set's cfvo.200func pointOf(v [2]string) (sheet.ScalePoint, bool) {201 kind, ok := sheet.ParsePointKind(map[string]string{"min": "min", "max": "max", "num": "num",202 "percent": "percent", "percentile": "percentile", "autoMin": "min", "autoMax": "max"}[v[0]])203 return sheet.ScalePoint{Kind: kind, Value: v[1]}, ok204}205206// barRule reads a data bar.207func barRule(f sheet.CondFormat, c xlsxCF) (sheet.CondFormat, string) {208 if len(c.cfvo) != 2 || len(c.colors) == 0 {209 return f, "data bars 012 can't read"210 }211 for _, v := range c.cfvo {212 p, ok := pointOf(v)213 if !ok {214 return f, "data bars set by formulas"215 }216 f.Scale = append(f.Scale, p)217 }218 f.Bar, f.BarOnly, f.Style = c.colors[0].scaleColor(), c.hideValue, sheet.RuleStyle{}219 return f, ""220}221222// iconRule reads an icon set; its first point is the lowest value's.223func iconRule(f sheet.CondFormat, c xlsxCF) (sheet.CondFormat, string) {224 set, n, ok := iconSetOf(c.iconSet)225 if !ok || n != len(c.cfvo) || !contains(set.Sizes(), n) {226 return f, "icon sets 012 has no icons for"227 }228 for _, v := range c.cfvo[1:] {229 p, ok := pointOf(v)230 if !ok {231 return f, "icon sets set by formulas"232 }233 f.Scale = append(f.Scale, p)234 }235 f.Icons, f.Reverse, f.BarOnly, f.Style = set, c.reverse, c.hideValue, sheet.RuleStyle{}236 return f, ""237}238239func contains(ns []int, n int) bool {240 for _, m := range ns {241 if m == n {242 return true243 }244 }245 return false246}247248// rankRuleOf reads a top10, aboveAverage, duplicateValues or249// uniqueValues rule.250func rankRuleOf(f sheet.CondFormat, c xlsxCF) (sheet.CondFormat, string) {251 switch c.typ {252 case "duplicateValues":253 f.Op = sheet.RuleDuplicate254 case "uniqueValues":255 f.Op = sheet.RuleUnique256 case "aboveAverage":257 if c.stdDev != 0 || c.equalAverage {258 return f, "above or below average by deviations, or equal to it"259 }260 f.Op = sheet.RuleAboveAverage261 if !c.aboveAverage {262 f.Op = sheet.RuleBelowAverage263 }264 default: // top10265 f.Op = map[[2]bool]sheet.RuleOp{{false, false}: sheet.RuleTop, {false, true}: sheet.RuleTopPercent,266 {true, false}: sheet.RuleBottom, {true, true}: sheet.RuleBottomPercent}[[2]bool{c.bottom, c.percent}]267 f.Args[0] = strconv.Itoa(c.rank)268 }269 return f, ""270}271272// periodOf reads a timePeriod as 012's word for it.273func periodWord(period string) (string, bool) {274 for word, p := range excelPeriods {275 if p == period {276 return word, true277 }278 }279 return "", false280}