1package fileio23import (4 "src.rest/cat/012/internal/formula"5 "src.rest/cat/012/internal/sheet"6)78// Array formulas in XLSX. Excel 365 keeps a formula that spills (a9// dynamic array formula) as an array formula over the cells it spills10// into, <f t="array" ref="B2:C9">, on a cell whose metadata (cm) marks11// it dynamic, as metadata.xml defines; the cells it spills into hold12// their values. Older Excels read it as a legacy array formula ({=...})13// showing the same values. Formulas calling array functions are written14// this way even when they compute one value, so Excel doesn't read them15// with its implicit intersection (@).16//17// Names LET and LAMBDA bind are written with Excel's _xlpm. prefix, an18// ARRAYFORMULA around a whole formula is dropped (a dynamic array formula19// computes over arrays anyway), and array literals must hold constants,20// as Excel's do, or the formula is saved as its value.2122// arrayFns are the functions that return arrays, in upper case.23var arrayFns = map[string]bool{24 "ARRAYFORMULA": true, "FILTER": true, "SORT": true, "SORTN": true, "UNIQUE": true, "SEQUENCE": true,25 "TRANSPOSE": true, "FLATTEN": true, "CHOOSECOLS": true, "CHOOSEROWS": true, "LET": true, "LAMBDA": true,26 "MAP": true, "SCAN": true, "BYROW": true, "BYCOL": true, "MAKEARRAY": true, "SPLIT": true,27}2829// excelArrays rewrites a formula that may compute an array for Excel, as30// above, and reports whether it does compute one (a dynamic array31// formula), and false when Excel can't hold it.32func excelArrays(input string) (string, bool, bool) {33 if !mayHoldArrays(input) {34 return input, false, true35 }36 n, err := sheet.Parse(input)37 if err != nil {38 return input, false, true // the text is translated as it is39 }40 arrays, ok := false, true41 if c, isCall := n.(formula.Call); isCall && c.Fn.Signature().Name == "ARRAYFORMULA" {42 n, arrays = c.Args[0], true43 }44 var walk func(n formula.Node)45 walk = func(n formula.Node) {46 switch n := n.(type) {47 case formula.Call:48 arrays = arrays || arrayFns[n.Fn.Signature().Name]49 case formula.Array:50 arrays = true51 ok = ok && constants(n)52 }53 formula.EachChild(n, walk)54 }55 walk(n)56 n, _ = formula.Rewrite(n, formula.Rewriter{Local: func(l formula.Local) formula.Node {57 return formula.Name{Name: "_xlpm." + l.Name}58 }})59 return formula.Text(n), arrays, ok60}6162// mayHoldArrays reports whether a formula's text may hold an array63// literal or call an array function, so only those are parsed.64func mayHoldArrays(input string) bool {65 for i := 0; i < len(input); i++ {66 c := input[i]67 if c == '{' {68 return true69 }70 if !isIdentByte(c) || i > 0 && isIdentByte(input[i-1]) {71 continue72 }73 j := i74 for j < len(input) && isIdentByte(input[j]) {75 j++76 }77 if j < len(input) && input[j] == '(' && arrayFns[upperASCII(input[i:j])] {78 return true79 }80 i = j - 181 }82 return false83}8485func upperASCII(s string) string {86 b := []byte(s)87 for i, c := range b {88 b[i] = upper(c)89 }90 return string(b)91}9293// constants reports whether an array literal holds only constants.94func constants(a formula.Array) bool {95 for _, row := range a.Rows {96 for _, e := range row {97 if u, ok := e.(formula.Unary); ok && u.Op == "-" {98 e = u.X99 }100 switch e.(type) {101 case formula.Num, formula.Str, formula.Bool:102 default:103 return false104 }105 }106 }107 return true108}109110// metadataXML defines cell metadata 1 as a dynamic array formula, as111// Excel writes it.112const metadataXML = xmlHead + `<metadata xmlns="` + sheetMain + `" xmlns:xda="http://schemas.microsoft.com/office/spreadsheetml/2017/dynamicarray">` +113 `<metadataTypes count="1"><metadataType name="XLDAPR" minSupportedVersion="120000" copy="1" pasteAll="1" pasteValues="1" merge="1" splitFirst="1" rowColShift="1" clearFormats="1" clearComments="1" assign="1" coerce="1" cellMeta="1"/></metadataTypes>` +114 `<futureMetadata name="XLDAPR" count="1"><bk><extLst><ext uri="{bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}"><xda:dynamicArrayProperties fDynamic="1" fCollapsed="0"/></ext></extLst></bk></futureMetadata>` +115 `<cellMetadata count="1"><bk><rc t="1" v="0"/></bk></cellMetadata></metadata>`116117// arrayRef is the ref of a dynamic array formula at a: the cells it118// spills into, or itself.119func arrayRef(a sheet.Addr, spill sheet.Rect) string {120 if spill == (sheet.Rect{}) || spill.From == spill.To {121 return a.String()122 }123 return spill.From.String() + ":" + spill.To.String()124}