Issue 111779 - ERR:509 in cells where sheet name contains space, import of excel 2003 xml from Excel 2003
Summary: ERR:509 in cells where sheet name contains space, import of excel 2003 xml fr...
Status: REOPENED
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOo 3.2
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-05-23 05:58 UTC by jmichae3
Modified: 2013-01-29 21:44 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
cross-sheet references spreadsheet in excel 2003 xml format from excel 2003 (3.30 KB, text/xml)
2010-05-23 06:08 UTC, jmichae3
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description jmichae3 2010-05-23 05:58:18 UTC
when importing Excel 2003 XML files, cross-sheet references are
imported wrong as Err:509 when sheet names contain a space.  this is a special case
that is being handled wrong where single quotes are used to handle the special
case of having the space, for example, in Excel, the standard reference should
be ='this sheet name has spaces'!A1 but in OOo this gets converted to ='this
sheet name has spaces'A1, but in OOo a valid reference is =Restaurant.A1 or
='this sheet name has spaces'.A1 when you type in or click in an address. 

but in excel =Sheet1!A1 or Sheet1!R1C1 (latter is useful for XML files if you
want absolute cell references) is a valid cell reference.  until you get into
xml files.  then things get dicey.  A1 type references are not valid in Excel
2003 XML files.  only R C and RC references are valid in Excel 2003 XML files. 
and they are very messy unless you are doing absolute references. 
programmatically RC (for example, R1C1 or R208C31 is VERY convenient for doing
file exports if you are doing absolute references - it's as easy as doing a
printf with the row and column number.

after entering a correct spreadsheet into OOo with simple cross-sheet
references, OOo outputs as Excel 2003 XML the following incorrect format (herein
called the "cross-sheet references" example"):

<Row ss:Height="12.8376">
<Cell ss:StyleID="Normal" ss:Formula="of:=['this sheet name has
spaces'.A1]"><Data ss:Type="Number">1</Data></Cell>
<Cell ss:StyleID="Normal" ss:Formula="of:=[Sheet1.A1]"><Data
ss:Type="Number">1</Data></Cell>
</Row>
<Row ss:Height="12.8376"><Cell ss:Index="2" ss:StyleID="Normal"/></Row>

MS Excel outputs the following for the same spreadsheet:

   <Row>
    <Cell ss:Formula="='this sheet name has spaces'!RC"><Data
ss:Type="Number">1</Data></Cell>
    <Cell ss:Formula="=Sheet1!RC[-1]"><Data ss:Type="Number">1</Data></Cell>
   </Row>
Comment 1 jmichae3 2010-05-23 06:08:18 UTC
Created attachment 69578 [details]
cross-sheet references spreadsheet in excel 2003 xml format from excel 2003
Comment 2 jmichae3 2010-11-28 05:02:46 UTC
bug appears to have been fixed in 3.2.1.
Comment 3 Shan Zhu 2012-09-06 05:47:32 UTC
It can be reproduced on AOO340m1 build9590 and AOO350m1 build9610. Reopen it.