Description: I'm creating a Calc spreadsheet (LO version 6.0.5.2 on RHEL 7.5) with a perl module, Excel::Writer::XLSX, and this really works fine, I can add colours and other markup to the rows I create. Now I wanted to add some formulas for dynamic content and lookup some values from another sheet (my output consists of ~20 worksheets). The formula is quite simple and looks like =IFERROR(VLOOKUP(D6;$Lookup.$A$2:$A$100000;1;0);"") When I open the file all cells with a formula contain Err:508 (which the help describes as "parenthesis missing"). As soon as I edit a cell (e.g. delete a character and enter the same again) it begins to work and shows the expected value. If I save the file (not editing anything) as .ODS, then close and re-open the .ODS file the formulas start working. So basically I assume the formula syntax is correct and the perl module is creating a correct XLSX file. Also I noticed that the formulas completely appear in lower case in LO. As soon as I edit a formula it is changed to camel case. I planted some printf debug statements in the write_formula method of the perl module and I see that the XML output looks good - exactly as it should. So my guess is that it's a problem in LO. The following code at least does not produce Error 508 any longer but the cells are empty: When I open the file in LO all formulas are written in lowercase; as soon as I edit a line it gets canonicalized and starts working. The perl module creates the formula according to the "working" example but it seems LO messes it up while importing. not working: =IFERROR(VLOOKUP(D4;$lookup.$f$2:$F$10000;1;0);"") working: =IFERROR(VLOOKUP(D4;$Lookup.$F$2:$F$10000;1;0);"") Steps to Reproduce: 1. create XLSX with perl script 2. open in LO 3. navigate to a cell with a formula Actual Results: the formula is not calculated. F9 does not force recalculation, cell shows no result. formula is displayed all lowercase. Expected Results: cell should show the lookup result from the 2nd sheet Reproducible: Always User Profile Reset: No Additional Info: saving the generated XLSX file as ODS, then closing and re-opening the ODS now correctly shows values for formulas. Script works and generates valid XLSX but LO has problem importing the formula from XLSX. About: Version: 6.0.5.2 Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16 CPU threads: 4; OS: Linux 3.10; UI render: default; VCL: gtk2; Locale: de-DE (en_US.UTF-8); Calc: group
Created attachment 143777 [details] XLSX generated by perl script. Formula not working Formula for GECOS field not working. Save as ODS, close, re-open ODS. Formula works.
Created attachment 143778 [details] perl script to create XLSX with formula perl script generates XLSX with simple formula to lookup unix user GECOS field from 2nd worksheet in file. if formula works the GECOS field will be "root user".
The formula generated and stored by perl looks like <f>IFERROR(VLOOKUP(A1,$Lookup.$A$1:$B$10000,2,FALSE),"")</f> If I convert the spreadsheet to ODS, then save it again as XLSX the XML looks different: <f aca="false">IFERROR(VLOOKUP(D2,Lookup!$A$2:$A$10000,1,0),"")</f> (those are different cells, so don't mind the different arguments to the vlookup).
Problem solved. The syntax for the perl module should look like this: =IFERROR(VLOOKUP(A1,Lookup!$A$1:$B$10000,2,0),"")
Created attachment 143786 [details] perl script that works