Problem with RPN formulas
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
grandehombre · 2013-09-26 08:04

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 · 2013-09-27 00:37

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

----------------------------------------------------------------------
BigChimp · 2013-09-27 06:07

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 · 2013-09-27 07:14

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 · 2013-09-27 07:22

Quote from: grandehombre on September 27, 2013, 09:14:11 amIt 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.

Quote from: grandehombre on September 27, 2013, 09:14:11 amI 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.

Quote from: grandehombre on September 27, 2013, 09:14:11 amps: 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

Quote from: grandehombre on September 27, 2013, 09:14:11 ampps: 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 · 2013-09-27 07:25

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 · 2013-09-27 07:28

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 · 2013-09-27 07:32

Quote from: grandehombre on September 27, 2013, 09:28:01 amThanks
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 · 2013-09-27 08:15

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 · 2013-09-27 09:47

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

----------------------------------------------------------------------
grandehombre · 2013-09-27 09:50

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

----------------------------------------------------------------------
BigChimp · 2013-09-28 07:09

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

----------------------------------------------------------------------
Gert-Willem · 2013-10-24 10:07

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