1package fileio23import (4 "strings"56 "src.rest/cat/012/internal/formula"7)89// 012 reads Sheets' formula syntax and some of 1-2-3's (@SUM, A1..B3,10// #AND#). Excel files store formulas without the leading =, and prefix11// functions newer than Excel 2007 with _xlfn.1213// xlfn gives the prefix Excel stores 012's functions newer than Excel14// 2007 with.15var xlfn = map[string]string{16 "CONCAT": "_xlfn.", "DAYS": "_xlfn.", "IFNA": "_xlfn.", "IFS": "_xlfn.", "SWITCH": "_xlfn.",17 "TEXTJOIN": "_xlfn.", "XLOOKUP": "_xlfn.", "XOR": "_xlfn.",18 "FILTER": "_xlfn._xlws.", "SORT": "_xlfn._xlws.", "UNIQUE": "_xlfn.", "SEQUENCE": "_xlfn.",19 "CHOOSECOLS": "_xlfn.", "CHOOSEROWS": "_xlfn.", "LET": "_xlfn.", "LAMBDA": "_xlfn.",20 "MAP": "_xlfn.", "REDUCE": "_xlfn.", "SCAN": "_xlfn.", "BYROW": "_xlfn.", "BYCOL": "_xlfn.",21 "MAKEARRAY": "_xlfn.", "REGEXREPLACE": "_xlfn.",22 "PERCENTILE.INC": "_xlfn.", "PERCENTILE.EXC": "_xlfn.", "QUARTILE.INC": "_xlfn.", "QUARTILE.EXC": "_xlfn.",23 "MODE.SNGL": "_xlfn.", "RANK.EQ": "_xlfn.",24}2526// sheetsOnly lists 012's functions, from Sheets, that Excel has nothing27// the same as: formulas calling them are saved as values. (Excel's28// REGEXEXTRACT returns the whole match where Sheets' returns the capture29// group, and ARRAYFORMULA is dropped around a whole formula instead; see30// xlsxarray.go.)31var sheetsOnly = map[string]bool{32 "SORTN": true, "FLATTEN": true, "SPLIT": true, "REGEXMATCH": true, "REGEXEXTRACT": true, "ARRAYFORMULA": true,33}3435// toExcelFormula translates a formula entry to Excel's syntax, or36// reports false when Excel has no equivalent (JEV functions, functions37// only Sheets has, 1-2-3's #AND# operators), in which case the value is38// exported instead.39//40// renamed maps the key (formula.SheetKey) of each sheet written under41// another name to that name: references to it are rewritten to name it.42func toExcelFormula(input string, renamed map[string]string) (string, bool) {43 fx, _, ok := excelFormula(input, renamed)44 return fx, ok45}4647// excelFormula is toExcelFormula, and whether the formula computes an48// array, for Excel to hold as a dynamic array formula (xlsxarray.go).49func excelFormula(input string, renamed map[string]string) (string, bool, bool) {50 input, arrays, ok := excelArrays(input)51 if !ok {52 return "", false, false53 }54 fx, ok := excelTokens(input, renamed)55 return fx, arrays, ok56}5758// excelTokens translates a formula's text to Excel's token by token.59func excelTokens(input string, renamed map[string]string) (string, bool) {60 src := strings.TrimPrefix(input, "=")61 var b strings.Builder62 for i := 0; i < len(src); {63 next, ok := excelToken(&b, src, i, renamed)64 if !ok {65 return "", false66 }67 i = next68 }69 return b.String(), true70}7172// excelToken writes the token starting at src[i] in Excel's syntax and73// returns the index just past it, or false when Excel has no equivalent.74func excelToken(b *strings.Builder, src string, i int, renamed map[string]string) (int, bool) {75 c := src[i]76 switch {77 case c == '\'':78 // A quoted sheet name, 'Q3 plan'!A1, is the same in Excel, unless79 // the sheet is written under another name.80 j := quoteEnd(src, i)81 name := strings.ReplaceAll(strings.TrimSuffix(src[i+1:j], "'"), "''", "'")82 b.WriteString(excelSheetRef(src[i:j], name, j < len(src) && src[j] == '!', renamed))83 return j, true84 case c == '"':85 j := stringEnd(src, i)86 if j < 0 {87 return 0, false88 }89 b.WriteString(src[i:j])90 return j, true91 case c == '#':92 if p := prefixFold(src[i:], "#REF!"); p > 0 {93 b.WriteString("#REF!")94 return i + p, true95 }96 return 0, false // #AND#, #OR#, #NOT#97 case c == '.' && i+1 < len(src) && src[i+1] == '.':98 b.WriteByte(':') // 1-2-3's A1..B399 return i + 2, true100 case c == '@' || isIdentByte(c) && !isDigitByte(c) && c != '.':101 return excelName(b, src, i, renamed)102 }103 b.WriteByte(c)104 return i + 1, true105}106107// stringEnd returns the index just past the string literal starting at108// s[i], where a quote inside is written twice, or -1 if it isn't closed.109func stringEnd(s string, i int) int {110 for j := i + 1; j < len(s); j++ {111 if s[j] == '"' {112 if j+1 < len(s) && s[j+1] == '"' {113 j++114 continue115 }116 return j + 1117 }118 }119 return -1120}121122// excelName writes the name starting at src[i]: a function name is123// upper-cased and given Excel's _xlfn. prefix where it needs one, and124// 1-2-3's @PI becomes PI(); references and named ranges stay as they are.125func excelName(b *strings.Builder, src string, i int, renamed map[string]string) (int, bool) {126 at := src[i] == '@'127 j := i128 if at {129 j++130 }131 start := j132 for j < len(src) && isIdentByte(src[j]) && !strings.HasPrefix(src[j:], "..") {133 j++134 }135 name := src[start:j]136 if !at && j < len(src) && src[j] == '[' {137 return excelTableRef(b, src, start, j)138 }139 k := j140 for k < len(src) && src[k] == ' ' {141 k++142 }143 call := k < len(src) && src[k] == '('144 if call || at {145 if prefixFold(name, "_xlpm.") > 0 {146 b.WriteString(name) // a LAMBDA called by the name LET gave it147 return j, true148 }149 name = strings.ToUpper(name)150 if strings.HasPrefix(name, "JEV.") || sheetsOnly[name] {151 return 0, false152 }153 name = xlfn[name] + name154 if at && !call {155 name += "()" // @PI156 }157 }158 b.WriteString(excelSheetRef(name, name, !call && !at && j < len(src) && src[j] == '!', renamed))159 return j, true160}161162// excelTableRef writes the structured reference whose table's name163// starts at src[start] and whose brackets start at src[open] as Excel's164// files hold it: Sales[[#This Row],[Amount]] for Sales[@Amount].165func excelTableRef(b *strings.Builder, src string, start, open int) (int, bool) {166 end := formula.StructuredEnd(src, open)167 if end < 0 {168 return 0, false169 }170 x, ok := formula.ExcelTableRef(src[start:end])171 if !ok {172 return 0, false173 }174 b.WriteString(x)175 return end, true176}177178// excelSheetRef is text, a sheet name as written (name, unquoted) when179// sheet says it is followed by !, with the name the sheet is written180// under when that differs: always quoted, as Excel accepts.181func excelSheetRef(text, name string, sheet bool, renamed map[string]string) string {182 if !sheet {183 return text184 }185 if to, ok := renamed[formula.SheetKey(name)]; ok {186 return "'" + strings.ReplaceAll(to, "'", "''") + "'"187 }188 return text189}190191// fromExcelFormula translates an Excel formula (without its =) to a 012192// entry. The result may still fail to parse, e.g. with references to193// other sheets; the importer then keeps the cell's value.194func fromExcelFormula(f string) string {195 f = strings.TrimPrefix(f, "=")196 var b strings.Builder197 b.WriteByte('=')198 inStr := false199 for i := 0; i < len(f); i++ {200 c := f[i]201 if c == '\'' && !inStr {202 j := quoteEnd(f, i)203 b.WriteString(f[i:j])204 i = j - 1205 continue206 }207 if c == '"' {208 inStr = !inStr209 }210 if !inStr {211 rest := f[i:]212 if p := prefixFold(rest, "_xlfn."); p > 0 {213 i += p - 1214 continue215 }216 if p := prefixFold(rest, "_xlws."); p > 0 {217 i += p - 1218 continue219 }220 if p := prefixFold(rest, "_xlpm."); p > 0 {221 i += p - 1222 continue223 }224 }225 b.WriteByte(c)226 }227 return b.String()228}229230// quoteEnd returns the index just past the quoted sheet name starting at231// s[i], where a quote inside is written twice.232func quoteEnd(s string, i int) int {233 for j := i + 1; j < len(s); j++ {234 if s[j] == '\'' {235 if j+1 < len(s) && s[j+1] == '\'' {236 j++237 continue238 }239 return j + 1240 }241 }242 return len(s)243}244245// prefixFold returns len(prefix) if s starts with it, ignoring case.246func prefixFold(s, prefix string) int {247 if len(s) >= len(prefix) && strings.EqualFold(s[:len(prefix)], prefix) {248 return len(prefix)249 }250 return 0251}252253func isIdentByte(c byte) bool {254 return c >= 'A' && c <= 'Z' || c >= 'a' && c <= 'z' || c == '_' || c == '$' || c == '.' || isDigitByte(c)255}256257func isDigitByte(c byte) bool { return c >= '0' && c <= '9' }