SDK: Sheets
A sheet is a workbook: what a person sees is Excel, and its source is text. A formula is code, results are computed and never stored, and data, logic and look each have a home. A person works in the editor as in Excel, and what they do lands in these files.
A sheet item
Section titled “A sheet item”A sheet is its workbook file, a YAML file named as you like and listed in allyhub.yaml as kind: sheet (project.md), and the files it names, each from its folder:
finance/budget/ budget.yaml the workbook: sheets, cells, formulas, formats, names, checks orders.csv a table's rows, or a large block of typed cells: what its `rows` name forecast.ts optional: code that makes sheets from data: what its `code` names logo.png what cells name: pictures and other filesIts id is its file without its extension (finance/budget/budget). Nothing is found by a fixed name: its tables are the CSV files their rows name, and its code is the module its code names. Files go wherever you put them, and the workbook names each by its path from its folder, as an import would. A table or a file the editor, the SDK or an import adds goes beside the workbook’s file under a name no file there has (orders.csv, else orders-2.csv), and the workbook names it; move it, and write its new path.
The workbook file
Section titled “The workbook file”title: Budgetcode: ./forecast.ts # optional: the module that makes sheets, from this file's foldernames: growth: 0.08 # a constant revenue: =Summary!$B$1 # a place, or a formulasheets: - name: Orders freeze: A2 tables: - name: orders at: A1 # its header's top left rows: data/orders.csv # the header row, then the rows key: id # optional: how a merge lines rows up columns: total: { formula: "=[@price]*[@qty]", format: "$#,##0.00" } - name: Summary columns: { A: { width: 180 } } cells: A1: Revenue B1: "=SUM(orders[total])" B3:M3: { formula: "=A3*(1+growth)", format: "#,##0" } format: - { range: A1:A9, bold: true } merges: [A10:C10] blocks: - { at: A100, rows: data/hand.csv }checks: - { name: totals add up, formula: "=revenue=SUM(Summary!B3:M3)" }scenarios: high: { growth: 0.2 }Its JSON Schema is https://allyhub.com/schemas/sheet.schema.json.
| Field | What |
|---|---|
title |
The workbook’s title |
names |
Defined names: a constant, or a place or formula after =; hidden_names lists those Excel’s Name Manager does not show |
sheets[] |
name, at most 31 characters, none of \ / ? * [ ] :; hidden; freeze, the first cell that scrolls; gridlines: false; tab, its tab’s colour; names, its own, read on it before the workbook’s and from elsewhere as Sheet!name |
sheets[].tables[] |
name, at (its header’s top left), rows (a CSV file from the sheet’s folder), key, style (Excel’s, TableStyleMedium2 unless it says, none for none), columns (by name: formula, format, a look, width, files) |
sheets[].columns |
By letter: width in pixels, hidden, format, a look, files (see Files in a cell) |
sheets[].charts[] |
A chart over the cells: see Charts |
sheets[].rows |
By number: height in pixels, hidden, format, a look |
sheets[].cells |
By address, or a range one value or formula covers |
sheets[].format |
A list of { range, format, …look }: later entries win over earlier ones, a cell’s own look wins over them |
sheets[].merges |
Merged ranges |
sheets[].blocks |
{ at, rows }: a CSV file of typed cells, from a cell |
sheets[].filter |
An AutoFilter on a range: { range, columns }, its columns by letter, each { values, blanks }: what it keeps, as the column shows it. A table’s is tables[].filter, { columns } by column name, or false for no buttons. What it hides is worked out, never written |
sheets[].conditional |
Conditional formats, the first the strongest: { range, type, …, stop }; ranges apart by spaces. See below |
sheets[].validations |
Data validation: { range, type, operator, formula, values, blank, dropdown, prompt, error }. See below |
checks |
{ name, formula }: a formula that must hold. One that is false, 0 or an error is a problem (AK-M009) |
scenarios |
Named sets of inputs, each what names hold instead: see Scenarios |
iteration |
Excel’s iterative calculation: true, or { max_iterations, max_change } (Excel’s 100 and 0.001 unless it says). A circular reference is then computed again until it settles |
A cell is YAML’s value: a number, true, text; text that starts with = is a formula. In a { … } a formula with a comma or a bracket is quoted. Its long form is { value | formula, format, name, start, …look }: name is a defined name for that cell; start is what a formula’s cell holds before it is computed, where iterative calculation starts, as an Excel cell keeps its last value.
A range key (B3:M3) gives one value or formula to every cell in it. A formula is written for its first cell and moved as a fill moves it: relative references shift, $ ones stay. A single cell’s key wins over a range’s, and a later range over an earlier one; B5: null keeps a cell of a range empty.
format is Excel’s number format code. A look is bold, italic, underline (true or Excel’s single, double, singleAccounting, doubleAccounting), strike, font, size, color and fill (#RRGGBB; a fill may be { pattern, color, background }), align, valign, wrap, shrink, indent, rotation, border (left, right, top, bottom, each { style, color }). Later format entries win field by field, and a border side by side, so { style: none } takes one side away.
Conditional formats. A type is Excel’s, and a rule that gives a look says it in the rule: bold, italic, underline, strike, color, fill, border.
type |
With |
|---|---|
cellIs |
operator (between, notBetween, equal, notEqual, greaterThan, lessThan, greaterThanOrEqual, lessThanOrEqual), formula: one or two bounds, a number, text, or a formula after = |
containsText |
text, operator (contains unless it says, notContains, beginsWith, endsWith) |
duplicateValues, uniqueValues |
Nothing |
top10 |
rank, percent, bottom |
aboveAverage |
below, equal |
expression |
formula, written for the first cell of the range and moved as a fill moves it |
dataBar |
color, min and max (each { at, value }, at being min, max, num, percent, percentile or formula), show: false for the bar alone |
colorScale |
stops: two or three { at, value, color } |
iconSet |
icons (Excel’s 3Arrows, 3TrafficLights1, 5Rating…), stops, reverse, show |
Data validation. type is whole, decimal, list, date, time, textLength or custom; operator is between unless it says, and formula its bounds (a date as ISO text, or a formula after =). A list is values, spelled out, or formula: ["=$A$1:$A$9"]; dropdown: false leaves out its arrow. blank: false refuses an empty cell. prompt is { title, message, show }, what shows while the cell is selected, and error is { style, title, message, show }, where style is stop (unless it says), warning or information and show: false lets any value in. A person’s typing is refused as the rule says; what a script writes is not, and a value that breaks its rule is a fact in allyhub sheet.
A list with multiple: true lets a cell hold several of its values, joined by separator (, unless it says):
validations: - { range: D2:D99, type: list, values: [vip, new, churn], multiple: true }They are text, so a formula counts one with a wildcard, =COUNTIF(D:D,"*vip*"), or splits them, =TEXTSPLIT(D2,", "). A person ticks them in the cell’s dropdown and sees them as chips; a filter’s list offers each, and keeps a row that holds any it ticks. In an .xlsx the cell keeps its text and the column a list, whose refusal is turned off as Excel’s list takes one value: the render says so.
For the file
Section titled “For the file”A worksheet holds what Excel prints and shows around its cells. Each goes in the .xlsx. A PDF prints by print as Excel does; the grid and a PDF do not draw links, notes, pictures and outlines yet.
sheets: - name: Quote print: area: A1:G22 title_rows: "1:2" # and title_cols: A:B, repeated on every page orientation: landscape paper: a4 # letter, tabloid, ledger, legal, executive, a3, a4, a5, b4, b5 fit: { width: 1, height: 0 } # one page wide, as tall as it takes; or scale: 75 (percent) breaks: { rows: [21] } # a page starts at row 21; cols: [E] too margins: { left: 0.5, right: 0.5 } # inches footer: { right: "Page &P of &N" } # Excel's codes; left, center, right links: { B2: "https://example.com", B3: "#Terms!A1" } notes: { C4: Agreed with Sam, C5: { text: Check, author: Finance } } pictures: - { file: files/logo.png, at: B1, x: 4, y: 4, height: 40 } # over the cells; pixels rows: { 12: { level: 1 } } # grouped, 1 to 7; columns too protect: true # Protect Sheet, with no passwordallyhub render says, with each Excel file, how each sheet prints (its pages across and down and its scale), the cells whose text its column and the empty cells after it cannot hold (Excel cuts it where the next cell starts), the numbers wider than their column (Excel shows ####), counted in characters of Calibri 11’s digit as Excel counts a column’s width, and the numbers of 1,000 or more with no number format.
In a script: ws.page_setup.orientation, .paperSize, .fitToWidth, .fitToHeight, ws.page_margins.left, ws.print_area, ws.print_title_rows, ws.print_title_cols, ws.oddFooter.right.text, cell.hyperlink, cell.comment = new Comment("Check", "Finance"), ws.add_image(new Image("files/logo.png"), "B1"), ws.row_dimensions[12].outlineLevel, ws.protection.sheet.
Files in a cell
Section titled “Files in a cell”A column with files holds files, each named by its path from the sheet’s folder:
sheets: - name: Team columns: C: { files: true, width: 120 } D: { files: { separator: "; ", height: 40 } } # 60 px tall, joined by ", ", unless it says cells: C2: files/ada.png D2: files/ada.pdf; files/cv.docxcolumns of a table take it too, by the column’s name. A person drops, pastes or chooses files on a cell: they are kept beside the sheet’s file, under names no file there has. A picture is drawn in its cell, its row as tall, any other file by its name. A file that is not there is a problem (AK-M009). In an .xlsx a PNG, JPEG or GIF goes in its cell and any other file is its name, which the render says.
Scenarios
Section titled “Scenarios”Each scenario says what names hold instead:
names: growth: 0.08 # a constant tax: =Inputs!$B$2 # a cellscenarios: high: { growth: 0.2, tax: 0.1 } low: { growth: 0.01 }A name that is one cell has that cell hold the value, as Excel’s Scenario Manager changes cells, so what reads it by its address sees it too. Any other name holds the value itself. A name that is not there is a problem (AK-M009). A person shows one in the editor’s Scenario Manager, and nothing is written. allyhub sheet calc <sheet> <what> --scenario high computes with one; --scenarios compares the base case and every one, a column each, over what: names, cells or ranges apart by commas, or every name no scenario sets. An .xlsx holds them as Excel’s own on the sheet of the cells they change, with a Base case to come back to; a name that is no cell has no place there, and the render says so.
Write scenarios on names of cells where the file will leave for Excel.
A CSV file is RFC 4180. A table’s starts with its header row, whose names are its columns; a block’s is cells from its first line. Blank lines in a table’s file are not rows.
A value reads as Excel reads what is typed:
| Written | Reads as |
|---|---|
12, -1.5e3, 007 |
A number. Past 15 digits it stays text, whole, where Excel would lose the rest |
12%, 1,234.50, $5 |
A number, and the format that implies |
TRUE, false |
TRUE or FALSE |
2026-10-14, 2026-10-14T09:30 |
A date (with its time), and the format yyyy-mm-dd; a zone is taken to UTC |
#N/A |
That error |
=… |
A formula |
'… |
What follows as text |
A calculated column has its formula in columns and no column in the CSV, or one whose non-empty cells are exceptions. One that is not the table’s last has an empty column there, as a column left out goes to the table’s end.
Formulas
Section titled “Formulas”The language is Excel’s: A1 and $ references, ranges, Sheet!A1, 'My Sheet'!A1, Jan:Dec!A1, whole columns and rows, defined names, structured references (orders[total], [@price], orders[@total], orders[[#Headers],[a]:[c]], orders for its rows), operators with Excel’s precedence (-2^2 is 4), array constants, LET, LAMBDA (a name may hold one, and is then called: =double(4)), error values. allyhub sheet capabilities lists the functions under formulas.
- Results are computed in order, each once.
NOW,TODAY,RAND,RANDBETWEEN,OFFSETandINDIRECTare volatile.NOWandTODAYread the clock when the sheet is built or exported;RANDin a build and an export is seeded by the sheet’s files, so the same files give the same rows. - A formula that reads itself is a problem, and its cells and what reads them show
#CYCLE!: Excel says so in a dialog. A cycle through a branchIFdoes not take is none, as in Excel. Withiterationon it is no problem: as Excel’s iterative calculation does, its cells and what reads them are computed again, each reading the latest value of the others (a cell not computed yet reads itsstart, or 0), until no value moves bymax_changeormax_iterationsrounds have run.allyhub inspect sheetsays how many rounds ran and how far a value moved in the last. - An array result spills, as in Excel: the formula’s cell shows the first value and the cells beside it the rest. They hold nothing in any file, read like any others, and
A1#is the range the spill covers now.#SPILL!says something stands in the way (a cell that holds anything, a merge, a table, the edge of the sheet), and what stands there going lets it spill. A calculated column cannot spill.=@A1:A9is the value in the formula’s own row.FILTER,SORT,SORTBY,UNIQUE,SEQUENCE,RANDARRAY(seeded asRANDis),TEXTSPLIT,TAKE,DROP,VSTACK,HSTACK,TOCOL,TOROW,CHOOSEROWS,CHOOSECOLS,EXPAND,WRAPROWSandWRAPCOLSgive arrays;MAP,REDUCE,SCAN,BYROW,BYCOLandMAKEARRAYtake aLAMBDA. - A data table is Excel’s What-If Analysis table,
=TABLE(row input, column input)over its cells as a range key (B5:F9: =TABLE($B$1,$B$2)), its references absolute: each cell is the table’s formula with the inputs set to the value atop its column and the value left of its row. A two-way table’s formula is the cell above its left column,A4here; with a column input alone (=TABLE(,$B$2)) the formulas are the row above it, one a column, and with a row input alone (=TABLE($B$1,)) the column left of it, one a row. It is worked out after what it reads, iterating where the model does, and again with every change; an.xlsxholds it as Excel’s own. - A formula cannot reach into another workbook.
In a script
Section titled “In a script”@allyhub/sdk/sheets is openpyxl’s API in TypeScript over these files. A script opens a workbook, changes it and saves it; what it did not change keeps its bytes, comments included.
import { Font, PatternFill, Table, Workbook, load_workbook,} from "@allyhub/sdk/sheets";
const wb = new Workbook();const ws = wb.active;ws.title = "Orders";ws.append(["id", "price", "qty", "total"]);ws.append(["a1", 2.5, 4, "=B2*C2"]);ws.append(["a2", 7, 2, "=B3*C3"]);ws.add_table(new Table({ displayName: "orders", ref: "A1:D3" }));ws.freeze_panes = "A2";
const summary = wb.create_sheet("Summary");const total = summary.cell(1, 2, "=SUM(orders[total])");total.number_format = "$#,##0.00";total.font = new Font({ bold: true });total.fill = new PatternFill({ fill_type: "solid", fgColor: "FFEECC" });wb.save("finance/budget.yaml");
const again = load_workbook("finance/budget.yaml");console.log(again.get_sheet_by_name("Summary")?.cell(1, 2).computed);console.log(again.check());ws.insert_rows(idx, amount), ws.delete_rows, ws.insert_cols and ws.delete_cols rewrite every formula in the workbook as Excel does: a reference moves, a range grows or shrinks, one to what went is #REF!. Tables, blocks, merges, looks and sizes move with them, and rows or columns put in a table go in its CSV. A table’s header row is not deleted unless all of the table is.
Each change lands in the file it belongs in: a cell of a table in its CSV; a cell overtyped in a calculated column as an exception in the CSV; a run of one formula, moved, as one range key; a column whose every row has one formula as a calculated column; 100 or more typed cells together (wb.block_cells) as a block in a CSV file of their own; any other cell in cells. A number computed in a script is a number in the source, so a sheet’s arithmetic belongs in its formulas.
Rules are openpyxl’s too, each a field of an object:
import { CellIsRule, ColorScaleRule, DataValidation, PatternFill, Workbook,} from "@allyhub/sdk/sheets";
const ws = new Workbook().active;ws.conditional_formatting.add( "D2:D9", CellIsRule({ operator: "greaterThan", formula: ["1000"], fill: new PatternFill({ bgColor: "FFC7CE" }), }),);ws.conditional_formatting.add( "E2:E9", ColorScaleRule({ start_color: "F8696B", end_color: "63BE7B" }),);
const stage = new DataValidation({ type: "list", formula1: '"Open,Won,Lost"' });stage.add("F2:F9");ws.add_data_validation(stage);
ws.auto_filter.ref = "A1:F9";ws.auto_filter.add_filter_column(5, ["Open"]);FormulaRule, DataBarRule, IconSetRule and new Rule({ type, … }) are held as well, and a table has its own table.autoFilter. What a filter leaves out is worked out from what it keeps; sorting is the rows of a table’s CSV file, or the cells, put in order.
Charts
Section titled “Charts”A chart floats over the cells at its anchor and reads them live: it holds references, never numbers, so it follows what its cells compute. In the workbook file:
charts: - type: column-clustered # or column-stacked, column-stacked-100, bar-…, line, line-markers, # area, area-stacked, area-stacked-100, pie, doughnut, scatter, scatter-lines, scatter-smooth at: E2 # the cell its top left is in; x and y, pixels into it width: 480 # pixels: 480 × 288 unless it says height: 288 title: Revenue # words, or a cell: =Summary!A1 categories: orders[month] # a range, Summary!A2:A13, a table's column, or a spill, A1# series: - { values: "orders[total]", name: "=C1", color: "#4472C4" } - { values: "D2:D13", name: Cost, smooth: true, line_width: 2, marker_size: 6 } legend: bottom # top, left, right; absent: none labels: { value: true, position: outEnd } # or true; percentage, category, series, format, size, color value_axis: { title: Dollars, min: 0, max: 100, major: 20, format: "0%", gridlines: false } category_axis: { title: Month, reverse: true, visible: true } gap_width: 80 overlap: 100 vary_by_categories: true hole_size: 60 size: 9 # its text, points; title_size 14 and color #595959 unless it saysIn a flow map ({ … }), quote a reference with [. A scatter’s x names its places across, each series its own, the chart’s categories unless it says.
A combination chart is Excel’s: a column, line or area chart whose series each may be drawn as another of those kinds, type (column-clustered, column-stacked, column-stacked-100, line, line-markers, area, area-stacked, area-stacked-100), and on a second value axis at the right, secondary: true, which secondary_axis sets as value_axis sets the first ({ title, min, max, major, format, gridlines, visible }). Areas are drawn first, then columns, then lines, the second axis’s over the first’s:
- type: column-clustered at: F3 categories: orders[month] series: - { values: "orders[total]" } - { values: "orders[margin]", type: line-markers, secondary: true } secondary_axis: { min: 0, max: 1, format: "0%" }Rows and columns a person or a filter hides are left out, as Excel plots what shows; an empty cell or #N/A is a gap, text is 0. An inserted row, a renamed table or column and a cut reach a chart’s references as they reach a formula, and allyhub check reports one that names nothing.
In a script they are openpyxl’s classes. A Reference is a range; a table’s column or a spill, which it cannot name, is a formula’s text:
import { BarChart, PieChart, Reference, ScatterChart, Series, Workbook,} from "@allyhub/sdk/sheets";
const wb = Workbook.open("finance/budget");const ws = wb.get_sheet_by_name("Orders");if (!ws) throw new Error("no Orders");
const bars = new BarChart();bars.type = "col";bars.grouping = "stacked";bars.title = "By month";bars.y_axis.title = "Dollars";bars.add_data( new Reference(ws, { min_col: 2, max_col: 3, min_row: 1, max_row: 13 }), { titles_from_data: true },);bars.set_categories( new Reference(ws, { min_col: 1, min_row: 2, max_row: 13 }),);bars.width = 18; // centimetres, as openpyxl'sws.add_chart(bars, "E2");
const pie = new PieChart();pie.add_data("orders[total]", { title: "Share" });pie.set_categories("orders[month]");ws.add_chart(pie, "E20");
const points = new ScatterChart();points.append( new Series("orders[total]", { xvalues: "orders[qty]", title: "By size" }),);ws.add_chart(points, "E40");wb.save();LineChart, AreaChart and DoughnutChart are held as well, and chart.legend (null for none, else its position), chart.dataLabels, chart.x_axis and y_axis (title, scaling, majorUnit, number_format, delete, majorGridlines), series.marker, series.smooth and series.graphicalProperties. ws._charts lists a sheet’s charts.
Its code
Section titled “Its code”The module the workbook file’s code names (code: ./forecast.ts, from the file’s folder) makes sheets from data and code: a sheet per region, a sheet per month, a schedule from a parameter. It uses the same classes as a script, on a workbook of its own that starts with no sheets:
import { Font, workbook } from "@allyhub/sdk/sheets";
interface Region { region: string; base: number;}
export default workbook((wb, { data }) => { for (const { region, base } of data("data/regions.csv") as Region[]) { const ws = wb.create_sheet(region); ws.cell(1, 1, "Base"); ws.cell(1, 2, base); ws.cell(2, 1, "Next year").font = new Font({ bold: true }); ws.cell(2, 2, "=B1*(1+growth)"); }});- It holds logic, never a number it computed: what it writes are inputs, formulas and references, so a change of an input computes again here and in Excel. A number it typed that neither its data nor its code holds is a fact,
sheet-typed. data(path)reads a file of the project, from the code’s folder: CSV as an object per row, numbers as numbers where a whole column is; YAML and JSON as they are. It reads files, never what a formula computes.- It runs with no clock, randomness or network.
Date.now(),new Date(),Math.random(),crypto,performance.now()and Node’s own modules are AK-M012, with their line, andfetchAK-B003; it runs twice and what it makes must be the same (AK-M012). One that hangs is stopped (AK-B004), one that throws is AK-R001. A date or a random number belongs in the data, or in a formula (TODAY,RAND). - What it makes is never written. Every command reads it with the workbook file:
check,render,sheet calc,sheet trace,sheet diff,inspect,frames, and the editor, which shows a change to it as another hand’s. Its formulas read the workbook file’s names and tables, and the workbook file’s read its sheets.wb.defined_namesadds names; one the workbook file has stands. A problem on a generated sheet names the code’s file. - Its sheets come after the workbook file’s, and the workbook file holds at least one.
- A person sees them locked. A change is refused as on a protected sheet, offering to ask you or Make Ordinary Sheet, which writes that sheet to the workbook file and its rows to CSV files. A sheet the workbook file holds by a name is the workbook file’s: what the code makes of that name is left out, which
allyhub inspectsays (sheet-ordinary). Take it out of the workbook file to give it back to the code. - An
.xlsxholds them as plain sheets. - Since a generated formula is not rewritten when a person inserts a row or renames a sheet, it reads names and tables (
=growth,=orders[total]), not addresses, wherever it can.
ws["A1"] is a cell in TypeScript’s types, so a value is written ws["A1"].value = 5 or ws.cell(1, 1, 5). TypeScript has no way to type openpyxl’s ws["A1"] = 5, though it runs.
A name of openpyxl’s that is not held throws where it is used, naming what the class holds. allyhub sheet capabilities lists what is held, how it differs and what is added: cell.computed, what a cell computes to as the workbook stands, and wb.check(), what allyhub check would say.
Commands
Section titled “Commands”allyhub checkreports AK-M009, each with its file and line: YAML that is not, a field the format does not have, a cell or range that is not one, two sheets, tables or names of one name, a table’s rows that are not there, a CSV quote never closed, a header with an empty or repeated name, a row with more fields than its header, a repeated or empty key, tables and cells that overlap, a formula that does not parse, an unknown function, name, sheet, table or column, a function given the wrong number of arguments, a circular reference, achecksentry that does not hold, a scenario’s name that is not there, a file a cell names that is not there; and AK-M012 for its code. An error value in a cell is not a problem:=1/0says what it says.- A command names a sheet by its id or its file.
allyhub sheet calc <sheet> [what]prints what a workbook computes, as an aligned table: a sheet (Summary, its used range), a range (Summary!B3:M3), a table, a name, or every sheet.--scenario highcomputes it with a scenario’s inputs;--scenarioscompares them.allyhub sheet trace <sheet> <cell>prints a cell’s formula, where it is written, what it reads and what reads it.allyhub sheet <sheet>measures each table’s columns: counts, empty cells, distinct values, errors, smallest and largest, sum and mean, lengths, the most frequent values.allyhub inspect <sheet>, andallyhub inspectfor a whole project, says what a workbook’s formulas and cells are like, as facts, never problems:sheet-error, each cell whose value is an error and whether it starts there;sheet-number, numbers written into a formula and what takes each, once for a formula a range holds;sheet-unlike, a cell between neighbours that read alike, unlike them;sheet-exception, a cell set apart from its calculated column’s formula;sheet-unused, names and typed numbers nothing reads (no formula, chart, rule, check or name);sheet-invalid, a value under a validation that breaks it;sheet-typed, a number its code typed that neither its data nor its code holds;sheet-ordinary, a sheet its code makes and the workbook file holds too;sheet-iteration, withiterationon, the rounds iterative calculation ran over how many cells and how far a value moved in the last.allyhub inspect sheet <sheet>has their options:--plain 0,1,-1the numbers not said,--around 1how many neighbours each side must agree,--kind,--allfor every finding, every number and a table’s columns nothing reads.allyhub sheet diff <was> <now>compares two versions, two folders or a folder and an.xlsx: what changed value under a formula that did not, what changed formula with its value the same, both, what came and went, names, a table’s rows lined up by itskey, else by their place.--tolerance,--all,--json.allyhub sheet capabilitiessays what the SDK holds of openpyxl’s.allyhub sheet import <file> --to finance/forecast.yamlmakes a sheet of an Excel, CSV or TSV file, its tables’ rows infinance/data/, never over a place that is taken (see Excel).allyhub renderwrites each sheet to.allyhub/out/<id>/:--type xlsx(the default),pdf,png,csv(a file a sheet),md,json. A sheet with a problem is not written.allyhub frames <sheet>draws a picture of a sheet as a person sees it, letters and numbers on its edges;--rangenames a sheet (Summary), a range (Summary!B3:D9), columns (A:C) or rows (1:20). Without a range it draws the first 200 rows and 60 columns of each sheet, and says what it left out.
allyhub help sheet and allyhub help render give every option.
Out. --type xlsx writes every sheet with typed values, formulas live with what they compute cached (so Preview and Numbers show numbers; Excel computes everything again on load), styles, merges, frozen panes, sizes, hidden sheets, names, tables with their calculated columns, filters, conditional formats, data validation and charts. The same workbook is the same bytes. checks have no place in a file, and the render says so. Generated sheets are plain sheets, scenarios are Excel’s own, and several choices and files in a cell go as above. A formula that needs arrays is written as a dynamic array formula, spilling into the cells it covers.
In. allyhub sheet import <file.xlsx> reads a file whatever wrote it and writes what it means as short text: neighbours with one formula as one range key, a table as a table, a column of one formula as a calculated column, a large rectangle of typed cells as a CSV file, looks as rectangles. It prints:
- Excel’s scenarios come in by the names of the cells they set; a cell with no name is said, and a
Base casethat holds what the cells hold is left out. Not carried over:VBA, pivot tables, a chart of a kind a sheet has none of (radar, stock, surface, bubble, 3-D, a pie or a scatter among other kinds) or whose numbers are written in it, shapes, links to other workbooks, a protection’s password, page breaks, rich text (kept as text), filters by condition, kinds of rule the format has no place for, and so on, with where. Notes, links, pictures (beside the workbook file), outlines, Page Layout, protection, a sheet’s own names and hidden names come in.- A formula that cannot be computed here (a function the engine does not have, another workbook) keeps its value, listed with its cell and why. An array formula over several cells is its first cell’s, and the others spill from it.
Computed here against the file:every formula’s result compared with the value the file holds, each difference with its cell (--toleranceis how close numbers must be,--alllists every one; a formula that reads the clock or a random number is not compared). JSON has every difference.
An ordinary formula from a file that narrows a range to its row is written with @ (=@A1:A9*2), as Excel shows it.
A sheet in a piece
Section titled “A sheet in a piece”A piece reads a table’s computed rows with ctx.data("<sheet>#<table>"), by the sheet’s file, relative to the file that names it. Each row is an object by header: numbers, text, TRUE and FALSE as they are, a date as ISO text, an error as its code, an empty cell as null. A plain CSV file still reads as it is, with ctx.data(path) or by import: a column whose every non-empty value is a number becomes numbers, everything else stays text.
import { custom, part, video } from "@allyhub/sdk";
interface Order { id: string; total: number;}
export default video({ format: "16x9", parts: (ctx) => { const rows = ctx.data("../../finance/budget#orders") as Order[]; return [ part({ key: "orders", dur: 3, layers: [ custom({ render: (el) => { el.textContent = rows.map((r) => r.id).join(", "); }, }), ], }), ]; },});A deck’s chart reads one the same way: data: ../budget#orders in its slides file. A data file over 5 MB is not read by ctx.data.