Multiline cell editor

[straetch]

A cell in a worksheetgrid has wordwrap set.
If the user wants to edit the cell content, a single-line editor with text is opened in the cell along with the existing text in the cell.
TsWorksheetGrid is derived from the standard CustomGrid, which does not natively support multiline text, so a solution does not seem obvious.
I want to avoid a seperate TsCellEdit.
Is there an neat solution?
My workaround would be to overlay the cell with a TMemo, copy the cell content into the memo when entering the cell and copying back the memo content to the cell on exiting the cell. Or could I overlay the cell with a TsCellEdit instead of a TMemo???

[wp]

Did you try what the grids wiki article tells about a memo editor (http://wiki.lazarus.freepascal.org/Grids_Reference_Page#Example:_How_to_set_a_memo_editor_for_dbgrids)?

Here's an adaption of it to the worksheet grid - it's not 100% perfect, but shows the idea. To test it use a worksheet grid and add a memo component called CellMemo. Turn off the WantReturns of the memo to keep the reaction of the grid on the ENTER key. Add a handler for OnSelectEditor to the grid and for OnEditingDone to the memo:

Code: Pascal  [Select][+][-]

1. procedure TForm1.CellMemoEditingDone(Sender: TObject);
2. begin
3.   with sWorksheetGrid1 do
4.     Cells[Col, Row] := CellMemo.Text;
5. end;
6.  
7. procedure TForm1.sWorksheetGrid1SelectEditor(Sender: TObject;
8.   aCol, aRow: Integer; var Editor: TWinControl);
9. var
10.   R: TRect;
11.   cell: PCell;
12.   r1, r2, c1, c2: Cardinal;
13. begin
14.   with sWorksheetGrid1 do begin
15.     cell := Worksheet.FindCell(GetWorksheetRow(Row), GetWorksheetCol(Col));
16.     if Worksheet.IsMerged(cell) then begin
17.       Worksheet.FindMergedRange(cell, r1,c1,r2,c2);
18.       R := CellRect(GetGridCol(c1), GetGridRow(r1), GetGridCol(c2), GetGridRow(r2));
19.     end else
20.       R := CellRect(Col, Row);
21.     if VarIsNull(Cells[Col, Row]) then
22.       CellMemo.Text := '' else
23.       CellMemo.Text := Cells[Col, Row];
24.   end;
25.   CellMemo.BoundsRect := Bounds(R.Left, R.Top, R.Right - R.Left, R.Bottom - R.Top);
26.   CellMemo.BorderStyle := bsNone;
27.   Editor := CellMemo;
28. end;

Some extra code is added to handle the editor size correctly for merged cells .

[straetch]

Thanks for the code. I used it in my program, together with other changes. I experienced a crash, which I erroneously tought to be unrelated to this function (ref. other thread).
In attachment a small project. Three test cases are described in comments in main.pas .
I used your proposed code.
The problem I experience looks strange to me: the crash comes at the time that the WorksheetStringGrid is programmatically populated (Step 1 button in the project), whereas the user edit of a grid cell comes only later (after Step 2).
The WorksheetStringGrid is also a sort of data-aware component as a dbgrid is.
Using fpspreadsheet from lazarus snapshot 5845, lazarus version 1.6 svn revision 51630, FPC 3.0.0, Windows 10.

[wp]

The problem I experience looks strange to me: the crash comes at the time that the WorksheetStringGrid is programmatically populated (Step 1 button in the project), whereas the user edit of a grid cell comes only later (after Step 2).

But the cell editor is also selected internally.

When I exit DetailWorksheetGridSelectEditor immediately after entering the procedure, the crash is gone. Since the form is not yet visible in this state you can put the "exit" after an "if" statement to keep the behavior of the multiline editor:

Code: Pascal  [Select][+][-]

1. procedure TBeheer1ItemForm.DetailWorksheetGridSelectEditor(Sender: TObject;
2.   aCol, aRow: Integer; var Editor: TWinControl);
3. var
4.   ...
5. begin
6.   if not Visible then
7.     exit;
8.   ...

This fix, however, will not make you very happy because the multiline editor example is not well-integrated into the grid infrastructure, and is very buggy. I am working on adding a multiline editor the TsWorksheetGrid (but I have to decide on whether to keep or replace the old single-line editor...). Since your multiline editor will be obsolete sooner or later, I did not investigate to find out the reason for the strange behavior you observe.

[straetch]

That is a fair answer. It makes no sense to invest in a dead end solution.
In will work with a TsCellEdit for the time being.
Thanks.

[wp]

r5849 supports a multiline string cell editor for the grid. To activate it set the property EditorLineMode to elmMultiline (I left the default at elmSingleLine to keep the old behavior by default). Press CTRL-ENTER during editing to begin a new line.

Please test.

[straetch]

Wow! Great service!
I tested the new function on the test project. It works exactly as expected.
I will now introduce the new version of WorksheetGrid in my main program.
Many thanks.

[straetch]

I added a WorkbookSource and a CellEdit to the test case. CellEdit and WorksheetGrid are linked to the WorkbookSource.
I notice that edits in the grid are propagated to the celledit, but changes to the celledit are not propagated to the grid and are lost when selecting another cell.
Two smaller points:
1. Setting Showheaders to false in the IDE does not "stick". It has to be set programmatically.
2. When option RowSizing is set, it only works when ShowHeaders = true. RowSizing is particularly useful in multi-line editing.
Attached is the modified test case.

A further thought: the GridSelectEditor examples we saw earlier define the editor at cell level. That would make room for other editors in some future development (comboeditor, datepicker, ...). Not that I am personally interested.
But an advantage could be if there were an elmNoLine (or some other name) option that would disable cell editing for lack of a ReadOnly grid cell property (or at least, I didn't find one).

[wp]

(0) Changes made by CellEdit not propagated to the grid: if you turn off the grid option goAlwaysShowEditor and check WantReturns for the CellEdit the normal behavior is back. You will notice now that the predefined large row height will collapse to the text height. This is normal behavior if a row does not store rowheight information, and the defaultrowheight is only used for empty rows. But you can easily turn off row-height adjustment by calling WriteRowHeight(i, 20, suMillimeters) while you loop through all rows (i) in TBeheer1ItemForm.FormCreate.

(1) ShowHeaders=false is lost when the Workbooksource links a worksheet to the grid because the worksheet has its own setting for this (Option soShowHeaders). In an early version of the grid ShowHeaders has been introduced as a way to turn off the headers (like FixedRows = 0), but later it turned out that it should more tightly follow the values stored by the worksheet - the idea was that the grid is just a visual representation of the worksheet, but now the grid is turning more and more into a TStringGrid replacement. I still have not made a decision how to bring these conflicting requirements together.

(2) RowSizing not working when ShowHeaders is false: Yes - row and col sizing only work within the fixed cells, this is inherited from TCustomGrid, and if you turn off FixedCells by turning off Headers you cannot resize row heights any more. This is the same behavior as with Excel. However, if you use FrozenCols, FixedCells are back because FrozenCols and FrozenRows are nothing else than custom-drawn FixedCells. But this has other drawbacks: you cannot edit fixed cells... As a workaround you leave the first column empty and make it frozen (FrozenCols := 1). To avoid that this dummy column consumes too much space, you can reduce its width to - say - 2 mm (WriteColWidth(0, 2, suMillimeters)). One visual annoyance may be the black line that the grid draws along the right border of the last frozen column. To prevent this I added a new property FrozenBorderPen where you can set the Pen.Style to psClear now.

(3) An option elmNoLine to disable the cell editor: No, this is already possible: just leave the grid option goEditing at its default (false). Then the grid cannot be edited by user input.

[straetch]

(0) As indicated, in the test project I made these changes: grid goAlwaysShowEditor set to false, celledit WantReturns set true, WriteRowHeight(i,20,suMillimeters) in the first loop.
(0.1) The second column disappears! Commenting out the WriteRowHeight brings the column back. Strange
(0.2) Clicking in the celledit makes the contents of the cell in the grid disappear. Excel keeps the characters in the grid and in the celledit in synch. I would not go that far, but it would be preferred if the content of the grid stays intact until editing of the celledit is done.
(03) Setting WantReturns on the celledit makes that entering Return finishes editing and jumps to another cell. Ctr-Enter is needed to insert a line break. Shouldn't this be the other way around (as the api says)?

(1) If this can assist you in your strategy decision, FWIW. From my user's point of view, I use fpspreadsheet's visual presentation in two ways:
(1.1) Mimic of an Excel worksheet: For entry of data by end-users, who are familiar with Excel. In that case  ShowHeaders = true. The result of the user input is the worksheet.
(1.2) Use WorksheetGrid as a superior TStringGrid (simple setting of colors, etc. without seperate OnDrawCell event handling; now we have a multiline editor; many other advantages - thank you for this). The worksheet is just the backend store of the information and is not necessarily directly accessed. ShowHeaders = false.
I am not particularly interested in formulae because Free Pascal is far more superior in manipulating data than any spreadsheet formula can ever be. Except for e.g. financial calculations, but then one should use Excel itself.
Of course this is only my personal perspective. Other people might have their own uses and wishes on fpspreadsheet.

(2) I will experiment with the workaround for RowSizing. The dummy column in the grid brings also a dummy column in the worksheet. As indicated in 1.2 above this is not a problem for me provided I only access the data at the grid level.

(3) Indeed the entire grid can be disabled for user editing. But I am interested in disabling individual cells. End-users are only allowed write access to part of the information in the grid. Now I solve this by an On Validate event handler, setting NewValue := OldValue for the protected cells. Unfortunately this setting and resetting of the content of the cell sometimes fails and then the info is lost. I would be better to make such cell "ReadOnly". The selection of a "don't touch" celleditor might do the job.

(4) The event handler GridEditingDone in Beheer1Item does an UpdateRowHeights for each row. It has no effect. This seems consistent with your explanation in (0).

[wp]

(0) Could you re-post your current demo? There were some changes, and I don't know if we are still talking of the same program in every detail.

(2) Being a fixed column the added frozen column cannot be altered by the user (at least at the moment - I would like to make the frozen columns editable as well, just like in Excel).

(3) In order to inhibit editing of particular cells, you should activate worksheet protection. This feature of the worksheet is not yet propagated to the grid. It consists of two steps:
- Enable worksheet protection: Grid.Worksheet.Protect(true) --> This makes all cells readonly.
- In order to open the cell at some row and column again for user input you must call Grid.Worksheet.WriteCellProtection(row, column, []) which removes the flag that the cell is locked.
See http://wiki.lazarus.freepascal.org/FPSpreadsheet#Protection for more details.

(4) The GridEditingDone event handler of my old demo is not needed any more.

[straetch]

Attached the latest demo project. I removed some elements which are no longer relevant and changed to some simpler names.
I repeat the observations from previous post as far as relevant.

(0) As indicated in your post I made these changes: grid goAlwaysShowEditor set to false, celledit WantReturns set true, WriteRowHeight(i,20,suMillimeters) in the first loop, but not yet the frozen column.
(0.1) The second column disappears! Commenting out the WriteRowHeight brings the second column back. Strange.
(0.2) Clicking in the celledit makes the contents of the cell in the grid disappear. Excel keeps the characters in the grid and in the celledit in synch. I would not go that far, but it would be preferred if the content of the grid stays intact until editing of the celledit is done.
(03) Setting WantReturns on the celledit makes that entering Return finishes editing and jumps to another cell. Ctr-Enter is needed to insert a line break. Shouldn't this be the other way around (as the api says)?

(1) If this can assist you in your strategy decision, FWIW. From my user's point of view, I use fpspreadsheet's visual presentation in two ways:
(1.1) Mimic of an Excel worksheet: For entry of data by end-users, who are familiar with Excel. In that case  ShowHeaders = true. The result of the user input is the worksheet.
(1.2) Use WorksheetGrid as a superior TStringGrid (simple setting of colors, etc. without seperate OnDrawCell event handling; now we have a multiline editor; many other advantages - thank you for this). The worksheet is just the backend store of the information and is not necessarily directly accessed. ShowHeaders = false.
I am not particularly interested in formulae because Free Pascal is far more superior in manipulating data than any spreadsheet formula can ever be. Except for e.g. financial calculations, but then one should use Excel itself.
Of course this is only my personal perspective. Other people might have their own uses and wishes on fpspreadsheet.

(2) I will experiment with the workaround for RowSizing. The dummy column in the grid brings also a dummy column in the worksheet. As indicated in 1.2 above this is not a problem for me, provided I only access the data at the grid level. Here, the frozen column is dummy and will not be edited. I understand, in general, frozen columns cannot be edited for the time being.

(3) Thanks for the instruction to inhibit individual cell editing.

(4) Before in this discussion, we had a EditingDone handler for the CellMemo.
This is a new handler I made for the WorksheetGrid.
I introduced it to have the correct row heights in the worksheetgrid after editing in the grid or the celledit.
The event handler GridEditingDone in Beheer1Item does an UpdateRowHeights for each row. It has no effect. This seems consistent with your explanation in (0).

[wp]

(0.1) The second column disappears

I found a bug in the column count calculation if the grid's AutoExpand flags do not contain the flag aeDefault. I hope the fix has no sideeffects because I had to touch this location already several times...

BTW, there's another bug in your code:

Code: Pascal  [Select][+][-]

1. procedure TBeheer1ItemForm.FormCreate(Sender: TObject);
2. begin
3. ...
4.     WriteColWidth(0,30,suChars);
5.     WriteColWidth(1,30,suChars); // <--- 1st parameter must be 1 instead of 0
6.  

(0.2) Clicking in the celledit makes the contents of the cell in the grid disappear. Excel keeps the characters in the grid and in the celledit in synch. I would not go that far, but it would be preferred if the content of the grid stays intact until editing of the celledit is done.

I don't quite understand... When you click into a cell and open the cell editor, all the cell content is selected, it should not disappear in this state - I've never seen this. However, it is standard behavior of the Lazarus (and Delphi) Edit controls that the first character entered replaces the edit text if the entire text is selected. If users don't want to overwrite the current cell content, but, e.g. only add some characters, they should press the right arrow key - this unselects the edit text and moves the cursor to the end of the text

(03) Setting WantReturns on the celledit makes that entering Return finishes editing and jumps to another cell. Ctr-Enter is needed to insert a line break. Shouldn't this be the other way around (as the api says)?

Which api?

In Excel (and in the Lazarus StringGrid), if you press ENTER the active cell moves either horizontally or vetically; in the Lazarus StringGrid the direction can be defined by the AutoAdvance property (also in Excel, somewhere in its settings). The WorksheetGrid has an intermediate step opening the cell editor, i.e. you need ENTER twice to advance to the next cell.

In Excel the special key to add an manual line break into a cell text is ALT+ENTER. This is different in the Lazarus grid where CTRL+ENTER is needed.

(3) Indeed the entire grid can be disabled for user editing. But I am interested in disabling individual cells. End-users are only allowed write access to part of the information in the grid. Now I solve this by an On Validate event handler, setting NewValue := OldValue for the protected cells.

As I wrote in the previous post I would not do it like that because cell protection is built into the worksheet and is respected by the grid. The concept is maybe a bit difficult to understand, but Excel and LibreOffice Calc do it in the same way: At first you lock the entire worksheet, then you unlock those cells which users are allowed to edit. This is much more efficient than the opposite case of keeping the sheet unlocked and locking the readonly cells because then the user still can enter data in empty cells which don't have a data record to store the "locked" information.

(4) The event handler GridEditingDone in Beheer1Item does an UpdateRowHeights for each row. It has no effect. This seems consistent with your explanation in (0).

Sure. But you must remove this event handler because it will make the row heights shrink to the height of the text entered. If you want this then you still don't need an event handler because the grid will shrink the row height automatically. But you must remove the WriteRowHeight(i,20,suMillimeters) in this case because this forces the row height to the specified value, regardless of whether there is cell content or not, and regardless of the text height in the cells.

[straetch]

I found a bug in the column count calculation if the grid's AutoExpand flags do not contain the flag aeDefault. I hope the fix has no sideeffects because I had to touch this location already several times...

I will test it soon.

    (0.2) Clicking in the celledit makes the contents of the cell in the grid disappear. Excel keeps the characters in the grid and in the celledit in synch. I would not go that far, but it would be preferred if the content of the grid stays intact until editing of the celledit is done.

I don't quite understand... When you click into a cell and open the cell editor, all the cell content is selected, it should not disappear in this state -

I think this is misunderstood: a cell contains data, displayed in the grid. When clicking in the CellEdit, the cell content display in the WorksheetGrid is gone.

    (03) Setting WantReturns on the celledit makes that entering Return finishes editing and jumps to another cell. Ctr-Enter is needed to insert a line break. Shouldn't this be the other way around (as the api says)?

Which api?

When sCellEdit1 is selected in the IDE object inspector, select WantReturns, you get following explanation:

Allows the user to insert Return characters (line breaks) into the text.
The Enter key typically is used to press the default button in a form, so that it cannot be used to add line breaks into the text.
Setting WantReturns to True allows one to enter line breaks, when the control has the focus.
Even if WantReturns is False, CTRL-Enter inserts an line break.
See also TCustomMemo.WantTabs
Package LCLBase

[wp]

I think this is misunderstood: a cell contains data, displayed in the grid. When clicking in the CellEdit, the cell content display in the WorksheetGrid is gone.

If you remove the line with "AutoExpand := AutoExpand - [aeDefault]" the grid cell will not be erased when you click into the CellEdit. It did a lot of searching, but at the moment I am lost and do not understand why this is happening.

But BTW, the flag aeDefault is not needed in your case anyway. It is needed when loading files that occupy only a few cells and you want the grid to expand to get a "spreadsheet" look & feel. In your case, setting the ColCount should be enough.