Your code has an issue in the copy block:
// copy 3 rows of cells
for aRow := 9 to 12 do
for aCol := 0 to 3 do begin
srcCell := ws.FindCell(9, aCol);
cell := ws.FindCell(aRow, aCol);
ws.AddCell(aRow, aCol);
ws.CopyFormula(srcCell, cell);
end;
In Line 5 you try to find the destination cell, then you add a new cell (line 6) and copy the formula to the previously found cell (line 7), but not to the added cell. But what if the destination cell was nil? Then you would have copied the formula to a nil cell. TsWorksheet.CopyFormula detects this case and does not copy anything at all (well, it could even be argued that FPSpreadsheet should raise an exception here...).
But why do you call FindCell at all? You could comment this line out and simple call AddCell and take its return value as destination cell:
// copy 3 rows of cells
for aRow := 9 to 12 do
for aCol := 0 to 3 do begin
srcCell := ws.FindCell(9, aCol);
// cell := ws.FindCell(aRow, aCol);
cell := ws.AddCell(aRow, aCol);
ws.CopyFormula(srcCell, cell);
end;
In this case I have an exception in the expression parser. This is because when you look for the source cell in row 9 which is the first row handled by the loop. In other words, the destination cell already exists, but the code still adds a new cell to the cells tree at a position where already a cell exists. This is something which the AVL_Tree (the data structure working behind the scenes) does not like at all - there is also a comment in the header of TsWorksheetAddCell: "It is not checked if another cell already exists at the same location. This case must be avoided. USE CAREFULLY WITHOUT FindCell (e.g., during reading into empty worksheets)."
Therefore, the correct code is to checks whether the destination cell already exists (FindCell) and to add a new one only if it does not yet exist:
// copy 3 rows of cells
for aRow := 9 to 12 do
for aCol := 0 to 3 do begin
srcCell := ws.FindCell(9, aCol);
cell := ws.FindCell(aRow, aCol);
if cell = nil then
cell := ws.AddCell(aRow, aCol);
ws.CopyFormula(srcCell, cell);
end;
OK - no more crash, but probably still not what you are expecting because the routine copies - nothing...
Again while copying the first row (row 9): a formula cell it copied here to itself. One of the first steps that FPSpreadsheet is doing in CopyFormula, is to delete the formula in the destination cell. But since in this special case source and destination cells are the same, it deletes the formula in the source cell and has nothing left to copy... To fix this I added a check to FPSpreadsheet which exits the copying process prematurely when source and destination cells are the same. If you don't work with the development version of FPSpreadsheet from CCR patch your fpspreadsheet.pas unit as shown here:
procedure TsWorksheet.CopyFormula(AFromCell, AToCell: PCell);
var
srcBook, destBook: TsWorkbook;
srcSheet, destSheet: TsWorksheet;
srcFormula, destFormula: PsFormula;
begin
if (AFromCell = nil) or (AToCell = nil) then
exit;
srcSheet := TsWorksheet(AFromCell^.Worksheet);
destSheet := TsWorksheet(AToCell^.Worksheet);
srcBook := TsWorkbook(srcSheet.Workbook);
destBook := TsWorkbook(destSheet.Workbook);
if (AFromCell = AToCell) and (srcSheet = destSheet) then // <---- ADDED
exit; // <---- ADDED
destSheet.DeleteFormula(AToCell);
if not HasFormula(AFromCell) then
exit;
...
Recent
