Recent

Author Topic: AA formulas are incorrectly handled  (Read 469 times)

jollytall

  • Full Member
  • ***
  • Posts: 213
AA formulas are incorrectly handled
« on: April 08, 2022, 11:49:30 am »
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

  • Hero Member
  • *****
  • Posts: 10030
Re: AA formulas are incorrectly handled
« Reply #1 on: April 08, 2022, 12:57:39 pm »
Ooops... Fixed in CCR.

jollytall

  • Full Member
  • ***
  • Posts: 213
Re: AA formulas are incorrectly handled
« Reply #2 on: April 08, 2022, 03:58:08 pm »
Sorry for my - so-far - non-usage of svn. How can I get the latest version (or what lines shall I change)?

wp

  • Hero Member
  • *****
  • Posts: 10030
Re: AA formulas are incorrectly handled
« Reply #3 on: April 08, 2022, 04:28:55 pm »
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

  • Full Member
  • ***
  • Posts: 213
Re: AA formulas are incorrectly handled
« Reply #4 on: April 08, 2022, 06:15:49 pm »
Thanks, I think it works, though I could not fully test it due to another error (see next Post).

 

TinyPortal © 2005-2018