YOUSEFUL / DELPHI / SAMPLE PROJECTS

Workbook report

Console program Self-checking Product: Atlas VCL · Components: TAtlasTable, TAtlasPivot, TAtlasWorkbook, TAtlasSpellChecker

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.

Project files

Download all 34 projects

The ZIP contains every sample project with its README. See getting started for the search path to add.

Source

WorkbookReport.dpr

program WorkbookReport;

// Atlas VCL data models without a window: a TAtlasTable is sorted, filtered
// and exported to CSV, TAtlasPivot summarizes it, TAtlasWorkbook computes the
// totals with formulas and cell formats, and TAtlasSpellChecker checks the
// report caption. Every result is verified; the exit code is 0 on success.

{$APPTYPE CONSOLE}

uses
  System.SysUtils, System.Classes, System.IOUtils,
  Atlas.Data, Atlas.Pivot, Atlas.Spreadsheet, Atlas.Spell;

type
  TChangeCounter = class
    Count: Integer;
    procedure Changed(Sender: TObject);
  end;

procedure TChangeCounter.Changed(Sender: TObject);
begin
  Inc(Count);
end;

procedure Expect(Condition: Boolean; const What: string);
begin
  if not Condition then
    raise Exception.Create('Check failed: ' + What);
  Writeln('  ok: ', What);
end;

procedure ExpectText(const Expected, Actual, What: string);
begin
  if Expected <> Actual then
    raise Exception.CreateFmt('%s: expected [%s], got [%s]', [What, Expected, Actual]);
  Writeln('  ok: ', What, ' = ', Actual);
end;

function BuildSales: TAtlasTable;
begin
  Result := TAtlasTable.Create(nil);
  Result.AddColumn('Region', ackText, 120, False);
  Result.AddColumn('Quarter', ackText, 80, False);
  Result.AddColumn('Sales', ackNumber, 100);
  Result.AddColumn('Closed', ackDate, 110);
  Result.AddRow(['East', 'Q1', '18000', '2026-03-31']);
  Result.AddRow(['East', 'Q2', '22000', '2026-06-30']);
  Result.AddRow(['West', 'Q1', '24500', '2026-03-31']);
  Result.AddRow(['West', 'Q2', '29800.50', '2026-06-30']);
  Result.AddRow(['North', 'Q1', '16400', '']);
end;

procedure TableSection(Sales: TAtlasTable);
var
  Copy: TAtlasTable;
  Before, FileName: string;
  Failed: Boolean;
begin
  Writeln('TAtlasTable');
  Expect((Sales.ColumnCount = 4) and (Sales.RowCount = 5), 'four columns, five rows');
  ExpectText('29800.5', Sales.Cell(2, 3), 'numbers are stored in invariant form');
  Sales.Sort(2, False);
  ExpectText('West', Sales.Cell(0, 0), 'descending numeric sort puts the largest sale first');
  Expect(Sales.SourceIndex(0) = 3, 'view row 0 maps to source row 3 after sorting');
  Sales.SetFilter('east', 0);
  Expect(Sales.RowCount = 2, 'case-insensitive filter on the Region column');
  Sales.SetCell(2, 1, '18500');
  Sales.SetFilter('');
  Sales.Sort(-1);
  ExpectText('18500', Sales.Cell(2, 0), 'an edit made through the filtered view reached the right source row');
  Failed := False;
  try
    Sales.SetCell(2, 0, 'lots');
  except
    on E: EAtlasDataError do
      Failed := True;
  end;
  Expect(Failed, 'a non-numeric value is rejected for a number column');
  Failed := False;
  try
    Sales.SetCell(0, 0, 'South');
  except
    on E: EAtlasDataError do
      Failed := True;
  end;
  Expect(Failed, 'Region is a read-only column');

  FileName := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), 'sales.csv');
  TFile.WriteAllText(FileName, Sales.ExportCSV(False), TEncoding.UTF8);
  Copy := TAtlasTable.Create(nil);
  try
    Copy.AddColumn('Region', ackText, 120, False);
    Copy.AddColumn('Quarter', ackText, 80, False);
    Copy.AddColumn('Sales', ackNumber, 100);
    Copy.AddColumn('Closed', ackDate, 110);
    Copy.ImportCSV(TFile.ReadAllText(FileName, TEncoding.UTF8));
    ExpectText(Sales.ExportCSV(False), Copy.ExportCSV(False), 'CSV round trip through ' + FileName);
    Before := Copy.ExportCSV(False);
    Failed := False;
    try
      Copy.ImportCSV('Region,Quarter,Sales,Closed' + sLineBreak + 'East,Q3,ten,' + sLineBreak);
    except
      on E: EAtlasDataError do
        Failed := True;
    end;
    Expect(Failed and (Copy.ExportCSV(False) = Before), 'a failed import leaves the previous rows in place');
  finally
    Copy.Free;
  end;
