Issue 118157

Summary: Reference to Sheet with space in name silently dropped in .xlsm
Product: Calc Reporter: m_v
Component: open-importAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P3 CC: brechbuehler, elish, frank, issues, rainerbielefeld_ooo_qa
Version: 3.3.0 or older (OOo)   
Target Milestone: ---   
Hardware: PC   
OS: All   
See Also: https://bugs.freedesktop.org/show_bug.cgi?id=51926
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
File to illustrate the issue.
none
File converted to .xls which displays correctly. none

Description m_v 2011-06-10 15:56:36 UTC
Created attachment 76602 [details]
File to illustrate the issue.

We've noticed that with .xlsm files created with Microsoft Office 2010, if there is an embedded space in the tab name, Calc will silently discard that value.  I'm attaching a file to illustrate this problem.

The first row of numbers are linked to a file named test.xlsm, which
you don't need to have around for testing, as office knows to use the
last available value and not update the link.  test.xlsm has three tabs:
tab1, tab_2, and "tab 3".

The second row links to a file "test with spaces.xlsm", same idea.


Microsoft Office 2010, display user.xlsm:

----------------
link to test:
1       2       3


link to test with spaces
1       2       3
----------------

OpenOffice 3.3.0, display user.xlsm:

----------------
link to test:
1       2


link to test with spaces
1       2
----------------
Comment 1 m_v 2011-06-10 15:58:39 UTC
Created attachment 76603 [details]
File converted to .xls which displays correctly.

Here's the same file, converted to .xls with Microsoft Office 2010.  It displays correctly.
Comment 2 m_v 2011-07-19 15:30:54 UTC
This actually also happens with the Windows version of OpenOffice 3.3.0.  If I take apart the .xslm file and look at xl/worksheets/sheet1.xml, this is the relevant bit.  

<sheetData><row r="1" spans="1:3" x14ac:dyDescent="0.25">
  <c r="A1" t="s"><v>0</v></c></row><row r="2" spans="1:3" x14ac:dyDescent="0.25">
  <c r="A2"><f>[1]tab1!$A$1</f><v>1</v></c>
  <c r="B2"><f>[1]tab_2!$A$1</f><v>2</v></c>
  <c r="C2"><f>'[1]tab 3'!$A$1</f><v>3</v></c>
  </row><row r="5" spans="1:3" x14ac:dyDescent="0.25">
  <c r="A5" t="s"><v>1</v></c></row><row r="6" spans="1:3" x14ac:dyDescent="0.25">
  <c r="A6"><f>[2]tab1!$A$1</f><v>1</v></c>
  <c r="B6"><f>[2]tab2!$A$1</f><v>2</v></c>
  <c r="C6"><f>'[2]tab 3'!$A$1</f><v>3</v>
  </c></row>
</sheetData>


OO has trouble parsing the space in C2 and C6, even though it is quoted.  As a result, it discards those values, which obviously can lead to lots of errors in the document.
Comment 3 brechbuehler 2011-10-12 11:57:11 UTC
I confirm this bug affects the Open Office Calc that comes with Ubuntu 10.04.3 LTS

This is frustrating, as it holds up my move from Micro$oft to OpenOffice.org

<quote file="/usr/lib/openoffice/program/versionrc">
[Version]
AllLanguages=en-US
BuildVersion=ooo-build 3.2.0.10, Debian 1:3.2.0-7ubuntu4.2, Wed Jan 26 22:22:24 UTC 2011
buildid=320m12(Build:9483)
ExtensionUpdateURL=http://updateext.services.openoffice.org/ProductUpdateService/check.Update
OOOBaseVersion=3.2
ProductBuildid=9483
ProductMajor=320
ProductMinor=12
ProductSource=OOO320
UpdateID=OpenOffice.org_3_en-US
UpdateURL=
UpdateUserAgent=<PRODUCT> (${buildid}; ${_OS}; ${_ARCH}; BundledLanguages=${AllLanguages})
Vendor=Debian and Ubuntu
</quote>
Comment 4 Oliver-Rainer Wittmann 2012-06-13 12:16:59 UTC
getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.
Comment 5 Frank Tilley 2012-07-09 20:03:15 UTC
I'm having the same issue with version 3.5.4.2 in Ubuntu 12.04
and 3.5.2.2 in Windows 7
Comment 6 Frank Tilley 2012-07-09 20:03:49 UTC
of libreoffice that is :)
Comment 7 Edwin Sharp 2013-11-17 13:29:27 UTC
Confirmed with

AOO410m1(Build:9750)  -  Rev. 1539999
2013-11-09_04:08:11 - Rev. 1540252
Debian
Comment 8 Rainer Bielefeld 2014-04-04 08:12:20 UTC
Version has been modified erroneous, so back to most early version

Reproducible with "AOO 4.0.1 Release – German UI / German locale [AOO401m5(Build:9714)  -  Rev. 1524958 2013-09-20 11:40:29]" on German WIN7 Home Premium (64bit)", “historic” 4.0 User Profile used for all predecessor versions:
In column D only "=" is left instead of complete reference.

There is a fixed "LibO#51926 - FILEOPEN particular .xlsm: Formula with link to external contents missing if target sheet name contains space " concerning this problem

Might be related to "Issue 120199 - With space character used at the beginning of a sheet name in a 3D reference, it can not be parsed properly, while import .xlsx file"