Documents

NextGrid & NextDBGrid

NextGrid

NextDBGrid

NextInspector

NextSheet

NextCollection

Misc

Labs

NxReport Project

NextGrid .NET

NextSheet Quick Start

Info

Skill:
Version: 1.0.0
Author: Bojan Nikolic
Created:
Updated: 2009-07-30

This article require NextSheet 1.0.0

NextSheet is Grid component similar to Microsoft(tm) Excel(tm) table where each cell has own formatting (Color, Border, Font...). Each cell may display user defined value, or it may be calculated with using formulas.

1. Basic Principe

Using NextSheet is very similar as using standard StringGrid component.

Most important difference between NextSheet and StringGrid is that in NextSheet each cell is a object with own properties (Text, Color...) and methods instead of string. Columns and Rows are also objects with own properties such as Size or Index.

NextSheet also give possibility to draw borders around cells in various different styles and colors:

Cell's text may be aligned horizontally and/or vertically, and it may be rotated. Custom Font may be also set.

2. Common Properties

Following properties are most common in NextSheet component:

ColCount - Specify number of columns inside component.

RowCount - Specify number of rows inside component.

By setting this properties cells will be added, or deleted depending from previous value. Very similar to ColCount and RowCount properties in StringGrid.

Style - Specify appearance style (drawing and colors) of component (Office 2003, Office 2007).

Options property

Options - Specify main appearance and behavior options. This property consist from following flags:

Table 1: Options Flags
Name	Meaning
soAutoCalculation	When False sheet need to be manualy calculated by calling CalculateSheet.
soClipboard 1.3.5	When True Ctrl C (Copy) and Ctrl V (Paste) keys are enabled.
soEditing	Enable/disable editing cells.
soGridLines	Show/hide grid lines of cells.
soHeadings	Show/hide row and column headings.
soResizing	Enable/disable resizing rows or columns with mouse.
soShowContent	When set to true content of cells (such as formulas) will be shown.

Selection properties

SelectedCol - Determine column of selected cell. This property is zero-based.

SelectedRow - Determine row of selected cell. This property is zero-based.

Selection - Determine range of currently selected cells. This property is TCellsRange type with sub-values: StartCol, StartRow, EndCol, EndRow.

Caption := 'Selection: '  
  IntToStr(NextSheet1.Selection.StartCol)   ', '  
  IntToStr(NextSheet1.Selection.StartRow)   ', '  
  IntToStr(NextSheet1.Selection.EndCol)   ', '  
  IntToStr(NextSheet1.Selection.EndRow);

Caption = "Selection: "  
  IntToStr(NextSheet1->Selection->StartCol)   ", "  
  IntToStr(NextSheet1->Selection->StartRow)   ", "  
  IntToStr(NextSheet1->Selection->EndCol)   ", "  
  IntToStr(NextSheet1->Selection->EndRow);

This code will write currently selected range inside Form's caption.

SelectionMoveDirection - Determine in which direction selection will move after pressing Enter key. It may be Down, Left, Right, Up or selection may stay on same cell.

3. Common Methods

AddColumn - Add new column(s) at the end of columns array.

AddRow - Add new row(s) at the end of rows array.

CalculateSheet - Manualy re-calculate sheet (formulas). For using when soAutoCalculation in Options is set to False.

DeleteColumn(Index) - Delete column with specified Index.

DeleteRow(Index) - Delete row with specified Index.

EditCell - Edit specified cell. Example: EditCell(4, 3)

MergeCells - Merge cells inside specified range into one cell (cell in top-left corner). Example: MergeCells(2, 3, 4, 4)

MergeSelection - Merge currently selected cells.

SplitCells - Un-merge cells inside specified range. This is opposite method of MergeCells method.

4. Cell Properties

Each Cell in NextSheet is a object with own properties and methods. Accessing single Cell may be done with using Cell property of NextSheet.

Cell[Col, Row].(PropertyOrMethod)

Cell[Col][Row]->(PropertyOrMethod)

This are most important properties for each Cell:

Alignment - Specify Cell's Text alignment. Values:

