To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=95045
User jmichae3 changed the following:
What |Old value |New value
================================================================================
Summary|can't open Excel files in |can't open Excel 2003 xml
|OOO, can't open OOO files |files in OOO, can't open O
|in Excel |OO excel 2003 xml files in
| | Excel
--------------------------------------------------------------------------------
------- Additional comments from jmich...@openoffice.org Tue May 18 08:07:42
+0000 2010 -------
main issue:
steps to reproduce:
1.create spreadsheet with calc with cell references. do sheet boundaries.
2.save as Excel 2003 xml format file.
3. close OOo so file is not locked.
4. optional: open xml file in programmer's editor, edit XML file and put in
carriage returns so it's readable. compare OOo version with MS version and look
at differences. xml files definitely ignore all whitespace (spaces, carriage
returns, tabs) and should ignore standard XML <!-- --> comments. close file.
5. open the document in MS Excel 2003.
results: you get an excel error complaining about the cell references whern
trying to open the OOo Excel 2003 format xml file with MS Excel 2003.
expected: should open and parse the XML file and convert the formulas into
OpenOffice.org compatible format (they ARE different, such as IF). for example,
the argument separator is a semicolon instead of a comma:
Excel: IF(condition,truevalue,falsevalue)
OOo: IF(condition;truevalue;falsevalue)
to be honest, I didn't discover the underlying problems I talked about in these
posts until I dug deeper into the xml file itself and started reading the
outputs of both programs.
changed the bug title (added the word XML) to reflect the problem more exactly
(more clarity).
problem #1: 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.
problem #2: Issue 111666 3.2.0 (the new version) can't save budget.xls calc file
as Excel 2003 xml, export is now broken (or trying my budget spreadsheet is a
new problem I've discovered). complicated 1-sheet spreadsheets don't export
either. simpler spreadsheets with only 4 entries do export.
32-bit 2GB limit problem I think.
problem #3: OOo 3.2.0 currently inserts about 7-15+ cell "Index" datum per
formula cell, if I am understanding what I am seeing correctly.
<Row ss:Height="12.8376"><Cell ss:Index="11"/></Row> (the index number changes)
in the file, which is not necessary.
it is the equivalent of doing a double for loops across the rows and columns of
(I can't tell what) and outputting the data for the entire matrix, filling in
all the empty spaces of the sparse matrix, rather than walking the sparse matrix
and outputting the list of items you find.
this issue alone may be causing the "gigantic file" issue with my budget
spreadsheet, which has about 20 tabs and not a lot of data on most tabs.
I think it is this excessive cell style data is what is causing my larger
500-line spresdsheets to fail to save as XML files.
http://en.wikipedia.org/wiki/Sparse_matrix
This is generally how spreadsheets are represented in memory to save LOTS of
memory. with that you can handle HUGE spreadsheets. Something is wrong with
the OOo 2003 Excel XML file.
I think you are using sparse matrices, but something is going wrong with the
walk algorithm when it comes to handling those cell "Index" items. each of them
has its own surrounding <Row ...> </Row> elements. so as you can imagine, with
7-15 of these for every formula cell
something is VERY wrong with the OOo algorithm for handling "Index". The real
Excel file is much more sparse. compare them.
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@sc.openoffice.org
For additional commands, e-mail: issues-h...@sc.openoffice.org
---------------------------------------------------------------------
To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org
For additional commands, e-mail: allbugs-h...@openoffice.org
