Download it now!
Bug 131372 - FILESAVE XLSX Paste special of #N/A formula result result gives in Err:520 in Calc after reload
Summary: FILESAVE XLSX Paste special of #N/A formula result result gives in Err:520 in...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.0 all versions
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.0.0 target:6.4.4 target:6.3.6
Keywords: bibisected, bisected
Depends on:
Blocks:
 
Reported: 2020-03-16 08:08 UTC by NISZ LibreOffice Team
Modified: 2020-04-07 07:41 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file from Excel (10.61 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-03-16 08:09 UTC, NISZ LibreOffice Team
Details
The original file after using Paste Special and saved by Calc (6.70 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-03-16 08:09 UTC, NISZ LibreOffice Team
Details
Screenshot of the exported document side by side in Excel and Calc (103.65 KB, image/png)
2020-03-16 08:10 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description NISZ LibreOffice Team 2020-03-16 08:08:53 UTC
Description:
If a formula gives #N/A result – because of missing data – and this result is copied and pasted using Paste Special, then saved as XLSX the reloaded file gives Err:520, while Excel gives #N/A.

Steps to Reproduce:
1. Open attached file. It contains a simple MATCH formula in B2:B3 that refers to the second sheet. The function in B3 gives #N/A since it does not find a match on the second sheet.
2. The contents of B2:B3 were copied and pasted as text in Excel to B5:B6. The result in B6 is imported fine as in Calc. User intent was to get the formula results as plain text.
3. Copy the B2:B3 cells contents (in the example B8:B9) and insert it using Paste Special, with default settings (Selection: Text, Numbers, Date&Time).
4. Save the file as XLSX and reload.

Actual Results:
In Calc the B9 cells content appears as Err:520, while in Excel it appears as #N/A

Expected Results:
B9 cell should show #N/A too


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 7.0.0.0.alpha0+ (x64)
Build ID: cf96cb11e2a46c452a273ded1c66c556118983cf
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: default; VCL: win; 
Locale: hu-HU (hu_HU); UI-Language: en-US
Calc: CL

Started to happen in:
Version: 4.4.0.3
Build ID: de093506bcdc5fafd9023ee680b8c60e3e0645d7
Locale: hu_HU
Comment 1 NISZ LibreOffice Team 2020-03-16 08:09:22 UTC
Created attachment 158715 [details]
Example file from Excel
Comment 2 NISZ LibreOffice Team 2020-03-16 08:09:42 UTC
Created attachment 158716 [details]
The original file after using Paste Special and saved by Calc
Comment 3 NISZ LibreOffice Team 2020-03-16 08:10:03 UTC
Created attachment 158717 [details]
Screenshot of the exported document side by side in Excel and Calc
Comment 4 NISZ LibreOffice Team 2020-03-16 08:11:21 UTC
Bibisected using bibisect-win32-5.0 to: 
URL: https://cgit.freedesktop.org/libreoffice/core/commit/?id=a1dc5e97da273bf35d58d54e625149022569a993
author	Eike Rathke <erack@redhat.com>	2014-12-16 01:56:58 +0100
committer	Eike Rathke <erack@redhat.com>	2014-12-16 02:02:14 +0100
summary: construct grouped ScFormulaCell with bDirty=true, fdo#79249 related

Although this probably just made the problem visible:
Before this the B2 cell appeared empty in the grid, yet contained a formula in the edit bar. 
In 4.1 the B2 cell was not empty this way, copying #N/A seemed to work like today, but saving to XLSX and reopening results in a simple = sign instead of the Err:520.
Not adding regression tag.
Comment 5 m.a.riosv 2020-03-16 21:09:41 UTC
Works for me with
Version: 7.0.0.0.alpha0+ (x64)
Build ID: 6f82f5163fdd43ea22958dfb28926c88ed97fa7c
CPU threads: 4; OS: Windows 10.0 Build 19582; UI render: Skia/Raster; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US Calc: threaded
Comment 6 Eike Rathke 2020-03-30 16:35:33 UTC
I can reproduce.
* load the first attached document
* copy formula cell B3 with #N/A error
* paste special (without formulas) somewhere
  => #N/A special formula cell is created
* save as .xlsx
* reload
  => the pasted cell has an Err:520 instead
Comment 7 Eike Rathke 2020-03-30 16:41:21 UTC
In fact happens with any error result, =NA() #N/A or =1/0 #DIV/0! both become Err:520.
Comment 8 Commit Notification 2020-03-31 00:56:44 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/456f093975ce4e4266eb12b62590eaaba6f2de88

Resolves: tdf#131372 Write "error cell" formula to OOXML

It will be available in 7.0.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 9 Eike Rathke 2020-03-31 12:40:36 UTC
Pending review
https://gerrit.libreoffice.org/c/core/+/91390 for 6-4
https://gerrit.libreoffice.org/c/core/+/91393 for 6-3
Comment 10 Xisco Faulí 2020-03-31 14:04:50 UTC
Verified in

Version: 7.0.0.0.alpha0+
Build ID: 169a10f0e4680814145b668c6320be04038d7a89
CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

@Eike, thanks for fixing this issue!!
Comment 11 Commit Notification 2020-03-31 15:21:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/02c0824899b2459bba98d4cfef933bfe7376f41f

Resolves: tdf#131372 Write "error cell" formula to OOXML

It will be available in 6.4.4.

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

Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2020-03-31 16:29:32 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/437d30fa8e7daaf12990cf9672614e630e915ba5

tdf#131372: Add unittest

It will be available in 7.0.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2020-04-07 07:41:54 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/commit/bcbf27beddabf6e76331e93fd7bead9ec534605e

Resolves: tdf#131372 Write "error cell" formula to OOXML

It will be available in 6.3.6.

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

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