Bug 75167 - Tools | Options | LibreOffice Calc | Calculate | Enable Regular Expressions in formulas setting not saved
Summary: Tools | Options | LibreOffice Calc | Calculate | Enable Regular Expressions i...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
4.1.5.3 release
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: XLS-Limitations XLSX-Limitations
  Show dependency treegraph
 
Reported: 2014-02-18 18:19 UTC by Dave
Modified: 2015-01-07 20:10 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Dave 2014-02-18 18:19:31 UTC
Hi,

In the very latest version of Libre Calc, the settings in Tools | Options | LibreOffice Calc | Calculate | "Enable Regular Expressions in formulas" is not saved between exiting the spreadsheet. 

This should be a global option that is saved and returned to it's previous state regardless of the file that is open.

Thank you!

Dave
Comment 1 Dominique Boutry 2014-02-20 10:50:46 UTC
Hi.

I can't understand your sentence "...is not saved between exiting the spreadsheet.".
Could you please provide a step by step ?

Thanks
Comment 2 Dave 2014-02-21 11:55:30 UTC
Ok.

2 scenarios:

SCENARIO 1
* Start Libre Calc
* Go to Tools | Options | LibreOffice Calc | Calculate | "Enable Regular Expressions in formulas" - CHECK the box.
* Shutdown Calc. Do not save the Untitled spreadsheet.
* Restart Calc.
* Go to Tools | Options | LibreOffice Calc | Calculate. 
NOTE: "Enable Regular Expressions in formulas" is still CHECKED and Calc has remembered the user setting.


SCENARIO 2
* Start Libre Calc
* Go to Tools | Options | LibreOffice Calc | Calculate | "Enable Regular Expressions in formulas" - CHECK the box.
* Shutdown Calc. Do not save the Untitled spreadsheet.
* Find a spreadsheet in Windows explorer. 
* Double click the spreadsheet to open it in the associated Libre Calc.
  (the spreadsheet does not have to contain use of regular expressions).
* Go to Tools | Options | LibreOffice Calc | Calculate. 
NOTE: "Enable Regular Expressions in formulas" is CLEARED. Calc has failed to restore the previous user setting.

SCENARIO 2 shows the bug.

Does that make sense now?
Comment 3 Dominique Boutry 2014-02-23 14:44:39 UTC
OK, thank you.
I reproduce the behaviour with LibO 4.2.1.1 on Win7.

I understand that part of the options are global (as should be "regular expression", you say), and that another part is carried by a document (as "Date [origin]" in the same Tab). What criteria to fall into one category or the other? I wonder whether some people may build Calc applications that build texts to be interpreted as regular expression, and would therefore prefer that the "regular expression" option be carry by their documents.

Status put back to "Unconfirmed" to gather additionnal advices.
Comment 4 Dave 2014-02-24 09:53:27 UTC
I think I have more information that pins down what is happening here. It is a difference between saving spreadsheets in Excel XLS format and ODS format:

SCENARIO 3 - XLS vs ODS
* Start Libre Calc
* Go to Tools | Options | LibreOffice Calc | Calculate | "Enable Regular Expressions in formulas" - CHECK the box.
* Enter the following data into cells:

   A					B
1  Data1				1
2  DataEnter2				2
3  =VLOOKUP("DataE.*",A1:B2,2,0)

* Save the spreadsheet "Untitled1.xls" - Excel format.

* Open "Untitled1.xls" spreadsheet from Windows explorer. 
* Go to Tools | Options | LibreOffice Calc | Calculate. 
*************************************************
NOTE: "Enable Regular Expressions in formulas" is CLEARED. Calc has failed to restore the previous user setting. Cell A3 shows "#N/A"
*************************************************

* With the XLS spreadsheet still open:
Go to Tools | Options | LibreOffice Calc | Calculate | "Enable Regular Expressions in formulas" - CHECK the box.
* Save the spreadsheet in ODS format.
* Close the spreadsheet.
* Open the spreadsheet again.
* Go to Tools | Options | LibreOffice Calc | Calculate. 
*************************************************
NOTE: "Enable Regular Expressions in formulas" is CHECKED. Calc has stored the setting in the ODS format of file. Cell A3 shows correct result.
*************************************************


**************************
* CONCLUSION
**************************
Understandably, Calc does not save Regular Expression settings in a format of spreadsheet that is incompatible with Excel (Excel does not support Regular Expressions). 

If a user saves a spreadsheet into a XLS format, user must be warned that certain features are contained within the spreadsheet that will be lost as a result of using a different format.

Options available in Tools | Options could clearly label or indicate which features are available:

* as a GLOBAL setting
* as a DOCUMENT setting
* as an ODS DOCUMENT setting


I realise this might be quite a bit of work but it may save the headaches of people like me using the software and getting what appear to be unpredictable results. 

Hope all that helps.
Comment 5 John Rasor 2014-09-17 15:12:57 UTC
I can reproduce the behavior reported by Dave.

Platform: Xubuntu 14.04 on 64-bit AMD homebuilt.
Comment 6 Buovjaga 2014-11-14 08:34:53 UTC
(In reply to Dave from comment #4)
> If a user saves a spreadsheet into a XLS format, user must be warned that
> certain features are contained within the spreadsheet that will be lost as a
> result of using a different format.
> 

There is this warning by default:
This document may contain formatting or content that cannot be saved in the currently selected file format “Microsoft Excel 97/2000/XP/2003”.

Do you propose to add more information to this?
Comment 7 Robinson Tryon (qubit) 2014-12-30 01:56:39 UTC
(In reply to Beluga from comment #6)
> There is this warning by default:
> This document may contain formatting or content that cannot be saved in the
> currently selected file format “Microsoft Excel 97/2000/XP/2003”.
> 
> Do you propose to add more information to this?

status -> NEEDINFO
Comment 8 Dave 2014-12-30 10:52:51 UTC
Although there is a warning on saving the document of "This document may contain formatting or content that cannot be saved in the currently selected file format “Microsoft Excel 97/2000/XP/2003” as you said, I still think that the options need to be marks "ODS File format only" in the options. 

Therefore, suggestion: Tools | Options | LibreOffice Calc | Calculate | "Enable Regular Expressions in formulas" needs a note below it: "ODS Files only".

It will save a lot of confusion & support time for confused users. No user remembers switching on "Enable Regular Expressions in formulas" after working with a sheet for an hour+ and then relates that specific option to the warning message about Excel 97/2000/XP/2003 file formats.

If you wish to close this bug, by all means. I leave this suggestion in your capable hands.
Comment 9 Robinson Tryon (qubit) 2015-01-07 20:10:20 UTC
TESTING with Ubuntu 14.04 + LO 4.4.0.1

(Want to test with XLSX format as well)

(In reply to Dave from comment #2)
> 
> SCENARIO 2
> * Start Libre Calc
> * Go to Tools | Options | LibreOffice Calc | Calculate | "Enable Regular
> Expressions in formulas" - CHECK the box.

Already checked

I typed "turtle" in cell A1, and saved the file as ODS, XLS, and XLSX. Then I closed LibreOffice and opened the files one by one.

> * Go to Tools | Options | LibreOffice Calc | Calculate. 
> NOTE: "Enable Regular Expressions in formulas" is CLEARED. Calc has failed
> to restore the previous user setting.

RESULTS:
- ODS:  [x] Enable Regular Expressions in formulas
- XLS:  [ ] Enable Regular Expressions in formulas (as expected)
- XLSX: [ ] Enable Regular Expressions in formulas (ALSO unchecked)


Okay, so looks like this affects XLSX as well:
Blocks -> XLSX-limitations