How to Customize All Cell Objects at Once
- 6 minutes to read
The Spreadsheet and Report Designer controls store cell data and cell custom styles in cell objects, usually accessible via the Table View worksheet’s Cells property and the CreateCell function.
Call the CreateCell function to access a specific cell object without any additional checks. If you are using the Cells property to access a specific cell object by its row and column indexes within a worksheet, check whether the cell object exists and create it if necessary:
var
ATableView: TdxSpreadSheetTableView;
ACell: TdxSpreadSheetCell; // A variable for working with a cell object
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
if(ATableView.Cells[0, 0] <> nil) then // If the cell object exists...
ACell := ATableView.Cells[0, 0] // Assigns it to the ACell variable
else // If there is no cell object at the specified position...
ACell := ATableView.CreateCell(0, 0); // Creates a new cell object at the position
While using the CreateCell function is more convenient, the Cells property is useful if you need to work only with initialized/modified worksheet cells without creating additional cell objects. For instance, if you need to apply specific style parameters to all initialized cells within the modified worksheet area, refer to the following code example:
var
ATableView: TdxSpreadSheetTableView;
ACell: TdxSpreadSheetCell;
I, J: Integer; // Cycle counters
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
ATableView.BeginUpdate;
for I := 0 to ATableView.Dimensions.Bottom do // Cycles through the all modified rows within the active worksheet
begin
for J := 0 to ATableView.Dimensions.Right do // Cycles through the all modified columns within the active worksheet
begin
if(ATableView.Cells[I, J] <> nil) then // If the cell object is actually exists...
begin
// Applies the custom background style to the cell
ACell := ATableView.Cells[I, J];
ACell.Style.Brush.BackgroundColor := clHighlight;
ACell.Style.Brush.ForegroundColor := clMoneyGreen;
ACell.Style.Brush.Style = sscfsThinRevDiagonalStrip;
end;
end;
end;
ATableView.EndUpdate;
As a result, the new background style is applied to all initialized cells in the active worksheet:
However, the ExpressSpreadSheet Suite provides a more convenient way to apply the same set of changes to all initialized cells in a worksheet. To use it, invoke either the Table View worksheet’s ForEachCell procedure or the EnumCells procedure of a particular row object, depending on the situation.
The EnumCells and ForEachCell procedures accept an anonymous procedure referring to the cell objects created in a row or worksheet, respectively. In the case of Delphi code, you can simplify the example above by including the cell background customization code within the anonymous procedure implementation:
var
ATableView: TdxSpreadSheetTableView;
ACell: TdxSpreadSheetCell;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
ATableView.ForEachCell(
procedure(ACell: TdxSpreadSheetCell)
begin
// The ACell parameter addresses each valid cell object within the Table View worksheet
ACell.Style.Brush.BackgroundColor = clHighlight;
ACell.Style.Brush.ForegroundColor = clMoneyGreen;
ACell.Style.Brush.Style = sscfsThinRevDiagonalStrip;
end);
Since C++Builder does not support anonymous functions, you need to wrap the following TdxSpreadSheetTableViewForEachCellProc method interface exposed as the Invoke method:
__interface TdxSpreadSheetTableViewForEachCellProc;
typedef System::DelphiInterface<TdxSpreadSheetTableViewForEachCellProc> _di_TdxSpreadSheetTableViewForEachCellProc;
__interface TdxSpreadSheetTableViewForEachCellProc: public System::Interface
{
public:
virtual void __fastcall Invoke(TdxSpreadSheetCell* ACell) = 0;
};
The following C++ code shows a template example that you can use to pass C++ methods as method references to Delphi. Place both the template class and the dummy enumeration type declaration to the project header file:
enum __DummyType {}; // The parameter type used as default
template<
typename InvokeInterface, // Interface with the virtual Invoke method
typename TFunction, // The function type
typename TReturned, // The returned data type
typename TParameter1 = __DummyType> // The returned parameter type
class TMethodReference: public TInterfacedObject, public InvokeInterface
{
private:
TFunction callback;
public:
TMethodReference(TFunction _callback): callback(_callback) {}
HRESULT STDMETHODCALLTYPE QueryInterface(const GUID& riid, void** ppvObject)
{
return TinterfacedObject::QueryInterface(riid, ppvObject);
}
ULONG STDMETHODCALLTYPE AddRef(void)
{
return TinterfacedObject::_AddRef();
}
ULONG STDMETHODCALLTYPE Release(void)
{
return TinterfacedObject::_Release();
}
TReturned __fastcall Invoke(TParameter1 p1)
{
return callback(p1);
}
};
Then, implement the method used as an anonymous procedure, which contains all instructions applied to all initialized cells in a worksheet. The ACell parameter is used as a pointer to all valid cell objects within the Table View worksheet’s Dimensions area:
void CustomizeEachValidCell(TdxSpreadSheetCell* ACell)
{
ACell->Style->Brush->BackgroundColor = clHighlight;
ACell->Style->Brush->ForegroundColor = clMoneyGreen;
ACell->Style->Brush->Style = sscfsThinRevDiagonalStrip;
}
Finally, invoke the CustomizeEachValidCell method:
TdxSpreadSheetTableView* ATableView;
//...
ATableView = dxSpreadSheet1->ActiveSheetAsTable;
// Creates a new instance of the created TMethodReference template class
_di_TdxSpreadSheetTableViewForEachCellProc proc = new
TdxMethodReference<TdxSpreadSheetTableViewForEachCellProc, // The referenced procedure type
void (*)( // The returned value type
TdxSpreadSheetCell*), // The argument type (the pointer to a cell object)
void, // The data type returned by the method
TdxSpreadSheetCell* // Pointer to the addressed cell objects
>(CustomizeEachValidCell); // The referenced method's name
// Invokes the ForEachCell method:
ATableView->ForEachCell(proc);
The second overloaded TdxSpreadSheetTableView.ForEachCell procedure variant allows you to limit the area to which the anonymous procedure’s instructions are applied:
var
ATableView: TdxSpreadSheetTableView;
AArea: TRect;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
// Limits the processed cell objects with the selection area
AArea := ATableView.Selection.Area;
ATableView.ForEachCell(AArea,
procedure(ACell: TdxSpreadSheetCell)
begin
ACell.Style.Brush.BackgroundColor := clHighlight;
ACell.Style.Brush.ForegroundColor := clMoneyGreen;
ACell.Style.Brush.Style := sscfsThinRevDiagonalStrip;
end);
The row object’s EnumCells procedure applies only to cell objects within that row. In all other aspects, it works similarly to the ForEachCell procedure discussed earlier. Note that you need to check whether the row object actually exists within the worksheet:
var
ATableView: TdxSpreadSheetTableView;
ARowNumber: Integer;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
// Use the focused cell's row index to specify the row
ARowNumber := ATableView.Selection.Area.Top;
// If the selected row excutally exits...
if(ATableView.Rows[ARowNumber] <> nil) then
begin
ATableView.Rows[ARowNumber].EnumCells(
procedure(ACell: TdxSpreadSheetCell)
begin
ACell.Style.Brush.BackgroundColor := clHighlight;
ACell.Style.Brush.ForegroundColor := clMoneyGreen;
ACell.Style.Brush.Style := sscfsThinRevDiagonalStrip;
end);
end;
