Repository Public
1import Foundation2import SheetCore3import Statistics4import WhatIf5import AnalysisKit67/// One- and two-variable data tables.8enum DataTableTools {9 static let info: [String] = [10 L("The model is recalculated once per input value (or pair of values) on a private copy of the workbook; the input cells of the live workbook keep their values."),11 L("Results are written as values, not as live TABLE() formulas: rerun the tool after changing the model."),12 L("Validated against the model algebra (exact for linear models, 1e-12 relative for an annuity grid)."),13 ]1415 static let oneVariable: ToolDescriptor = {16 var t = ToolDescriptor(17 id: "datatable1", title: L("Data Table (One Variable)"), group: L("Tables"), systemImage: "tablecells",18 summary: L("Recalculates one or more formula cells for a list of values of one input cell."),19 fields: [20 .cell("input", L("Input cell"), help: L("The cell whose value is replaced by each value of the list.")),21 .text("values", L("Input values"), placeholder: L("e.g. 8000; 10000; 12000 or D2:D9"), help: L("A typed list, or a range holding the values.")),22 .cells("outputs", L("Output formula cells")),23 ],24 info: info, needsModel: true, run: runOne)25 t.demo = { _, v in26 v.set("input", text: "Model!B5"); v.set("values", text: "6000; 8000; 10000; 12000; 14000; 16000; 18000"); v.set("outputs", text: "Model!B12, Model!B15, Model!B17")27 }28 return t29 }()3031 static let twoVariable: ToolDescriptor = {32 var t = ToolDescriptor(33 id: "datatable2", title: L("Data Table (Two Variables)"), group: L("Tables"), systemImage: "tablecells.badge.ellipsis",34 summary: L("Recalculates one formula cell for every combination of the values of two input cells."),35 fields: [36 .cell("rowInput", L("Row input cell"), help: L("Its values run across the top of the table.")),37 .text("rowValues", L("Row input values"), placeholder: L("e.g. 44; 48; 52 or D2:D9"), help: L("A typed list, or a range holding the values.")),38 .cell("columnInput", L("Column input cell"), help: L("Its values run down the first column.")),39 .text("columnValues", L("Column input values"), placeholder: L("e.g. 8000; 10000; 12000 or D2:D9"), help: L("A typed list, or a range holding the values.")),40 .cell("output", L("Output formula cell")),41 ],42 info: info, needsModel: true, run: runTwo)43 t.demo = { _, v in44 v.set("rowInput", text: "Model!B4"); v.set("rowValues", text: "40; 44; 48; 52; 56")45 v.set("columnInput", text: "Model!B5"); v.set("columnValues", text: "8000; 10000; 12000; 14000; 16000")46 v.set("output", text: "Model!B17")47 }48 return t49 }()5051 static func runOne(_ input: ToolInput) throws -> ToolResult {52 let model = try input.requireModel()53 let wb = model.workbook54 let cell = try input.cell("input"), outputs = try input.cells("outputs")55 guard outputs.count <= 50 else { throw ToolError(L("Choose at most %d output cells.", 50)) }56 try WhatIfSupport.requireInput(cell, in: wb)57 let values = try WhatIfSupport.values(input.values.text("values"), input: input, model: model, field: L("Input values"))58 guard values.count <= 5000 else { throw ToolError(L("At most %d input values are supported.", 5000)) }59 let r = try DataTable.oneVariable(engine: model, input: cell.modelCell, values: values, outputs: outputs.map(\.modelCell))6061 let inputName = WhatIfSupport.name(cell, in: wb)62 let names = outputs.map { WhatIfSupport.name($0, in: wb) }63 var table = ReportTable(title: L("Data table"), rows: [[.text(inputName)] + names.map { .text($0) }] + zip(r.inputs, r.values).map { [$0] + $1 }, headerColumn: false)64 table.columnFormats[0] = WhatIfSupport.format(cell, in: wb)65 for (j, o) in outputs.enumerated() { table.columnFormats[j + 1] = WhatIfSupport.format(o, in: wb) }66 var blocks: [ResultBlock] = [.table(table)]6768 let series = outputs.indices.compactMap { j -> ChartKit.Line? in69 let y = r.values.map { $0[j].numberValue ?? .nan }70 return y.contains(where: \.isFinite) ? ChartKit.Line(names[j], y, x: values, marker: .circle) : nil71 }72 if values.count > 1, !series.isEmpty {73 blocks.append(.chart(ChartBlock(title: L("Outputs against %@", inputName),74 spec: ChartKit.lines(series, xTitle: inputName, yTitle: series.count == 1 ? series[0].name : L("Output")),75 summary: L("Line chart of %d output(s) for %d values of %@ from %@ to %@.", series.count, values.count, inputName,76 Fmt.number(values.min() ?? 0, locale: input.locale), Fmt.number(values.max() ?? 0, locale: input.locale)),77 height: 230)))78 }79 var notes = [L("Input cell: %@ (current value %@, left unchanged).", WhatIfSupport.label(cell, in: wb),80 Fmt.text(wb.sheets[cell.sheet].value(cell.cell.row, cell.cell.col), table.columnFormats[0] ?? .auto, locale: input.locale))]81 for o in outputs { notes.append(L("Output %@: %@", WhatIfSupport.label(o, in: wb), WhatIfSupport.formulaText(o, in: wb) ?? L("constant (no formula)"))) }82 notes.append(L("The table holds values computed when the tool ran; it does not update when the model changes."))83 blocks.append(.note(notes, title: L("Inputs of this table")))84 return ToolResult(title: L("Data Table (One Variable)"), subtitle: L("%d values of %@", values.count, WhatIfSupport.nameWithAddress(cell, in: wb)),85 blocks: blocks, sheetName: L("Data Table"))86 }8788 static func runTwo(_ input: ToolInput) throws -> ToolResult {89 let model = try input.requireModel()90 let wb = model.workbook91 let rowCell = try input.cell("rowInput"), colCell = try input.cell("columnInput"), output = try input.cell("output")92 guard rowCell != colCell else { throw ToolError(L("The row and column input cells must be different cells.")) }93 try WhatIfSupport.requireInput(rowCell, in: wb)94 try WhatIfSupport.requireInput(colCell, in: wb)95 let rowValues = try WhatIfSupport.values(input.values.text("rowValues"), input: input, model: model, field: L("Row input values"))96 let colValues = try WhatIfSupport.values(input.values.text("columnValues"), input: input, model: model, field: L("Column input values"))97 guard rowValues.count * colValues.count <= 40_000, rowValues.count <= 200 else { throw ToolError(L("The table is too large (at most 200 columns and 40,000 cells).")) }98 let r = try DataTable.twoVariable(engine: model, rowInput: rowCell.modelCell, rowValues: rowValues, columnInput: colCell.modelCell,99 columnValues: colValues, output: output.modelCell)100 let rowName = WhatIfSupport.name(rowCell, in: wb), colName = WhatIfSupport.name(colCell, in: wb), outName = WhatIfSupport.name(output, in: wb)101 let outFormat = WhatIfSupport.format(output, in: wb)102 var table = ReportTable(title: L("%@ by %@ (down) and %@ (across)", outName, colName, rowName),103 rows: [[.text(colName + " \\ " + rowName)] + r.rowInputs] + zip(r.columnInputs, r.values).map { [$0] + $1 }, headerColumn: true)104 table.rowFormats[0] = WhatIfSupport.format(rowCell, in: wb)105 table.columnFormats[0] = WhatIfSupport.format(colCell, in: wb)106 for j in rowValues.indices { table.columnFormats[j + 1] = outFormat }107 for i in 0...colValues.count { table.cellFormats[CellRef(i, 0)] = WhatIfSupport.format(colCell, in: wb) }108 for j in rowValues.indices { table.cellFormats[CellRef(0, j + 1)] = WhatIfSupport.format(rowCell, in: wb) }109 let notes = [110 L("Row input cell: %@ (values across the top).", WhatIfSupport.nameWithAddress(rowCell, in: wb)),111 L("Column input cell: %@ (values down the first column).", WhatIfSupport.nameWithAddress(colCell, in: wb)),112 L("Output %@: %@", WhatIfSupport.label(output, in: wb), WhatIfSupport.formulaText(output, in: wb) ?? L("constant (no formula)")),113 L("The table holds values computed when the tool ran; it does not update when the model changes."),114 ]115 return ToolResult(title: L("Data Table (Two Variables)"), subtitle: L("%d × %d values of %@", colValues.count, rowValues.count, outName),116 blocks: [.table(table), .note(notes, title: L("Inputs of this table"))], sheetName: L("Data Table"))117 }118}119