Repository Public
1// =============================================================================2// SP BOUCHER | SOFTWARE & RESEARCH3// ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project : ORIX Sheet9// File : Packages/SheetAnalytics/Sources/Audit/Formula/FormulaHighlighter.swift10// Module : Audit11// Repository : https://git.spboucher.ai/orix-sheet12// Created : 2026-11-2113// Last Modified : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author : Simon-Pierre Boucher20// Position : Professor of Finance21// Institution : Université du Québec en Outaouais (UQO)22// Department : Département des sciences administratives23// Organization : SP Boucher24//25// Website : https://spboucher.ai26// Git : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General : info@spboucher.ai33// Professional : contact@spboucher.ai34// Direct : simon@spboucher.ai35// Research : research@spboucher.ai36// Development : dev@spboucher.ai37// Support : support@spboucher.ai38// Security/Admin : admin@spboucher.ai39// Notifications : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification : Public66// Distribution : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License : None specified (no license file in this repository)80// Maintainer : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import SheetCore8990/// Tolerant, tokenizer-based syntax highlighting. It never fails: incomplete or invalid text91/// (unterminated strings, half-typed references, missing parentheses) still yields spans, so it can92/// run on every keystroke. All offsets are UTF-16 code units (NSString / text-view ranges).93public enum FormulaHighlighter {94 public enum TokenKind: String, Sendable, Codable, CaseIterable {95 case function, reference, number, string, `operator`, error, name, boolean, punctuation, whitespace, unknown96 }9798 public struct Span: Hashable, Sendable, Codable {99 public var kind: TokenKind100 /// UTF-16 offset of the first code unit.101 public var location: Int102 /// Length in UTF-16 code units.103 public var length: Int104 public var text: String105 /// For references: index of the distinct reference this span denotes (first appearance = 0).106 /// The same range written twice (`A1`, `$A$1`, `a1`) gets the same index.107 public var colorIndex: Int?108 public var end: Int { location + length }109 }110111 /// One distinct reference of the formula, for outlining ranges in the grid with matching colours.112 public struct ReferencedRange: Hashable, Sendable, Codable {113 public var colorIndex: Int114 /// Sheet name when the reference is sheet-qualified.115 public var sheet: String?116 /// The cells referenced; nil for structured references and incomplete text.117 public var range: RangeRef?118 /// Text of the first occurrence.119 public var text: String120 }121122 public struct Result: Hashable, Sendable, Codable {123 public var spans: [Span]124 public var references: [ReferencedRange]125 /// Spans without whitespace.126 public var significant: [Span] { spans.filter { $0.kind != .whitespace } }127 }128129 /// Tokenizes and classifies formula text (with or without the leading "=").130 public static func highlight(_ text: String, syntax: FormulaSyntax = .canonical) -> Result {131 var lexer = Lexer(text, syntax)132 var spans = lexer.run()133 var keys = [String: Int]()134 var refs = [ReferencedRange]()135 for i in spans.indices where spans[i].kind == .reference {136 let (sheet, range) = parseReference(spans[i].text)137 let key = (sheet?.uppercased() ?? "") + "!" + (range?.a1 ?? spans[i].text.uppercased().replacingOccurrences(of: "$", with: ""))138 if let k = keys[key] {139 spans[i].colorIndex = k140 } else {141 let k = refs.count142 keys[key] = k143 spans[i].colorIndex = k144 refs.append(ReferencedRange(colorIndex: k, sheet: sheet, range: range, text: spans[i].text))145 }146 }147 return Result(spans: spans, references: refs)148 }149150 /// Splits reference text into its sheet qualifier and cell range ("'My Sheet'!$A$1:B2" → ("My Sheet", A1:B2)).151 static func parseReference(_ text: String) -> (sheet: String?, range: RangeRef?) {152 var sheet: String?153 var rest = Substring(text)154 if rest.first == "'" {155 // quoted sheet name with '' escapes156 var name = "", i = rest.index(after: rest.startIndex), closed = false157 while i < rest.endIndex {158 if rest[i] == "'" {159 let next = rest.index(after: i)160 if next < rest.endIndex, rest[next] == "'" { name.append("'"); i = rest.index(after: next); continue }161 closed = true; i = next; break162 }163 name.append(rest[i]); i = rest.index(after: i)164 }165 guard closed, i < rest.endIndex, rest[i] == "!" else { return (closed ? name : nil, nil) }166 sheet = name167 rest = rest[rest.index(after: i)...]168 } else if let bang = rest.firstIndex(of: "!"), !rest.contains("[") {169 sheet = String(rest[..<bang])170 rest = rest[rest.index(after: bang)...]171 }172 return (sheet, RangeRef(a1: String(rest)))173 }174175 // MARK: Lexer176177 struct Lexer {178 let u: [UInt16]179 let syn: FormulaSyntax180 var i = 0181 var out = [Span]()182183 init(_ text: String, _ syntax: FormulaSyntax) { u = Array(text.utf16); syn = syntax }184185 func at(_ k: Int) -> UInt16 { k >= 0 && k < u.count ? u[k] : 0 }186 static func isDigit(_ b: UInt16) -> Bool { b >= 48 && b <= 57 }187 static func isLetter(_ b: UInt16) -> Bool { (b >= 65 && b <= 90) || (b >= 97 && b <= 122) }188 static func isWordStart(_ b: UInt16) -> Bool { isLetter(b) || b == 95 || b == 92 || b >= 0x80 }189 static func isWord(_ b: UInt16) -> Bool { isLetter(b) || isDigit(b) || b == 95 || b == 46 || b == 92 || b == 63 || b >= 0x80 }190 static func isSpace(_ b: UInt16) -> Bool { b == 32 || b == 9 || b == 10 || b == 13 || b == 0xA0 }191192 mutating func emit(_ kind: TokenKind, _ start: Int, _ end: Int) {193 guard end > start else { return }194 out.append(Span(kind: kind, location: start, length: end - start, text: String(decoding: u[start..<end], as: UTF16.self), colorIndex: nil))195 }196197 /// End of `[$]COL[$]ROW` starting at k, or nil.198 func cellEnd(_ k: Int) -> Int? {199 var j = k200 if at(j) == 36 { j += 1 }201 var col = 0, letters = 0202 while Self.isLetter(at(j)) {203 let up = at(j) >= 97 ? at(j) - 32 : at(j)204 col = col * 26 + Int(up - 64); letters += 1; j += 1205 if letters > 3 { return nil }206 }207 guard letters > 0, col <= Limits.maxCols else { return nil }208 if at(j) == 36 { j += 1 }209 var row = 0, digits = 0210 while Self.isDigit(at(j)) {211 if digits == 0 && at(j) == 48 { return nil }212 row = row * 10 + Int(at(j) - 48); digits += 1; j += 1213 if digits > 7 { return nil }214 }215 guard digits > 0, row <= Limits.maxRows else { return nil }216 return j217 }218219 func colEnd(_ k: Int) -> Int? {220 var j = k221 if at(j) == 36 { j += 1 }222 var col = 0, letters = 0223 while Self.isLetter(at(j)) {224 let up = at(j) >= 97 ? at(j) - 32 : at(j)225 col = col * 26 + Int(up - 64); letters += 1; j += 1226 if letters > 3 { return nil }227 }228 return letters > 0 && col <= Limits.maxCols ? j : nil229 }230231 func rowEnd(_ k: Int) -> Int? {232 var j = k233 if at(j) == 36 { j += 1 }234 let start = j235 while Self.isDigit(at(j)) { j += 1 }236 return j > start && j - start <= 7 && at(start) != 48 ? j : nil237 }238239 /// End of a cell, cell range, column range or row range starting at k (must not run into a word or a call).240 func referenceEnd(_ k: Int) -> Int? {241 func terminated(_ e: Int) -> Bool { !Self.isWord(at(e)) && at(e) != 40 && at(e) != 36 }242 if let e1 = cellEnd(k) {243 if at(e1) == 58, let e2 = cellEnd(e1 + 1), terminated(e2) { return e2 }244 if terminated(e1) { return e1 }245 }246 if let c1 = colEnd(k), at(c1) == 58, let c2 = colEnd(c1 + 1), terminated(c2) { return c2 }247 if let r1 = rowEnd(k), at(r1) == 58, let r2 = rowEnd(r1 + 1), terminated(r2) { return r2 }248 return nil249 }250251 /// End of a balanced `[...]` group starting at k (the whole remainder when unbalanced).252 func bracketEnd(_ k: Int) -> Int {253 var depth = 0, j = k254 while j < u.count {255 if u[j] == 39, j + 1 < u.count { j += 2; continue } // 'x escapes inside structured references256 if u[j] == 91 { depth += 1 } else if u[j] == 93 { depth -= 1; if depth == 0 { return j + 1 } }257 j += 1258 }259 return u.count260 }261262 mutating func run() -> [Span] {263 // leading "=" (after optional spaces)264 var k = 0265 while Self.isSpace(at(k)) && k < u.count { k += 1 }266 if at(k) == 61 { emit(.whitespace, 0, k); emit(.operator, k, k + 1); i = k + 1 }267 while i < u.count { step() }268 return out269 }270271 mutating func step() {272 let b = u[i], start = i273 if Self.isSpace(b) {274 while i < u.count, Self.isSpace(u[i]) { i += 1 }275 emit(.whitespace, start, i); return276 }277 if b == 34 { // string278 i += 1279 while i < u.count {280 if u[i] == 34 { if at(i + 1) == 34 { i += 2; continue }; i += 1; break }281 i += 1282 }283 emit(.string, start, i); return284 }285 if b == 39 { // 'quoted sheet'!ref286 i += 1287 var closed = false288 while i < u.count {289 if u[i] == 39 { if at(i + 1) == 39 { i += 2; continue }; i += 1; closed = true; break }290 i += 1291 }292 if closed, at(i) == 33 { i += 1; consumeAfterSheet() }293 emit(.reference, start, i); return294 }295 if b == 35 { // error literal or spill operator296 for e in CellError.allCases {297 let lit = Array(e.rawValue.utf16)298 if i + lit.count <= u.count, zip(lit, u[i..<(i + lit.count)]).allSatisfy({ a, c in a == c || (Self.isLetter(c) && a == c - 32) }) {299 i += lit.count300 emit(.error, start, i); return301 }302 }303 if let last = out.last, last.kind == .reference, last.end == i { i += 1; emit(.operator, start, i); return }304 i += 1305 while i < u.count, Self.isLetter(u[i]) || u[i] == 47 || u[i] == 33 || u[i] == 63 || Self.isDigit(u[i]) { i += 1 }306 emit(.error, start, i); return307 }308 let decimal = UInt16(syn.decimalSeparator)309 if Self.isDigit(b) || (b == decimal && Self.isDigit(at(i + 1))) {310 if Self.isDigit(b), let e = referenceEnd(i) { i = e; emit(.reference, start, i); return }311 while Self.isDigit(at(i)) { i += 1 }312 if at(i) == decimal, decimal == 46 || Self.isDigit(at(i + 1)) {313 i += 1314 while Self.isDigit(at(i)) { i += 1 }315 }316 if at(i) == 69 || at(i) == 101 {317 var j = i + 1318 if at(j) == 43 || at(j) == 45 { j += 1 }319 if Self.isDigit(at(j)) { i = j; while Self.isDigit(at(i)) { i += 1 } }320 }321 emit(.number, start, i); return322 }323 if b == 36 || Self.isWordStart(b) {324 if let e = referenceEnd(i) { i = e; emit(.reference, start, i); return }325 if b == 36 { i += 1; emit(.unknown, start, i); return }326 while i < u.count, Self.isWord(u[i]) { i += 1 }327 if at(i) == 33 { i += 1; consumeAfterSheet(); emit(.reference, start, i); return }328 if at(i) == 40 { emit(.function, start, i); return }329 if at(i) == 91 { i = bracketEnd(i); emit(.reference, start, i); return }330 let word = String(decoding: u[start..<i], as: UTF16.self).uppercased()331 emit(word == "TRUE" || word == "FALSE" ? .boolean : .name, start, i); return332 }333 if b == 91 { i = bracketEnd(i); emit(.reference, start, i); return }334 switch b {335 case 60: // < <= <>336 i += (at(i + 1) == 61 || at(i + 1) == 62) ? 2 : 1337 emit(.operator, start, i)338 case 62:339 i += at(i + 1) == 61 ? 2 : 1340 emit(.operator, start, i)341 case 43, 45, 42, 47, 94, 38, 61, 37, 58, 64:342 i += 1; emit(.operator, start, i)343 case 40, 41, 123, 125:344 i += 1; emit(.punctuation, start, i)345 default:346 i += 1347 let sep = b == UInt16(syn.argSeparator) || b == UInt16(syn.arrayRowSeparator) || b == UInt16(syn.arrayColSeparator)348 emit(sep ? .punctuation : .unknown, start, i)349 }350 }351352 /// After `Sheet!`: a reference, an error literal or a name.353 mutating func consumeAfterSheet() {354 if let e = referenceEnd(i) { i = e; return }355 if at(i) == 35 { while i < u.count, u[i] == 35 || Self.isLetter(u[i]) || u[i] == 47 || u[i] == 33 || u[i] == 63 { i += 1 }; return }356 while i < u.count, Self.isWord(u[i]) || u[i] == 36 { i += 1 }357 }358 }359}360