Table 2: Alignment values
Name	Meaning
caTopLeft	Top-left alignment.
caTopCenter	Top-center alignment.
caTopRight	Top-right alignment
caCenterLeft	Vertical center-left alignment.
caCenter	Vertical center-center alignment
caCenterRight	Vertical center-right alignment
caBottomLeft	Bottom-left alignment
caBottomCenter	Bottom-center alignment
caBottomRight	Bottom-right alignment

Color - Specify Cell's color. Each Cell may have own color.

Font - Specify Cell's font (TFont).

Text - Specify Cell's text. Value for this property may be also a formula.

There are several read-only properties for each Cell:

Active - If cell is merged to some other cell, value of this property will be False.

DisplayText - Get text displayed in cell. DisplayText may be different than value in Text property, for example when cell is calculated with using formula. In that case Text property will be something like '=SUM(4,3,2)' and DisplayText will be '9'. Also, DisplayText contain formatted value (when some formatting is applied).

ValueReturn unformated or calculated value of cell, similar to Text property but with formulas applied.

Col - Determine index of column in which cell belong. This property is read-only.

Row - Determine index of row in which cell belong. This property is read-only.

Kind - Specify Cell's kind. With different cell kind different FormatMask rules will be applied.

FormatMask - Determine formating rules for cell. More about this property in .

5. Cell Borders

Setting Cell's Borders may be done with next properties:

1. BorderLeft
2. BorderTop
3. BorderBottom
4. BorderRight

Each Border-property have next properties:

LineStyle (lsThinLine, lsDouble...) - Specify Border's Line style.

Color - Border color

Delphi Example:

NextSheet1.Cell[4, 5].BorderTop.Color := clRed;
NextSheet1.Cell[4, 5].BorderTop.LineStyle := lsDouble;

NextSheet1->Cell[4][5]->BorderTop->Color = clRed;
NextSheet1->Cell[4][5]->BorderTop->LineStyle = lsDouble;

When Top border of cell is changed, bottom border of cell above will be changed to same value automatically. It is same in other directions too (example: when Left border of cell is changed, right border of previous cell will be changed to same value).

6. Columns and Rows

As mentioned before, each Column and Row is a object with own properties.

Column may be accessed via Column array property. Index represent Column's index and it is zero-based.

NextSheet1.Column[Index].Size := 40;

NextSheet1->Column[Index]->Size = 40;

Row may be accessed via Row array property. Index represent Row's index and it is zero-based.

Column and Row object properties

Beside Size property, there is also next properties:

Index - Determine Column (Row) index. Read-only property.

Caption - Specify Column (Row) caption (title). If this property is not empty, default caption inside headings will be replaced with value from this property.

NextSheet1.Column[1].Caption := 'Price';

NextSheet1->Column[1]->Caption = "Price";

Result:

7. Events

OnAfterEdit - Occur after cell exit from edit state. This event give possibility to reject editing, or to adjust text which will be applied into cell after editing.

Table 3: OnAfterEdit parameters
Name	Type	Meaning
ACol	Integer	Represent column of cell being edited.
ARow	Integer	Represent row of cell being edited.
Accept	Boolean	Specify if editing will be accepted and value from InplaceEditor applied to the cell. Default value is True..
Text	WideString	Text which will be applied to the cell. This parameter variable and it may be changed.

OnBeforeEdit - Occur before cell enter into edit state. This event give possibility to reject start editing, or to adjusting text.

Table 4: OnBeforeEdit parameters
Name	Type	Meaning
ACol	Integer	Represent column of cell to be edited.
ARow	Integer	Represent row of cell to be edited.
CanEdit	Boolean	When set to False, editing will be canceled. Default value is True.
Text	WideString	Text to be edited. This parameter is var parameter and it may be changed.

OnSelect - Occur when selection is changed (e.g. another cell(s) is selected).

Table 5: OnSelect parameters
Name	Type	Meaning
ACol	Integer	Represent column of selected cell.
ARow	Integer	Represent row of selected cell.

8. Formulas

Each cell may contain expression (formula) which may be calculated with using standard operators ( . -. *, /) and some of build in functions such as SUM, AVG, RND, MAX, MIN, COS...

