Problem with RPN formulas
Original Reporter info from Mantis: grandehombre
-
Reporter name: grande hombre
Original Reporter info from Mantis: grandehombre
- Reporter name: grande hombre
Description:
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!
Steps to reproduce:
use above code and the results will be as described.
Mantis conversion info:
- Mantis ID: 25096
- OS: Windows
- OS Build: Win7-64
- Build: svn 42478
- Platform: Intel i7
- Version: 2.6.2
- Monitored by: » grandehombre (grande hombre), » BigChimp (Reinier Olislagers)