end;

procedure PivotSection(Sales: TAtlasTable; Workbook: TAtlasWorkbook);
var
  Pivot: TAtlasPivot;
  Summary: TAtlasTable;
  Row, Col: Integer;
begin
  Writeln('TAtlasPivot');
  Pivot := TAtlasPivot.Create(nil);
  try
    Summary := Pivot.Summarize(Sales, 0, 1, 2, aaSum); // rows: Region, columns: Quarter, values: Sales
    try
      Expect((Summary.ColumnCount = 4) and (Summary.RowCount = 4), 'three regions plus a total row; Q1, Q2 and a total column');
      ExpectText('Rows: Region', Summary.Columns[0].Name, 'row dimension header');
      ExpectText('1: Q1', Summary.Columns[1].Name, 'column keys are numbered to stay unique');
      ExpectText('40500', Summary.Cell(3, 0), 'East row total');
      ExpectText('0', Summary.Cell(2, 1), 'North has no Q2 sales, so its sum is 0');
      ExpectText('111200.5', Summary.Cell(3, 3), 'grand total');
      // Copy the summary into the workbook, then let formulas recompute the totals.
      for Row := 0 to Summary.RowCount - 2 do
        for Col := 0 to Summary.ColumnCount - 2 do
          Workbook.SetCell(AtlasCellAddress(Col + 1, Row + 2), Summary.Cell(Col, Row));
    finally
      Summary.Free; // Summarize returns a table owned by the caller
    end;
    Summary := Pivot.Summarize(Sales, 0, 1, 2, aaAverage);
    try
      ExpectText('20250', Summary.Cell(3, 0), 'East average uses the original observations');
    finally
      Summary.Free;
    end;
  finally
    Pivot.Free;
  end;
end;

procedure WorkbookSection(Workbook: TAtlasWorkbook);
var
  Counter: TChangeCounter;
  Format: TAtlasCellFormat;
  Copy: TAtlasWorkbook;
