TAdvSpreadGrid Tutorial: Advanced Data Filtering and Formatting

TAdvSpreadGrid Tutorial: Advanced Data Filtering and Formatting

Delphi and C++Builder developers often require grid components that handle heavy data loads while providing an Excel-like user experience. The TAdvSpreadGrid component, part of the TMS VCL UI Pack, fulfills this need by extending standard grid functionalities with advanced math formulas, cell formatting, and data filtering capabilities.

This tutorial demonstrates how to implement advanced data filtering and cell formatting in TAdvSpreadGrid to build a dynamic, user-friendly data management interface. 1. Setting Up the Grid Component

Before implementing advanced features, ensure your grid is correctly configured on your VCL Form. Drop a TAdvSpreadGrid component onto your form and set up the base properties to enable filtering and formatting. Key Initial Properties GridLines: Set to glBoth for clear visual separation.

FilterDropDown: Enable this to automatically show Excel-like filter dropdown arrows in fixed header cells.

PageMode: Set to False if you need to perform complex, multi-cell formula calculations across the entire dataset in memory.

procedure TForm1.FormCreate(Sender: TObject); begin // Configure basic grid properties AdvSpreadGrid1.ColCount := 6; AdvSpreadGrid1.RowCount := 20; AdvSpreadGrid1.FixedRows := 1; // Enable Excel-style filtering dropdowns in headers AdvSpreadGrid1.FilterDropDown.ColumnWidth := True; AdvSpreadGrid1.FilterDropDownAuto := True; // Populate sample data PopulateSampleData; end; Use code with caution. 2. Implementing Advanced Data Filtering

TAdvSpreadGrid provides two primary methods for filtering data: programmatic execution using the Filter collection and user-driven filtering via built-in column dropdowns. Programmatic Multi-Column Filtering

You can filter data behind the scenes based on complex criteria. The component allows you to stack multiple conditions using logical operators.

procedure TForm1.ApplyAdvancedFilterClick(Sender: TObject); begin AdvSpreadGrid1.Columns[1].Filter := ‘>100’; // Column 1: Values greater than 100 AdvSpreadGrid1.Columns[2].Filter := ‘Active’; // Column 2: Exact string match AdvSpreadGrid1.FilterActive := True; // Execute the filter criteria end; Use code with caution. Supported Filter Operators

The filtering engine natively understands several logical evaluation formats:

Numeric Comparisons: >, <, >=, <=, =100, 10..50 (range evaluation) Wildcards: text (contains), text* (starts with) Logical OR: ValueA | ValueB Clearing Filters

To instantly restore the complete dataset visibility, disable the active state and clear the columns condition cache.

procedure TForm1.ClearFilterClick(Sender: TObject); begin AdvSpreadGrid1.FilterActive := False; AdvSpreadGrid1.ClearFilters; end; Use code with caution. 3. Conditional and Advanced Formatting

Transform raw information into scannable data by applying conditional cell properties. You can modify background colors, font styles, and text alignment based on data boundaries. Dynamic Row and Cell Formatting

Utilize the OnGetCellColor event to evaluate data at runtime and inject custom UI modifications seamlessly without hardcoding cell properties.

procedure TForm1.AdvSpreadGrid1GetCellColor(Sender: TObject; ARow, ACol: Integer; AState: TGridDrawState; ABrush: TBrush; AFont: TFont); var CellVal: Double; begin // Skip fixed header rows to protect layout styles if ARow < AdvSpreadGrid1.FixedRows then Exit; // Target specific column for numeric evaluation (e.g., Column 3: Profit Margin) if ACol = 3 then begin if TryStrToFloat(AdvSpreadGrid1.Cells[ACol, ARow], CellVal) then begin if CellVal < 0 then begin ABrush.Color := clWebMistyRose; // Soft red background for negative values AFont.Color := clRed; AFont.Style := [fsBold]; end else if CellVal > 500 then begin ABrush.Color := clWebHoneydew; // Soft green background for high performance AFont.Color := clDarkGreen; end; end; end; end; Use code with caution. Applying Content-Specific Formats

Formatting individual columns to display currencies, percentages, or precise date formats maintains grid cleanliness.

procedure TForm1.FormatGridColumns; begin // Format Column 3 as Currency (\(1,234.56) AdvSpreadGrid1.ColFormat[3] := '\)#,##0.00’; // Format Column 4 as Short Date AdvSpreadGrid1.ColFormat[4] := ‘dd/mm/yyyy’; end; Use code with caution. 4. Best Practices for High Performance

When managing tens of thousands of rows with combined filtering and background formatting routines, use these optimization rules:

Use BeginUpdate and EndUpdate: Always wrap data load, sort, or manual filter sequences inside update blocks to prevent unnecessary screen redraw cycles.

Avoid Hardcoded Cell Colors: Do not manually assign colors to individual cells inside large loops using AdvSpreadGrid1.Colors[C,R]. Use the OnGetCellColor event instead to calculate styling parameters only when a cell becomes visible on the screen.

Keep Calculations Efficient: If your grid contains heavy cell formulas (e.g., SUM, AVERAGE), toggle AutoCalc := False before filtering data, then invoke AdvSpreadGrid1.Recalc manually when filtering finishes.

To help refine this implementation, please provide a few more details:

What specific version of Delphi or TMS VCL UI Pack are you targeting?

What type of data sources (e.g., TDataset, JSON arrays, CSV strings) are you using to feed the grid?