[Libreoffice-bugs] [Bug 118946] New: XLSX file generated from perl script does not import formulas correctly

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=118946

            Bug ID: 118946
           Summary: XLSX file generated from perl script does not import
                    formulas correctly
           Product: LibreOffice
           Version: 6.0.5.2 release
          Hardware: x86-64 (AMD64)
                OS: Linux (All)
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: thomas.seeling at gmx.net

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

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20180726/bce12be4/attachment-0001.html>