Download it now!
Bug 91278 - xlsx formula not importing correctly, with Table structured references in named expressions and MATCH lookup array creation
Summary: xlsx formula not importing correctly, with Table structured references in nam...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Eike Rathke
URL:
Whiteboard: target:5.1.0 target:5.0.1 target:5.0.0
Keywords:
Depends on:
Blocks: Excel-2013-Templates
  Show dependency treegraph
 
Reported: 2015-05-14 03:25 UTC by Joel Madero
Modified: 2016-10-25 19:24 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Original.xlsx (150.87 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-05-14 03:25 UTC, Joel Madero
Details
Original.pdf (226.40 KB, application/pdf)
2015-05-14 03:25 UTC, Joel Madero
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Joel Madero 2015-05-14 03:25:08 UTC
Created attachment 115588 [details]
Original.xlsx

@Triager - Markus has said that I don't have to identify the exact portion of the formula that is wrong, it is sufficient just to confirm that it's not being imported correctly :)


Windows 7
Version: 5.0.0.0.alpha1+
Build ID: 5b3a30f40a7ce476922649b734f6ede1c2fdef4b
TinderBox: Win-x86@62-merge-TDF, Branch:MASTER, Time: 2015-05-13_01:52:36
Locale: en-US (en_US)

1) Open Original.xlsx

Observed: Look at column F - lots of Err:508 errors, compare to original.pdf


Formula: =IFERROR(INDEX(Input[],MATCH(DATEVALUE(DateVal)&DailySchedule[[#This Row],[time]],LookUpDateAndTime,0),3),"-")
Comment 1 Joel Madero 2015-05-14 03:25:58 UTC
Created attachment 115589 [details]
Original.pdf
Comment 2 GerardF 2015-05-14 08:20:35 UTC
Error came from this part of formula:
DailySchedule[[#This Row],[time]]
If we have database range for "DailySchedule", I can't see a "time" column in this range.
There is also another error in the DATEVALUE function:
DATEVALUE(DateVal) fails because DateVal has date-text formatted as MMMM D, YYYY

Eike did a great job with structered reference in master 4.5
see https://bugs.documentfoundation.org/show_bug.cgi?id=85063
Comment 3 Markus Mohrhard 2015-05-16 05:47:18 UTC
Looks like an error in our formula parser.

@Eike: might be one for you as it seems to be related to the new structured reference parsing.
Comment 4 Eike Rathke 2015-06-02 22:23:32 UTC
There are two (or three) problems:

1. LookUpDateAndTime is a named expression that contains Table
   structured references. TableRefs in named expressions aren't
   supported yet.
   * If occurrences of LookUpDateAndTime in the formulas are replaced
     with it's content  Input[DATE]&Input[TIME]  the Err508 goes away.

However, the formula then still does not work as can be seen if the
IFERROR functionality is removed so the formula reads

=INDEX(Input[],MATCH(DATEVALUE(DateVal)&DailySchedule[[#This Row],[time]],Input[DATE]&Input[TIME],0),3)

because

2. The DailySchedule table is defined to $'Daily Schedule'.$E$4:$F$36
   that does not include a header row and there is no TIME header field.
   Excel apparently allows such construct and remembers the column
   headers in the OOXML <table><tableColumns><tableColumn> elements.
   Importing tableColumns definitions is not implemented yet.
   * This can be worked around by entering TIME in E3 and defining the
     DailySchedule name to $'Daily Schedule'.$E$3:$F$36

3. Furthermore, Excel seems to treat the MATCH lookup array argument
   special, because both Input[DATE] and Input[TIME] evaluate to range
   references ($'Event Scheduler'.$E$5:$E$17 and
   $'Event Scheduler'.$F$5:$F$17) with which an implicit intersection
   reference is created (in LibreOffice, in other context also in Excel)
   depending on the position of the formula where they are used, so
   actually only F5:F17 could have meaningful results.
   * My guess is that Excel internally creates an array of the
     Input[DATE]&Input[TIME] expression when used as a lookup array
     argument of the MATCH function. This may be a general feature of
     the MATCH function, and maybe [HV]LOOKUP as well, and not
     restricted to TableRef arguments. Needs to be evaluated in Excel.

All this is lots to do..
Comment 5 Eike Rathke 2015-07-21 12:44:26 UTC
Update:
1) named expressions with TableRefs is implemented for 5.0
2) importing tables without header fields as cell content is implemented
   for 5.0, but such tables currently don't survive modifications of the
   table structure, for example inserting/deleting columns operations 
3) remains to be done
Comment 6 Eike Rathke 2015-07-21 13:59:17 UTC
Yet another quirk:
4. with  DailySchedule[[#This Row],[6:00 AM]]  Excel takes the result of
   a numeric cell content as formatted as string ("6:00 AM") as column
   header, which we currently don't support. Which isn't necessarily
   a good idea either, because representation may change depending on
   the locale, in this case it works because the locale is set to fixed
   English (USA). Something the user has to be aware of.
Comment 7 Eike Rathke 2015-07-21 19:30:33 UTC
Hum.. or not. The original formula has DailySchedule[[#This Row],[Time]] but DailySchedule is a table without header cells, it seems we adapt the formula to the first row's content of that column then. So more work to do.

However, that document relies on undefined behaviour, e.g. DateVal is defined to 'Daily Schedule'!$F$3 that contains a date in string form, formatted to ""MMMM D, YYYY"" and is used in DAY(DateVal) whereas DAY() expects a date serial number instead, or DateVal+1 is calculated. These things may or may not work, even DATEVALUE(DateVal) which is used heavily relies on that locale dependent date representation being accepted. The document probably won't work in many other locales than en-US. Bad coding ;-)
Comment 8 Commit Notification 2015-07-23 12:35:22 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

TableRef: generate error for header-less column references, tdf#91278 related

It will be available in 5.1.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 9 Commit Notification 2015-07-23 16:21:43 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=18b5fe95d2d5b4158c5749c467298e4564537876&h=libreoffice-5-0

TableRef: generate error for header-less column references, tdf#91278 related

It will be available in 5.0.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.
Comment 10 Commit Notification 2015-07-24 13:41:06 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=12e0032fc91c9f7bc3cf8dcd13b9fbbae26890f0&h=libreoffice-5-0-0

TableRef: generate error for header-less column references, tdf#91278 related

It will be available in 5.0.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 11 Eike Rathke 2015-09-11 15:32:32 UTC
Formulas and named expressions referring header-less tables are now (master, to-be 5.1) also imported and exported, see https://wiki.documentfoundation.org/ReleaseNotes/5.1#Table_structured_references