Forum > FPSpreadsheet
MoveCell functionality
jollytall:
wp,
Is there a MoveCell functionality? I would like to move a cell within a spreadsheet, just like in a GUI a drag and drop. I could not find such a function.
I would like something like either
MyCell.MoveTo(Row, Column); (i.e. a method of a Cell) or
MySheet.MoveCell(FromRow, FromColumn, ToRow, ToColumn); just like MySheet.CopyCell; or
a cut and paste like functionality.
I can simply change the MyCell.Row := NewRow;, but it does not update all the formulas in the sheet pointing to this cell.
Similarly a CopyCell does not work either.
Thanks,
wp:
--- Quote from: jollytall on April 22, 2022, 11:39:07 am ---CopyCell does not work either.
--- End quote ---
What exactly did you do? Look at the attached sample project which demonstrates that even attached data, such as a comment, move along with the copied cell, and formulas are adjusted to point to the same location (in a relative sense).
It is true that a "MoveCell" does not exist, but it can be combined by CopyCell and DeleteCell - again, study the attached project.
jollytall:
Thanks, but I think I was not clear enough. It is not about the formula in the moved cell, but the formulas pointing to the moved cell.
Add one more cell to your sheet in A5 with a formula = A1. If you manually drag and drop in a GUI application A1 to B2 then A5 points to B2. If I use your program then A5 still points to A1. This is what I meant when wrote that CopyCell does not work. Correctly I should have said "neither CopyCell can be used for this purpose". It is logic, that a Copy does not change the formulas pointing to the source of the copy as it is still there then. When you delete the source cell, it cannot remember that in the previous step it was copied, so it is still there, so that is again not a time to update the formulas pointing to it.
jollytall:
Actually, I have just realized, that even the formula in the cell being moved behaves differently when the cell is moved and when it is copied. In case of copy (as your program also shows), the references (unless $ made) in the formula move relative with the copied cell. In case of move, the formula remains the same.
P.S. This is all about plain move. When something is sorted, even Calc/Excel behave "strange". When a cell is moved as a result of the sort, it is not a real move (i.e. formulas point to the same physical cell as before), but more a copy (formulas move relative calculated from the new position of a moved cell). This is not only giving incorrect results, but can also give formula error (e.g. if B3 has a formula = B1 and when sorting by column A, row 3 gets to be row 1, and then the formula would turn into B-1, what does not exist.
On forums they recommend to use $ formulas, but then again, the literal of the formula remains (as in a move of the cell containing the formula) but not taking into account that the formula it points to also moves.
The solution would be to do the sort as many little moves, when both the moving cell would keep its formula and also when the target cell of the formula moves, then the formula is also updated.
I guess it is the same in FPSpreadsheet (I need to test).
wp:
Added a method TsWorksheet TsWorksheet.MoveCell(ACell: PCell; AToRow, AToCol: Cardinal).
In addition to what CopyCell does it also adjusts any formula which points to the initial cell location such that it points to the new location after the move, no matter whether for formula has a relative or absolute reference. The formula is not changed, however, when it points to a cell range (example: when the formula is SUM(A1:B6) and A1 is moved to somewhere else, the formula does not change). The cell at the initial location is deleted completely.
Unlike CopyCells, a formula assigned to a cell to be moved does not change at the new location, as you mentioned in the previous note.
All this is like in Excel (hopefully...).
There's a variety of unit tests which are passed.
Navigation
[0] Message Index
[#] Next page