Excel To App Guide

Purpose

• Document the current no-code path from a single-sheet Excel workbook to a working Nano app.
• Explain the current limits around formulas and UI aggregates.
• Capture the next pending steps without overstating what Nano already does.

Status

• .xlsx import is now implemented for a single worksheet.
• Excel-defined data types are preferred over raw value guessing whenever the workbook is explicit.
• Supported same-row SUM(...) formulas can be imported as live computed fields.
• Consistent row-local arithmetic expressions can now be promoted into real computed fields.
• Cross-row arithmetic expressions can now be preserved as per-row computed cells, including references such as =A2*4+B5.
• Nested supported range functions inside imported formulas now work in both computed columns and preserved per-row formulas.
• Formula-heavy summary rows can be excluded from imported data and translated into aggregate suggestions.
• Dataset-level aggregates are available in DQT as a UI feature configured per field.

---

Current Flow

An admin can now:

1. Upload a single-sheet .xlsx, a .csv, or an exported Nano entity package.
2. Let Entity Builder generate the entity schema and backing table.
3. Review field names, labels, types, required/default-view flags, and computed markers in the preview.
4. Import the entity and its rows.
5. Activate the entity as a Nano app.
6. Put the app in the sidebar, publish it into mesh.json, and use User Administration to grant non-admin users access to the app.

This is the current “Excel to app” story in Nano.

---

Excel Contract

Current .xlsx support is intentionally narrow:

• exactly one worksheet
• headers in row 1
• values imported from the workbook, including cached values for formula cells
• workbook size limits:
  • upload file: 10 MB
  • uncompressed workbook archive: 30 MB
  • preview rows: 5000
  • preview columns: 200

If a formula cell has no cached calculated value, preview/import is rejected. The workbook must be recalculated and saved in Excel first.

---

Data Types From Excel

Nano now derives field types from the Excel definition first, not just from the visible values.

That means:

• currency-formatted columns become currency
• date-formatted columns become date
• boolean cells stay boolean
• explicit numeric formats are preferred over text guessing

Fallback inference is still used only when the workbook itself is ambiguous, for example a column left in Excel General format.

---

Formula Support Today

Nano does not try to become a full Excel formula engine yet.

Current supported formula pattern:

• same-row contiguous SUM(...)
• same-row contiguous AVERAGE(...), MIN(...), MAX(...), and MEDIAN(...)
• row-local arithmetic expressions using numeric literals, +, -, *, /, parentheses, and same-row Excel cell references
• row-local IF(...) expressions with basic comparisons (=, <>, >, >=, <, <=)
• nested supported range functions inside arithmetic expressions, for example =MAX(B2:C2)*3+D2
• supported cross-row/range formulas preserved as imported per-row formulas when they are not a reusable field rule, for example =AVERAGE(B1:G2)
• manual formula authoring in Entity Builder using field names, for example IF(formula_1 > 250, formula_1, 0)

Example workbook:

Property	JAN	FEB	MAR	...	DEC	Year Total
Queensborough	1700	1700	1700	...	1700	=SUM(B2:M2)

When Nano detects that pattern during .xlsx preview:

• the result column still keeps its normal result type, for example currency
• the preview marks the column with Σ
• import stores the field as a computed field
• the field stays read-only in row forms
• the value is recomputed in the form when dependent fields change
• the server recomputes it again on save as the final guard

For consistent arithmetic columns, Nano now also promotes patterns such as:

• =B2+C2
• =B2*C2
• =B2+50+C2

When every formula in the column is the same row-local shape, Nano stores that as a reusable field-level computed formula instead of one-off cell metadata.

Current limitation:

• same-row contiguous SUM(...) and consistent row-local arithmetic expressions are translated into live computed Nano fields
• row-local computed fields may now depend on other row-local computed fields, as long as the dependency graph is acyclic
• client-side recomputation now follows the same dependency ordering for row-local computed fields, including chained formulas
• supported cross-row formulas are still import-only and are stored per imported row
• cross-row formulas can reference other imported rows, but Nano currently treats those references as stable imported-record links, not live Excel-style positional row numbers
• package export/import does not yet preserve per-row computed-cell formulas
• other formulas are imported by cached value only

That distinction matters:

• =SUM(B2:M2) can stay live after import
• other formulas currently come in as values unless Nano learns that formula pattern later

---

Example: Monthly Budget Sheet

A good first example is a worksheet like:

Property	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	Year Total
Queensborough	1700	1700	1700	1700	1700	1700	1700	1700	1700	1700	1700	1700	=SUM(B2:M2)
PNC	1200	1200	1200	1200	1200	1200	1200	1200	1200	1200	1200	1200	=SUM(B3:M3)

