Forum > FPSpreadsheet
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: ---
// 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);
--- End code ---
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:
--- Quote from: grandehombre on September 27, 2013, 09:14:11 am ---It almost seems that I went out of my way to do it all the wrong way! :-[
--- End quote ---
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 am ---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.
--- End quote ---
Thanks.
--- Quote from: grandehombre on September 27, 2013, 09:14:11 am ---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.
--- End quote ---
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 am ---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.
--- End quote ---
Ah ok. That does probably eliminate some complexity in tracing/solving the bug ;)
Navigation
[0] Message Index
[#] Next page