Repository Public
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