AA formulas are incorrectly handled

[jollytall]

Wp,

I found another, - I guess, - bug. Take a very simple spreadsheet (made in e.g. OpenOffice Calc) with cells used after column Z. E.g. I have A1 := 1, B1 := 2, etc. upto AC1 := 29. Then in A2 := =+A1, etc. AC2 := =+AC1;
Then run the following program:

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

1. program project1;
2. {$mode objfpc}
3. {$H+}
4. uses
5.   sysutils,
6.   fpsTypes, fpspreadsheet, fpsopendocument;
7.  
8. var
9.   WB : TsWorkbook;
10. begin
11. WB := TsWorkbook.Create;
12. WB.Options := WB.Options + [boReadFormulas];
13. WB.ReadFromFile('test.ods');
14. WB.WriteToFile('test2.ods');
15. WB.Free;
16. end.
17.  

The new file has formulas right upto Z2, but AA2 == +A1, AB2 == +B1 and AC2 == +C1.

I guess it is wrong when the formula is read from the file, but not sure.

Can you have a look at it?

Thanks,

[wp]

Ooops... Fixed in CCR.

[jollytall]

Sorry for my - so-far - non-usage of svn. How can I get the latest version (or what lines shall I change)?

[wp]

Download the zipped snapshot from https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/ and install it manually.

Or load the following files and apply the following modifications:

• source/common/fpexprparser.pas
  • function TsExpressionScanner.DoCellRangeODS: add "val := 0;" at the top
  • in "case C of 'A'..'Z'" replace "val := val*10 + ord(C) - ord('A');" by "val := val*26 + ord(C) - ord('A') + 1;" (in words: multiply by 26 rather than by 10, and add 1 to the expression.
  • in '"case C of 'a'..'z'" replace "val := val*10 + ord(C) - ord('a');" by "val := val*26 + ord(C) - ord('a') + 1;"
  • in "case C of '0'..'9'" replace "FCellRange.Col1 := val;" by "FCellRange.Col1 := val - 1;" (in words: subtract 1 from value)
  • in "case c of '0'..'9'" replace FCellRange.Col2 := val;" by "FCellRange.Col1 := val - 1;"
• source/common/fpsutils.pas
  
  • in the nested function "Scan" of "function ParseCellString(const AStr: String; out ACellRow, ACellCol: Cardinal; out AFlags: TsRelFlags)" add "ACellCol := 0;" at the top.

[jollytall]

Thanks, I think it works, though I could not fully test it due to another error (see next Post).