Bug 63960

Summary:	FILESAVE: Formula string truncated when longer than 255 letters
Product:	LibreOffice	Reporter:	Tomas Vavrda <tvavrda>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	CLOSED NOTABUG
Severity:	major	CC:	bfo.bugmail, erack, jmadero.dev, jorendc
Priority:	medium
Version:	4.0.2.2 release
Hardware:	x86-64 (AMD64)
OS:	macOS (All)
Whiteboard:
Crash report or crash signature:		Regression By:
Attachments:	Dialog Warning about XLS Saving

Description Tomas Vavrda 2013-04-26 13:30:47 UTC

When part of the formula is a string in quotes (e.g. "this is a string") which is more than 255 letters in total, this string is truncated when saving.

Steps to reproduce:
1. Create a new spreadsheet
2. Create a formula with a quoted string that is longer than 255 letters
3. Save the spreadsheet
4. Open the spreadsheet again.
5. The string will be truncated.

When doing the same in MS Excel the user gets an error while editing the formula, asking him to shorten the string by e.g. splitting it and use concatenation functions.

Comment 1 Urmas 2013-04-26 13:55:08 UTC

I cannot reproduce it with ODS spreadsheet, but with old Excel format only. Are you trying to save it as Excel spreadsheet? Which version of Excel?

Comment 2 Tomas Vavrda 2013-04-26 14:01:14 UTC

Originally that was an XLS document. But I saved it to ODS (from XLS) and it did the same. I actually did not try to save a brand new document.

Comment 3 Joel Madero 2013-04-30 22:40:12 UTC

This is a limitation of .xls I believe. I can confirm that behavior (INVALID as it's a limitation of xls). 

For saving to ODS - this works fine for me - saved from a xls file, had 255 characters, wrote 100 more, saved as ods, everything was there.

will get one more  opinion (OSX QA member before closing this)

Joren - you mind trying this out?


Version: 4.1.0.0.alpha0+Build ID: 6e3e6ef7257e93743a72719581ef6fe0016e58e
Date:   Thu Apr 11 15:24:38 2013 +0200 
Platform: Bodhi Linux 2.2 x64

Comment 4 Tomas Vavrda 2013-05-01 05:53:58 UTC

Are you sure the user shall not be warned before his work is lost because of whatever reason?

Comment 5 Joel Madero 2013-05-01 06:10:03 UTC

you are warned when you save as a xls with the "confirm file format" dialog.

It is the users responsibility to know the limitations of file formats

Comment 6 Joel Madero 2013-05-01 06:12:01 UTC

Created attachment 78689 [details]
Dialog Warning about XLS Saving

Comment 7 bfoman (inactive) 2013-06-26 12:59:30 UTC

See bug 35411 for general discussion about max formula length in Calc.

Comment 8 Joel Madero 2013-06-28 18:50:42 UTC

Eike - reading the report that bfoman put - I'm curious if I am wrong and the limit isn't 255 for xls. Mind confirming - is this a dupe of the other one (I think the other one is about xlsx though).

Reading documentation about xls specs it looks like 1,024 characters may be the limit - not sure why we're limiting to 255.

Comment 9 Eike Rathke 2013-07-01 19:38:25 UTC

Excel in a formula expression does not accept literal string values longer than 255 characters. This is not to be confused with a max formula length, be it characters, tokens or other limits discussed in bug 35411.

Attempting to enter this formula in Excel2010
="1234567891123456789212345678931234567894123456789512345678961234567897123456789812345678991234567890123456789112345678921234567893123456789412345678951234567896123456789712345678981234567899123456789012345678911234567892123456789312345678941234567895123456"

results in an error box "Text values in formulas are limited to 255 characters. ..."