Issue 116319 - sheet reference error on Excel 2003 xml import
Summary: sheet reference error on Excel 2003 xml import
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOO320m18
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: ms_interoperability
Depends on:
Blocks:
 
Reported: 2011-01-05 21:46 UTC by bz922x
Modified: 2023-01-12 01:38 UTC (History)
2 users (show)

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

Attachments
Excel 2003 xml file with cross sheet range reference (2.03 KB, text/xml)
2011-01-05 21:49 UTC, bz922x 		no flags Details
OOo spreadsheet file with correct cross sheet range references (6.79 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-01-05 21:50 UTC, bz922x 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description bz922x 2011-01-05 21:46:29 UTC 
When reading an excel 2003 xml file with a cross sheet range reference, the
second (and subsequent sheet references are not correctly processed. 

With this in the XML file: ss:Formula="=SUM(Sheet1!RC:Sheet1!RC[1])"
The imported cell formula should be: =SUM(Sheet1.A1:Sheet1.B1)
but instead is shows as: =SUM(Sheet1.A1:#REF!.B1)
Comment 1 bz922x 2011-01-05 21:49:12 UTC 
Created attachment 75476 [details]
Excel 2003 xml file with cross sheet range reference
Comment 2 bz922x 2011-01-05 21:50:32 UTC 
Created attachment 75477 [details]
OOo spreadsheet file with correct cross sheet range references
Comment 3 bz922x 2011-01-05 22:02:34 UTC 
To reproduce the error, import the Excel 2003 xml file, then look at sheet2 cell
A1. The reference error will be in the cell formula.
Comment 4 Marcus 2011-01-06 09:41:36 UTC 
confirmed with OOo 3.3.0 RC8 (OOO330m18) on Solaris Sparc
Comment 5 damjan 2023-01-12 01:38:41 UTC 
If we run:

xsltproc  main/filter/source/xslt/import/spreadsheetml/spreadsheetml2ooo.xsl 116319\ Test.xml

and we look at the formula cell on Sheet2:

<table:table-cell
  table:style-name="ta1"
  table:formula="oooc:=SUM([Sheet1.A1:[Sheet1.B1])"
  office:value-type="float"
  office:value="3">
    <text:p>3</text:p>
</table:table-cell>

the table:formula of:
oooc:=SUM([Sheet1.A1:[Sheet1.B1])

has 2 "[" but only 1 "]", and nests Sheet1.B1 in a possibly unnecessary or wrong pair of "[" and "]".