TjanGrid
 TjanGrid Properties Methods Events

TjanGrid is a TstringGrid descendant with spreadsheet functionality.

Unit

janGrid

Description

Add a TjanGrid object to a form to present textual and numerical data in a tabular format.

TjanGrid introduces the ability to sort data and to enter formulea for automatic calculation.

New in release 4

•	Select betwen A1 or r1c1 cell reference style

•	Cellhints show CellName and Formula

•	CellNames

•	Integral CellNames dialog

Modified in release 4

The component name is changed from TjvStringGrid to TjanGrid.

I have now registered the jan prefix with:

http://developers.href.com/dpr:PGREGISTER:153006807

The ToSortType eneumerated type has been changed from:

Type

  ToSortType  = (Character, Numeric, Date);

to:

Type

  ToSortType  = (tstCharacter, tstNumeric, tstDate);

This to avoid a conflict with the Delphi Date function.


Properties
TjanGrid Properties TjanGrid

A1Style

AdvanceDirection

AutoCalculate

AutoSizemargin

BandsColor

BandsInterval

BandsShow

CaseSensitiv

ColumnHeaders

EndIndex

FormuleMode

HighlightURL

HowToSort

KeyMappings

KeyMappingsEnabled

NumbersalRight

NumberFormat

PrintOptions

ShowValues

ShowMessageOnError

SortDirection

SortIndex

SortType

StartIndex

WordWrap

In PrintOptions

BorderStyle

Copies

FromRow

JobTitle

Leftpadding

PageTitle

PageTitleMargin

PreviewPage

ToRow


Methods
TjanGrid Methods TjanGrid

AppendColumn

AppendRow

AutoSizeColumns

AutoSizeColumn

AutoSizeRows

AutoSizeRow

ClearAllCells

ClearNormalCells

ClearRange

ColumnDelete

CopyRange

CutRange

DuplicateRow

FillRange

FilterRows

HideColumns

HideRows

InsertColumn

InsertRow

LoadFromCSV

PageCount

PasteRange

Print

PrintPreview

Recalculate

RowDelete

SaveToCSV

SaveToHTML

SaveToXML

ShowCellNamesDialog

ShowColumns

ShowFormuleDialog

ShowQueryDialog

ShowRows

Sort


Events
TjanGrid Events TjanGrid

onSizeChanged

The following protected methods of TStringGrid are overidden. You can use the associated events but should be aware that TjanGrid heavily relies on the implementation of these methods.

•	DrawCell

•	KeyDown

•	KeyPress

•	KeyUp

•	SelectCell

•	SizeChanges


HighlightURL
 TjanGrid.HighlightURL TjanGrid

property HighlightURL: boolean;

Description

Set HighlightURL to highlight web-url's that start with http: and email addresses that start with mailto:.


AdvanceDirection
 TjanGrid.AdvanceDirection TjanGrid

Type

  TAdvanceDirection= (none,right,down);

property AdvanceDirection: TAdvanceDirection;

Description

Determines which cell will be selected after you press enter.


NumberFormat
 TjanGrid.NumberFormat TjanGrid

property NumberFormat : string;

Description

Determines the format with which calculated values are displayed.

Format strings must be of the format %.2f where 2 is the number of decimal places and f is a format type.

The format type f can be one of the following:

•	f - floating point

•	e - scientific notation

•	n - number with thousand seperator

•	g - general number

•	m - currency

In your application use the global CurrencyString, CurrencyFormat and NegCurrFormat variables to further determine how currency is displayed.


AutoCalculate
 TjanGrid.AutoCalculate TjanGrid

property AutoCalculate : boolean;

Description

Determines if the grid will be recalculated automatically when you press the enter key in a cell.


ShowValues
 TjanGrid.ShowValues TjanGrid

property ShowValues : boolean;

Description

Determines if formulea or their calculated values will be displayed.


ColumnHeaders
 TjanGrid.ColumnHeaders TjanGrid

property ColumnHeaders: Tstrings;

Description

Determines the column headers of the Grid and can be set in design mode.


StartIndex
 TjanGrid.StartIndex TjanGrid

property StartIndex : integer;

Description

Determines the start row when sorting the grid.


EndIndex
 TjanGrid.EndIndex TjanGrid

property EndIndex : integer;

Description

Determines the End row when sorting the grid.


SortIndex
 TjanGrid.SortIndex TjanGrid

property SortIndex : integer;

Description

Determines the Sort column when sorting the grid.


SortType
 TjanGrid.SortType TjanGrid

