Issue 111778 - export excel 2003 xml format uses of:=['sht nm']. instead of Excel's ='sht nm'! for cross sheet references
Summary: export excel 2003 xml format uses of:=['sht nm']. instead of Excel's ='sht nm...
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: save-export (show other issues)
Version: 4.1.0-dev
Hardware: PC Windows, all
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2010-05-23 05:27 UTC by jmichae3
Modified: 2013-11-21 12:48 UTC (History)
4 users (show)

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


Attachments
sample basic cross-sheet reference spreadsheet .ods (8.43 KB, application/vnd.oasis.opendocument.spreadsheet)
2010-05-23 05:54 UTC, jmichae3
no flags Details
error log file (856 bytes, text/plain)
2013-11-21 12:48 UTC, Edwin Sharp
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:27:06 UTC
excel only uses RC-relative cell references (at least in its 2003 xml format). 
OOo uses A1-type references.  RC-relative references are a royal
pain.  but it's the only thing Excel 2003 will accept.

given the log information from a recent very small 3-entry sheet (2 numbers
and an AVERAGE() in OOo, an attempt to open in Excel reveals this log file:

XML Spreadsheet Warning in Table
REASON:	Bad Value
FILE:	C:\Documents and Settings\Jim\My
Documents\average-can-i-open-this-ooo3-in-excel.xml
GROUP:	Row
TAG:	Cell
ATTRIB:	Formula
VALUE:	of:=AVERAGE([.A1:.A2])

excel output:
<Cell ss:Formula="=AVERAGE(R[-2]C:R[-1]C)">
OOo3.0 xml output:
<Cell ss:Formula="of:=AVERAGE([.A1:.A2])">

the only way I could fix up the XML file  so it would import into Excel 2003 was
to put it in some sort of RC format, which I know nothing about.
<Cell ss:Formula="=AVERAGE(R1C1:R2C1)">
which made the formula absolutely absolute, but at least it imported.
Comment 1 jmichae3 2010-05-23 05:46:43 UTC
in the Excel 2003 XML format, excel uses ! instead of the OOo's . after the
sheet name for cross sheet references.

OOo:

<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 2 jmichae3 2010-05-23 05:54:35 UTC
Created attachment 69577 [details]
sample basic cross-sheet reference spreadsheet .ods
Comment 3 Regina Henschel 2010-05-23 11:45:01 UTC
Goto Tools > Options > Load/Save > General and set the ODF version to 1.0/1.1.
That will fix most of your problems with export to Excel 2003 xml format.
Comment 4 Edwin Sharp 2013-11-21 12:48:16 UTC
Save as xml attachment 69577 [details] and open in Excel 2010 fails.

AOO410m1(Build:9750)  -  Rev. 1543812
Rev.1543812
Win 7
Comment 5 Edwin Sharp 2013-11-21 12:48:47 UTC
Created attachment 81973 [details]
error log file