EhLib
ContentsIndexHome
PreviousUpNext
TDBGridEh Class

TDBGridEh displays and manipulates records from a dataset in a tabular grid.

Pascal
TDBGridEh = class(TCustomDBGridEh);

TDBGridEh provides all functionality of TDBGrid and adds several new features as follows:

  • Allows to select records, columns and rectangle areas.
  • Special titles that can correspond to several/all columns.
  • Footer that is able to show sum/count/other field values.
  • Automatic column resizing to set grid width equal client width.
  • Ability to change row and title height.
  • Allows automatic broken of a single line long title and data row to a multiline.
  • Title can act as button and, optionally show a sort marker.
  • Automatically sortmarking.
  • Ability to truncate long text with ellipsis.
  • Lookup list can show several fields.
  • Incremental search in lookup fields.
  • Frozen columns.
  • DateTime picker support for TDateField and TDateTimeField.
  • Allows to show bitmaps from TImageList depending on field value.
  • Allows to hide and track horizontal or vertical scrollbars.
  • Allows to hide columns.
  • Allows to show 3D frame for frozen, footer and data rows.
  • Allows to draw memo fields.
  • Multiline inplace editor.
  • Proportional scrolling independently of sequenced of dataset.
  • Automatically show checkboxes for Boolean fields. Allows to show checkboxes for other type of fields.
  • Has a procedures to save and restore layout (visible columns, columns order, columns width, sortmarkers, row height) in/from registry or ini file.
  • Allows to show bitmap in titles instead of caption.
  • Allows to forbid/allow keys to insert, append, delete, update records.
  • Allows to export data to Text, CSV, HTML, RTF, XLS and internal formats.
  • Allows to import data from Text and internal formats.
  • Can sort data in various dataset's.
  • Can filter data in various dataset's.

 

Instructions to use. 

 

Inplace editor takes Color and Font from cell color. Cell takes Color and Font from Column Color and Font, and OnGetCellParams event. Inplace editor automatically set multiline mode when row height > height of one line and property WordWrap for column is True. 

In lookup inplace editor you can clear (set to Null) LookupKeyField value at runtime. Simply select text and press Del. 

 

Complex titles. 

 

To create title corresponding to several columns, the label of the field or caption of title of the column must consist of several parts divided by sign "|", where every part is same for several columns. Other fields or captions must contain same text in corresponding parts. Set also DBGridEh.UseMultiTitle property to True. 

For instance:

    Field1.DisplayLabel := 'Title1|SubTitle1'; or DBGridEh.Columns[0].Title.Caption := 'Title1|SubTitle1';
    Field2.DisplayLabel := 'Title1|SubTitle2'; or DBGridEh.Columns[1].Title.Caption := 'Title1|SubTitle2';

 

Footer and total values. 

 

To show footer row(s) need to set FooterRowCount property. 

To force grid to calculate total values need to activate SumList (DBGridEh.SumList.Active := True). 

In footer cell, it is possible to show sum value for specified of field, (record count, value of a field or static text). Use property Column.Footer.ValueType. If ValueType = fvtStaticText, then set the property Value. If ValueType = fvtFieldValue, then you need to set property FieldName. 

Set ValueType to fvtSum and grid must to show sum value for column field, you can also specify Column.Footer.FieldName to calculate total value for other field. 

If you have more then one footer row you can use Column.Footers collection to specify value for individual footer cell in column. 

 

Using DBGridEh for sorting and filtering data in the DataSet. 

 

