Problem with RPN formulas

[grandehombre]

I am using Lazarus v1.0.12  i386-win32-win32/win64,  FPC v2.6.2, svn42478 on Win7-64.
I can't tell what the fpspreadsheet version is but it is from after July 3rd, when I raised the issue of incorrect file format (http://forum.lazarus.freepascal.org/index.php/topic,21389.15.html)

I am creating an XLS (excel5) spreadsheet and am having trouble with inserting a SUM formula.
The spreadsheet already contains formulas of the form ('=A1+B1', in the corresponding RPN format).
They all work.
Opening the file in LibreOffice results in an ERR520 error. It shows '=' in the cell, whereas I expect to see something like '=SUM(R[-20]C:R[-2]C)' or some such.

Opening the file in Excel results in an error (right after the file is opened) of 'File error. Data may have been lost'.
Pressing Ok continues and shows the spreadsheet, however instead of the SUM formula, I see '#N/A'.

The code I use for the 2 types of formulas is shown below.
The first bit works but not the second.

Code: [Select]


// Works
SetLength(myFormula1, 3);
myFormula1[0].ElementKind:=fekCell;
myFormula1[0].Col := 3;
myFormula1[0].Row := curRow;
myFormula1[1].ElementKind:=fekCell;
myFormula1[1].Col := 5;
myFormula1[1].Row := curRow;
myFormula1[2].ElementKind:=fekMul;
ws.WriteRPNFormula(curRow, 13, myFormula1);

// Doesn't work
SetLength(myFormula4, 2);
myFormula4[0].ElementKind:=fekCellRange;
myFormula4[0].Row := 2;     // hard-coded for testing
myFormula4[0].Row2 := 5;
myFormula4[0].Col := 13;
myFormula4[0].Col2 := 13;
myFormula4[1].ElementKind := fekOpSUM;
ws.WriteRPNFormula(curRow+1, 13, myFormula4);

// write to file...
wb.WriteToFile(SaveDialog1.FileName, sfExcel5, True);


Any comments/suggestions would be most welcome.
I just know I am missing something bleeding obvious but I can't figure it out.
I copied the SUM formula code from the wiki.


ps: Changing the output format to sfExcel8 causes it to:
- work in LibreOffice
- but in Excel, I get '#VALUE' errors in every cell with a formula. Click into those cells and pressing F2 then enter makes it work. (!!!????)
I just can't win!

[grandehombre]

I have now raised a ticket for it: http://mantis.freepascal.org/view.php?id=25096

[BigChimp]

Please attach a compilable example program to the bug report that shows the problem, not just code snippets.
Also include a version of the xls used to append data to so the devs can try to reproduce and trace the error.

Also the bug should have been reported in the Lazarus CCR category, not FPC; see http://wiki.lazarus.freepascal.org/FPSpreadsheet#Support_and_Bug_Reporting.

Finally, I assume you tried to run the code on an empty xls and that worked, right?

[grandehombre]

Hi Chimp,

Thanks for jumping in to help.

It almost seems that I went out of my way to do it all the wrong way!

--- Take 2.

I have uploaded a test project that writes a few rows of numbers and then inserts Add, Multiply and Sum formulas.

It then writes one Excel5 and one Excel8 file, both of which exhibit the problems I described in the original post.

Thanks again!

ps: I am not appending to an existing spreadsheet, rather, I am creating one from scratch.
When I said 'The spreadsheet already contains formulas of the form...', I meant I had already inserted such formulas in the spreadsheet in question and they worked, thus my surprise that SUM failed.

[BigChimp]

It almost seems that I went out of my way to do it all the wrong way!

That's how I feel a lot of times, too fortunately I think everybody is happy you're making the effort of reporting the bug; the rest can be fixed later.

I have uploaded a test project that writes a few rows of numbers and then inserts Add, Multiply and Sum formulas.

It then writes one Excel5 and one Excel8 file, both of which exhibit the problems I described in the original post.

Thanks.