Type ToSortType=(Character, Numeric, Date);

property Sorttype : ToSortType;

Description

Determines if the grid is sorted alphabetic, numeric or to date.


CaseSensitiv
 TjanGrid.CaseSensitiv TjanGrid

property CaseSensitiv : boolean;

Description

Determines if sorting is done case sensitive.


SortDirection
 TjanGrid.SortDirection TjanGrid

Type

 SortDirType=(Ascending, Descending);

property SortDirection : SortDirType;

Description

Determines if sorting is ascending or descending.


HowToSort
 TjanGrid.HowToSort TjanGrid

Type

 SortType=(stRow,stColumn);

property HowToSort : SortType;

Descriptionm

Determines if sorting is by row or by column.


ShowMessageOnError
 TjanGrid.ShowMessageOnError TjanGrid

property ShowMessageOnError : boolean;

Description

Determines if a message error is displayed when an error occurs in sorting.


PrintOptions
 TjanGrid.PrintOptions TjanGrid

property PrintOptions : TPrintOptions;

Description

Determines various options for printing that can be set as subproperties in the object inspector or via programming code.


JobTitle
 TPrintOptions.JobTitle TjanGrid

property JobTitle : string;

Description

Determines the Job title when printing the Grid.


PageTitle
 TPrintOptions.PageTitle TjanGrid

property PageTitle : string;

Description

Determines the Page title that is printed on every page when printing the Grid.


PageTitleMargin
 TPrintOptions.PageTitleMargin TjanGrid

property PageTitleMargin : Cardinal;

Description

Determines the margin between the top of the page and the first row of the grid when printing the grid.


Copies
 TPrintOptions.Copies TjanGrid

property Copies : Cardinal;

Description

Determines the number of copies printed when sending the grid to the printer.


FromRow
 TPrintOptions.FromRow TjanGrid

property FromRow : Cardinal;

Description

Determines the first row to be printed.


ToRow
 TPrintOptions.ToRow TjanGrid

property ToRow : Cardinal;

Description

Determines the last row to be printed.


PreviewPage
 TPrintOptions.PreviewPage TjanGrid

property PreviewPage : Cardinal;

Description

Determines which page will be shown with print preview.


BorderStyle
 TPrintOptions.BorderStyle TjanGrid

Type

 TBorderStyle=bsNone..bsSingle;

property BorderStyle : TBorderStyle;

Description

Determines if a border will be printed around each cell.


Leftpadding
 TPrintOptions.Leftpadding TjanGrid

property Leftpadding : Cardinal;

Description

Determines the amount of left padding applied when placing the text in a cell when printing.


AutoSizemargin
 TjanGrid.AutoSizemargin TjanGrid

property AutoSizemargin : LongInt;

Description

Determines the margin that is applied when autosizing columns. A margin of 0 will give a narrow text fit.


WordWrap
 TjanGrid.WordWrap TjanGrid

property WordWrap : boolean;

Description

Determines if words are wrapped end the end of a cell.


A1Style
 TjanGrid.A1Style TjanGrid

property A1Style:boolean;

Description

Determines if cell coordinates will be show in the A1 style or in the r1c1 style. r1c1 style is default.


Recalculate
 TjanGrid.Recalculate TjanGrid

procedure Recalculate;

Descriptions

Recalculates all the formules in the grid. Formules start with = and can contain references to cells, arithmetic operators +,-,*,/,^,MOD , functions and some special variables that act like user functions.

Expression can use brackets : =(c2r4+3)/34

Cell references must be of the form r2c4 or c4r2. Indicating the absolute column and row number as displayed at the grid borders.

Alternatively you can use the A1 style, using alphabet characters for the columns like used in Lotus 1-2-4 and Microsoft Excel.

Cells can have a name. You can use this name in a formule. If cell A1 is named income  you can use in any cell the formule =income*1.1

The following functions are supported:

 ABS, ATAN, COS, EXP, LN, ROUND, SIN, SQRT, SQR, TRUNC

Functions require an argument between brackets: =sin(c2r3)

The following constant is supported: PI or pi

The following special variables that act like function are supported:

sumrow sums values in the row from column 1 up to the cell containing the formule.

Example: =sumrow

sumcol sums values in the column from row 1 up to the cell containing the formule.

avgrow averages values in the row from column 1 up to the cell containing the formule.

avgcol averages values in the column from row 1 up to the cell containing the formule.

minrow returns lowest value in the row from column 1 up to the cell containing the formule.