8.1 Setting Expression in Cell

Cell contain expression need to have Text property started with =, otherwise cell will display expression as regular text.

NextSheet1.Cell[1, 1].Text := '=2*4';

NextSheet1->Cell[1][1]->Text = "=2*4";

Result:

Cell will display 8

8.2 Cell Address in Expression

Cell address or range of cells may be used within expression:

=A2*4

A2 will be replaced with value from cell Col: 1, Row: 2 (A2).

or using more cells in expression:

=A2*((B1 B2) 25)

or using range (FROMCELL:TOCELL) of cells inside function:

=SUM(B1:B5)

8.3 Functions

This is a list of some of functions built-in inside NextSheet:

Table 6: Functions list
Name	Menaing	Example
ABS	Returns the absolute value of the argument.	=ABS(-4) =ABS(A1)
ARCTAN	Returns the arctangent of a given number.
AVERAGE AVG	Returns the average value of the arguments.	=AVERAGE(4,6,8)
COS	Returns the cosine of the angle.
LENGTH	Returns the length of a given string.	=LENGTH("Test") =LENGTH(A1)
LOG	Returns the natural log of a real expression.
MAX	Returns the most greater of the arguments.	=MAX(2,6,3) =MAX(A1:A5)
MIN	Returns the most lesser of the arguments.	=MIN(7,2,11) =MIN(A1:A5)
PI	Returns the mathematical value pi.	=PI
RND	Returns random numbers within a specified range.	=RND(4)
ROUND	Rounds a real-type value to an integer-type value.	=ROUND(5.42) =ROUND(A1)
SIN	Returns the sine of the argument.	=SIN(PI)
STRCOMP	Compare 2 strings and return 1 if match.	=STRCOMP(A1, A2)
STDEV STD	Returns the sample standard deviation for elements in an array.	=STDEV(1, 4) =STDEV(A1:A5)
SQR	Returns the square of the argument.	=SQR(3)
SQRT	Returns the square root of argument.	=SQRT(9)
SUM	Returns the sum of arguments.	=SUM(A1, A2) =SUM(1,4,3) =SUM(A1:A5)

As expected Formulas and operators may be combined within expression.

=ROUND(AVG(4,7,8)) SUM(A1:B4)

9. Formating Cells

Cell formating may be done in 2 ways:

1. By targeting single cell
2. By using StylePainter object.

Cell may be formated by simply using Cell[] array property and setting some of cell properties. Example:

NextSheet1.Cell[1, 1].Color := clRed;
NextSheet1.Cell[1, 1].BorderTop.Color := clBlue;

NextSheet1->Cell[1][1]->Color = clRed;
NextSheet1->Cell[1][1]->BorderTop.Color := clBlue;

With using StylePainter object of TNextSheet, formating number of cells may be done faster. StylePainter apply selected style (Cell's color, type, font, formatmask) to a range of cell by using Apply method.

Accessing StylePainter need to be done in this way:

NextSheet1.StylePainter...

NextSheet1->StylePainter...

StylePainter object include following properties:

Table 7: StylePainter properties
Name	Meaning
Alignment	Alignment of cells applied by StylePainter.
Color	Color of cells applied by StylePainter.
Font	Font of cells applied by StylePainter.
FormatMask	FormatMask of cells applied by StylePainter.
Kind	Kind of cells applied by StylePainter.

// Set style properties
with NextSheet1.StylePainter do
begin
  Alignment := caCenterRight;
  Kind := ckNumber;
  FormatMask := '#,##0.00';
end;

// Apply style to cells
NextSheet1.StylePainter.Apply(1, 1, 3, 3);

// Set style properties
NextSheet1.StylePainter->Alignment = caCenterRight;
NextSheet1.StylePainter->Kind = ckNumber;
NextSheet1.StylePainter->FormatMask = '#,##0.00';

// Apply style to cells
NextSheet1->StylePainter->Apply(1, 1, 3, 3);

Code above will apply specified Alignment, Kind and FormatMask settings to range of cells (from 1,1 to 3, 3). StylePainter may be look as some kind of "virtual" cell which may be copied to the range of cells.