1package sheet23import (4 "bufio"5 "encoding/json"6 "fmt"7 "strings"8)910// Rules in the file are two optional fields after a sheet's cells and11// charts, one rule per line, which older builds ignore (losing the12// rules, as they lose charts), so they need no version:13//14// "conditionalFormats": [15// {"ranges": "B2:B20", "condition": "gt", "values": ["100"], "fill": "green", "bold": true},16// {"ranges": "C2:C20", "scale": [{"type": "min", "color": "red"}, {"type": "max", "color": "green"}]},17// {"ranges": "D2:D20", "dataBar": {"color": "blue", "min": {"type": "min"}, "max": {"type": "max"}}},18// {"ranges": "E2:E20", "iconSet": {"icons": "arrows", "points": [{"type": "percent", "value": "33"}, {"type": "percent", "value": "67"}]}}19// ],20// "validations": [21// {"ranges": "D2:D20", "criteria": "list", "items": ["Yes", "No"], "reject": true}22// ]23//24// The same lines are the answers of the commands that add rules, so a25// recorded macro adds a rule as the file would.2627type fileCondFormat struct {28 Ranges string `json:"ranges"`29 Condition string `json:"condition,omitempty"`30 Values []string `json:"values,omitempty"`31 Text string `json:"text,omitempty"`32 Fill string `json:"fill,omitempty"`33 Bold bool `json:"bold,omitempty"`34 Italic bool `json:"italic,omitempty"`35 Underline bool `json:"underline,omitempty"`36 Strike bool `json:"strikethrough,omitempty"`37 Scale []fileScaled `json:"scale,omitempty"`38 // Data bars and icon sets need no version bump: earlier builds ignore39 // them, losing the rule, as they lose rules they don't know.40 DataBar *fileBar `json:"dataBar,omitempty"`41 IconSet *fileIcons `json:"iconSet,omitempty"`42}4344type fileScaled struct {45 Type string `json:"type"`46 Value string `json:"value,omitempty"`47 Color string `json:"color"`48}4950// filePoint is a data bar's or icon set's point.51type filePoint struct {52 Type string `json:"type"`53 Value string `json:"value,omitempty"`54}5556type fileBar struct {57 Color string `json:"color"`58 Min filePoint `json:"min"`59 Max filePoint `json:"max"`60 BarOnly bool `json:"barOnly,omitempty"`61}6263type fileIcons struct {64 Icons string `json:"icons"`65 Points []filePoint `json:"points"`66 Reverse bool `json:"reverse,omitempty"`67 IconOnly bool `json:"iconOnly,omitempty"`68}6970func filePointOf(p ScalePoint) filePoint {71 fp := filePoint{Type: p.Kind.String()}72 if p.Kind.TakesValue() {73 fp.Value = p.Value74 }75 return fp76}7778func (fp filePoint) point() (ScalePoint, error) {79 k, ok := ParsePointKind(fp.Type)80 if !ok {81 return ScalePoint{}, fmt.Errorf("invalid point %q", fp.Type)82 }83 return ScalePoint{Kind: k, Value: fp.Value}, nil84}8586type fileValidation struct {87 Ranges string `json:"ranges"`88 Criteria string `json:"criteria"`89 Condition string `json:"condition,omitempty"`90 Values []string `json:"values,omitempty"`91 Items []string `json:"items,omitempty"`92 Source string `json:"source,omitempty"`93 Reject bool `json:"reject,omitempty"`94 Help string `json:"help,omitempty"`95 // Display needs no version bump: earlier builds draw every dropdown96 // with its arrow.97 Display string `json:"display,omitempty"` // "chip" or "plain"98}99100// values lists the first n of args, as files keep them.101func values(args [2]string, n int) []string {102 if n == 0 {103 return nil104 }105 return args[:n:n]106}107108// JSON writes the rule as a line of the file, e.g.109// {"ranges":"B2:B20","condition":"gt","values":["100"],"fill":"green"}.110func (f CondFormat) JSON() string {111 fc := fileCondFormat{Ranges: rangesText(f.Ranges)}112 switch {113 case f.IsBar() && len(f.Scale) == 2:114 fc.DataBar = &fileBar{Color: f.Bar.String(), Min: filePointOf(f.Scale[0]), Max: filePointOf(f.Scale[1]), BarOnly: f.BarOnly}115 case f.IsIcons():116 fc.IconSet = &fileIcons{Icons: f.Icons.String(), Reverse: f.Reverse, IconOnly: f.BarOnly}117 for _, p := range f.Scale {118 fc.IconSet.Points = append(fc.IconSet.Points, filePointOf(p))119 }120 case f.IsScale():121 for _, p := range f.Scale {122 sp := fileScaled{Type: p.Kind.String(), Color: p.Color.String()}123 if p.Kind.TakesValue() {124 sp.Value = p.Value125 }126 fc.Scale = append(fc.Scale, sp)127 }128 default:129 st := f.Style130 fc.Condition, fc.Values = f.Op.String(), values(f.Args, f.Op.Args())131 fc.Text, fc.Fill = st.Text.String(), st.Fill.String()132 fc.Bold, fc.Italic, fc.Underline, fc.Strike = st.Bold, st.Italic, st.Underline, st.Strikethrough133 }134 raw, _ := json.Marshal(fc)135 return string(raw)136}137138// ParseCondFormat reads a rule written by JSON, checking it.139func ParseCondFormat(line string) (CondFormat, error) {140 var fc fileCondFormat141 if err := json.Unmarshal([]byte(line), &fc); err != nil {142 return CondFormat{}, err143 }144 f, err := fc.rule()145 if err != nil {146 return CondFormat{}, err147 }148 return f, f.Check()149}150151func (fc fileCondFormat) rule() (CondFormat, error) {152 rs, ok := ParseRanges(fc.Ranges)153 if !ok {154 return CondFormat{}, fmt.Errorf("invalid ranges %q", fc.Ranges)155 }156 f := CondFormat{Ranges: rs}157 switch {158 case fc.DataBar != nil:159 return fc.DataBar.rule(f)160 case fc.IconSet != nil:161 return fc.IconSet.rule(f)162 }163 for _, sp := range fc.Scale {164 k, ok := ParsePointKind(sp.Type)165 c, cok := ParseColor(sp.Color)166 if !ok || !cok {167 return CondFormat{}, fmt.Errorf("invalid color scale point %q %q", sp.Type, sp.Color)168 }169 f.Scale = append(f.Scale, ScalePoint{Kind: k, Value: sp.Value, Color: c})170 }171 if f.IsScale() {172 return f, nil173 }174 op, ok := ParseRuleOp(fc.Condition)175 text, tok := ParseColor(fc.Text)176 fill, fok := ParseColor(fc.Fill)177 if !ok || !tok || !fok || len(fc.Values) > 2 {178 return CondFormat{}, fmt.Errorf("invalid rule %q", fc.Condition)179 }180 f.Op = op181 copy(f.Args[:], fc.Values)182 f.Style = RuleStyle{Text: text, Fill: fill, Bold: fc.Bold, Italic: fc.Italic, Underline: fc.Underline, Strikethrough: fc.Strike}183 return f, nil184}185186func (fb *fileBar) rule(f CondFormat) (CondFormat, error) {187 c, ok := ParseColor(fb.Color)188 lo, err1 := fb.Min.point()189 hi, err2 := fb.Max.point()190 if !ok || c == ColorNone || err1 != nil || err2 != nil {191 return CondFormat{}, fmt.Errorf("invalid data bar %q", fb.Color)192 }193 f.Bar, f.Scale, f.BarOnly = c, []ScalePoint{lo, hi}, fb.BarOnly194 return f, nil195}196197func (fi *fileIcons) rule(f CondFormat) (CondFormat, error) {198 set, ok := ParseIconSet(fi.Icons)199 if !ok || set == IconsNone || len(fi.Points) > 4 {200 return CondFormat{}, fmt.Errorf("invalid icon set %q", fi.Icons)201 }202 f.Icons, f.Reverse, f.BarOnly = set, fi.Reverse, fi.IconOnly203 for _, fp := range fi.Points {204 p, err := fp.point()205 if err != nil {206 return CondFormat{}, err207 }208 f.Scale = append(f.Scale, p)209 }210 return f, nil211}212213// JSON writes the rule as a line of the file, e.g.214// {"ranges":"D2:D20","criteria":"list","items":["Yes","No"]}.215func (v Validation) JSON() string {216 fv := fileValidation{Ranges: rangesText(v.Ranges), Criteria: v.Kind.String(), Reject: v.Reject, Help: v.Help}217 if v.Kind.Dropdown() {218 fv.Display = v.Display.String()219 }220 switch {221 case v.Kind == ValidList, v.Kind == ValidCheckbox:222 fv.Items = v.Items223 case v.Kind == ValidRange:224 fv.Source = v.Source225 case v.Kind == ValidFormula:226 fv.Values = values(v.Args, 1)227 case v.Kind.Compares():228 fv.Condition, fv.Values = v.Op.String(), values(v.Args, v.Op.Args())229 }230 raw, _ := json.Marshal(fv)231 return string(raw)232}233234// ParseValidation reads a rule written by JSON, checking it.235func ParseValidation(line string) (Validation, error) {236 var fv fileValidation237 if err := json.Unmarshal([]byte(line), &fv); err != nil {238 return Validation{}, err239 }240 v, err := fv.rule()241 if err != nil {242 return Validation{}, err243 }244 return v, v.Check()245}246247func (fv fileValidation) rule() (Validation, error) {248 rs, ok := ParseRanges(fv.Ranges)249 if !ok {250 return Validation{}, fmt.Errorf("invalid ranges %q", fv.Ranges)251 }252 k, ok := ParseValidKind(fv.Criteria)253 op, opOK := ParseRuleOp(fv.Condition)254 d, dOK := ParseDropDisplay(fv.Display)255 if !ok || !opOK || !dOK || len(fv.Values) > 2 {256 return Validation{}, fmt.Errorf("invalid validation %q %q", fv.Criteria, fv.Condition)257 }258 v := Validation{Ranges: rs, Kind: k, Op: op, Items: fv.Items, Source: fv.Source, Reject: fv.Reject, Help: fv.Help, Display: d}259 copy(v.Args[:], fv.Values)260 return v, nil261}262263// writeRules writes the sheet's rules, one per line, after its other264// fields.265func (s *Sheet) writeRules(b *bufio.Writer, indent string) {266 list := func(key string, lines []string) {267 if len(lines) == 0 {268 return269 }270 fmt.Fprintf(b, ",\n%s%q: [", indent, key)271 for i, l := range lines {272 if i > 0 {273 b.WriteByte(',')274 }275 fmt.Fprintf(b, "\n%s %s", indent, l)276 }277 b.WriteString("\n" + indent + "]")278 }279 var fs, vs []string280 for _, f := range s.rules.formats {281 fs = append(fs, f.JSON())282 }283 for _, v := range s.rules.validations {284 vs = append(vs, v.JSON())285 }286 list("conditionalFormats", fs)287 list("validations", vs)288}289290// readRules restores the sheet's rules from a file. A rule that doesn't291// make sense (a later build's criteria, say) is an error, as a bad cell292// is.293func (s *Sheet) readRules(fcs []fileCondFormat, fvs []fileValidation) error {294 for i, fc := range fcs {295 f, err := fc.rule()296 if err == nil {297 err = f.Check()298 }299 if err != nil {300 return fmt.Errorf("conditional format %d: %s", i+1, strings.TrimSuffix(err.Error(), "."))301 }302 s.rules.formats = append(s.rules.formats, f)303 }304 for i, fv := range fvs {305 v, err := fv.rule()306 if err == nil {307 err = v.Check()308 }309 if err != nil {310 return fmt.Errorf("data validation %d: %s", i+1, strings.TrimSuffix(err.Error(), "."))311 }312 s.rules.validations = append(s.rules.validations, v)313 }314 s.looks.reset()315 return nil316}