SPB Git

Repository Public

FormulaIntelTests.swift 16.8 KB 277 lines swift

1import Foundation2import Testing3import SheetCore4@testable import Audit56/// Small workbook harness for the Audit tests: set cells by A1 text, read computed values.7final class ACBook {8    let wb: Workbook9    let engine: CalcEngine10    let parser = InputParser(locale: Locale(identifier: "en_US"))11    init(sheets: [String] = ["Sheet1"], registry: FunctionRegistry = .standard) {12        wb = Workbook(sheetNames: sheets)13        engine = CalcEngine(workbook: wb, registry: registry)14    }15    func set(_ a1: String, _ text: String, sheet: Int = 0) {16        let ref = CellRef(a1: a1)!17        try! engine.setInput(sheet: sheet, row: ref.row, col: ref.col, text, parser: parser)18    }19    func setText(_ a1: String, _ text: String, sheet: Int = 0) {20        let ref = CellRef(a1: a1)!21        engine.set(sheet: sheet, row: ref.row, col: ref.col, .value(.text(text)))22    }23    func get(_ a1: String, sheet: Int = 0) -> Value {24        engine.recalculate()25        let ref = CellRef(a1: a1)!26        return wb.sheets[sheet].value(ref.row, ref.col)27    }28    func ref(_ a1: String) -> CellRef { CellRef(a1: a1)! }29}3031@Suite struct FormulaShapeTests {32    func shape(_ f: String, _ at: String) -> String { FormulaShape(formula: f, at: CellRef(a1: at)!)!.r1c1 }3334    @Test func relativeAndAbsoluteReferences() {35        #expect(shape("=A1+B$2*$C3", "B2") == "R[-1]C[-1]+R2C*R[1]C3")36        #expect(shape("=$A$1", "Z99") == "R1C1")37        #expect(shape("=B2", "B2") == "RC")38        #expect(shape("=SUM(A:A)", "C5") == "SUM(C[-2]:C[-2])")39        #expect(shape("=SUM($A:B)", "C5") == "SUM(C1:C[-1])")40        #expect(shape("=SUM($2:4)", "C5") == "SUM(R2:R[-1])")41        #expect(shape("=SUM(A1:$B$3)", "A4") == "SUM(R[-3]C:R3C2)")42        #expect(shape("=Sheet2!A1+'My Sheet'!$B$2", "A1") == "Sheet2!RC+'My Sheet'!R2C2")43        #expect(shape("=IF(A1>0,\"x\",-B1%)", "C1") == "IF(RC[-2]>0,\"x\",-RC[-1]%)")44        #expect(shape("=Table1[Amount]*2", "C1") == "Table1[Amount]*2")45        #expect(FormulaShape(formula: "=1+", at: CellRef(0, 0)) == nil)46    }4748    @Test func copiesShareAShape() {49        #expect(shape("=B2*C2", "D2") == shape("=B3*C3", "D3"))50        #expect(shape("=B2*$C$1", "D2") == shape("=B9*$C$1", "D9"))51        #expect(shape("=B2*C2", "D2") != shape("=B3*C4", "D3"))52        #expect(shape("=SUM(B2:B10)", "B11") == shape("=SUM(C2:C10)", "C11"))53        let s = FormulaShape(formula: "=SUM(B2:B10)/2", at: CellRef(a1: "B11")!)!54        #expect(s.tokens == ["SUM", "(", "R[-9]C:R[-1]C", ")", "/", "2"])55        #expect(s.tokens.joined() == s.r1c1)56    }5758    @Test func diffs() {59        let same = FormulaComparison.relativeDiff("=B2*C2", at: CellRef(a1: "D2")!, "=B3*C3", at: CellRef(a1: "D3")!)!60        #expect(same.isIdentical && same.changes.isEmpty && same.summary == "identical")61        let d = FormulaComparison.relativeDiff("=B2*C2", at: CellRef(a1: "D2")!, "=B3*C3+10", at: CellRef(a1: "D3")!)!62        #expect(!d.isIdentical)63        #expect(d.changes == [FormulaDiff.Change(old: "", new: "+10")])64        #expect(d.summary == "(nothing) → +10")65        let a1 = FormulaComparison.diff("=SUM(A1:A3)*2", "=SUM(A1:A4)*2")!66        #expect(a1.changes == [FormulaDiff.Change(old: "A1:A3", new: "A1:A4")])67        #expect(a1.segments.map(\.kind) == [.equal, .equal, .removed, .added, .equal, .equal, .equal])68        let fn = FormulaComparison.diff("=MAX(A1,B1)", "=MIN(A1,B1)")!69        #expect(fn.changes == [FormulaDiff.Change(old: "MAX", new: "MIN")])70        let del = FormulaComparison.diff("=A1+B1+C1", "=A1+C1")!71        #expect(del.changes.count == 1 && del.changes[0].new.isEmpty)72        #expect(FormulaComparison.diff("=A1+", "=A1") == nil)73        let rel = FormulaComparison.relativeDiff("=B2*C2", at: CellRef(a1: "D2")!, "=B3*C4", at: CellRef(a1: "D3")!)!74        #expect(rel.changes == [FormulaDiff.Change(old: "RC[-1]", new: "R[1]C[-1]")])75        let exprDiff = FormulaComparison.diff(try! FormulaParser.parse("=A1"), try! FormulaParser.parse("=A2"))76        #expect(exprDiff.changes == [FormulaDiff.Change(old: "A1", new: "A2")])77        let empty = FormulaDiff(old: [], new: ["A1"])78        #expect(empty.changes == [FormulaDiff.Change(old: "", new: "A1")])79    }80}8182@Suite struct FormulaHighlighterTests {83    typealias K = FormulaHighlighter.TokenKind84    func kinds(_ s: String) -> [K] { FormulaHighlighter.highlight(s).significant.map(\.kind) }85    func texts(_ s: String) -> [String] { FormulaHighlighter.highlight(s).significant.map(\.text) }8687    @Test func incompleteFormulaWhileTyping() {88        let s = "=SUM(A1:B2, IF(C1>0,"89        #expect(texts(s) == ["=", "SUM", "(", "A1:B2", ",", "IF", "(", "C1", ">", "0", ","])90        #expect(kinds(s) == [.operator, .function, .punctuation, .reference, .punctuation, .function, .punctuation, .reference, .operator, .number, .punctuation])91        #expect(kinds("=\"abc") == [.operator, .string])92        #expect(texts("=\"abc") == ["=", "\"abc"])93        #expect(kinds("=A1+") == [.operator, .reference, .operator])94        #expect(kinds("=SU") == [.operator, .name])95        #expect(kinds("='My Sh") == [.operator, .reference])96        #expect(kinds("=Sheet2!") == [.operator, .reference])97        #expect(kinds("=#RE") == [.operator, .error])98        #expect(kinds("=A1:") == [.operator, .reference, .operator])99        #expect(kinds("=Table1[Am") == [.operator, .reference])100        #expect(kinds("") == [])101        #expect(kinds("=") == [.operator])102    }103104    @Test func tokenClasses() {105        #expect(kinds("=1.5E+3%") == [.operator, .number, .operator])106        #expect(texts("=1.5E+3%") == ["=", "1.5E+3", "%"])107        #expect(kinds("=#REF!+#N/A+#DIV/0!") == [.operator, .error, .operator, .error, .operator, .error])108        #expect(kinds("=TRUE&false") == [.operator, .boolean, .operator, .boolean])109        #expect(kinds("=LOG10(100)") == [.operator, .function, .punctuation, .number, .punctuation])110        #expect(kinds("=A:A+1:3+$B:$C") == [.operator, .reference, .operator, .reference, .operator, .reference])111        #expect(kinds("=A1#") == [.operator, .reference, .operator])112        #expect(kinds("=TaxRate*B2") == [.operator, .name, .operator, .reference])113        #expect(kinds("=a1<>B2") == [.operator, .reference, .operator, .reference])114        #expect(texts("=a1<=B2>=C3") == ["=", "a1", "<=", "B2", ">=", "C3"])115        #expect(kinds("={1,2;3,4}") == [.operator, .punctuation, .number, .punctuation, .number, .punctuation, .number, .punctuation, .number, .punctuation])116        #expect(kinds("=Table1[[#Headers],[Amount]]+[@Qty]") == [.operator, .reference, .operator, .reference])117        #expect(kinds("=\"a\"\"b\"&\"c\"") == [.operator, .string, .operator, .string])118        #expect(texts("=\"a\"\"b\"&\"c\"") == ["=", "\"a\"\"b\"", "&", "\"c\""])119        #expect(kinds("=XFD1048576+XFE1") == [.operator, .reference, .operator, .name])120        #expect(kinds("=SUM(1;2)") == [.operator, .function, .punctuation, .number, .punctuation, .number, .punctuation])121        #expect(kinds("=A1 ? B1") == [.operator, .reference, .unknown, .reference])122        // semicolon syntax: comma is the decimal separator123        let fr = FormulaHighlighter.highlight("=SUM(1,5;2)", syntax: .semicolon).significant124        #expect(fr.map(\.text) == ["=", "SUM", "(", "1,5", ";", "2", ")"])125        #expect(fr[3].kind == .number)126    }127128    @Test func referenceColoursAreStablePerDistinctReference() {129        let r = FormulaHighlighter.highlight("=A1+$A$1+a1+B2:C3+Sheet2!A1+b2:c3")130        let refs = r.spans.filter { $0.kind == .reference }131        #expect(refs.map(\.colorIndex) == [0, 0, 0, 1, 2, 1])132        #expect(r.references.count == 3)133        #expect(r.references[0].range == RangeRef(r0: 0, c0: 0, r1: 0, c1: 0) && r.references[0].sheet == nil)134        #expect(r.references[1].range == RangeRef(r0: 1, c0: 1, r1: 2, c1: 2))135        #expect(r.references[2].sheet == "Sheet2" && r.references[2].range == RangeRef(r0: 0, c0: 0, r1: 0, c1: 0))136        #expect(r.spans.filter { $0.kind != .reference }.allSatisfy { $0.colorIndex == nil })137        let q = FormulaHighlighter.highlight("='My Sheet'!$A$1:B2*'It''s'!C3+A:B")138        #expect(q.references.map(\.sheet) == ["My Sheet", "It's", nil])139        #expect(q.references[0].range == RangeRef(r0: 0, c0: 0, r1: 1, c1: 1))140        #expect(q.references[1].range == RangeRef(r0: 2, c0: 2, r1: 2, c1: 2))141        #expect(q.references[2].range?.isWholeColumns == true)142        let t = FormulaHighlighter.highlight("=Table1[Amount]+Table1[amount]+Sheet2!")143        #expect(t.references.count == 2 && t.references[0].range == nil)144        #expect(t.spans.filter { $0.kind == .reference }.map(\.colorIndex) == [0, 0, 1])145    }146147    @Test func spansCoverTheTextWithUTF16Offsets() {148        let samples = ["=SUM(A1:B2, IF(C1>0,", "  = A1 +  2", "=)(,,]]'x", "=\"é😀\"&A1", "plain text", "=[@Qty]*$", "=1e5+.5+5.", "='a'!#REF!+A1#", "=_xlfn.XLOOKUP(1,2,3)", "=été+1", "=$"]149        for s in samples {150            let spans = FormulaHighlighter.highlight(s).spans151            var pos = 0152            for sp in spans {153                #expect(sp.location == pos, "gap in \(s)")154                #expect(sp.length > 0)155                #expect(sp.text == String(decoding: Array(s.utf16)[sp.location..<sp.end], as: UTF16.self))156                pos = sp.end157            }158            #expect(pos == s.utf16.count, "coverage of \(s)")159        }160        let e = FormulaHighlighter.highlight("=\"é😀\"&A1").significant161        #expect(e[1].kind == .string && e[1].location == 1 && e[1].length == 5)162        #expect(e[3].kind == .reference && e[3].location == 7)163        let lead = FormulaHighlighter.highlight("  = A1").spans164        #expect(lead.map(\.kind) == [.whitespace, .operator, .whitespace, .reference])165    }166}167168@Suite struct FormulaAssistTests {169    @Test func enclosingFunctionAndArgumentIndex() {170        let text = "=SUM(A1:B2, IF(C1>0,"171        let end = FormulaAssist.context(text: text, cursor: text.utf16.count)172        #expect(end.function?.name == "IF")173        #expect(end.function?.argumentIndex == 1)174        #expect(end.function?.isKnown == true)175        #expect(end.function?.parameters == ["condition", "value_if_true", "[value_if_false]"])176        #expect(end.function?.activeParameter == 1)177        #expect(end.function?.nameLocation == 12)178        #expect(!end.inString)179        let inSum = FormulaAssist.context(text: text, cursor: 11)       // just after "A1:B2,"180        #expect(inSum.function?.name == "SUM" && inSum.function?.argumentIndex == 1)181        #expect(inSum.function?.signature == "SUM(number1, [number2], …)")182        #expect(inSum.function?.summary == "Adds its arguments.")183        #expect(inSum.function?.category == "Math")184        #expect(inSum.function?.activeParameter == 1)185        let first = FormulaAssist.context(text: "=SUM(A1,B1)", cursor: 6)186        #expect(first.function?.name == "SUM" && first.function?.argumentIndex == 0 && first.function?.activeParameter == 0)187        let closed = FormulaAssist.context(text: "=SUM(A1,B1)", cursor: 11)188        #expect(closed.function == nil)189        let nested = FormulaAssist.context(text: "=SUM(MAX(1,2),3", cursor: 15)190        #expect(nested.function?.name == "SUM" && nested.function?.argumentIndex == 1)191        let inner = FormulaAssist.context(text: "=SUM(MAX(1,2),3", cursor: 11)192        #expect(inner.function?.name == "MAX" && inner.function?.argumentIndex == 1)193        let array = FormulaAssist.context(text: "=SUM({1,2,3},", cursor: 13)194        #expect(array.function?.argumentIndex == 1)195        let many = FormulaAssist.context(text: "=SUM(1,2,3,4,5,", cursor: 15)196        #expect(many.function?.argumentIndex == 5 && many.function?.activeParameter == 1)   // variadic: "[number2]" repeats197        let paren = FormulaAssist.context(text: "=ABS((A1+B1)*", cursor: 13)198        #expect(paren.function?.name == "ABS" && paren.function?.argumentIndex == 0)199        let grouped = FormulaAssist.context(text: "=ABS((A1+", cursor: 9)200        #expect(grouped.function?.name == "ABS")201        let unknown = FormulaAssist.context(text: "=FOO(1,", cursor: 7)202        #expect(unknown.function?.name == "FOO" && unknown.function?.isKnown == false && unknown.function?.argumentIndex == 1)203        #expect(unknown.function?.signature == "" && unknown.function?.activeParameter == nil)204        let none = FormulaAssist.context(text: "=A1+", cursor: 4)205        #expect(none.function == nil && none.completion == nil)206        let semi = FormulaAssist.context(text: "=SUM(1,5;2;", cursor: 11, syntax: .semicolon)207        #expect(semi.function?.argumentIndex == 2)208        let clamped = FormulaAssist.context(text: "=SUM(", cursor: 999)209        #expect(clamped.function?.name == "SUM" && clamped.function?.argumentIndex == 0)210        #expect(FormulaAssist.parameters(ofSignature: "PMT(rate, nper, pv, [fv], [type])") == ["rate", "nper", "pv", "[fv]", "[type]"])211        #expect(FormulaAssist.parameters(ofSignature: "NOW()") == [])212    }213214    @Test func stringsSuppressHints() {215        let s = FormulaAssist.context(text: "=IF(A1=\"SU", cursor: 10)216        #expect(s.inString && s.function == nil && s.completion == nil)217        let mid = FormulaAssist.context(text: "=IF(A1=\"a,b\",1,", cursor: 10)218        #expect(mid.inString)219        let after = FormulaAssist.context(text: "=IF(A1=\"a,b\",1,", cursor: 15)220        #expect(!after.inString && after.function?.argumentIndex == 2)    // the comma inside the string is not a separator221        let closedQuote = FormulaAssist.context(text: "=IF(A1=\"ab\"", cursor: 11)222        #expect(!closedQuote.inString && closedQuote.function?.name == "IF")223    }224225    @Test func completions() {226        let wb = Workbook(sheetNames: ["Summary", "Sales Data"])227        wb.setDefinedName(DefinedName(name: "SalesTax", formula: "Summary!$B$1"))228        var hidden = DefinedName(name: "SecretName", formula: "Summary!$B$2")229        hidden.hidden = true230        wb.setDefinedName(hidden)231        var table = TableDef(id: 1, name: "Sales", range: RangeRef(a1: "A1:C5")!, columns: [TableColumn(name: "Region"), TableColumn(name: "Amount"), TableColumn(name: "Amount EUR")])232        table.hasHeader = true233        wb.sheets[1].meta.tables = [table]234235        let su = FormulaAssist.context(text: "=SU", cursor: 3, workbook: wb).completion236        #expect(su?.prefix == "SU" && su?.replaceLocation == 1 && su?.replaceLength == 2)237        #expect(su?.candidates.first?.text == "SUM" && su?.candidates.first?.kind == .function)238        #expect(su?.candidates.first?.insertText == "SUM(" && su?.candidates.first?.detail == "SUM(number1, [number2], …)")239        #expect(su?.candidates.first?.category == "Math")240        #expect(su?.candidates.contains { $0.kind == .sheet && $0.text == "Summary" && $0.insertText == "Summary!" } == true)241242        let s = FormulaAssist.context(text: "=1+s", cursor: 4, workbook: wb).completion!243        let kinds = Set(s.candidates.map(\.kind))244        #expect(kinds == [.function, .definedName, .table, .sheet])245        #expect(s.candidates.contains { $0.kind == .definedName && $0.text == "SalesTax" && $0.detail == "=Summary!$B$1" })246        #expect(!s.candidates.contains { $0.text == "SecretName" })247        #expect(s.candidates.contains { $0.kind == .table && $0.text == "Sales" })248        #expect(s.candidates.contains { $0.kind == .sheet && $0.insertText == "'Sales Data'!" })249        #expect(s.candidates.contains { $0.kind == .function && $0.text == "SWITCH" })250        let fnNames = s.candidates.filter { $0.kind == .function }.map(\.text)251        #expect(fnNames.allSatisfy { $0.hasPrefix("S") })252        // functions come first, then names, tables, sheets253        let order = s.candidates.map(\.kind)254        #expect(order.firstIndex(of: .definedName)! > order.lastIndex(of: .function)!)255        #expect(s.replaceLocation == 3 && s.replaceLength == 1)256257        let exact = FormulaAssist.context(text: "=IF", cursor: 3).completion!258        #expect(exact.candidates.first?.text == "IF")259        #expect(exact.candidates.map(\.text).contains("IFERROR"))260261        let col = FormulaAssist.context(text: "=SUM(Sales[Am", cursor: 13, workbook: wb).completion!262        #expect(col.prefix == "Am" && col.replaceLocation == 11 && col.replaceLength == 2)263        #expect(col.candidates.map(\.text) == ["Amount", "Amount EUR"])264        #expect(col.candidates[0].kind == .tableColumn && col.candidates[0].insertText == "Amount]")265        let allCols = FormulaAssist.context(text: "=Sales[", cursor: 7, workbook: wb).completion!266        #expect(allCols.candidates.count == 3 && allCols.prefix == "")267268        #expect(FormulaAssist.context(text: "=ZZZZ", cursor: 5, workbook: wb).completion == nil)269        #expect(FormulaAssist.context(text: "=A1", cursor: 3, workbook: wb).completion == nil)       // a reference, not a word270        #expect(FormulaAssist.context(text: "=SUM(", cursor: 5, workbook: wb).completion == nil)271        let limited = FormulaAssist.context(text: "=S", cursor: 2, workbook: wb, limit: 2).completion!272        #expect(limited.candidates.count == 2)273        let midWord = FormulaAssist.context(text: "=SUM(A1)+MA", cursor: 11).completion!274        #expect(midWord.candidates.map(\.text) == ["MAX"])275    }276}277
Files main
Menu

Theme