Repository Public
1import Foundation2import SheetCore34/// Autocomplete and argument hints for the formula editor. Offsets are UTF-16 code units.5public enum FormulaAssist {6 /// The function call enclosing the cursor.7 public struct FunctionHint: Hashable, Sendable, Codable {8 /// Uppercased function name as typed.9 public var name: String10 /// Zero-based index of the argument the cursor is in.11 public var argumentIndex: Int12 /// True when the registry knows the function.13 public var isKnown: Bool14 /// e.g. "PMT(rate, nper, pv, [fv], [type])"; empty for unknown functions.15 public var signature: String16 public var summary: String17 public var category: String?18 /// Parameter names parsed from the signature (brackets kept for optional ones).19 public var parameters: [String]20 /// Index into `parameters` to emphasise (the last one repeats for variadic functions); nil when out of range.21 public var activeParameter: Int?22 /// UTF-16 location of the function name in the text.23 public var nameLocation: Int24 }2526 public enum CandidateKind: String, Sendable, Codable { case function, definedName, table, tableColumn, sheet }2728 public struct Candidate: Hashable, Sendable, Codable {29 public var kind: CandidateKind30 /// Display text (function name, defined name…).31 public var text: String32 /// Text to insert in place of the prefix (e.g. "SUM(", "'My Sheet'!", "Amount]").33 public var insertText: String34 /// Signature or other short detail.35 public var detail: String36 /// Function category, when applicable.37 public var category: String?38 }3940 public struct Completion: Hashable, Sendable, Codable {41 /// What the user has typed so far of the word being completed.42 public var prefix: String43 /// UTF-16 range (location, length) that the chosen candidate replaces.44 public var replaceLocation: Int45 public var replaceLength: Int46 public var candidates: [Candidate]47 }4849 public struct Context: Hashable, Sendable, Codable {50 public var function: FunctionHint?51 public var completion: Completion?52 /// True when the cursor is inside a string literal (no hints or completions apply).53 public var inString: Bool54 }5556 /// Analyses formula text at a cursor position.57 /// - Parameters:58 /// - cursor: UTF-16 offset of the insertion point (clamped to the text).59 /// - workbook: source of defined names, tables and sheet names (optional).60 /// - limit: maximum number of completion candidates.61 public static func context(text: String, cursor: Int, workbook: Workbook? = nil, registry: FunctionRegistry = .standard,62 syntax: FormulaSyntax = .canonical, limit: Int = 50) -> Context {63 let total = text.utf16.count64 let pos = max(0, min(cursor, total))65 let spans = FormulaHighlighter.highlight(text, syntax: syntax).spans66 // string under the cursor? (inside, or at the end of an unterminated one)67 if spans.contains(where: { $0.kind == .string && $0.location < pos && (pos < $0.end || (pos == $0.end && !isTerminated($0.text))) }) {68 return Context(function: nil, completion: nil, inString: true)69 }70 let hint = enclosingFunction(spans, pos: pos, registry: registry, syntax: syntax)71 let completion = completion(spans, pos: pos, workbook: workbook, registry: registry, limit: limit)72 return Context(function: hint, completion: completion, inString: false)73 }7475 /// Parameter names of a signature such as "PMT(rate, nper, pv, [fv], [type])".76 public static func parameters(ofSignature signature: String) -> [String] {77 guard let open = signature.firstIndex(of: "("), let close = signature.lastIndex(of: ")"), open < close else { return [] }78 let inner = signature[signature.index(after: open)..<close]79 return inner.split(separator: ",").map { $0.trimmingCharacters(in: .whitespaces) }.filter { !$0.isEmpty }80 }8182 private static func isTerminated(_ s: String) -> Bool {83 // a string literal is closed when, after the opening quote, the quotes pair up and the last one is unpaired84 var quotes = 085 for ch in s.dropFirst() where ch == "\"" { quotes += 1 }86 return s.count >= 2 && s.hasSuffix("\"") && quotes % 2 == 187 }8889 private static func enclosingFunction(_ spans: [FormulaHighlighter.Span], pos: Int, registry: FunctionRegistry, syntax: FormulaSyntax) -> FunctionHint? {90 struct Frame { var name: String?; var location: Int; var arg: Int }91 var stack = [Frame]()92 var braces = 093 var previous: FormulaHighlighter.Span?94 let sep = String(UnicodeScalar(syntax.argSeparator))95 for s in spans {96 if s.location >= pos { break }97 defer { if s.kind != .whitespace { previous = s } }98 guard s.kind == .punctuation else { continue }99 switch s.text {100 case "(":101 if let p = previous, p.kind == .function, p.end == s.location { stack.append(Frame(name: p.text.uppercased(), location: p.location, arg: 0)) }102 else { stack.append(Frame(name: nil, location: s.location, arg: 0)) }103 case ")": if !stack.isEmpty { stack.removeLast() }104 case "{": braces += 1105 case "}": braces = max(0, braces - 1)106 case sep: if braces == 0, !stack.isEmpty { stack[stack.count - 1].arg += 1 }107 default: break108 }109 }110 guard let frame = stack.last(where: { $0.name != nil }), let name = frame.name else { return nil }111 var canonical = name112 for prefix in ["_XLFN._XLWS.", "_XLFN.", "_XLWS.", "_XLL."] where canonical.hasPrefix(prefix) { canonical.removeFirst(prefix.count) }113 guard let spec = registry.lookup(canonical) else {114 if let form = FormulaExplainer.specialForms[canonical] {115 // functions evaluated by the engine itself (IF, LET…) are not in the registry116 let params = parameters(ofSignature: form.0)117 var active: Int? = frame.arg < params.count ? frame.arg : (params.last == "…" ? params.count - 1 : nil)118 if let a = active, params[a] == "…", a > 0 { active = a - 1 }119 return FunctionHint(name: canonical, argumentIndex: frame.arg, isKnown: true, signature: form.0, summary: form.1, category: "Logical",120 parameters: params, activeParameter: active, nameLocation: frame.location)121 }122 return FunctionHint(name: canonical, argumentIndex: frame.arg, isKnown: false, signature: "", summary: "", category: nil,123 parameters: [], activeParameter: nil, nameLocation: frame.location)124 }125 let params = parameters(ofSignature: spec.signature)126 var active: Int?127 if !params.isEmpty {128 if frame.arg < params.count { active = frame.arg }129 else if spec.maxArgs > params.count || params.last?.contains("…") == true || params.last?.contains("...") == true { active = params.count - 1 }130 // variadic signatures usually end with "…": point at the last real parameter instead131 if let a = active, a == params.count - 1, params[a] == "…" || params[a] == "...", a > 0 { active = a - 1 }132 }133 return FunctionHint(name: canonical, argumentIndex: frame.arg, isKnown: true, signature: spec.signature, summary: spec.summary,134 category: spec.category.rawValue, parameters: params, activeParameter: active, nameLocation: frame.location)135 }136137 private static func completion(_ spans: [FormulaHighlighter.Span], pos: Int, workbook: Workbook?, registry: FunctionRegistry, limit: Int) -> Completion? {138 guard let token = spans.last(where: { $0.end == pos && $0.kind != .whitespace }) else { return nil }139 // table column: "Table1[Am" or "Table1[[#Headers],[Am"140 if token.kind == .reference, let open = token.text.lastIndex(of: "["), !token.text.hasSuffix("]"), let workbook {141 let tableName = String(token.text[..<(token.text.firstIndex(of: "[") ?? open)])142 let partial = String(token.text[token.text.index(after: open)...])143 guard !partial.hasPrefix("#") else { return nil }144 let typed = partial.hasPrefix("@") ? String(partial.dropFirst()) : partial145 guard let (_, table) = workbook.table(named: tableName) else { return nil }146 let cands = table.columns.filter { typed.isEmpty || $0.name.uppercased().hasPrefix(typed.uppercased()) }.prefix(limit).map {147 Candidate(kind: .tableColumn, text: $0.name, insertText: $0.name + "]", detail: "Column of " + table.name, category: nil)148 }149 guard !cands.isEmpty else { return nil }150 return Completion(prefix: typed, replaceLocation: pos - typed.utf16.count, replaceLength: typed.utf16.count, candidates: Array(cands))151 }152 // a word being typed: names, and short reference-looking words such as "AB1" are left alone153 guard token.kind == .name || token.kind == .boolean || token.kind == .function else { return nil }154 let prefix = token.text155 let up = prefix.uppercased()156 var out = [Candidate]()157 for spec in registry.all where spec.name.hasPrefix(up) {158 out.append(Candidate(kind: .function, text: spec.name, insertText: spec.name + "(", detail: spec.signature, category: spec.category.rawValue))159 }160 for (name, form) in FormulaExplainer.specialForms where name.hasPrefix(up) && registry.lookup(name) == nil {161 out.append(Candidate(kind: .function, text: name, insertText: name + "(", detail: form.0, category: "Logical"))162 }163 out.sort { ($0.text != up ? 1 : 0, $0.text) < ($1.text != up ? 1 : 0, $1.text) }164 if let workbook {165 var names = [Candidate]()166 for dn in workbook.names.values where !dn.hidden && dn.name.uppercased().hasPrefix(up) {167 names.append(Candidate(kind: .definedName, text: dn.name, insertText: dn.name, detail: "=" + dn.formula, category: nil))168 }169 names.sort { $0.text.uppercased() < $1.text.uppercased() }170 var tables = [Candidate]()171 for sheet in workbook.sheets {172 for t in sheet.meta.tables where t.name.uppercased().hasPrefix(up) {173 tables.append(Candidate(kind: .table, text: t.name, insertText: t.name, detail: "Table on " + sheet.name + " (" + t.range.a1 + ")", category: nil))174 }175 }176 tables.sort { $0.text.uppercased() < $1.text.uppercased() }177 var sheets = [Candidate]()178 for sheet in workbook.sheets where sheet.name.uppercased().hasPrefix(up) {179 sheets.append(Candidate(kind: .sheet, text: sheet.name, insertText: FormulaPrinter.quoteSheet(sheet.name) + "!", detail: "Sheet", category: nil))180 }181 out += names + tables + sheets182 }183 guard !out.isEmpty else { return nil }184 return Completion(prefix: prefix, replaceLocation: token.location, replaceLength: token.length, candidates: Array(out.prefix(limit)))185 }186}187