TsWorksheetGrid

Overview

TsWorksheetGrid is a specialized grid component which interfaces to a TsWorksheet from the FPSpreadsheet library and displays spreadsheet data files like in a conventional TStringGrid.

Programming interface

TsWorksheetGrid inherits from TCustomGrid and behaves much like a standard TStringGrid. The main difference is that the grid itself does not store data, but data are stored in a TWorksheet of FPSpreadsheet.

A variety of properties is available to access cells, their values and attributes, in a StringGrid-like way, such as writing a cell value by means of Grid.Cells[ACol, ARow] := 1.234. The cells are identified in these properties by means of the cell's column and row indexes. Please not that the indexes are passed in the order "column first / row last", and they include the indexes occupied by the fixed cells, i.e. the top/left data cell has the indexes col=1 and row=1. This is different from fpspreadshet where the indexes always start at 0 and are passed to functions in the opposite order "row first / column last".

Access to cell values

The property Cells[ACol, ARow] provides access to the data in a cell given by its column and row indexes. This is similar to TStringGrid. Unlike TStringGrid which works with strings only, TsWorksheetGrid, however, accepts numbers, dates, booleans, and formulas as well. For this reason, the data type of Cells is a variant.

Writing of cell values

Use this code to write data to a cell in a TsWorksheetGrid named Grid for simplicity:

  // Write a string
  Grid.Cells[1, 1] := 'This is a string';  
  // Write a number
  Grid.Cells[1, 2] := 1.2345;
  // Write a date
  Grid.Cells[1, 3] := EncodeDate(2016, 1, 18);
  // Write a formula
  Grid.Cells[1, 4] := '=A2+2';
  // etc.

Reading of cell values

In the same way, cell values can be read into variables of decidated types:

var
  str: String;
  x: Double;
begin
  str := Grid.Cells[1, 1];  // Variable "str" will contain the value "This is a string"
  x := Grid.Cells[1, 2];    // x will be 1.2345
  str := Grid.Cells[1, 2];  // Although the cell contains a number it is converted to the string "1.2345"
end;

Comparing cell values

Since the Cells property is a variant it is a bit more difficult to compare string values. Use the string-to-variant conversion function VarToStr() provided by unit variants.

  // This straightforward comparison will fail:
  // if Grid.Cells[1,1] = 'This is a string' then ...

  // Use this instead:
  if VarToStr(Grid.Cells[1,1]) = 'This is a string' then ...

Numerical values often can be compared without an explicit conversion:

  if Grid.Cells[1,2] = 1.2345 then ...

See also

FPSpreadsheet