[Libreoffice-bugs] [Bug 118990] New: VLOOKUP in XLSX with external reference to Windows share saved incorrectly

[bugzilla-daemon at bugs.documentfoundation.org]

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

            Bug ID: 118990
           Summary: VLOOKUP in XLSX with external reference to Windows
                    share saved incorrectly
           Product: LibreOffice
           Version: 5.0.0.5 release
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Keywords: filter:xlsx
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: baron at caesar.elte.hu
            Blocks: 108897, 109329

Created attachment 143815
  --> https://bugs.documentfoundation.org/attachment.cgi?id=143815&action=edit
Data for vlookup (XLSX)

Prerequisite steps (Windows share and Excel needed):
- Put the attached data source spreadsheet into a shared folder.
- In Excel, in a new spreadsheet, enter:
 - in A1: =VLOOKUP(B1,'\\<share>\<path>\[lookupsource.xlsx]Sheet1'!A1:B5,2)
 - in B1: C
 (ie. in the shared spreadsheet, in A1:B5 range try to look up the
corresponding value for the row containing "C")
- Save the file.

- Open file in Calc, verify that A1 contains the correct lookup result (you
might have to allow loading external data), then save and reopen it.
For reference note that when opening the original file, the formula looks like
this in Calc (the "good" version):
=VLOOKUP(B1;'file://<share>/<path>/lookupsource.xlsx'#$Sheet1.A1:B5;2)

=> The location in the VLOOKUP formula is changed to something like:
<drive>/<path>/lookupsource.xlsx

When unzipping the files, in 'xl\externalLinks\_rels\externaLink1.xml.rels' has
a significant difference between the original, and the one roundtripped in
Calc:
- original:
  <Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath"
Target="file:///\\<share>\<path>\lookupsource.xlsx" TargetMode="External"/>
- roundtripped:
  <Relationship Id="rId1"
Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath"
Target="/<path>/lookupsource.xlsx" TargetMode="External"/>

The Target attribute is incorrect.

The bug has some resemblance to bug 87973

Observed using LO 6.1.0.2 & 5.0.0.5 / Windows 7.


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=108897
[Bug 108897] [META] XLSX (OOXML) bug tracker
https://bugs.documentfoundation.org/show_bug.cgi?id=109329
[Bug 109329] [META] VLookup function bugs and enhancements
-- 
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/20180730/2e38ed44/attachment.html>