Documents

NextGrid & NextDBGrid

NextGrid

NextDBGrid

NextInspector

NextSheet

NextCollection

Misc

Labs

NextGrid .NET

NextSheet Quick Start

Related Articles:

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 Formating in NextSheet.

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.

Was This Article Useful?

Only constructive comments, code contributions... will be publishes. Questions, non-official discussion will not be published.