1package fileio23import (4 "encoding/xml"5 "math"6 "strconv"7 "strings"89 "src.rest/cat/012/internal/sheet"10)1112// Reading a sheet's conditional formatting and data validation (see13// xlsxrules.go for how they map): the elements after the rows, and the14// same rules in Excel 2010's extension (x14:dataValidation, used for15// lists from other sheets). Colors come in as the nearest named color.1617// xlsxColor is a color as SpreadsheetML gives it: ARGB, a theme color,18// or an index into the legacy palette.19type xlsxColor struct {20 rgb string21 theme int22 indexed int23}2425func readColor(se xml.StartElement) xlsxColor {26 c := xlsxColor{theme: intAttr(se, "theme", -1), indexed: intAttr(se, "indexed", -1)}27 c.rgb, _ = attr(se, "rgb")28 return c29}3031// officeTheme is the default Office theme's colors by index, and32// legacyPalette the first entries of the indexed palette.33var (34 officeTheme = []uint32{0xFFFFFF, 0x000000, 0xE7E6E6, 0x44546A, 0x4472C4, 0xED7D31, 0xA5A5A5, 0xFFC000, 0x5B9BD5, 0x70AD47}35 legacyPalette = []uint32{0x000000, 0xFFFFFF, 0xFF0000, 0x00FF00, 0x0000FF, 0xFFFF00, 0xFF00FF, 0x00FFFF,36 0x000000, 0xFFFFFF, 0xFF0000, 0x00FF00, 0x0000FF, 0xFFFF00, 0xFF00FF, 0x00FFFF,37 0x800000, 0x008000, 0x000080, 0x808000, 0x800080, 0x008080, 0xC0C0C0, 0x808080}38)3940// value is the color's RGB, if it has one 012 knows.41func (c xlsxColor) value() (uint32, bool) {42 switch {43 case len(c.rgb) >= 6:44 v, err := strconv.ParseUint(c.rgb[len(c.rgb)-6:], 16, 32)45 return uint32(v), err == nil46 case c.theme >= 0 && c.theme < len(officeTheme):47 return officeTheme[c.theme], true48 case c.indexed >= 0 && c.indexed < len(legacyPalette):49 return legacyPalette[c.indexed], true50 }51 return 0, false52}5354// named is the named color nearest c: one of 012's own colors exactly,55// else by hue, where grays (and a color 012 can't read) are none.56func (c xlsxColor) named() sheet.Color {57 v, ok := c.value()58 if !ok {59 return sheet.ColorNone60 }61 hex := strings.ToUpper(strconv.FormatUint(uint64(v)|0xFF000000, 16))62 for i, rgb := range ruleRGB {63 for _, own := range rgb {64 if own == hex {65 return sheet.Color(i)66 }67 }68 }69 return hueColor(v)70}7172// hueColor is the named color of v's hue, or none for grays.73func hueColor(v uint32) sheet.Color {74 r, g, b := float64(v>>16&0xFF), float64(v>>8&0xFF), float64(v&0xFF)75 hi, lo := max(r, g, b), min(r, g, b)76 if hi == 0 || (hi-lo)/hi < 0.05 {77 return sheet.ColorNone78 }79 var h float6480 switch d := hi - lo; hi {81 case r:82 h = math.Mod((g-b)/d, 6)83 case g:84 h = (b-r)/d + 285 default:86 h = (r-g)/d + 487 }88 h = math.Mod(h*60+360, 360)89 switch {90 case h < 20 || h >= 330:91 return sheet.ColorRed92 case h < 70:93 return sheet.ColorYellow94 case h < 165:95 return sheet.ColorGreen96 case h < 200:97 return sheet.ColorCyan98 case h < 260:99 return sheet.ColorBlue100 }101 return sheet.ColorMagenta102}103104// scaleColor is the named color nearest c for a color scale, whose105// points need one: grays, white included, take the scale color nearest106// them.107func (c xlsxColor) scaleColor() sheet.Color {108 if n := c.named(); n != sheet.ColorNone {109 return n110 }111 v, _ := c.value()112 best, dist := sheet.ColorYellow, math.Inf(1)113 for i := 1; i < sheet.NumColors; i++ {114 s, _ := strconv.ParseUint(ruleRGB[i][2][2:], 16, 32)115 dr, dg, db := float64(v>>16&0xFF)-float64(s>>16&0xFF), float64(v>>8&0xFF)-float64(s>>8&0xFF), float64(v&0xFF)-float64(s&0xFF)116 if d := dr*dr + dg*dg + db*db; d < dist {117 best, dist = sheet.Color(i), d118 }119 }120 return best121}122123// readDxf reads the rest of a <dxf>: its font's styles and color, and124// its fill's color (a dxf's solid fill is its pattern's background).125func readDxf(x *xmlStream) (sheet.RuleStyle, error) {126 var st sheet.RuleStyle127 var fg, bg xlsxColor128 in := ""129 for depth := x.depth; ; {130 t, err := x.next()131 if err != nil {132 return st, eofAsUnexpected(err)133 }134 switch t := t.(type) {135 case xml.StartElement:136 if x.depth == depth+1 {137 in = t.Name.Local138 }139 readDxfPart(t, in, &st, &fg, &bg)140 case xml.EndElement:141 if x.depth < depth {142 st.Fill = bg.named()143 if st.Fill == sheet.ColorNone {144 st.Fill = fg.named()145 }146 return st, nil147 }148 }149 }150}151152// readDxfPart reads an element of a dxf's font or fill.153func readDxfPart(t xml.StartElement, in string, st *sheet.RuleStyle, fg, bg *xlsxColor) {154 switch {155 case in == "font" && t.Name.Local == "b":156 st.Bold = boolAttr(t, "val", true)157 case in == "font" && t.Name.Local == "i":158 st.Italic = boolAttr(t, "val", true)159 case in == "font" && t.Name.Local == "strike":160 st.Strikethrough = boolAttr(t, "val", true)161 case in == "font" && t.Name.Local == "u":162 st.Underline = attrOr(t, "val", "single") != "none"163 case in == "font" && t.Name.Local == "color":164 st.Text = readColor(t).named()165 case in == "fill" && t.Name.Local == "bgColor":166 *bg = readColor(t)167 case in == "fill" && t.Name.Local == "fgColor":168 *fg = readColor(t)169 }170}171172// xlsxCF is a <cfRule>, with the sqref of its <conditionalFormatting>.173type xlsxCF struct {174 sqref string175 typ, op, text, period string176 dxf, priority int177 formulas []string178 cfvo [][2]string // type and val179 colors []xlsxColor180 // top10's, aboveAverage's, dataBar's and iconSet's settings; see181 // xlsxrulesmore.go.182 rank, stdDev int183 percent, bottom bool184 aboveAverage, equalAverage bool185 iconSet string186 reverse, hideValue bool187}188189// xlsxDV is a <dataValidation>, of the main part or the extension.190type xlsxDV struct {191 typ, op, style, prompt, err, sqref string192 showErr, hideArrow bool193 formulas [2]string194}195196// maxRules caps the rules read from one sheet.197const maxRules = 10000198199// readRuleElement reads a <conditionalFormatting> or <dataValidation>200// (of the main part or the extension) just started, at any depth, into201// the reader's rules; it reports whether se was one.202func (r *xlsxSheetReader) readRuleElement(se xml.StartElement) (bool, error) {203 switch {204 case len(r.cfs)+len(r.dvs) >= maxRules:205 return false, nil206 case se.Name.Local == "conditionalFormatting" && r.x.depth == 2:207 return true, r.readCondFormatting(se)208 case se.Name.Local == "dataValidation":209 return true, r.readValidation(se)210 case se.Name.Local == "conditionalFormatting":211 return true, r.readExtRules()212 }213 return false, nil214}215216// readExtRules counts the rules of a <conditionalFormatting> of Excel217// 2010's extension, which 012 leaves out: its own icon sets and the218// like. A data bar there is the extension's copy of one the main part219// holds too, with settings 012 doesn't draw, so it isn't counted.220func (r *xlsxSheetReader) readExtRules() error {221 for depth := r.x.depth; ; {222 t, err := r.x.next()223 if err != nil {224 return eofAsUnexpected(err)225 }226 switch t := t.(type) {227 case xml.EndElement:228 if r.x.depth < depth {229 return nil230 }231 case xml.StartElement:232 if t.Name.Local == "cfRule" && attrOr(t, "type", "") != "dataBar" {233 r.extRules++234 }235 }236 }237}238239func (r *xlsxSheetReader) readCondFormatting(se xml.StartElement) error {240 ref, _ := attr(se, "sqref")241 var cur *xlsxCF242 for depth := r.x.depth; ; {243 t, err := r.x.next()244 if err != nil {245 return eofAsUnexpected(err)246 }247 switch t := t.(type) {248 case xml.EndElement:249 if r.x.depth < depth {250 return nil251 }252 case xml.StartElement:253 if t.Name.Local == "cfRule" && len(r.cfs) < maxRules {254 r.cfs = append(r.cfs, xlsxCF{sqref: ref, typ: attrOr(t, "type", ""), op: attrOr(t, "operator", ""),255 text: attrOr(t, "text", ""), period: attrOr(t, "timePeriod", ""), dxf: intAttr(t, "dxfId", -1),256 priority: intAttr(t, "priority", len(r.cfs)+1), rank: intAttr(t, "rank", 10), stdDev: intAttr(t, "stdDev", 0),257 percent: boolAttr(t, "percent", false), bottom: boolAttr(t, "bottom", false),258 aboveAverage: boolAttr(t, "aboveAverage", true), equalAverage: boolAttr(t, "equalAverage", false)})259 cur = &r.cfs[len(r.cfs)-1]260 } else if err := r.cfPart(t, cur); err != nil {261 return err262 }263 }264 }265}266267// cfPart reads an element inside a <cfRule>: a formula, a color scale's,268// data bar's or icon set's point or color, or the bar or set itself.269// Each rule keeps at most three formulas and colors and five points.270func (r *xlsxSheetReader) cfPart(t xml.StartElement, cur *xlsxCF) error {271 switch t.Name.Local {272 case "dataBar":273 if cur != nil {274 cur.hideValue = !boolAttr(t, "showValue", true)275 }276 case "iconSet":277 if cur != nil {278 cur.iconSet, cur.reverse, cur.hideValue = attrOr(t, "iconSet", ""), boolAttr(t, "reverse", false), !boolAttr(t, "showValue", true)279 }280 case "formula":281 var err error282 if r.buf, err = r.x.text(r.buf[:0]); err != nil {283 return err284 }285 if cur != nil && len(cur.formulas) < 3 {286 cur.formulas = append(cur.formulas, string(r.buf))287 }288 case "cfvo":289 if cur != nil && len(cur.cfvo) < 5 {290 cur.cfvo = append(cur.cfvo, [2]string{attrOr(t, "type", ""), attrOr(t, "val", "")})291 }292 case "color":293 if cur != nil && len(cur.colors) < 3 {294 cur.colors = append(cur.colors, readColor(t))295 }296 }297 return nil298}299300func (r *xlsxSheetReader) readValidation(se xml.StartElement) error {301 dv := xlsxDV{typ: attrOr(se, "type", "none"), op: attrOr(se, "operator", "between"), style: attrOr(se, "errorStyle", "stop"),302 prompt: attrOr(se, "prompt", ""), err: attrOr(se, "error", ""), sqref: attrOr(se, "sqref", ""),303 showErr: boolAttr(se, "showErrorMessage", false), hideArrow: boolAttr(se, "showDropDown", false)}304 for depth := r.x.depth; ; {305 t, err := r.x.next()306 if err != nil {307 return eofAsUnexpected(err)308 }309 switch t := t.(type) {310 case xml.EndElement:311 if r.x.depth < depth {312 r.dvs = append(r.dvs, dv)313 return nil314 }315 case xml.StartElement:316 k := -1317 switch t.Name.Local {318 case "formula1":319 k = 0320 case "formula2":321 k = 1322 case "sqref":323 k = 2324 default:325 continue326 }327 if r.buf, err = r.x.text(r.buf[:0]); err != nil {328 return err329 }330 if k == 2 {331 dv.sqref = string(r.buf)332 } else {333 dv.formulas[k] = string(r.buf)334 }335 }336 }337}