1package fileio23import (4 "encoding/binary"5 "math"6 "strconv"7 "strings"89 "src.rest/cat/012/internal/sheet"10)1112// Lotus formulas are stored as reverse Polish bytecode. lotusFormula13// rebuilds the expression in 012's syntax, adding parentheses where 012's14// precedence needs them. It reports false, with the formula written as15// far as possible in 1-2-3's own spelling, when something has no16// equivalent; the importer then keeps the cell's value.1718// expr is a subexpression and how tightly it binds, using 012's binding19// powers (see sheet/parse.go), so parentheses go exactly where needed.20type expr struct {21 text string22 power int23}2425const (26 atomPower = 10027 unaryPower = 7528 notPower = 2029 comparePower = 3030)3132var lotusBinary = map[byte]struct {33 op string34 power int35}{36 0x09: {"+", 40}, 0x0A: {"-", 40}, 0x0B: {"*", 50}, 0x0C: {"/", 50}, 0x0D: {"^", 70},37 0x0E: {"=", comparePower}, 0x0F: {"<>", comparePower}, 0x10: {"<=", comparePower},38 0x11: {">=", comparePower}, 0x12: {"<", comparePower}, 0x13: {">", comparePower},39 0x14: {"#AND#", 10}, 0x15: {"#OR#", 10}, 0x18: {"&", 35},40}4142// lotusFunc is a 1-2-3 function: its name, its argument count (-1 when a43// count byte follows the opcode), and how to write it in 012's syntax.44// A nil write means 012 has no equivalent.45type lotusFunc struct {46 name string47 args int48 write func(args []string) string49}5051// call writes a 012 function call.52func call(name string) func([]string) string {53 return func(args []string) string { return name + "(" + strings.Join(args, ",") + ")" }54}5556// plusOne adds one to a 0-based 1-2-3 argument, as 012 counts from 1.57func plusOne(s string) string {58 if v, err := strconv.ParseFloat(s, 64); err == nil {59 return numInput(v + 1)60 }61 return "(" + s + ")+1"62}6364var lotusFuncs = map[byte]lotusFunc{65 0x1F: {"NA", 0, call("NA")},66 0x20: {"ERR", 0, nil},67 0x21: {"ABS", 1, call("ABS")},68 0x22: {"INT", 1, call("TRUNC")},69 0x23: {"SQRT", 1, call("SQRT")},70 0x24: {"LOG", 1, call("LOG10")},71 0x25: {"LN", 1, call("LN")},72 0x26: {"PI", 0, call("PI")},73 0x27: {"SIN", 1, nil}, 0x28: {"COS", 1, nil}, 0x29: {"TAN", 1, nil},74 0x2A: {"ATAN2", 2, nil}, 0x2B: {"ATAN", 1, nil}, 0x2C: {"ASIN", 1, nil}, 0x2D: {"ACOS", 1, nil},75 0x2E: {"EXP", 1, call("EXP")},76 0x2F: {"MOD", 2, nil}, // 1-2-3 keeps the dividend's sign, Sheets the divisor's77 0x30: {"CHOOSE", -1, func(a []string) string {78 return call("CHOOSE")(append([]string{plusOne(a[0])}, a[1:]...))79 }},80 0x31: {"ISNA", 1, call("ISNA")},81 0x32: {"ISERR", 1, call("ISERROR")},82 0x33: {"FALSE", 0, call("FALSE")},83 0x34: {"TRUE", 0, call("TRUE")},84 0x35: {"RAND", 0, call("RAND")},85 0x36: {"DATE", 3, func(a []string) string { // years since 190086 return call("DATE")([]string{"1900+" + paren(a[0]), a[1], a[2]})87 }},88 0x37: {"TODAY", 0, call("TODAY")},89 // PMT, PV and FV take (amount, rate, term); Sheets' take the rate90 // and term first, and a present value that is paid out is negative.91 0x38: {"PMT", 3, func(a []string) string { return call("PMT")([]string{a[1], a[2], "-" + paren(a[0])}) }},92 0x39: {"PV", 3, func(a []string) string { return call("PV")([]string{a[1], a[2], "-" + paren(a[0])}) }},93 0x3A: {"FV", 3, func(a []string) string { return call("FV")([]string{a[1], a[2], "-" + paren(a[0])}) }},94 0x3B: {"IF", 3, call("IF")},95 0x3C: {"DAY", 1, call("DAY")},96 0x3D: {"MONTH", 1, call("MONTH")},97 0x3E: {"YEAR", 1, func(a []string) string { return "(" + call("YEAR")(a) + "-1900)" }},98 0x3F: {"ROUND", 2, call("ROUND")},99 0x40: {"TIME", 3, call("TIME")},100 0x41: {"HOUR", 1, call("HOUR")},101 0x42: {"MINUTE", 1, call("MINUTE")},102 0x43: {"SECOND", 1, call("SECOND")},103 0x44: {"ISNUMBER", 1, call("ISNUMBER")},104 0x45: {"ISSTRING", 1, call("ISTEXT")},105 0x46: {"LENGTH", 1, call("LEN")},106 0x47: {"VALUE", 1, call("VALUE")},107 0x48: {"STRING", 2, nil},108 0x49: {"MID", 3, func(a []string) string { return call("MID")([]string{a[0], plusOne(a[1]), a[2]}) }},109 0x4A: {"CHAR", 1, nil},110 0x4B: {"CODE", 1, nil},111 0x4C: {"FIND", 3, func(a []string) string {112 return "(" + call("FIND")([]string{a[0], a[1], plusOne(a[2])}) + "-1)"113 }},114 0x4D: {"DATEVALUE", 1, call("DATEVALUE")},115 0x4E: {"TIMEVALUE", 1, call("TIMEVALUE")},116 0x4F: {"CELLPOINTER", 1, nil},117 0x50: {"SUM", -1, call("SUM")},118 0x51: {"AVG", -1, call("AVERAGE")},119 0x52: {"COUNT", -1, call("COUNTA")},120 0x53: {"MIN", -1, call("MIN")},121 0x54: {"MAX", -1, call("MAX")},122 0x55: {"VLOOKUP", 3, func(a []string) string { return call("VLOOKUP")([]string{a[0], a[1], plusOne(a[2])}) }},123 0x56: {"NPV", 2, call("NPV")},124 0x57: {"VAR", -1, call("VARP")},125 0x58: {"STD", -1, call("STDEVP")},126 0x59: {"IRR", 2, func(a []string) string { return call("IRR")([]string{a[1], a[0]}) }},127 0x5A: {"HLOOKUP", 3, func(a []string) string { return call("HLOOKUP")([]string{a[0], a[1], plusOne(a[2])}) }},128 0x5B: {"DSUM", -1, nil}, 0x5C: {"DAVG", -1, nil}, 0x5D: {"DCNT", -1, nil}, 0x5E: {"DMIN", -1, nil},129 0x5F: {"DMAX", -1, nil}, 0x60: {"DVAR", -1, nil}, 0x61: {"DSTD", -1, nil},130 0x62: {"INDEX", -1, func(a []string) string {131 if len(a) != 3 {132 return ""133 }134 return call("INDEX")([]string{a[0], plusOne(a[2]), plusOne(a[1])})135 }},136 0x63: {"COLS", 1, call("COLUMNS")},137 0x64: {"ROWS", 1, call("ROWS")},138 0x65: {"REPEAT", 2, call("REPT")},139 0x66: {"UPPER", 1, call("UPPER")},140 0x67: {"LOWER", 1, call("LOWER")},141 0x68: {"LEFT", 2, call("LEFT")},142 0x69: {"RIGHT", 2, call("RIGHT")},143 0x6A: {"REPLACE", 4, func(a []string) string {144 return call("REPLACE")([]string{a[0], plusOne(a[1]), a[2], a[3]})145 }},146 0x6B: {"PROPER", 1, call("PROPER")},147 0x6C: {"CELL", 2, nil},148 0x6D: {"TRIM", 1, call("TRIM")},149 0x6E: {"CLEAN", 1, nil},150 0x6F: {"S", 1, nil},151 0x70: {"N", 1, nil},152 0x71: {"EXACT", 2, call("EXACT")},153 0x72: {"APP", 1, nil},154 0x73: {"@", 1, nil},155 0x74: {"RATE", 3, nil},156 0x75: {"TERM", 3, nil},157 0x76: {"CTERM", 3, nil},158 0x77: {"SLN", 3, nil},159 0x78: {"SYD", 4, nil},160 0x79: {"DDB", 4, nil},161}162163func paren(s string) string {164 if _, err := strconv.ParseFloat(s, 64); err == nil {165 return s166 }167 if isRef(s) {168 return s169 }170 return "(" + s + ")"171}172173func isRef(s string) bool {174 _, ok := sheet.ParseAddr(strings.ReplaceAll(s, "$", ""))175 return ok176}177178// lotusRef decodes a cell reference in a formula at cell at. Bit 15 of179// each coordinate marks it relative, stored as a two's complement offset180// from the formula's cell: 8 bits for columns, 11 (.wks) or 13 (.wk1)181// for rows.182func lotusRef(col, row uint16, at sheet.Addr, v wk1Version) (string, bool) {183 a := sheet.Addr{}184 ref := ""185 if col&0x8000 != 0 {186 a.Col = at.Col + int(int8(col&0xFF))187 } else {188 a.Col = int(col & 0xFF)189 ref += "$"190 }191 ref += sheet.ColName(max(a.Col, 0))192 rowBits := uint(13)193 if v == wksFile {194 rowBits = 11195 }196 mask := uint16(1)<<rowBits - 1197 if row&0x8000 != 0 {198 off := int(row & mask)199 if off&(1<<(rowBits-1)) != 0 {200 off -= 1 << rowBits201 }202 a.Row = at.Row + off203 } else {204 a.Row = int(row & mask)205 ref += "$"206 }207 if !a.Valid() {208 return "#REF!", false209 }210 return ref + strconv.Itoa(a.Row+1), true211}212213// lotusFormula decodes the bytecode of the formula in cell at.214func lotusFormula(code []byte, at sheet.Addr, v wk1Version) (string, bool) {215 d := &lotusDecoder{code: code, at: at, version: v, ok: true}216 for d.i < len(code) {217 if !d.step() {218 return "", false219 }220 }221 if len(d.stack) != 1 {222 return "", false223 }224 return d.stack[0].text, d.ok225}226227// lotusDecoder runs a formula's bytecode on a stack of subexpressions.228// ok turns false when a part has no equivalent in 012; decoding goes on229// so the formula can still be written in 1-2-3's spelling.230type lotusDecoder struct {231 code []byte232 i int // the next opcode233 at sheet.Addr234 version wk1Version235 stack []expr236 ok bool237}238239// lotusOperands decode the opcodes that are neither binary operators nor240// functions. Each reports false when the bytecode is malformed.241var lotusOperands = map[byte]func(*lotusDecoder) bool{242 0x00: (*lotusDecoder).number,243 0x01: (*lotusDecoder).cell,244 0x02: (*lotusDecoder).cellRange,245 0x03: (*lotusDecoder).end,246 0x04: (*lotusDecoder).parens,247 0x05: (*lotusDecoder).integer,248 0x06: (*lotusDecoder).str,249 0x08: unary("-", unaryPower),250 0x16: unary("#NOT#", notPower),251 0x17: unary("+", unaryPower),252}253254// step decodes the opcode at d.i, reporting false when the bytecode is255// malformed.256func (d *lotusDecoder) step() bool {257 op := d.code[d.i]258 if decode, ok := lotusOperands[op]; ok {259 return decode(d)260 }261 if bin, ok := lotusBinary[op]; ok {262 return d.binary(bin.op, bin.power)263 }264 if fn, ok := lotusFuncs[op]; ok {265 return d.function(fn)266 }267 return false268}269270func (d *lotusDecoder) push(text string, power int) {271 d.stack = append(d.stack, expr{text, power})272}273274func (d *lotusDecoder) pop() (expr, bool) {275 if len(d.stack) == 0 {276 return expr{}, false277 }278 e := d.stack[len(d.stack)-1]279 d.stack = d.stack[:len(d.stack)-1]280 return e, true281}282283// has reports whether n bytes of operands follow the opcode.284func (d *lotusDecoder) has(n int) bool { return d.i+1+n <= len(d.code) }285286func (d *lotusDecoder) u16(at int) uint16 { return binary.LittleEndian.Uint16(d.code[at:]) }287288// literal pushes a number; a negative one binds as a unary minus does.289func (d *lotusDecoder) literal(text string, negative bool) {290 if negative {291 d.push(text, unaryPower)292 } else {293 d.push(text, atomPower)294 }295}296297func (d *lotusDecoder) number() bool {298 if !d.has(8) {299 return false300 }301 f := math.Float64frombits(binary.LittleEndian.Uint64(d.code[d.i+1:]))302 if math.IsNaN(f) || math.IsInf(f, 0) {303 return false304 }305 d.literal(numInput(f), f < 0)306 d.i += 9307 return true308}309310func (d *lotusDecoder) integer() bool {311 if !d.has(2) {312 return false313 }314 n := int16(d.u16(d.i + 1))315 d.literal(strconv.Itoa(int(n)), n < 0)316 d.i += 3317 return true318}319320// ref decodes the cell reference whose coordinates start at code[at].321func (d *lotusDecoder) ref(at int) string {322 ref, good := lotusRef(d.u16(at), d.u16(at+2), d.at, d.version)323 d.ok = d.ok && good324 return ref325}326327func (d *lotusDecoder) cell() bool {328 if !d.has(4) {329 return false330 }331 d.push(d.ref(d.i+1), atomPower)332 d.i += 5333 return true334}335336func (d *lotusDecoder) cellRange() bool {337 if !d.has(8) {338 return false339 }340 from, to := d.ref(d.i+1), d.ref(d.i+5)341 d.push(from+":"+to, atomPower)342 d.i += 9343 return true344}345346func (d *lotusDecoder) end() bool {347 d.i = len(d.code)348 return true349}350351// parens are parentheses as the formula was written.352func (d *lotusDecoder) parens() bool {353 e, good := d.pop()354 if !good {355 return false356 }357 d.push("("+e.text+")", atomPower)358 d.i++359 return true360}361362func (d *lotusDecoder) str() bool {363 end := indexByte(d.code[d.i+1:], 0)364 if end < 0 {365 return false366 }367 s := lics(string(d.code[d.i+1 : d.i+1+end]))368 d.push(`"`+strings.ReplaceAll(s, `"`, `""`)+`"`, atomPower)369 d.i += end + 2370 return true371}372373// unary decodes a prefix operator that binds with the given power.374func unary(sign string, power int) func(*lotusDecoder) bool {375 return func(d *lotusDecoder) bool {376 x, good := d.pop()377 if !good {378 return false379 }380 if x.power < power {381 x.text = "(" + x.text + ")"382 }383 d.push(sign+x.text, power)384 d.i++385 return true386 }387}388389func (d *lotusDecoder) binary(op string, power int) bool {390 r, good1 := d.pop()391 l, good2 := d.pop()392 if !good1 || !good2 {393 return false394 }395 if l.power < power {396 l.text = "(" + l.text + ")"397 }398 if r.power <= power {399 r.text = "(" + r.text + ")"400 }401 d.push(l.text+op+r.text, power)402 d.i++403 return true404}405406// function decodes a call whose arguments are on the stack. A function407// with no equivalent is written as 1-2-3 spells it, clearing ok.408func (d *lotusDecoder) function(fn lotusFunc) bool {409 n, size := fn.args, 1410 if n < 0 {411 if !d.has(1) {412 return false413 }414 n, size = int(d.code[d.i+1]), 2415 }416 if n > len(d.stack) {417 return false418 }419 args := make([]string, n)420 for k := range args {421 args[k] = d.stack[len(d.stack)-n+k].text422 }423 d.stack = d.stack[:len(d.stack)-n]424 text := ""425 if fn.write != nil && (fn.args >= 0 || n > 0) {426 text = fn.write(args)427 }428 if text == "" {429 d.ok = false430 text = "@" + fn.name431 if n > 0 {432 text += "(" + strings.Join(args, ",") + ")"433 }434 }435 d.push(text, atomPower)436 d.i += size437 return true438}