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]

CopyCell does not work either.

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.

[jollytall]

Thank you,
You are really a "Hero Member"  .
At my first tests it all works. I will use it more and report should I find anything wrong.

[jollytall]

A bit of bad news. It fails when a cell is moved multiple times with a formula. See the following code:

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

1.    wb := TsWorkbook.Create;
2.   ws := wb.AddWorksheet('test');
3.   ws.WriteFormula(0, 0, 'A2');
4.   ws.WriteNumber(1, 0, 123);
5.   wb.WriteToFile('beforemove.ods', true);
6.   ws.MoveCell(ws.FindCell(1, 0), 0, 0);
7.   wb.WriteToFile('after1move.ods', true);
8.   writeln(ws.ReadFormula(0, 0));
9.   ws.MoveCell(ws.FindCell(0, 0), 0, 1);
10.   wb.WriteToFile('after2moves.ods', true);
11.   writeln(ws.ReadFormula(0, 0));
12.   wb.Free;

First I make a normal worksheet, with a value in A2 and a formula (=A2) in A1. This is OK.
Then I move A2 to A1. This is allowed, since the moved cell overwrites (or to be precise should overwrite) the formula (and anything else) in A1 with A2, what is just a number in this case.
As it can be seen in the writeln line, the formula is still there in A1, and actually it is also updated because the target cell (A2) was moved to a new location. As it happens the target cell's new location is just where it was referenced from (A1), so it almost makes a circular reference in A1, but somehow the formula is lost (or something like that), when it is saved to after1move, and it is still OK if you open it in a standard spreadsheet package (I use Calc, but I guess it is the same in Excel).
However if the cell is moved again, it takes the left-over formula more "seriously" then the value and updates it again. As the cell is moved from A1 to B1 and the formula (incorrectly) was =A1, it becomes =B1, and this is not lost during the WriteToFile and a circular reference error is made (using autocalc on, the error occurs during the second move).

Can you fix it?

[wp]

In the new commit, a circular reference error is created already in the first move. I also added unit tests for the cases that the formula cell is moved to the value cell and vice versa.

[jollytall]

wp, Thanks, but I think you went the wrong way.
The problem was not that it did not raise a circular reference in the first move. That is a totally legit move. The problem was with the fact that a formula (either circular or not) remained in A1 after the move when the moved cell had no formula only a value. It was just a pure coincidence that I noticed it through circular reference.
See the following example:

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

1.   wb := TsWorkbook.Create;
2.   wb.Options := wb.Options + [boAutoCalc];
3.   ws := wb.AddWorksheet('test');
4.   ws.WriteFormula(0, 0, 'A3');
5.   ws.WriteNumber(1, 0, 123);
6.   ws.WriteNumber(2, 0, 234);
7.   wb.WriteToFile('beforemove.ods', true);
8.   ws.MoveCell(ws.FindCell(1, 0), 0, 0);
9.   wb.WriteToFile('after1move.ods', true);
10.   writeln(ws.ReadFormula(0, 0));
11.   ws.MoveCell(ws.FindCell(0, 0), 0, 1);
12.   wb.WriteToFile('after2moves.ods', true);
13.   writeln(ws.ReadFormula(0, 0));
14.   wb.Free;
15.  

Although I move A2 to A1, A1 still has the old formula in it (i.e. = A3) and in this case it remains the active formula even after the first move (and of course the second as well).

I think the solution would be to delete the old content of the target cell before the move, but then it must be sure that other cells pointing to this do not get invalid between the delete and the move. I added DeleteCell before the first move and it apparently solves it, but I am not sure I can see all the implications. I added B2 (=A1) to test it and it seems to be working. If it is indeed a solution then only DeleteCell needed well placed in MoveCell.

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

1.   wb := TsWorkbook.Create;
2.   wb.Options := wb.Options + [boAutoCalc];
3.   ws := wb.AddWorksheet('test');
4.   ws.WriteFormula(0, 0, 'A3');
5.   ws.WriteNumber(1, 0, 123);
6.   ws.WriteNumber(2, 0, 234);
7.   ws.WriteFormula(1,1,'A1'); // <<< Testing how a reference pointing to the destination (first
8.                                           // move) and then to the source (second move) behaves
9.   wb.WriteToFile('beforemove.ods', true);
10.   ws.DeleteCell(ws.FindCell(0,0)); // <<< This is to forget the old formula
11.   ws.MoveCell(ws.FindCell(1, 0), 0, 0);
12.   wb.WriteToFile('after1move.ods', true);
13.   writeln(ws.ReadFormula(0, 0));
14.   ws.MoveCell(ws.FindCell(0, 0), 0, 1);
15.   wb.WriteToFile('after2moves.ods', true);
16.   writeln(ws.ReadFormula(0, 0));
17.  

[wp]

You are right. I had thought I had seen "my" behaviour on Calc yesterday, but now I cannot reproduce this. I modified MoveCell such that the destination cell is erased before doing anything. I also added the case that the destination cell is deleted when the source cell is nil (in this case, the destination cell was left unchanged so far).