SPB Git

Repository Public

DataTableTools.swift 8.7 KB 119 lines swift

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
Files main
Menu

Theme