YOUSEFUL / DELPHI / COMPONENTS

`TAtlasSheetGrid`

A string grid that shows a TAtlasWorkbook: calculated values in the cells, the raw formula in the editor.

Visual control Palette: Atlas Unit Atlas.SheetGrid · Inherits TStringGrid · Product Atlas VCL

Description

TAtlasSheetGrid (unit Atlas.SheetGrid) is a TStringGrid that shows a TAtlasWorkbook: column letters in the fixed top row, row numbers in the fixed left column, each cell's Display text painted with its format (background, text color and bold only for cells with an explicit format), and the raw text, such as a formula, in the in-place editor. The grid follows the workbook through a listener and refreshes every cell after each change; freeing the workbook sets Workbook to nil.

The visible range is the grid's size: Create makes columns A to Z and rows 1 to 100. Change ColCount and RowCount (published by TStringGrid) before assigning Workbook, or call RefreshValues afterwards. Each refresh calls Display for every cell of the grid, so keep the grid no larger than you need.

Limitation of this baseline (checked against the VCL grid code with simulated keystrokes): the grid writes the editor text to the workbook on every keystroke, and the refresh that follows reloads the editor and selects all of its text, so the next keystroke replaces it. Typing =A1*2 leaves 2 in the cell; typing a single character or pasting a whole value works. Escape does not undo, because the workbook already holds the text. Until this is fixed, remove goEditing from Options and edit through a separate edit box that calls Workbook.SetCell, as in the formula bar example.

Members

Member	Kind	What it does
`Create`	constructor	Creates a grid with 27 columns (row numbers plus A to Z) and 101 rows (letters plus 1 to 100), 100-pixel columns and 25-pixel rows, adding editing, Tab navigation and column sizing to the `TStringGrid` options.
`Destroy`	destructor	Detaches from the workbook, then destroys the grid.
`RefreshValues`	method	Rewrites the column letters, the row numbers and the display text of every cell from the workbook (empty cells when `Workbook` is nil).
`Workbook`	property	The workbook to show, or nil.

Constructors

`Create` constructor

constructor Create(AOwner: TComponent); override;

Creates a grid with 27 columns (row numbers plus A to Z) and 101 rows (letters plus 1 to 100), 100-pixel columns and 25-pixel rows, adding editing, Tab navigation and column sizing to the TStringGrid options.

Destructors

`Destroy` destructor

destructor Destroy; override;

Detaches from the workbook, then destroys the grid.

Properties

`Workbook` property

property Workbook: TAtlasWorkbook read FWorkbook write SetWorkbook;

The workbook to show, or nil. Assigning it fills the grid.

Methods

`RefreshValues` method

procedure RefreshValues;

Rewrites the column letters, the row numbers and the display text of every cell from the workbook (empty cells when Workbook is nil). The grid calls it after every workbook change; call it yourself after changing ColCount or RowCount.

Sheet.ColCount := 30;
Sheet.RefreshValues;

Examples

Show a workbook

For A1 = 2 and B1 = =A1*21 the grid shows 2 and 42 under A and B.

uses Vcl.Forms, Vcl.Controls, Atlas.Spreadsheet, Atlas.SheetGrid;

function ShowWorkbook(Form: TForm; Book: TAtlasWorkbook): TAtlasSheetGrid;
begin
  Result := TAtlasSheetGrid.Create(Form);
  Result.Parent := Form;
  Result.Align := alClient;
  Result.ColCount := 9;    // the row-number column plus A..H
  Result.RowCount := 41;   // the letter row plus rows 1..40
  Result.Workbook := Book; // fills the cells; after later size changes call RefreshValues
end;

Edit through a formula bar

The workaround for the keystroke limitation: the grid is display-only and a TEdit above it shows the raw text of the selected cell and stores it on Enter. Verified: selecting B1 shows =A1+1; entering =A1*3 makes the grid show 15.

uses Vcl.Forms, Vcl.Controls, Vcl.StdCtrls, Vcl.Grids, Atlas.Spreadsheet, Atlas.SheetGrid;

type
  // Edits cells through a formula bar instead of the grid's in-place editor.
  TFormulaBar = class
  private
    FSheet: TAtlasSheetGrid;
    FEdit: TEdit;
    procedure SheetSelectCell(Sender: TObject; ACol, ARow: Longint; var CanSelect: Boolean);
    procedure EditKeyPress(Sender: TObject; var Key: Char);
  public
    constructor Create(Form: TForm; Book: TAtlasWorkbook);
  end;

constructor TFormulaBar.Create(Form: TForm; Book: TAtlasWorkbook);
begin
  inherited Create;
  FEdit := TEdit.Create(Form);
  FEdit.Parent := Form;
  FEdit.Align := alTop;
  FEdit.OnKeyPress := EditKeyPress;
  FSheet := TAtlasSheetGrid.Create(Form);
  FSheet.Parent := Form;
  FSheet.Align := alClient;
  FSheet.Options := FSheet.Options - [goEditing];
  FSheet.OnSelectCell := SheetSelectCell;
  FSheet.Workbook := Book;
  FEdit.Text := Book.Raw('A1');
end;

procedure TFormulaBar.SheetSelectCell(Sender: TObject; ACol, ARow: Longint;
  var CanSelect: Boolean);
begin
  if (ACol > 0) and (ARow > 0) then
    FEdit.Text := FSheet.Workbook.Raw(AtlasCellAddress(ACol, ARow));
end;

procedure TFormulaBar.EditKeyPress(Sender: TObject; var Key: Char);
begin
  if Key = #13 then
  begin
    FSheet.Workbook.SetCell(AtlasCellAddress(FSheet.Col, FSheet.Row), FEdit.Text);
    Key := #0;
  end;
end;

TAtlasWorkbook · TAtlasCellFormat

Also in Atlas VCL

TAtlasTable · TAtlasGrid · TAtlasValueEdit · TAtlasTree · TAtlasWorkbook · TAtlasPivot · TAtlasInspector · TAtlasRichEdit · TAtlasSpellChecker · TAtlasChart · TAtlasGauge · TAtlasThemer · TAtlasWizard · TAtlasWorkspace · TAtlasCommandBar · TAtlasActionTile · TAtlasTileBoard · TAtlasNavigation · TAtlasMap · TAtlasGraph · TAtlasDiagram · TAtlasPrintDocument · TAtlasSchedule · TAtlasScheduler · TAtlasTaskPlan · TAtlasGantt · TAtlasPdfDocument · TAtlasPdfViewer

TAtlasSheetGrid