Manipulate Cell Contents
- 9 minutes to read
The ExpressSpreadSheet provides two ways to enter data into cells: either manually or programmatically. If the ExpressSpreadSheet control has focus and none of the cells are being edited, the text entered by an end-user is written into the active cell, erasing its previous content when the first symbol is entered. In order to modify a cell value without overwriting its contents, an end-user should double-click the desired cell to activate the cell editor.
In order to enter a cell value programmatically, you should use one of the properties of the TdxSpreadSheetCell class, such as AsBoolean, AsCurrency, AsDateTime, etc. These properties enable you to enter values into a cell in the respective format. This class also provides the SetText method used to enter a text string or a formula into a cell.
Refer to one of the following subtopics for more information:
Entering a Text Value or Formula into a Cell.
Entering Date and Time Values into a Cell.
End-user Cell Editing Options.
Entering a Text Value or Formula into a Cell
When entering a text value either manually or in code, the input value is stored within the AsString property and the DataType property is automatically set to cdtString.
You can use the AsString property to get or set a text string that is stored within a cell. This text string stores the displayed value of the cell. In addition to the AsString property, the TdxSpreadSheetCell provides the SetText method, which can also be used for entering a text string or a formula as a cell value.
Let’s develop a simple report table to get acquainted with the SetText method. Assigning a text string or a formula to a cell is performed in the SetCellText function. You must provide method arguments, including the cell coordinates, the worksheet which contains the required cell, the text string for the cell, and a Boolean value showing whether the text string you provided should be parsed as formula.
procedure SetCellText(ASheet: TdxSpreadSheetCustomView; ARow, ACol: Integer; ATextData: string; isFormula: Boolean);
var
ACell: TdxSpreadSheetCell;
begin
ACell := TdxSpreadSheetTableView(ASheet).CreateCell(ARow, ACol);
ACell.SetText(ATextData, isFormula);
end;
The following code example assigns text captions to row and column headers of a report table.
var
ASheet: TdxSpreadSheetCustomView;
begin
ASheet := dxSpreadSheet1.ActiveSheet;
SetCellText(ASheet, 0, 0, 'Division A', False); //The text for A1
SetCellText(ASheet, 0, 1, 'Product 1', False); //The text for B1
SetCellText(ASheet, 0, 2, 'Product 2', False); //The text for C1
SetCellText(ASheet, 0, 3, 'Product 3', False); //The text for D1
SetCellText(ASheet, 0, 4, 'Total', False); //The text for E1
SetCellText(ASheet, 1, 0, 'East', False); //The text for A2
SetCellText(ASheet, 2, 0, 'West', False); //The text for A3
SetCellText(ASheet, 3, 0, 'Total', False); //The text for A4
end;
The results of execution of this code are displayed in the following image (cell ranges A1:A4, B1:E1 receive their values via the call of the SetText method.
Then, you can fill the range B2:D3 with meaningful data. Assume that data represents a quantity of goods sold by Division A for a specific period of time. The following code serves as an example which illustrates how to populate the mentioned cell range with random numbers. The entered data will be used for total amount calculation.
var
I, J: Integer;
AValue: string;
ASheet: TdxSpreadSheetCustomView;
//...
ASheet := dxSpreadSheet1.ActiveSheet;
//...
Randomize;
for I := 1 to 2 do // the I variable specifies the row index
for J := 1 to 3 do // the J variable specifies the column index
begin
AValue := IntToStr(Random(100));
SetCellText(ASheet, I, J, AValue, False);
end;
The image below displays the workbook state after the code execution.
And now you can use the SetText method to enter formulas which will calculate the total amount of each product and the total amount of all products sold by the Division A department for a certain period of time. Thus cells B4, C4, and D4 will contain formulas that return total quantities of Product 1, Product 2, and Product 3 correspondingly and cell E4 will contain a formula calculating the total amount of all products sold.
The following code example demonstrates how to enter formulas at runtime.
var
ASheet: TdxSpreadSheetCustomView;
//...
ASheet := dxSpreadSheet1.ActiveSheet;
//...
SetCellText(ASheet, 3, 1, '=Sum(B2:B3)', True); // The formula for B4
SetCellText(ASheet, 3, 2, '=Sum(C2:C3)', True); // The formula for C4
SetCellText(ASheet, 3, 3, '=Sum(D2:D3)', True); // The formula for D4
SetCellText(ASheet, 3, 4, '=Sum(B4:D4)', True); // The formula for E4
The final look of the workbook containing the report table is displayed on the following image.
Entering Date and Time Values into a Cell
To enter date/time values into a cell you should use the AsDateTime property. The DataType property is automatically set to cdtDateTime.
Since the AsDateTime property is of the TDateTime type, the ExpressSpreadSheet control treats dates and time as numbers. Dates are stored as sequential numbers known as serial values. Time is stored as decimal fractions because time is considered to be a portion of a day. Dates and time are numeric values, and therefore, are subject to addition, subtraction and other calculations.
In order to illustrate the setting of the AsDateTime property in code, let’s add the current date and time value to the above-mentioned report table example. The following code example inserts the current date and time into the C5 cell. You can format a cell value by assigning one of the predefined formats via the Style.DataFormat.FormatCodeID property.
// Assign a current date and time to the C5 cell in 'm/d/yy h:mm' format
var
ASheet: TdxSpreadSheetCustomView;
ACell: TdxSpreadSheetCell;
//...
ASheet := dxSpreadSheet1.ActiveSheet;
ACell := TdxSpreadSheetTableView(ASheet).CreateCell(4, 2);
ACell.Style.DataFormat.FormatCodeID := $16; // sets date and time format as 'm/d/yy h:mm' for C5
ACell.AsDateTime := Now; // sets both current date and time for C5
The result of the execution of the code example is displayed on the following image.
End-User Cell Editing Options
The ExpressSpreadSheet control provides an in-place editor to enter and modify data in worksheet cells. The active cell editor allows typing in either a value of any type, or a formula expression, including references to cells other than the current cell. Unlike simple data, formulas and references begin with the equals sign (=) and are recognized automatically upon deactivation of an in-place editor, provided that the entry was correct.
End-users need to activate an in-place cell editor before entering new data or changing an existing cell value. In order to enter data into an empty cell or quickly replace the current cell value, an end-user can start typing in the focused cell. The F2 key simply activates the focused cell’s in-place editor, allowing users to edit the current value. Finally, a double-click on any worksheet cell focuses it and activates its in-place editor.
In order to either apply or discard changes made within an in-place editor, an end-user needs to deactivate it. The following table lists the available cell editor deactivation options:
| End-User Action | Cell Value Status | Cell Focus Status |
|---|---|---|
| Pressing the Esc key | All changes made to the cell value by using an in-place editor, are discarded. | The edited cell remains focused. |
| Clicking a cell outside the current one | The current value of an in-place editor is applied as the new cell value. | The focus moves to a clicked cell. |
| Pressing the Enter key | The current value of an in-place editor is applied as the new cell value. | The focus moves to the next cell within the current column. |
| Pressing the Tab key | The current value of an in-place editor is applied as the new cell value. | The focus moves to the next cell within the current row. |
| Pressing the Enter key while holding down the Shift key | The current value of an in-place editor is applied as the new cell value. | The focus moves to the previous cell within the current column. |
| Pressing the Tab key while holding down the Shift key | The current value of an in-place editor is applied as the new cell value. | The focus moves to the previous cell within the current row. |
To change the cell value’s data type or customize the cell’s style settings, end-users can invoke the Format Cells dialog window by clicking the respective item within a cell’s context menu.