DBGridEh can not sort or filter data by itself. But it can pass action for sorting or filtering to the specal object that will do it in DataSet. You can adjust grid fot a two type of sorting (local or server), using property SortLocal and two type of filtering (also, local and server), but real possibility to aplly each type of operation depended of the type of DataSet. For instance, TBDEDataSet does not suppot sorting locally (inside DataSet) so you can not sort data locally when grid is linked to TQuery or TTable. Furthermore, EhLib have only special objects for standart type of Data Set (not for a third party DataSet's). 

Below you can see a table of special object and DataSet in witch these objects can sort or filter data. 

 

Unit 
DataSet 
Local sorting 
Server sorting 
Local filtering 
Server filtering 
TQuery, TTable 
TADOQuery,TADODataSet 
TClientDataSet 
TSQLQuery,TSQLDataSet 
TIBQuery, TIBDataSet 

When grid is adjusted for a local filtering the special object will build expression and assign it to a property Filter of DataSet. So need also to set Filtered to True manually. 

When grid is adjusted for a server filtering, the special object will build SQL:ORDER BY expression and replace ORDER BY string in SQL or Command property of DataSet and reopen DataSet. 

When grid is adjusted for a local sorting the special object will do a sorting using specific of the type of DataSet. 

When grid is adjusted for a server sorting the special object will build expression for SQL 'WHERE' clause in SQL string of dataset. It will try to found string that beginning from key string '/*Filter*/' (You can change this key string using global variable - SQLFilterMarker) in SQL expression and adds filter expression after this string in same line. So your SQL query have to have a line that beginning from '/*Filter*/'. For instance:

' SELECT *
FROM table1
WHERE
/*Filter*/ 1=1 '

For automatically filtering/sorting data in dataset need to add one of the units EhLibXXX (EhLibADO, EhLibBDE, EhLibCDS ... dependently of the dataset that grid is linked to) to 'uses' clause of any unit of your project. This units have code to register TDatasetFeaturesEh based classes (special object) for respective DataSet's. For other third party DataSet's you have to write and register your own special object or you have to write OnApplyFilter or/and OnSortMarkingChanged event for every required grid. More other, you can write OnApplySorting and/or OnApplyFilter for the global varia ble DBGridEhDataService. These events has TNotifyEvent type where Sender is TCustomDBGridEh

 

Adjusting grid for sorting data: 

 

TDBGridEh allows to show special sortmarking bitmaps (small triangles) in the right part of title cell. In order to automatically marking title by sortmarking bitmaps add dghAutoSortMarking to OptionsEh property. Add dghMultiSortMarking too OptionsEh in order to allow sortmarking several columns simultaneously. Set Column.Title.TitleButton to true for titles which will have possibility to change sortmarkers at run time. Or you can set ColumnDefValues.Title.TitleButton to allow to set sortmarking for all columns which Column.Title.TitleButton is not changed. At runtime clicking on title will change sortmarking. Holding Ctrl key allows to mark several columns simultaneously. After user change sortmarking grid call OnSortMarkingChanged event or pass action to special object, if it registered. Special object will use Grid.SortLocal property to determine how to sort data: locally o r on the server. If you write OnSortMarkingChanged you can use TDBGrid.SortMarkedColumns property to access to sortmarked columns and TColumnEh.Title.SortMarker property to get state of the sortmarker. 

 

Adjusting grid for filtering data: 

 

TDBGridEh allows to show special row (under title) where user enter expressions in cells for filtering records in dataset. (Expression in the cell can look like: "1", ">=1", ">1 AND <=5", "%TestStr%"). Set STFilter.Visible to True to show this special row. AT Run time, when cursor reside in the filter cell they can press ENTER to apply filter. Grid will parse expression in every not empty filter cell and call TDBGridEh.ApplyFilter, call OnApplyFilter event or pass action to special object, if it registered. 

 

For normally parsing, the expression in cell have to has next format:

[Operator1] Operand1 [)] [AND|OR Operator2 Operand2 [)] ]

