Issue 124625 - large row numbers exceeding .xls limit become unpredictable broken row numbers in .xls
Summary: large row numbers exceeding .xls limit become unpredictable broken row numbe...
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: save-export (show other issues)
Version: 3.3.0 or older (OOo)
Hardware: All All
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-04-07 23:20 UTC by Wolfgang Kleinoeder
Modified: 2014-04-09 05:23 UTC (History)
3 users (show)

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

Attachments
Calligra Sheets vs. Calc (229.94 KB, image/png)
2014-04-08 13:27 UTC, Edwin Sharp 		no flags Details
Excel 2013 (13.87 KB, image/png)
2014-04-08 13:39 UTC, Andre 		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 Wolfgang Kleinoeder 2014-04-07 23:20:00 UTC 
Please export this spreadsheet as .xls, and read it again.

Sheet1 is empty
Sheet2 contains in A1

=SUM($Sheet1.A1:A65535) --> =SUM($Sheet1.A1:A65535)			OK
=SUM($Sheet1.A1:A65536) --> =SUM($Sheet1.A1:A1048576)			wrong
=SUM($Sheet1.A1:A65537) --> =SUM($Sheet1.#REF!#REF!:#REF!#REF!)		broken

(I am pretty sure that this is a very simple bug, just change somewhere SHORT to INT).
Comment 1 Edwin Sharp 2014-04-08 06:42:47 UTC 
Confirmed with
AOO410m14(Build:9760)  -  Rev. 1585331
2014-04-07_04:11:07 - Rev. 1585379
Debian
Comment 2 Andre 2014-04-08 07:24:39 UTC 
I am not sure that this is really an error.  When I save the document in Excel 97 format I am warned that the document can not be converted.  I suspect that that format does not allow row numbers that take more than 16 bits to encode.
Comment 3 Edwin Sharp 2014-04-08 13:27:57 UTC 
Created attachment 83136 [details]
Calligra Sheets vs. Calc
Comment 4 Andre 2014-04-08 13:39:02 UTC 
Created attachment 83137 [details]
Excel 2013
Comment 5 Rainer Bielefeld 2014-04-09 04:31:09 UTC 
Definitively a bug, but
UNCONFIRMED until we know whether EXCEL 97 supports those row numbers and some additional details have been clarified
Comment 6 Rainer Bielefeld 2014-04-09 05:23:27 UTC 
Excel Specifications can be found here:
<http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx>: 
Maximum limit 65536 rows by 256 columns

(a) If I save a document with contents in rows 65536 or bigger I get a warning
    "too many rows for target format". That warning is missing in LibO, 
    AOO does it better
(b) No concrete message if only a reference in a formula has a too large
    row number
(c) The problem is not limited to SUM. Also in Additions seem to be broken
(d) Currently I am not 100% sure, but it seems that export to .xls breaks 
    Numbering in a crazy way. A row number 100 000 will be shown as 34464
    (what is the difference betwene 100 000 and excel limit 65536 rows, 
    But the correct looking "Tabelle1.A34464)" does not work before I replace
    a digit with the same digit typed manually
(e) There also is a problem with simple references to large row numbers: 
    "=Tabelle1.A65535" ub .ods becomes =$#REF!.IW65535  in exported .xls
    what might be related 
(f) I think too big row numbers simply should be cut to .xls maximum. 
(g) similar problems (but with different details in some cases) already with
    OOo 3.3.0

This research still is rather incomplete, not "ready for fixing", but I currently do not have the time to continue.