CopyColumns and CopyCells not update the formulas reference

[flls]

I have a template excel sheet with the structure of a calculation of daily costs. This worksheet has a hidden column that contains only formulas that are later copied for daily columns.

I open the worksheet model with TXLSSpreadSheet component and then copy them hidden column for each day that I am calculating the cost.

In version 4 when I copied the formula to another column, the references of which were updated according to the new location. Ex: SUM (F1: F10) when copied to the "H" column was as SUM (H1: H10). This was correct.

In version 5 when I copy the formula SUM (F1: F10) to the "H" column, their references are not updated, the formula remains SUM (F1: F10). Why?

I've tried both methods of copy below, works in version 4, but version 5 does not work. Is it a bug?

Method 1:

Code: Select all

Plan3.XLS.CopyColumns(0, rec.TopLeft.X, rec.TopLeft.X, 0, point.X)

Method 2:

Code: Select all

Plan3.XLS.Sheets[0].CopyCells(rec.TopLeft.X, 0, rec.TopLeft.X, rec.BottomRight.Y, point.X, 0, CopyAllCells + [ccoAdjustCells, ccoForceAdjust]);

XLSReadWriteII V5.20.02
XLSSpreadSheet V2.00.00

PS:
Attached is a spreadsheet model that I use as a base. Hidden columns "F" and "G" are copied to the daily columns. Note that the formulas in columns "H: AF" are identical formulas of the "F" column, the references were not updated.
(https://www.mediafire.com/?yf8d5npep3d1vkd)

[larsa]

Hello

Please try the latest update.

[flls]

Hello

Please try the latest update.

Unfortunately the problem persists in the new version.

[flls]

I installed version 5.20.18a but still with the same error when copying cells that contain formulas.

It's hard for me to explain because my native language is Portuguese and not English, but I will try to demonstrate using your sample application called "CopyMoveDelete".

1) Open the sample worksheet "MoveCopyTest.xlsx" with Excel itself;
2) Change all cells in the column "B" that contains the formula "=A1*10" ... "=A25*10";
3) Change all cells in the column "C" that contains the formula "=B1*10" ... "=B25*10";
4) Change all cells in the column "D" that contains the formula "=C1*10" ... "=C25*10";
5) Save the spreadsheet and exit Excel;
6) Run the demo application SampleCopyMoveDelete.exe;
7) In the "Read" field enter the worksheet "MoveCopyTest.xlsx";
8 ) In "Write" field enter the worksheet "MoveCopyTest2.xlsx" for example;
9) Click the "Read"
10) Click on the "Copy, Move and Delete cells" button;
11) Click the "Write" button
12) Click the "Close" button;
13) Open the spreadsheet "MoveCopyTest2.xlsx" in Excel;
14) Select the "Sheet2" tab and place the cursor in cell "A1". Note that the formula displays "=C1*10" and the cell "A1" appears the value "1000". However, the cell "C1" is empty and the result of the formula should return "0". The same applies to all other cells of the column "A".
15) Select the "Sheet1" tab and place the cursor in cell "H3". Note that the formula displays "= G3 * 10" and the cell "H3" appear the value "30". However, the cell "G3" is empty and the result of the formula should return "0". The same applies to "H4: H27" cells.

Conclusion, there is an error in updating references of formulas when copying / moving cells.

I hope I have succeeded in demonstrating that the error occurs when copying and moving cells with formulas. The Delphi version I'm using is the XE5.

Thank you.

[flls]

I noticed one thing analyzing the code of your example "CopyMoveDelete".

If I have in cell "B1" to the following formula: "=A1*10"

When I copy the cell "B1" using the command: XLS.CopyCells(0,1,0,1,0,0,2,0); // Copy B1:B1 from Sheet0 to C1
the cell "C1" formula is "=A1*10", ie, the formula is copied as text and its reference is not updated in cell "C1" which should contain the value "=B1*10".



When I copy the cell "B1" using the command: XLS[0].CopyCells(1,0,1,0,2,0); // Copy B1:B1 to C1
the cell "C1" formula is "=B1*10", but the result of it is the same cell "B1", ie, it updates the formula but the result of it is wrong.



So we have two CopyCells methods are generating different results.

PS: Confirmed. The XLSReadWrite when copying formulas from .xls spreadsheets (Excel97) always copies the formulas as text and never updates the references. When copying formulas from .xlsx spreadsheets (Excel2007) happens the problems listed above.

[flls]

SOLVED the problem for Excel2007 spreadsheets (.xlsx)

For Excel2007 spreadsheets just put the "XLS.CompileFormulas" command after the "XLS.Read" (open the spreadsheet) command or before the "XLS.Write" (save the spreadsheet) command.

Excel97 spreadsheets and earlier remain with the same problem.

We really need a more complete product documentation, in addition to the html folder of help do not help much.