begin
  Writeln('TAtlasWorkbook');
  Counter := TChangeCounter.Create;
  Copy := TAtlasWorkbook.Create(nil);
  try
    Workbook.AddListener(Counter.Changed);
    Workbook.SetCell('A1', 'Region');
    Workbook.SetCell('B1', 'Q1');
    Workbook.SetCell('C1', 'Q2');
    Workbook.SetCell('D1', 'Total');
    Workbook.SetCell('D2', '=B2+C2');
    Workbook.SetCell('D3', '=SUM(B3:C3)');
    Workbook.SetCell('D4', '=SUM(B4:C4)');
    Workbook.SetCell('A5', 'All regions');
    Workbook.SetCell('D5', '=SUM(D2:D4)');
    Workbook.SetCell('A6', 'Best quarter');
    Workbook.SetCell('D6', '=MAX(B2:C4)');
    Workbook.SetCell('A7', 'East share');
    Workbook.SetCell('D7', '=D2/D5');
    Expect(Counter.Count = 13, 'every SetCell notified the listener once');
    ExpectText('40500', Workbook.Display('D2'), 'East total from a cell formula');
    ExpectText('111200.5', Workbook.Display('D5'), 'grand total from SUM over formula cells');
    ExpectText('29800.5', Workbook.Display('D6'), 'MAX over a two-column range');
    Expect(Abs(Workbook.Value('D7') - 40500 / 111200.5) < 1E-12, 'Value returns the Double behind Display');
    Format := TAtlasCellFormat.Default;
    Format.NumberFormat := anfCurrency;
    Format.Bold := True;
    Workbook.SetCellFormat('D5', Format);
    ExpectText('$111200.50', Workbook.Display('D5'), 'currency format with two decimals');
    Format := TAtlasCellFormat.Default;
    Format.NumberFormat := anfPercent;
    Format.Decimals := 1;
    Workbook.SetCellFormat('D7', Format);
    ExpectText('36.4%', Workbook.Display('D7'), 'percent format');
    ExpectText('=D2/D5', Workbook.Raw('D7'), 'Raw returns the formula text');
    Workbook.SetCell('E1', '=B2/0');
    ExpectText('#DIV/0!', Workbook.Display('E1'), 'division by zero shows an error code');
    Workbook.SetCell('E2', '=E3');
    Workbook.SetCell('E3', '=E2');
    ExpectText('#CYCLE!', Workbook.Display('E2'), 'a dependency cycle shows an error code');
    Workbook.SetCell('E4', '=A1+1');
    ExpectText('#VALUE!', Workbook.Display('E4'), 'arithmetic on text shows an error code');
    Workbook.SetCell('E1', '');
    Workbook.SetCell('E2', '');
    Workbook.SetCell('E3', '');
    Workbook.SetCell('E4', '');
    Copy.LoadJSON(Workbook.ToJSON);
    Copy.LoadFormatsJSON(Workbook.FormatsJSON);
    ExpectText(Workbook.ToJSON, Copy.ToJSON, 'cell JSON round trip');
    ExpectText(Workbook.FormatsJSON, Copy.FormatsJSON, 'format JSON round trip');
    ExpectText('$111200.50', Copy.Display('D5'), 'the copy computes and formats the same total');
    Workbook.RemoveListener(Counter.Changed);
  finally
    Copy.Free;
    Counter.Free;
  end;
end;

procedure SpellingSection;
var
  Spell: TAtlasSpellChecker;
  Issues: TArray<TAtlasSpellingIssue>;
  Suggestions: TArray<string>;
begin
  Writeln('TAtlasSpellChecker');
  Spell := TAtlasSpellChecker.Create(nil);
  try
    Spell.LoadDictionary('# report vocabulary' + sLineBreak +
      'regional' + sLineBreak + 'sales' + sLineBreak + 'report' + sLineBreak +
      'total' + sLineBreak + 'quarter' + sLineBreak + 'east' + sLineBreak + 'west' + sLineBreak +
      'and' + sLineBreak + 'by');
    Spell.AddWord('north');
    Issues := Spell.Check('Regional sales reprot: East, West and North by quater');
    Expect(Length(Issues) = 2, 'two unknown words found');
    ExpectText('reprot', Issues[0].Word, 'first issue word');
    Expect((Issues[0].StartIndex = 16) and (Issues[0].Length = 6), 'one-based start index and length');
    Suggestions := Spell.Suggest('reprot');
    ExpectText('report', Suggestions[0], 'best suggestion by edit distance');
    Spell.IgnoreWord('quater');
    Expect(Length(Spell.Check('by quater')) = 0, 'ignored words are not reported');
    Expect(Spell.Known('NORTH'), 'lookups ignore case');
  finally
    Spell.Free;
  end;
end;

var
  Sales: TAtlasTable;
  Workbook: TAtlasWorkbook;
begin
  try
    Sales := BuildSales;
    Workbook := TAtlasWorkbook.Create(nil);
    try
      TableSection(Sales);
      PivotSection(Sales, Workbook);
      WorkbookSection(Workbook);
      SpellingSection;
    finally
      Workbook.Free;
      Sales.Free;
    end;
    Writeln('PASS: WorkbookReport');
  except
    on E: Exception do
    begin
      Writeln('FAIL: ', E.ClassName, ': ', E.Message);
      Halt(1);
    end;
  end;
end.