ps: I don't think I can relocate the ticket, so I am waiting for the admins to do it.
Many thanks to Reinier Olislagers who has already posted a note requesting this.

No, I don't think you can relocate that ticket.... and on behalf of my alter ego: my pleasure

pps: I am not appending to an existing spreadsheet, rather, I am creating one from scratch.
When I said 'The spreadsheet already contains formulas of the form...', I meant I had already inserted such formulas in the spreadsheet in question and they worked, thus my surprise that SUM failed.

Ah ok. That does probably eliminate some complexity in tracing/solving the bug

[BigChimp]

PS: Subtlety is my middle name but after repeating the "please post a sample project" etc a lot of times you do get to be a bit terse sometimes... Sorry for that.

[grandehombre]

You beat me to it.
I realised you were Reiner only a couple of minutes ago, after I'd finished composing the new notes and adding screenshots.
I removed the 1st 'ps' from my post, only to find out you beat me  to it.

Some days, 3 cups of coffee just don't do it!

Thanks again. In a roundabout way, we seem to manage to get things fixed, I by falling on them and you by looking patiently and quietly, with a raised eyebrow. :-)

Lets hope this one is solved too.


ps: this was posted before I saw your msg #5

[BigChimp]

Thanks again. In a roundabout way, we seem to manage to get things fixed, I by falling on them and you by looking patiently and quietly, with a raised eyebrow. :-)

Mmm, compliments this early will get you anywhere. I'm now recompiling Laz x64 to go and trace through your code and the fpspreadsheet stuff. No promises (I'm not that much of a guru) but who knows

[BigChimp]

Mmm. Reducing the code a bit to 1 row, only multiplication.

Code: [Select]

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  fpspreadsheet;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
const
  TOTAL_ROWS = 1; //15;
  STARTING_SUMMARY_COL = 3;
var
  wb: TsWorkbook;
  ws: TsWorksheet;
  i: integer;
  curRow, row: integer;
  myFormula1, myFormula2, myFormula4: TsRPNFormula;
begin
  wb := TsWorkbook.Create;
  ws := wb.AddWorksheet('Completed jobs');

  for curRow := 1 to TOTAL_ROWS do
  begin
    ws.WriteNumber(curRow, 0, 10); //A1..
    ws.WriteNumber(curRow, 1, 20); //B1..

    SetLength(myFormula1, 3);
    myFormula1[0].ElementKind := fekCell;
    myFormula1[0].Col := 0;
    myFormula1[0].Row := curRow;
    myFormula1[1].ElementKind := fekCell;
    myFormula1[1].Col := 1;
    myFormula1[1].Row := curRow;
    myFormula1[2].ElementKind := fekMul;
    ws.WriteRPNFormula(curRow, STARTING_SUMMARY_COL, myFormula1); //C1..
  end;

  wb.WriteToFile(ExtractFilePath(Application.ExeName) + 'test excel5.xls', sfExcel5, true);
  wb.WriteToFile(ExtractFilePath(Application.ExeName) + 'test excel8.xls', sfExcel8, true);
  wb.Free;
end;

end.
Same problem.

Attached the program-generated faulty xls8 file and the fixed file (Excel 2002/XP, by pressing F2 and enter in the multiplication cell, D2)

Going to have a look at the way the excel8 format if written... Don't hold too much hope though.

[BigChimp]

Okay, I'm giving up - don't know enough about the format and would have to go dig a lot.

[grandehombre]

Many thanks for trying anyway. Lets see if anyone else responds to the ticket

[BigChimp]

Noticed bug that may be related; entered it as a related bug in the existing ticket.

[Gert-Willem]

If I understand it right, 

excel.Cells[33,2].formula := '=Sum(B1:B32)';

works and
 
s := '=Sum(B'+inttostr(day+1)+':E'+inttostr(day+1)+')';
    excel.Cells[day+1,6].formula := s;

works to if you difine s as a olevariant
s : olevariant;

i do'n know why but it works for me