Bug 59727 - FILESAVE, FILEOPEN: Valid Formula Causes #NAME? Error on Re-Open
Summary: FILESAVE, FILEOPEN: Valid Formula Causes #NAME? Error on Re-Open
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.4 release
Hardware: Other All
: medium normal
Assignee: Winfried Donkers
URL:
Whiteboard: BSA target:4.2.0 target:4.1.2 target:...
Keywords:
Depends on:
Blocks:
 
Reported: 2013-01-22 18:19 UTC by EricO
Modified: 2013-08-16 16:15 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
file saved as xlsx (4.15 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-02-12 12:11 UTC, Winfried Donkers
Details
file saved as ods (7.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-02-12 12:15 UTC, Winfried Donkers
Details
list of add-in functions in new ods (13.33 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-05-02 10:08 UTC, Winfried Donkers
Details
list of add-in functions saved as xlsx (7.90 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-05-02 10:09 UTC, Winfried Donkers
Details
list of add-in functions saved as xlsx, reopened (15.70 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-05-02 10:18 UTC, Winfried Donkers
Details
file used for testing (partial) patches (12.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-08-08 09:17 UTC, Winfried Donkers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description EricO 2013-01-22 18:19:12 UTC
Problem description: 

Steps to reproduce:
1. Create or use an existing Microsoft xslx format document.
2. Enter the formula: =PRODUCT(SUM(MONTHS("10/21/2012",NOW(),1),MONTHS("11/6/2012",NOW(),1)),50)
3. Close then re-open the document; cells with above formula show #NAME? error

Current behavior:

The MONTHS() function is changed to lower case.  When closing/re-opening the file, the formula is not calculated.  However, editing the formula, such as changing months() to MONTHS(), causes the formula to be calculated.  The months() function is also forced back to lower case. This behavior does not exist in xls or ods formats; in the ods and xls formats, MONTHS() will remain in the case it is typed and the formula is calculated.

Expected behavior:

Closing and re-opening the xlsx format spreadsheet should continue to calculate the formula the same as in the xls and ods formatted spreadsheets.

              
Operating System: Ubuntu
Version: 3.4.4 release
Comment 1 Winfried Donkers 2013-02-12 12:09:55 UTC
I confirm the error with version 4.0.0.3 on Windows XP.
On reopening the xlsx document the formula is changed to:
=PRODUCT(SUM(com.sun.star.sheet.addin.datefunctions.getdiffmonths("21-10-2012";NOW();1);com.sun.star.sheet.addin.datefunctions.getdiffmonths("11-6-2012";NOW();1));50)

(Please disregard the date format and ;-separator, these are locale settings.)
Comment 2 Winfried Donkers 2013-02-12 12:11:17 UTC
Created attachment 74675 [details]
file saved as xlsx
Comment 3 Winfried Donkers 2013-02-12 12:15:22 UTC
Created attachment 74676 [details]
file saved as ods
Comment 4 dg1727 2013-04-22 18:51:58 UTC
I added keywords FILESAVE, FILEOPEN to the Summary.  I hope that's OK.
Comment 5 Winfried Donkers 2013-05-02 05:29:00 UTC
The problem also occurs with function WEEKS, but not with OCT2HEX, so not with all addin functions.

I am going to investigate which add-in functions (MONTHS, WEEKS, OCT2HEX are add-in functions) are affected. Parallel I will see if I can fix the bug, which seems to be an add-in function problem and not solely a MONTHS-problem.

BTW, if the function is shown in lower case, that means that the function is not recognised.
Comment 6 Winfried Donkers 2013-05-02 10:08:41 UTC
Created attachment 78772 [details]
list of add-in functions in new ods

File created with version 4.0.3.2, UI set to Dutch, function names set to English
File contains all add-in functions.
Col A shows the function name as text
Col B has the function call without arguments, entered in English; most function names were not recognised, despite the setting for English function names
Col C has manually entered Dutch function names, when I knew them

This file was saved as ods and saved as xlsx (next attachment)
Comment 7 Winfried Donkers 2013-05-02 10:09:37 UTC
Created attachment 78773 [details]
list of add-in functions saved as xlsx

See attachment 78772 [details] for explanation.
Comment 8 Winfried Donkers 2013-05-02 10:18:40 UTC
Created attachment 78774 [details]
list of add-in functions saved as xlsx, reopened

See attachment 78772 [details] for explanations.

I added background colours per cell as there are several buggy behaviours:
-some cells have got the com.sun.star.etc. contents (yellow)
-some cells with English function names are now shown in Dutch (green pastel)
-some cells have lost the function name, only [=()] remains (orange)
-some cells have the function name as a text string, () are lost, e.g. [='bin2hex'], also the text is in lower case (salmon)
-some cells have error 504 (error in argument list), some have error 511 (missing argument), I would have expected all to have the same error (either 504 or 511), this is not consistent (no colour marking of these, not a bug, but asking for improvement)
Comment 9 Winfried Donkers 2013-08-08 09:17:49 UTC
Created attachment 83817 [details]
file used for testing (partial) patches

This is the file used with patched code that I test and would like to be tested by other developers.

procedure:
1. open file (all should be well)
2. save as xlsx
3. close file
4. open xlsx file
Comment 10 Commit Notification 2013-08-14 13:38:23 UTC
Winfried committed a patch related to this issue.
It has been pushed to "master":

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

fdo#59727 fix #NAME?-error with add-in functions on (re)open of xlsx



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 Commit Notification 2013-08-14 14:13:53 UTC
Winfried committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

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

fdo#59727 fix #NAME?-error with add-in functions on (re)open of xlsx


It will be available in LibreOffice 4.1.2.

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 12 Commit Notification 2013-08-14 14:23:56 UTC
Winfried committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=569843373b6654e155f897a2a9e3a02d6e010675&h=libreoffice-4-0

fdo#59727 fix #NAME?-error with add-in functions on (re)open of xlsx


It will be available in LibreOffice 4.0.6.

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 13 Eike Rathke 2013-08-14 14:40:19 UTC
So, great, the above commits fix this for the currently active branches, writing and reading with one of these versions will work.

What we could need now is reading files that were saved using versions not including the new map where the function names were written as com.sun.star.sheet.addin.* like in attachment 74675 [details]. I think that should be possible by duplicating the new entries but for the OOXML names use the com.sun.star.sheet.addin.* names and for flags use
FUNCFLAG_IMPORTONLY | FUNCFLAG_EXTERNAL

Winfried, would you be willing to try that out?

Btw, you could set your full real name for git commits, depending on your setup in ~/.gitconfig or <liboworktree>/.git/config in the [user] section change
name = Winfried
to
name = Winfried Donkers
Comment 14 Winfried Donkers 2013-08-15 05:39:36 UTC
(In reply to comment #13)
> What we could need now is reading files that were saved using versions not
> including the new map where the function names were written as
> com.sun.star.sheet.addin.* like in attachment 74675 [details]. I think that
> should be possible by duplicating the new entries but for the OOXML names
> use the com.sun.star.sheet.addin.* names and for flags use
> FUNCFLAG_IMPORTONLY | FUNCFLAG_EXTERNAL
> 
> Winfried, would you be willing to try that out?
> 
> Btw, you could set your full real name for git commits, depending on your
> setup in ~/.gitconfig or <liboworktree>/.git/config in the [user] section
> change
> name = Winfried
> to
> name = Winfried Donkers

Eike,
Thank you for helping & hinting me, otherwise there would have been no patch :)

Yes, I will see if I can make reading of files saved using unpatched versions work properly.

I have just completed my git user.name and I will check my git user.name on my home machine as well.
Comment 15 Commit Notification 2013-08-16 16:04:12 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

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

import .xlsx files saved by Calc before fdo#59727 was patched



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 16 Commit Notification 2013-08-16 16:04:35 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

differentiated warning for symbol/AddIn pair insertion, fdo#59727



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 17 Commit Notification 2013-08-16 16:14:33 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=27a5fa2c83c1acb119d5ae9000f040de3fb17c09&h=libreoffice-4-1

import .xlsx files saved by Calc before fdo#59727 was patched


It will be available in LibreOffice 4.1.2.

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 18 Commit Notification 2013-08-16 16:14:55 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=218728583950aed00be955b5ccdebec69986cff2&h=libreoffice-4-0

import .xlsx files saved by Calc before fdo#59727 was patched


It will be available in LibreOffice 4.0.6.

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.