Where OperatorX is one of [ = | <> | != | > | < | >= | <= | ~ | !~ | in ( ]
OperandX is a numeric, date or string value or for the operator 'IN' it is a list of values divided by comma.

 

For instance:

'1'
'>=1'
'>1 AND <=5'
'~ TestStr%'
'!~ TestStr_'
'in (1,2,3,4,5,6) and >100'
'in (Str1,Str2,Str3)'

 

Data row height. 

 

Use RowHeight and RowLines properties to specify the data row height. Full data row height = height in pixels of RowLines + RowHeight. Set RowSizingAllowed to True to enable ability to change row height using mouse at run time. 

To brake a long text in data row to a multiline set Column.WordWrap to True. Text will wrap if row height > one text line. 

 

Title acts as button. 

 

To force title act as button set Column.Title.TitleButton to True. Write an OnTitleBtnClick event to take specific action when the user clicks on title. 

 

Several fields in the dropdown lookup list. 

 

To show several fields in lookup list for lookup fields set Column.LookupDisplayFields to the list of fields.Semicolons should separate multiple field names. Name Column.Field.LookupResultField must be present in LookupDisplayFields list. Lookup list with several fields can be applyed only for lookup fields. 

 

Frozen zone. 

 

To specify the count of right nonscrolling columns set FrozenCols property. 

 

Dropdown calendar. 

 

For TDateField and TDateTimeField fields in-place editor will show dropdown button to show dropdown calendar. Set Column.ButtonStyle to cbsNone to prevent the display of the dropdown button. 

 

Show bitmaps in data cells depending on field values. 

 

To show bitmaps depending on field values need: Fill list of values to Column.KeyList property of field values and set Column.ImageList property to ImageList control that has the bitmap in according index. Set Column.NotInKeyListIndex to index of bitmap that will be shown if field's value does not correspond to any value in KeyList (for instance you can set index of image for Null field value). You are not allowed to edit bitmap column. Use blank and mouse click to set next value from Column.KeyList to the field; Shift-blank and Shift-Mouse click to set previous value from Column.KeyList. Set Column.DblClickNextval to True to change value on mouse double click. 

 

Show bitmaps in titles. 

 

To show bitmap in titles instead of caption use TitleImages property of TDBGridEh and ImageIndex property of TColumnTitleEh 

 

Column with simple lookup values. 

 

You can show another text in column depending on field values. Use KeyList and PickList property. KeyList have to contain a values which kept in the field but PickList in according index have to contain a values to show. Set Column.NotInKeyListIndex to index of text from PickList that will be shown if field value do not contain in KeyList (for instance you can set index of text for Null field value). Set Column.DblClickNextval to True to change value on mouse double click. 

 

Proportional scrollbar. 

 

To show proportional scrollbar for no sequenced dataset (dataset is sequenced if TDataSet.IsSequenced = True) set SumList.VirtualRecords to True to create list of record bookmars. Keep in mind that VirtualRecords will work only for full relationship bookmars dataset (DataSet.ComapreBookmark function has to return > 0 if bookmark1 > bookmark1, = 0 if bookmark1 = bookmark1, < 0 if bookmark1 = bookmark1. TBDEDataSet in most cases support full relationship bookmarks). 

Use VertSctollbar, HorzSctollbar properties to hide and/or track horizontal or vertical scrollbars. 

 

Checkboxes for boolean and noboolean field. 

 

Grid automatically shows checkboxes for boolean field. To show checkboxes for non boolean fields fill first line of KeyList that corresponds to the checked state of the checkbox, second line - non checked state, and set Column.Checkboxes property to True. Line of KeyList can represent more than one value in a semicolon-delimited list of items. 

 

3D and flat look. 

 

Use OptionsEh property to show/hide 3D frame for fixed, frozen, footer and data rows. 

Use Flat property to show grid in flat style. 

 

Draw memo fields. 

 

To draw memo fields as text set DrawMemoText to True. 

 

Increment search 

 

Use dghIncSearch and dghPreferIncSearch values (OptionsEh property) to manipulate increment search in the grid. 

dghIncSearch value allows to do increment search in grid. At run time you can use next key for increment searching: 

 

Ctrl+F 
to begin increment searching. 
Ctrl+Enter 
to search next matching. 
Ctrl+Shift+Enter 
to search prior matching. 

 

If dghIncSearch in OptionsEh and column is read only then grid will set increment search mode automatically on first key press and will set normal mode after 1.5 sec. 

dghPreferIncSearch value determine that the grid will set increment search mode automatically on first key press instead of cell editing.

Copyright (c) 1998-2013. All rights reserved.
What do you think about this topic? Send feedback!