Expected Nano result:

• Property imports as string
• JAN through DEC import as currency
• Year Total imports as currency
• Year Total is marked computed when the formula matches the supported SUM(...) shape

After import:

• editing JAN through DEC updates Year Total before save
• Year Total cannot be edited directly

---

Package Round-Trip

When you export an entity package:

• the CSV contains the current values
• the package JSON preserves computed-field metadata

When you import that package back into Nano:

• computed fields are restored as live computed fields
• they do not come back as frozen value-only columns

This matters for moving a generated app from one Nano to another.

---

Column Aggregates In DQT

Column aggregates are separate from computed fields.

Computed fields:

• work inside one row
• example: Year Total = SUM(JAN...DEC)

Aggregates:

• work across the currently visible rows in DQT
• are UI-only
• are not stored as entity rows

Supported aggregate modes:

• SUM
• MIN
• MAX
• AVG
• MEDIAN

Configuration:

• open the field in Entity Builder
• set Aggregate to one of the supported methods
• this is valid only for numeric types:
  • integer
  • number
  • real
  • currency

Display behavior:

• DQT adds an aggregate band directly under the header
• first row shows the method abbreviation:
  • SUM
  • MIN
  • MAX
  • AVG
  • MED
• second row shows the computed value
• each aggregate uses the field renderer formatting

Important:

• aggregate values are based on the current visible dataset in the DQT
• search/filter changes update the aggregate row
• this is not a persisted “totals row” in the entity table
• Excel summary rows that mostly total prior rows are better mapped to aggregates than stored as imported entity rows

---

Example: Aggregates On A Property Entity

Example configuration after import:

• JAN -> SUM
• Year Total -> SUM
• Area -> AVG

Result in DQT:

• a SUM marker appears above JAN
• a SUM marker appears above Year Total
• an AVG marker appears above Area
• the value row beneath shows the actual current totals/average for the visible rows

This is useful for budget sheets, rent rolls, small ledgers, and similar operational tables.

---

Security Story

After activation, the generated entity app can be:

• enabled or disabled locally per Nano
• published or hidden from mesh.json
• granted to selected non-admin users through User Administration (Allowed Apps)

Nano now enforces entity-backed app access by entity_id on the backing entity/data routes as well, not only on the app shell page.

That means the current story is:

• spreadsheet to generated app
• with basic per-user app access
• without handwritten CRUD code

---

Pending Items

Current pending items worth keeping visible:

• broader function support beyond arithmetic expressions and same-row contiguous range formulas
• package export/import support for per-row computed-cell formulas
• clearer formula introspection/debugging UI in Entity Builder
• entity-to-entity relationships so one generated app can point to another
• richer app manifests for native and entity-backed apps
• possible future server-side full-result aggregates beyond the current visible-row DQT model

Those are next layers on top of the current shipped Excel-to-app path, not blockers for the current version.

---

Formula Roadmap

The formula work should ship in stages so the runtime stays coherent with Nano's current entity/data model.

Stage 1: Imported formulas and expressions

Status:

• completed

Scope:

• single-sheet .xlsx import only
• promote consistent row-local arithmetic formulas like =B2+C2 into field-level computed columns
• preserve cross-row arithmetic formulas like =A2*4+B5 as per-row imported formulas
• support numeric literals, +, -, *, /, and parentheses
• support nested SUM / AVERAGE / MIN / MAX / MEDIAN nodes inside expression formulas
• normalize Excel refs into stored Nano row/field references during import
• recompute on the server after row edits

Important limits in this stage:

• row-local promoted formulas work for new rows because they become field rules
• cross-row formulas are preserved for imported rows only
• manual new rows do not automatically get a generated cross-row formula
• package export/import is still value-only for these per-row formulas
• field-level row-local formulas support client-side refresh in generic forms
• cross-row formula recomputation remains server-side; server is the source of truth

Stage 2: Conditional logic

Status:

• completed

Target:

• IF(...)
• basic comparisons (=, <>, >, >=, <, <=)

This now unlocks many status and classification columns without requiring a full Excel engine.

Stage 3: Manual formula authoring

Status:

• completed

Scope:

• author/edit formulas in Entity Builder
• validate dependencies before save
• reuse the same runtime AST and recomputation rules already proven by import

Current authoring model:

• formulas are written against field names, not Excel cell references
• supported functions and operators match the current row-local runtime support
• chained row-local computed fields are allowed if the dependency graph has no cycle

Stage 4: Reporting and cross-row functions

Target:

• SUMIF
• COUNTIF
• package export/import support for per-row computed-cell formulas
• possible later reporting/query aggregates

These should be treated carefully because they start to overlap with reporting features rather than simple row storage.