TAtlasWorkbook

Create constructor

constructor Create(AOwner: TComponent); override;

Creates an empty workbook.

Destroy destructor

destructor Destroy; override;

Frees the cells and formats. A TAtlasSheetGrid showing the workbook receives a free notification and sets its Workbook to nil.

OnChange event

property OnChange: TNotifyEvent read FOnChange write FOnChange;

Called after every change, after the listeners.

SetCell method

procedure SetCell(const Address, Text: string);

Stores Text in the cell at Address (any case, for example b12), or removes the cell when Text is empty. Text starting with = is a formula; anything else is kept exactly as given (29800.50 stays 29800.50 in Raw and displays as 29800.5 with the general format). A formula is not checked here; errors appear when the cell is evaluated. Raises EAtlasFormulaError (#REF!) for an invalid address. Notifies listeners.

Book.SetCell('C1', '=SUM(A1:B1)');
Book.SetCell('C1', ''); // removes the cell

Raw method

function Raw(const Address: string): string;

Returns the text stored at Address (the formula itself for a formula cell), or '' for an empty cell.

Value method

function Value(const Address: string): Double;

Evaluates the cell and returns its number: 0 for an empty cell, the number for a numeric cell, the result for a formula. Raises EAtlasFormulaError for a text cell (#VALUE!) and for any formula error; Code holds the error code.

Total := Book.Value('D4');

Display method

function Display(const Address: string): string;

Returns the text to show: '' for an empty cell, text cells unchanged, numbers and formula results formatted by the cell's TAtlasCellFormat, and error codes such as #DIV/0! instead of raising. Formats use . as the decimal point and no thousands separator: general 1234.5, fixed 1234.50, percent 12.50% (the value times 100), currency $1234.50 (always a dollar prefix; negative amounts show as $-5.00).

CellFormat method

function CellFormat(const Address: string): TAtlasCellFormat;

Returns the cell's format, or TAtlasCellFormat.Default when none was set.

HasCellFormat method

function HasCellFormat(const Address: string): Boolean;

True when a format was set for the cell with SetCellFormat or LoadFormatsJSON. TAtlasSheetGrid uses a format's colors only for such cells.

SetCellFormat method

procedure SetCellFormat(const Address: string; const Format: TAtlasCellFormat);

Sets the format of one cell; the cell may be empty. Raises EAtlasFormulaError (#VALUE!) when Decimals is outside 0..15 or NumberFormat is out of range. There is no call to remove one format; Clear and LoadFormatsJSON replace them all. Notifies listeners.

FormatsJSON method

function FormatsJSON: string;

Returns all formats as a JSON object keyed by address in sorted order, for example {"D5":{"numberFormat":3,"decimals":2,"bold":true,"background":16777215,"foreground":0}}. numberFormat is the ordinal of TAtlasNumberFormat; colors are integers.

LoadFormatsJSON method

procedure LoadFormatsJSON(const Text: string);

Replaces all formats with those of a document written by FormatsJSON. Every entry needs all five fields, valid values and a unique valid address. Raises EAtlasFormulaError (#VALUE! or #REF!) and keeps the current formats when the document is invalid. Cell contents are not touched.

Clear method

procedure Clear;

Removes all cells and all formats, and notifies.

ToJSON method

function ToJSON: string;

Returns all non-empty cells as a JSON object mapping address to raw text, sorted by address text: {"A1":"Item","B2":"=A1*2"}. Formats are not included.

LoadJSON method

procedure LoadJSON(const Text: string);

Replaces all cells with those of a document written by ToJSON. Values must be JSON strings; addresses must be valid and unique (a1 and A1 are the same cell). Raises EAtlasFormulaError and keeps the current cells when the document is invalid. Formats are not changed.

AddListener method

procedure AddListener(const Listener: TNotifyEvent);

Registers a method called after every change, before OnChange. Adding the same method again moves it to the end of the list instead of adding it twice; nil is ignored. TAtlasSheetGrid follows its workbook this way.

RemoveListener method

procedure RemoveListener(const Listener: TNotifyEvent);

Removes a listener registered with AddListener.

Compute an invoice with formulas and formats

Prints the four lines Design 125 $1500.00, Development 150 $6000.00, Total $7500.00, Tax $1500.00 (padded into columns) and Total as a number: 7500.00.

uses System.SysUtils, Atlas.Spreadsheet;

procedure BuildInvoice(Book: TAtlasWorkbook);
var
  Money: TAtlasCellFormat;
begin
  Book.Clear;
  Book.SetCell('A1', 'Item');
  Book.SetCell('B1', 'Hours');
  Book.SetCell('C1', 'Rate');
  Book.SetCell('D1', 'Amount');
  Book.SetCell('A2', 'Design');
  Book.SetCell('B2', '12');
  Book.SetCell('C2', '125');
  Book.SetCell('D2', '=B2*C2');
  Book.SetCell('A3', 'Development');
  Book.SetCell('B3', '40');
  Book.SetCell('C3', '150');
  Book.SetCell('D3', '=B3*C3');
  Book.SetCell('C4', 'Total');
  Book.SetCell('D4', '=SUM(D2:D3)');
  Book.SetCell('C5', 'Tax');
  Book.SetCell('D5', '=D4*0.2');
  Money := TAtlasCellFormat.Default;
  Money.NumberFormat := anfCurrency;
  Book.SetCellFormat('D2', Money);
  Book.SetCellFormat('D3', Money);
  Money.Bold := True;
  Book.SetCellFormat('D4', Money);
  Book.SetCellFormat('D5', Money);
end;

procedure PrintInvoice(Book: TAtlasWorkbook);
var
  Row: Integer;
begin
  for Row := 2 to 5 do
    Writeln(Book.Display(AtlasCellAddress(1, Row)).PadRight(12),
      Book.Display(AtlasCellAddress(3, Row)).PadRight(6),
      Book.Display(AtlasCellAddress(4, Row)));
  Writeln('Total as a number: ', Book.Value('D4'):0:2);
end;

See the error codes

Prints #VALUE! #DIV/0! #CYCLE! #NAME? 0 and #DIV/0! | #DIV/0! Division by zero at position 4.

uses System.SysUtils, Atlas.Spreadsheet;

procedure ShowFormulaErrors;
var
  Book: TAtlasWorkbook;
begin
  Book := TAtlasWorkbook.Create(nil);
  try
    Book.SetCell('A1', 'text');
    Book.SetCell('B1', '=A1+1');       // arithmetic on text
    Book.SetCell('B2', '=1/0');
    Book.SetCell('B3', '=B4');
    Book.SetCell('B4', '=B3');         // B3 and B4 refer to each other
    Book.SetCell('B5', '=ROUND(2.5)'); // not one of the five functions
    Book.SetCell('B6', '=SUM(A1:A3)'); // text and blank cells in a range are skipped
    Writeln(Book.Display('B1'), ' ', Book.Display('B2'), ' ', Book.Display('B3'), ' ',
      Book.Display('B5'), ' ', Book.Display('B6'));
    try
      Book.Value('B2');
    except
      on E: EAtlasFormulaError do
        Writeln(E.Code, ' | ', E.Message);
    end;
  finally
    Book.Free;
  end;
end;

Save and load cells and formats

Two files per workbook. The example run prints 25.00% for the reloaded percent cell.

uses System.SysUtils, System.IOUtils, Atlas.Spreadsheet;

procedure SaveWorkbook(Book: TAtlasWorkbook; const BaseName: string);
begin
  // Cell contents and formats are two separate documents; keep both.
  TFile.WriteAllText(BaseName + '.cells.json', Book.ToJSON, TEncoding.UTF8);
  TFile.WriteAllText(BaseName + '.formats.json', Book.FormatsJSON, TEncoding.UTF8);
end;

procedure LoadWorkbook(Book: TAtlasWorkbook; const BaseName: string);
begin
  // Each load validates its whole document first and keeps the old data on error.
  Book.LoadJSON(TFile.ReadAllText(BaseName + '.cells.json', TEncoding.UTF8));
  Book.LoadFormatsJSON(TFile.ReadAllText(BaseName + '.formats.json', TEncoding.UTF8));
end;