Bug Hunting Session
Bug 118990 - VLOOKUP in XLSX with external reference to Windows share saved incorrectly
Summary: VLOOKUP in XLSX with external reference to Windows share saved incorrectly
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0.0.5 release
Hardware: All All
: medium normal
Assignee: Mike Kaganski
URL:
Whiteboard: target:6.2.0 target:6.1.1
Keywords: filter:xlsx
Depends on:
Blocks: XLSX Function-Vlookup
  Show dependency treegraph
 
Reported: 2018-07-30 03:13 UTC by Aron Budea
Modified: 2018-08-16 15:00 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Data for vlookup (XLSX) (8.45 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-07-30 03:13 UTC, Aron Budea
Details
Sample XLSX (created in Excel) (9.56 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-08-02 02:19 UTC, Aron Budea
Details
Sample XLSX after roundtrip in Calc (5.95 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-08-02 02:20 UTC, Aron Budea
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Aron Budea 2018-07-30 03:13:56 UTC
Created attachment 143815 [details]
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.
Comment 1 Bartosz 2018-07-31 14:20:24 UTC
Please attach XLSX file with reference to external file, created by MS Excel.
Comment 2 Aron Budea 2018-08-02 02:19:24 UTC
Created attachment 143919 [details]
Sample XLSX (created in Excel)

Okay, the location is obviously just a placeholder, but the file contents can be observed, and exhibit the issue nevertheless.
Comment 3 Aron Budea 2018-08-02 02:20:24 UTC
Created attachment 143920 [details]
Sample XLSX after roundtrip in Calc

The difference is as described.
Comment 4 Mike Kaganski 2018-08-14 18:59:19 UTC
The problem is in XclExpHyperlink::BuildFileName

Confirming using current master.
Comment 5 Mike Kaganski 2018-08-15 09:58:49 UTC
https://gerrit.libreoffice.org/59064
Comment 6 Commit Notification 2018-08-15 16:57:24 UTC
Mike Kaganski committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f24b0ec13c5c8edda5ffb1336b0eb6da173dfc97

tdf#118990: use full URI for absolute references

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2018-08-16 10:04:32 UTC
Mike Kaganski committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e9e18b5a3a0e9651b7161278a61c6a7ce0b9df0b&h=libreoffice-6-1

tdf#118990: use full URI for absolute references

It will be available in 6.1.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.