maxrow returns highest value in the row from column 1 up to the cell containing the formule.

mincol returns lowest value in the column from row 1 up to the cell containing the formule.

maxcol returns highest value in the column from row 1 up to the cell containing the formule.


LoadFromCSV
 TjanGrid.LoadFromCSV TjanGrid

procedure LoadFromCSV( Afile:string);

Description

Loads a file in CSV (comma-seperated-values) format into the grid. This can be used to exchange date with other spreadsheet or database programs.


SaveToCSV
 TjanGrid.SaveToCSV TjanGrid

procedure SaveToCSV( Afile:string);

Description

Saves the grid as file in CSV (comma-seperated-values) format. This can be used to exchange date with other spreadsheet or database programs.


SaveToHTML
 TjanGrid.SaveToHTML TjanGrid

procedure SaveToHTML( Afile:string);

Description

Saves the grid as file in HTML format. This can be used to publish the table on the web.


SaveToXML
 TjanGrid.SaveToXML TjanGrid

procedure SaveToXML( Afile:string);

Description

Saves the grid as file in XML format. This can be used to publish the table on the web.


ClearNormalCells
 TjanGrid.ClearNormalCells TjanGrid

procedure ClearNormalCells;

Description

Clears all normal cells, that is the cells not part of the borders.


ClearAllCells
 TjanGrid.ClearAllCells TjanGrid

procedure ClearAllCells;

Description

Clears all cells, including the fixed border cells.


InsertRow
 TjanGrid.InsertRow TjanGrid

procedure InsertRow;

Description

Inserts a row and the current row position.


RowDelete
 TjanGrid.RowDelete TjanGrid

procedure RowDelete;

Description

Deletes the current row.


InsertColumn
 TjanGrid.InsertColumn TjanGrid

procedure InsertColumn;

Description

Inserts a column at the current column position.


ColumnDelete
 TjanGrid.ColumnDelete TjanGrid

procedure ColumnDelete;

Description

Removes the current column.


AppendRow
 TjanGrid.AppendRow TjanGrid

procedure AppendRow;

Description

Appends a new row at the end of the grid.


AppendColumn
 TjanGrid.AppendColumn TjanGrid

procedure AppendColumn;

Description

Appends a new column at the end of the grid.


AutoSizeColumns
 TjanGrid.AutoSizeColumns TjanGrid

procedure AutoSizeColumns;

Description

Autosizes all columns except column 0.


AutoSizeColumn
 TjanGrid.AutoSizeColumn TjanGrid

procedure AutoSizeColumn;

Description

Autosizes the current column.


AutoSizeRows
 TjanGrid.AutoSizeRows TjanGrid

procedure AutoSizeRows;

Description

Autosizes all rows.


AutoSizeRow
 TjanGrid.AutoSizeRow TjanGrid

procedure AutoSizeRow( Arow:integer);

Description

Autosizes a single row.


CopyRange
 TjanGrid.CopyRange TjanGrid

procedure CopyRange;

Description

Copies the selected range to the clipboard.


CutRange
 TjanGrid.CutRange TjanGrid

procedure CutRange;

Description

Cuts the selected range to the clipboard.


PasteRange
 TjanGrid.PasteRange TjanGrid

procedure PasteRange;

Description

Pastes a range from the clipboard with the top-left corner at the current cell.


ClearRange
 TjanGrid.ClearRange TjanGrid

procedure ClearRange;

Description

Clears the selected range.


FillRange
 TjanGrid.FillRange TjanGrid

procedure FillRange;

Description

Fills the selected range with the content of the range top-left cell.


DuplicateRow
 TjanGrid.DuplicateRow TjanGrid

procedure DuplicateRow;

Description

Duplicates the current row to the next row down.


Sort
 TjanGrid.Sort TjanGrid

function Sort: boolean;

Description

Sorts the grid according the sort properties and returns true when there were no errors in the sort.


Print
 TjanGrid.Print TjanGrid

procedure Print;

Description

Prints the grid to the printer according the printsettings.


PrintPreview
 TjanGrid.PrintPreview TjanGrid

procedure PrintPreview;

Description

Shows the integrated Print/Preview DialogBox. This procedure is changed in release 3.


PageCount
 TjanGrid.PageCount TjanGrid

function PageCount: integer;

Description

Returns the number of print pages. If you have a large grid this function can take some time.


onDrawCell
 TjanGrid.onDrawCell TjanGrid

This standard TStringGrid event is used in TjanGrid to draw the borders, take care of displaying formules/values etc.

