Repository Public
1import Foundation2import SheetCore34/// A cell on a specific sheet (index into `Workbook.sheets`).5public struct CellAddress: Sendable, Hashable {6 public var sheet: Int7 public var cell: CellRef8 public init(sheet: Int, cell: CellRef) { self.sheet = sheet; self.cell = cell }9}1011/// One column of a range read from the workbook.12///13/// **Missing-value rule.** Only cells holding a finite number are numeric data. Blank cells, text (even text14/// that looks like a number), booleans and error values are MISSING for numeric analyses: `numbers` holds NaN there.15public struct DataColumn: Sendable, Hashable {16 /// Header text, or the column letter when the range has no header (or the header cell is blank).17 public var name: String18 /// Offset of the column inside the range (0 = leftmost).19 public var index: Int20 /// Absolute sheet column.21 public var sheetColumn: Int22 public var values: [Value]23 /// One entry per data row; NaN where the cell is not a finite number.24 public var numbers: [Double]25 public var numericCount: Int26 public var textCount: Int27 public var blankCount: Int2829 public init(name: String, index: Int, sheetColumn: Int, values: [Value]) {30 self.name = name; self.index = index; self.sheetColumn = sheetColumn; self.values = values31 var numbers = [Double](); numbers.reserveCapacity(values.count)32 var n = 0, t = 0, b = 033 for v in values {34 switch v {35 case .number(let d) where d.isFinite: numbers.append(d); n += 136 case .blank: numbers.append(.nan); b += 137 case .text(let s): numbers.append(.nan); if s.trimmingCharacters(in: .whitespaces).isEmpty { b += 1 } else { t += 1 }38 default: numbers.append(.nan)39 }40 }41 self.numbers = numbers; numericCount = n; textCount = t; blankCount = b42 }4344 public var count: Int { values.count }45 /// Rows that are not finite numbers (blank, text, booleans, errors).46 public var missingCount: Int { values.count - numericCount }47 /// True when the column can feed a numeric analysis (at least one number, numbers outnumber text).48 public var isNumeric: Bool { numericCount > 0 && numericCount >= textCount }49 /// The finite numbers, in row order.50 public var present: [Double] { numbers.filter { !$0.isNaN } }51 /// Category labels: the text of each cell ("" for blank); numbers use the general format.52 public var labels: [String] {53 values.map { v in54 switch v {55 case .blank: ""56 case .text(let s): s.trimmingCharacters(in: .whitespaces)57 case .number(let d): Value.generalString(d)58 default: v.description59 }60 }61 }62}6364/// A rectangular block of data read from a sheet, split into columns.65public struct DataFrame: Sendable, Hashable {66 public var sheet: Int67 public var sheetName: String68 /// The range that was read (header row included), clipped to the used area of the sheet.69 public var range: RangeRef70 public var hasHeader: Bool71 public var columns: [DataColumn]7273 public init(sheet: Int, sheetName: String, range: RangeRef, hasHeader: Bool, columns: [DataColumn]) {74 self.sheet = sheet; self.sheetName = sheetName; self.range = range; self.hasHeader = hasHeader; self.columns = columns75 }7677 public var rowCount: Int { columns.first?.count ?? 0 }78 /// Sheet row of the first data row.79 public var firstDataRow: Int { range.r0 + (hasHeader ? 1 : 0) }80 public var names: [String] { columns.map(\.name) }81 public var numericColumns: [DataColumn] { columns.filter(\.isNumeric) }82 public func column(named name: String) -> DataColumn? {83 columns.first { $0.name.compare(name, options: .caseInsensitive) == .orderedSame }84 }8586 /// "Sheet1!A1:C20"87 public var label: String { FormulaPrinter.quoteSheet(sheetName) + "!" + range.a1 }8889 /// Listwise deletion: the rows where every given column holds a number.90 /// Returns the kept row offsets and the columns restricted to them.91 public static func listwise(_ columns: [[Double]]) -> (rows: [Int], columns: [[Double]]) {92 guard let n = columns.map(\.count).min(), n > 0 else { return ([], columns.map { _ in [] }) }93 let rows = (0..<n).filter { r in columns.allSatisfy { !$0[r].isNaN } }94 return (rows, columns.map { c in rows.map { c[$0] } })95 }9697 /// Splits a numeric column by the labels of a grouping column (rows with a missing value or a blank label are98 /// dropped). Groups come in order of first appearance.99 public static func groups(values: DataColumn, by factor: DataColumn) -> [(label: String, values: [Double])] {100 var order = [String](), map = [String: [Double]]()101 let labels = factor.labels102 for (i, x) in values.numbers.enumerated() where !x.isNaN && i < labels.count && !labels[i].isEmpty {103 if map[labels[i]] == nil { order.append(labels[i]) }104 map[labels[i], default: []].append(x)105 }106 return order.map { ($0, map[$0] ?? []) }107 }108109 /// Spreads values computed on a subset of rows back over all data rows (blank elsewhere).110 public func expand(_ values: [Double], rows: [Int]) -> [Value] {111 var out = [Value](repeating: .blank, count: rowCount)112 for (i, r) in rows.enumerated() where i < values.count && r < out.count { out[r] = values[i].isFinite ? .number(values[i]) : .blank }113 return out114 }115}116117/// Reads ranges of a workbook as data frames and resolves reference text.118public enum RangeReader {119 /// Parses "B2:D40", "Sheet2!A:C", "A1" or a defined name relative to `sheet`. nil when it is not a reference.120 public static func resolve(_ text: String, workbook: Workbook, sheet: Int) -> AreaRef? {121 let t = text.trimmingCharacters(in: .whitespaces)122 guard !t.isEmpty, sheet >= 0, sheet < workbook.sheets.count, let e = try? FormulaParser.parse(t) else { return nil }123 let ctx = EvalContext(workbook: workbook, sheet: sheet, cell: CellRef(0, 0))124 guard case .ref(let a)? = try? ctx.evaluate(e), a.sheet >= 0, a.sheet < workbook.sheets.count else { return nil }125 return a126 }127128 /// Reference text for an area as seen from `currentSheet` ("B2:D40" or "Data!B2:D40").129 public static func text(for area: AreaRef, workbook: Workbook, currentSheet: Int) -> String {130 guard area.sheet != currentSheet, area.sheet < workbook.sheets.count else { return area.range.a1 }131 return FormulaPrinter.quoteSheet(workbook.sheets[area.sheet].name) + "!" + area.range.a1132 }133134 /// Clips whole-row / whole-column references to the used area of the sheet.135 public static func clip(_ range: RangeRef, sheet: Sheet) -> RangeRef? {136 guard range.isWholeRows || range.isWholeColumns || range.r1 > sheet.cells.maxRow || range.c1 > sheet.cells.maxCol else { return range }137 guard let used = sheet.cells.usedRange() else { return nil }138 return range.intersection(used)139 }140141 /// Heuristic used to preset the header toggle: the first row is all text and the row below is not.142 public static func guessHeader(workbook: Workbook, area: AreaRef) -> Bool {143 let sh = workbook.sheets[area.sheet]144 guard let rg = clip(area.range, sheet: sh), rg.rows > 1 else { return false }145 var allText = true, belowAllText = true146 for c in rg.c0...min(rg.c1, rg.c0 + 200) {147 if !sh.value(rg.r0, c).isText { allText = false }148 let v = sh.value(rg.r0 + 1, c)149 if !v.isText && !v.isBlank { belowAllText = false }150 }151 return allText && !belowAllText152 }153154 /// Reads the computed values of `area` (holding the engine lock while reading).155 public static func read(engine: CalcEngine, area: AreaRef, hasHeader: Bool, maxRows: Int = 1_000_000) -> DataFrame? {156 let wb = engine.workbook157 guard area.sheet >= 0, area.sheet < wb.sheets.count else { return nil }158 let sh = wb.sheets[area.sheet]159 engine.lock.lock(); defer { engine.lock.unlock() }160 guard var rg = clip(area.range, sheet: sh) else { return nil }161 if rg.rows > maxRows { rg.r1 = rg.r0 + maxRows - 1 }162 let first = rg.r0 + (hasHeader ? 1 : 0)163 var columns = [DataColumn]()164 for c in rg.c0...rg.c1 {165 var name = A1.columnName(c)166 if hasHeader {167 switch sh.value(rg.r0, c) {168 case .blank: break169 case .text(let s): if !s.trimmingCharacters(in: .whitespaces).isEmpty { name = s.trimmingCharacters(in: .whitespaces) }170 case .number(let d): name = Value.generalString(d)171 case let v: name = v.description172 }173 }174 let values: [Value] = first <= rg.r1 ? (first...rg.r1).map { sh.value($0, c) } : []175 columns.append(DataColumn(name: name, index: c - rg.c0, sheetColumn: c, values: values))176 }177 return DataFrame(sheet: area.sheet, sheetName: sh.name, range: rg, hasHeader: hasHeader, columns: columns)178 }179180 /// Parses a comma/semicolon separated list of cells and ranges ("B3, D2:D5, Model!C7") into single cells.181 public static func cells(_ text: String, workbook: Workbook, sheet: Int, limit: Int = 10_000) -> [CellAddress]? {182 var out = [CellAddress]()183 for part in text.split(whereSeparator: { $0 == "," || $0 == ";" }) {184 guard let a = resolve(String(part), workbook: workbook, sheet: sheet) else { return nil }185 guard let rg = clip(a.range, sheet: workbook.sheets[a.sheet]) ?? (a.range.isSingleCell ? a.range : nil) else { continue }186 for r in rg.r0...rg.r1 { for c in rg.c0...rg.c1 {187 out.append(CellAddress(sheet: a.sheet, cell: CellRef(r, c)))188 if out.count > limit { return nil }189 } }190 }191 return out.isEmpty ? nil : out192 }193}194