# Workbook report

A console program that uses the Atlas VCL data models without a window and
verifies every result:

- `TAtlasTable` holds a small sales table (text, number and date columns, two of
  them read-only). The program sorts it numerically in descending order, filters it,
  edits a cell through the filtered view and checks that the edit reached the right
  source row, shows that a non-numeric value and an edit of a read-only column are
  rejected with `EAtlasDataError`, writes the table to `sales.csv`, reads it back
  into a second table with `ImportCSV`, and shows that a failed import keeps the
  previous rows.
- `TAtlasPivot.Summarize` groups the sales by region and quarter with `aaSum`
  (and once with `aaAverage`). The program checks the numbered column headers,
  a row total and the grand total, and frees each returned table.
- `TAtlasWorkbook` receives the summary cells, computes row totals with `=B2+C2`
  and `=SUM(...)`, a grand total, a `MAX` over a range and a share with division,
  applies currency and percent formats, shows the `#DIV/0!`, `#CYCLE!` and
  `#VALUE!` error codes, and round-trips cells and formats through `ToJSON` and
  `FormatsJSON`. A listener counts the change notifications.
- `TAtlasSpellChecker` loads a small word list, finds two misspelled words with
  their one-based offsets, suggests `report` for `reprot` and ignores a word on request.

A mismatch raises an exception, the program prints `FAIL: ...` and exits with code 1.

## Build and run

Open `WorkbookReport.dpr` in Delphi 13; the IDE creates the project file. Add the Atlas VCL `src` folder to the project's search path, then build and run for Win32 or Win64. No packages need to be installed. The program needs no input. It checks its own results and stops with an error if any of them differs from the output below.

## Expected output

```
TAtlasTable
  ok: four columns, five rows
  ok: numbers are stored in invariant form = 29800.5
  ok: descending numeric sort puts the largest sale first = West
  ok: view row 0 maps to source row 3 after sorting
  ok: case-insensitive filter on the Region column
  ok: an edit made through the filtered view reached the right source row = 18500
  ok: a non-numeric value is rejected for a number column
  ok: Region is a read-only column
  ok: CSV round trip through ...\sales.csv = Region,Quarter,Sales,Closed
  ...
  ok: a failed import leaves the previous rows in place
TAtlasPivot
  ok: three regions plus a total row; Q1, Q2 and a total column
  ok: row dimension header = Rows: Region
  ok: column keys are numbered to stay unique = 1: Q1
  ok: East row total = 40500
  ok: North has no Q2 sales, so its sum is 0 = 0
  ok: grand total = 111200.5
  ok: East average uses the original observations = 20250
TAtlasWorkbook
  ok: every SetCell notified the listener once
  ok: East total from a cell formula = 40500
  ok: grand total from SUM over formula cells = 111200.5
  ok: MAX over a two-column range = 29800.5
  ok: Value returns the Double behind Display
  ok: currency format with two decimals = $111200.50
  ok: percent format = 36.4%
  ok: Raw returns the formula text = =D2/D5
  ok: division by zero shows an error code = #DIV/0!
  ok: a dependency cycle shows an error code = #CYCLE!
  ok: arithmetic on text shows an error code = #VALUE!
  ok: cell JSON round trip = {"A1":"Region","A2":"East",...}
  ok: format JSON round trip = {"D5":{"numberFormat":3,"decimals":2,"bold":true,...}}
  ok: the copy computes and formats the same total = $111200.50
TAtlasSpellChecker
  ok: two unknown words found
  ok: first issue word = reprot
  ok: one-based start index and length
  ok: best suggestion by edit distance = report
  ok: ignored words are not reported
  ok: lookups ignore case
PASS: WorkbookReport
```

`sales.csv` is left next to the executable (UTF-8 with a byte order mark, CRLF
line ends). Note three details the checks make visible: `29800.50` is stored and
exported as `29800.5`; the pivot writes `0` for a sum over an empty group (other
aggregates leave the cell empty); and the CSV import skips the byte order mark
that `TFile.WriteAllText` writes.

## What to change first

- Replace `BuildSales` with your own columns and rows, or with `ImportCSV` on a
  file you already have. Then change the dimension and value column indices in
  the `Summarize` calls.
- Try the other aggregates, `aaCount`, `aaMinimum` and `aaMaximum`, and see which
  cells stay empty for groups without values.
- Add formulas of your own. The workbook understands `+ - * / ^`, parentheses,
  cell references, ranges such as `B2:C4` and five functions: `SUM`, `AVERAGE`,
  `MIN`, `MAX` and `COUNT`. Anything else shows `#NAME?` or `#VALUE!`.
- Store `ToJSON` and `FormatsJSON` in your own file; they are two separate strings
  and both are needed to restore a formatted sheet.