If you assign this event in your application then the results of what TjanGrid does in this event handler, are lost.


onKeyPress
 TjanGrid.onKeyPress TjanGrid

This standard TStringGrid event is used in TjanGrid to intercept the Enter key. When you press the enter key the grid is recalculated and rows are resized as needed.

If you assign this event in your application then the results of what TjanGrid does in this event handler, are lost.


 KeyMappingsEnabled

property KeyMappingsEnabled:Boolean;

Description

Determines if keyboard accelerators are enabled. Accelerators can be modified in the KeyMappings property.


 KeyMappings

property KeyMappings:TGridKeyMappings;

Description

Determines the key mappings for the following methods:

•	Row Insert - Shift+Ins

•	Row Delete - Shift+Del

•	Column Insert - Ctrl+Shift+Ins

•	Column Delete - Ctrl+Shift+Del

•	Pint Preview - Ctrl+P

•	Query Dialog - Ctrl+Q

•	Formule Dialog - F3

•	CellNames Dialog - Ctrl+N

You can modify the key mappings in the object inspector.


 BandsColor

property BandsColor: TColor;

Description

Determines the color of the row bands. Rows can be shown colored at intervals determined by the BandsInterval property.


BandsInterval

property BandsInterval: integer;

Description

Determines the row interval of band coloring.


BandsShow

property BandsShow: boolean;

Description

Determines if colored rowbands are visible.


 FormuleMode

property FormuleMode:boolean

Description

Determines if the column-row Cell reference is copied to the formule dialog when you click a cell.

FormuleMode is set to true when you select the Formule dialog and set to false when you close the dialog.


 NumbersalRight

property NumbersalRight:boolean;

Description

Determines if numbers are shown right aligned.


 FilterRows

procedure FilterRows(AFilter:string);

Description

Filters the rows that match the filter. Rows that do not match are hidden. They can be displayed again with ShowRows

Normally you can let the user use the QueryDialog that pops up when Ctrl+Q is pressed, to create filter queries.

Filter Format

The filter format is:

[FieldName] condition "Filter value"

FieldName is the name of any field as displayed in the header row of thge grid. In the query dialog field names can be added to the query filter by clicking their name.

Condition

can be any of the following:

= <> > < LIKE

Filter Value

Is a string.

You can have up to 10 items in the query:

[FieldName] condition "Filter value"  [FieldName2] condition2 "Filter value2"  etc.


 HideColumns

procedure HideColumns;

Description

Hides selected columns.


HideRows

procedure TjanGrid.HideRows;

Description

Hides Selected rows.


 ShowColumns

procedure TjanGrid.ShowColumns;

Description

Displays all columns, including the one that were hidden.


ShowFormuleDialog
 TjanGrid.ShowFormuleDialog TjanGrid

procedure TjanGrid.ShowFormuleDialog;

Description

Pops-up the integrated formule dialogbox, making it much easier to enter formules by just clicking operators, functions and cells.

When the formule dialogbox is displayed the grid is put in the Formule mode, meaning that when you click a cell its reference is inserted in the dialog box expression editor.


ShowCellNamesDialog
 TjanGrid.ShowCellNamesDialog TjanGrid

procedure TjanGrid.ShowCellNamesDialog;

Description

Displays the CellNames dialog box where the user can add and delete CellNames.

CellNames can be used in formulas and make your formules more readable.

This dialog has been designed so that no programming is required to give the user CellName support.

When a grid is saved as a CSV file, the CellNames are automatically saved in the cvf file.

CellNames are restored when you load a CSV file that has a cvf file with CellNames.

Goto Named Cell

The user can quickly goto a named cell by typiing Ctrl+N in the grid, select the name in the CellNames dialog and click OK.

Name a Cell

The user can quickly name a cell by typing Ctrl+N entering a name in the name box; Click the Add button and then the OK button. The Cellrefrence is allready filled in when the user popsup the dialog box.


ShowQueryDialog
 TjanGrid.ShowQueryDialog TjanGrid

procedure TjanGrid.ShowQueryDialog;

Description

Displays the query builder dialog box where users can create filter strings by clicking field names and conditions.


ShowRows

procedure TjanGrid.ShowRows;

Description

Displays all rows, including the one that were hidden.


 onSizeChanged

type

 TonSizeChanged=procedure (sender:Tobject;OldColCount, OldRowCount: Longint) of object;

property onSizeChanged: TonSizeChanged;

Description

Occurs when the row or columncount changes.