<html>
<head>
<base href="https://bugs.documentfoundation.org/">
</head>
<body><table border="1" cellspacing="0" cellpadding="8">
<tr>
<th>Bug ID</th>
<td><a class="bz_bug_link
bz_status_UNCONFIRMED "
title="UNCONFIRMED - XLSX file generated from perl script does not import formulas correctly"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=118946">118946</a>
</td>
</tr>
<tr>
<th>Summary</th>
<td>XLSX file generated from perl script does not import formulas correctly
</td>
</tr>
<tr>
<th>Product</th>
<td>LibreOffice
</td>
</tr>
<tr>
<th>Version</th>
<td>6.0.5.2 release
</td>
</tr>
<tr>
<th>Hardware</th>
<td>x86-64 (AMD64)
</td>
</tr>
<tr>
<th>OS</th>
<td>Linux (All)
</td>
</tr>
<tr>
<th>Status</th>
<td>UNCONFIRMED
</td>
</tr>
<tr>
<th>Severity</th>
<td>normal
</td>
</tr>
<tr>
<th>Priority</th>
<td>medium
</td>
</tr>
<tr>
<th>Component</th>
<td>Calc
</td>
</tr>
<tr>
<th>Assignee</th>
<td>libreoffice-bugs@lists.freedesktop.org
</td>
</tr>
<tr>
<th>Reporter</th>
<td>thomas.seeling@gmx.net
</td>
</tr></table>
<p>
<div>
<pre>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</pre>
</div>
</p>
<hr>
<span>You are receiving this mail because:</span>
<ul>
<li>You are the assignee for the bug.</li>
</ul>
</body>
</html>