Difference between revisions of "TsWorksheetGrid"
(→Programming interface: Link to grids wiki page) |
(→See also: Link to grids wiki page) |
||
Line 232: | Line 232: | ||
* [[RPN_Formulas_in_FPSpreadsheet|RPN formulas in FPSpreadsheet]] | * [[RPN_Formulas_in_FPSpreadsheet|RPN formulas in FPSpreadsheet]] | ||
* [[FPSpreadsheet:_List_of_formulas|FPSpreadsheet: List of formulas]] | * [[FPSpreadsheet:_List_of_formulas|FPSpreadsheet: List of formulas]] | ||
+ | * [[Grids_Reference_Page|Grids reference page]] | ||
[[Category:Lazarus]] | [[Category:Lazarus]] |
Revision as of 22:45, 19 January 2016
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. The worksheet can be accessed by using the property Worksheet of the grid. Similarly, Workbook allows to access the entire workbook to which the worksheet belongs.
A variety of properties are 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 note 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 string with embedded HTML codes
Grid.Cells[1, 2] := 'Chemical formula of <b>water</b>: H<sub>2</sub>O';
// Write a number
Grid.Cells[1, 3] := 1.2345;
// Write a date
Grid.Cells[1, 4] := EncodeDate(2016, 1, 18);
// Write a formula
Grid.Cells[1, 5] := '=A3+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 ...
Formulas in cells
Since the TsWorksheetGreed works on top of a spreadsheet all formulas supported by FPSpreadsheet can be entered. See this document for a complete list of supported formulas.
The formula string must begin with the character "=" and follow Excel's "A1" notation, i.e. column index must be characters "A".."Z" , and row index is a 1-based integer. If there are more than 26 columns then two (or three) characters can be used.
Note that formulas are not automatically calculated by default. In order to activate automatic calculation of formulas set the grid property AutoCalc to true, or set the option boAutoCalc of the workbook used by the grid. "Automatic calculation" means that all formulas in the entire worksheet are recalculated whenever the content of any cell changes. Therefore, it is not required that the cells addressed by the formula already have values when the formula is entered.
// Enable automatic calculation of formulas
Grid.AutoCalc := true;
// or: Grid.Workbook.Options := Grid.Workbook.Options + [boAutoCalc];
// Enter cells
Grid.Cells[1,1] := 1; // this is cell A1
Grid.Cells[1,2] := 2; // this is cell A2
// Enter formula
Grid.Cells[1,3] := '=A1+A2'; // Calculates the sum of the values in A1 and A2
// Another formula in Cell B3
Grid.Cells[2,3] := '=B1*B2'; // It does not matter that the cells B1 and B2 do not exist, yet.
// Enter cells needed by the formula
Grid.Cells[2,1] := '=A3'; // Use the result of the previous formula
Grid.Cells[2,2] := 10;
Cell formatting
Cell attributes can be attached to each cell in a similar way as the cell values. There is a set of grid properties representing each attribute:
- BackgroundColor[ACol, ARow: Integer]: TsColor - specifies the background color of the cell. TsColor is an integer containing the rgb components of the color. Unit fpsTypes provides a list of constants for a large number of predefined colors; the colors defined by the standard unit Graphics can be used as well (except for the system color values).
- CellBorder[ACol, ARow: Integer]: TsCellBorders - specifies which cell edges will be decorated by a border. Use the set values [cbEast, cbWest, cbNorth, cbSouth] for the right, left, top and border edges, respectively.
- CellBorderStyle[ACol, ARow: Integer; ABorder: TsCellBorder]: TsCellBorderStyle - specifies the style to be used for the cell border line given in the parameter ABorder. The TsCellBorderStyle is a record containing information on the line style and line color. Note that the set CellBorder[ACol, ARow] must contain the element ABorder in order to activate this border line.
- CellFont[ACol, ARow: Integer]: TFont - describes the font used when painting the cell text. Elements of the font can be changed separately by these properties:
- CellFontColor[ACol, ARow: Integer]: TsColor - identifies the text color. See "BackgroundColor" above for a description of the TsColor type.
- CellFontName[ACol, ARow: Integer]: String - is the name of the font.
- CellFontSize[ACol, ARow: Integer]: Single - is the point size of the font (1 pt = 1/72 inch).
- CellFontStyle[ACol, ARow: Integer]: TsFontStyles - is a set containing elements for using a bold, italic, underlined, or striked-out font.
- HorAlignment[ACol, ARow: Integer]: TsHorAlignment - allows to modify the horizontal alignment of the cell text (haLeft, haCenter, or haRight).
- NumberFormat[ACol, ARow: Integer]: String - is an Excel-compatible number format string, e.g. '0.000' for displaying a number value with 3 decimal places. The number format is important if numbers are to be displayed as date or time values.
- TextRotation[ACol, ARow: Integer]: TsTextRotation - must be used for rotating the text within the cell. The type TsTextRotation provides trHorizontal, rt90DegreeClockwiseRotation, rt90DegreeCounterClockwiseRotation, and rtStacked.
- VertAlignment[ACol, ARow: Integer]: TsVertAlignment - allows to modify the horizontal alignment of the cell text (vaTop, vaCenter, or vaBottom).
- Wordwrap[ACol, ARow: Integer]: Boolean - activates word-wrapping of text which is longer than the width of a cell (or the height if rotated text is used).
These properties can also be accessed for a range of cells specified by a TGridRect parameter. TGridRect is a record containing the col/row indexes of the top/left and bottom/right cells of the block. Since these properties are related to several cells they are spelled in plural form (with appended "s"):
- BackgroundColors[ARect: TGridRect]: TsColor
- CellBorders[ARect: TGridRect]: TsCellBorders
- CellBorderStyles[ARect: TGridRect; ABorder: TsCellBorder]: TsCellBorderStyle
- CellFonts[ARect: TGridRect]: TFont
- CellFontColors[ARect: TGridRect]: TsColor
- CellFontNames[ARect: TGridRect]: String
- CellFontStyles[ARect: TGridRect]: TsFontStyles
- CellFontSizes[ARect: TGridRect]: Single
- HorAlignments[ARect: TGridRect]: TsHorAlignment
- NumberFormats[ARect: TGridRect]: String
- TextRotations[ARect: TGridRect]: TsTextRotation
- VertAlignments[ARect: TGridRect]: TsVertAlignment
- Wordwraps[ARect: TGridRect]: Boolean
If properties to be read do not have identical values within the cell block then a neutral or default value is returned.
Example
This example adds a formula for today's date to cell A1, formats the cells to display the number as a date and selects a white, italic font on lightgray background. A red dotted border is drawn around the cell.
const
RED_DOTTED_BORDER: TsBorderStyle = (LineStyle: lsDotted; Color: scRed);
// Set cell content
Grid.Cells[1,1] := '=TODAY()';
// Format as date
Grid.Numberformat[1,1] := 'yyyy/mm/dd';
// Select background color
Grid.BackgroundColor[1,1] := scSilver;
// Select format
Grid.FontColor[1,1] := scWhite;
Grid.FontStyle[1,1] := [fssItalic];
// Activate cell borders
Grid.Border[1,1] := [cbEast, cbWest, cbNorth, cbSouth];
// Determine how cell borders will be drawn
Grid.BorderStyle[1,1, cbEast] := RED_DOTTED_BORDER;
Grid.BorderStyle[1,1, cbWest] := RED_DOTTED_BORDER;
Grid.BorderStyle[1,1, cbNorth] := RED_DOTTED_BORDER;
Grid.BorderStyle[1,1, cbSouth] := RED_DOTTED_BORDER;
Cell comments
Comments can be added to each cell by using the grid's property CellComment[ACol, ARow: Integer]: String. Cells containing a comment are marked with a red triangle in the upper right corner of a cell. If the mouse is moved into a cell with a comment a popup window appears to display the comment.
Example:
Grid.Cells[1,1] := '=pi()';
Grid.CellComment[1,1] := 'The number pi is needed to calculate the area and circumference of a circle.';
Hyperlinks in cells
Cells with attached hyperlinks allow the user to navigate to other cells or other documents by clicking on the cell. Hyperlinks can be accessed by using the property Hyperlink[ACol, ARow: Integer: String. The hyperlink string contains the hyperlink target and an optional tooltip text which is separated by means of a bar character ("|"). Internal targets are already handled by the grid, but for navitation to the hyperlink an event handler for OnClickHyperlink must be provided. To distinguish normal cell clicks from hyperlink clicks the mouse must be held down for fractions of a second before the hyperlink is executed.
Example:
// Example for adding an external hyperlink
Grid.Cells[1,1] := 'Lazarus';
Grid.Hyperlink[1,1] := 'www.lazarus-ide.org|Open Lazarus web site';
// Example for adding an internal hyperlink
Grid.Cells[2,2] := 'Summary';
Grid.Hyperlink[2,2] := '#Sheet2!B10|Go to the summary starting at cell B10 of sheet2';
// Example for an OnClickHyperlink event handler needed for external hyperlinks
uses
..., uriparser;
procedure TForm1.GridOnClickHyperlink(Sender: TObject; const AHyperlink: TsHyperlink);
begin
var
uri: TUri;
begin
uri := ParseURI(AHyperlink.Target);
case Lowercase(uri.Protocol) of
'http', 'https', 'ftp', 'mailto', 'file':
OpenUrl(AHyperlink.Target);
else
ShowMessage('Hyperlink ' + AHyperlink.Target + ' clicked');
end;
end;
Merged cells
A rectangular group of cells can be merged to a single block. The content of the top/left cell of this block is displayed across all the combined cells, the content of the other cells is ignored.
Use the method MergeCells to perform this operation. As a parameter, it requires a TRect containing the left, top, right and bottom cell coordinates of the the merged cells. The method Unmerge splits a previously merged block again in the individual cells, here the coordinates of a single cell from the merged range is sufficient.
// Example for merging
Grid.MergeCells(Rect(1,1, 3,1); // Combine the first 3 cells of the first row
Grid.Cells[1,1] := 'Summary'; // Write the text "Summary" across the 3 cells
Grid.HorAlignment[1,1] := haCenter; // and center it.
// Example for unmerging
Grid.UnmergeCells(1,1); // Splits the merged block (1,1..3,1)
Column widths and row heights
Column widths and row heights can be changed for all cells by setting the DefaultColWidth and DefaultRowHeight. This works also in design mode. In addition, the widths and heights of particular columns and rows can be modified by the properties ColWidths[ACol] and RowHeights[ARow]. These values must be given in pixels.
Grid.DefaultColWidth := 80; // This is the width of all columns
Grid.ColWidths[10] := 10; // except for column #10 which is only 10 pixels wide.
Grid.DefaultRowHeight := 20; // All rows are 20 pixels high
Grid.RowHeights[2] := 4; // Row #2 serves as a spacer and is only 4 pixels high.