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@lists.freedesktop.org
Reporter: thomas.seel...@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._______________________________________________
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
