Bugs. Range is not copied correctly

[veb86]

Hello! The bug is as follows:
When copying cells that contain a range of type SUM($A$1:A1), the range is broken; the output is SUM($A$1), but should be SUM($A$1:A2).
If we write: SUM($A$1:A2), we get SUM($A$1:A2), but it should have been SUM($A$1:A3).

The rules for copying ranges have been violated. Try copying the formulas into Excel yourself and you will see the difference in results

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

1.     b.Options := [boReadFormulas];
2.     b.ReadFromFile('d:\test1\start.xlsx', sfOOXML);
3.     sh := b.GetFirstWorksheet;  
4.     sh.CopyCell(0,1,1,1,b.GetFirstWorksheet);
5.     sh.CopyCell(0,2,1,2,b.GetFirstWorksheet);
6.     sh.CopyCell(0,3,1,3,b.GetFirstWorksheet);    
7.     sh.CalcFormulas;
8.     b.WriteToFile('d:\test1\res.xlsx', sfOOXML, true);  
9.  

[wp]

Please test the new commit in ccr.

[veb86]

Please test the new commit in ccr.

I immediately see the bug.
Cell C1 - SUM($A$1:A1), after saving it became SUM($A$1)

In the XML file (4.xlsx) the entry for this cell is as follows:
<c r="C1"><f>SUM($A$1:A1)</f><v>1</v></c>

This bug has no problems in working correctly, but this bug would be better fixed. It can ruin an existing Excel file and the ability to correctly copy cells will disappear.

I'll keep testing. I'll prepare my code for larger calculations soon.

Thank you very much for fixing the bugs!!!

[wp]

Found code in regeneration of string formula by parser which collapses a cell range to a single cell when both range ends are equal (A1:A1 --> A1). Fix might have side-effects somewhere, unit tests do not detect anything, though.

[Zvoni]

Found code in regeneration of string formula by parser which collapses a cell range to a single cell when both range ends are equal (A1:A1 --> A1). Fix might have side-effects somewhere, unit tests do not detect anything, though.

Having not tested anything: Did you account for a Range like "A:A" (Whole column A)?
and in TS special case: Range("$A$1:A1") <> Range("A1:A1")

[veb86]

Found code in regeneration of string formula by parser which collapses a cell range to a single cell when both range ends are equal (A1:A1 --> A1). Fix might have side-effects somewhere, unit tests do not detect anything, though.

I'll keep it on mind.
In any case, when bugs are discovered, I will write about it

[wp]

I don't know ATM how difficult it is to extend the formula parser so that it can deal with a column or row range alone, but I don't have plans to implement this. It was never the intention for fpspreadsheet to be able to emulate all of Excel/Calc.

[wp]

I'll keep testing.

It would be helpful if you could add your tests to the fpspreadsheet unit tests in folder unit-tests/common of the fpspreadsheet installation. Even (or better: in particular) trivial tests are welcome. The cell-copy tests are in unit copytests. I took the freedom to add your recent range tests (procedure CopyFormulasWithAbsRelRanges). Run application spreadtestgui (spreadtestcli should work as well, but I do not update it regularly). Send patch files or the test procedures ready to copy into the test suite.

[veb86]

It would be helpful if you could add your tests to the fpspreadsheet unit tests in folder unit-tests/common of the fpspreadsheet installation. Even (or better: in particular) trivial tests are welcome. The cell-copy tests are in unit copytests. I took the freedom to add your recent range tests (procedure CopyFormulasWithAbsRelRanges). Run application spreadtestgui (spreadtestcli should work as well, but I do not update it regularly). Send patch files or the test procedures ready to copy into the test suite.

Next time I'll try adding a test, but based on your commit it's not easy.

[wp]

Don't try too hard, it's good already when you post here your code and test files like before.