Bug 137203 - Conditional Formats corrupted by save/exit/reload
Summary: Conditional Formats corrupted by save/exit/reload
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.1.0.0.alpha0+
Hardware: All Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2020-10-02 07:00 UTC by Jim Avera
Modified: 2022-12-01 05:54 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
t.ods (see Steps to Reproduce) (92.30 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-10-02 07:01 UTC, Jim Avera
Details
Data file which must be in /tmp/tickers.csv (see Steps to Reproduce) (723 bytes, text/csv)
2020-10-02 07:02 UTC, Jim Avera
Details
t.ods (corrected) (see Steps to Reproduce) (92.16 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-10-12 18:54 UTC, Jim Avera
Details
t.ods (corrected & fixed to diagnose missing .csv)(see Steps to Reproduce) (91.90 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-11-29 17:57 UTC, Jim Avera
Details
Updated test file t.ods (fixes comment #10)(See Steps to Reproduce & Comment after #14) (83.03 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-12-01 05:45 UTC, Jim Avera
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jim Avera 2020-10-02 07:00:47 UTC
Description:
If a Conditional Format uses a formula which references columns via Named Ranges, and the Named Ranges are not sheet-specific, then:

If a Save, Exit, and restart is done, the Conditional Formats are corrupted by changing all the column references from non-sheet-specific to referencing a *different* sheet (I suspect it is the last sheet).

However, just doing Save and Reload does _not_ cause the problem.  

Steps to Reproduce:
0. Download attached "t.ods" and "tickers.csv" into /tmp
     t.ods must be writable.
     tickers.csv MUST be at the path /tmp/tickers.csv

   Set Macro Security to "Medium" (Tools->Options->Security->Macro Security).

1. /path/to/soffice t.ods ; Enable macros when prompted

2. Click the yellow "Reload csv" button

   (sheet looks correct);

3. File->Save

4. File->Exit

5. /path/to/soffice t.ods

   (sheet looks wrong)


Actual Results:
After Step #2 (clicking "Reload csv"), 
  File->Format->Conditional->Manage
shows correct formulas which reference named columns like "Shading".

However after Save/Exit/Newstart/Load, the Conditional Format
formulas have been changed to specify another sheet, e.g. "TSLA.Shading".
Since the sheet references is not the same sheet, the results are "interesting".

Expected Results:
.


Reproducible: Always


User Profile Reset: No



Additional Info:
(The "Reload csv" button runs a macro which reads /tmp/tickers.csv into the SVXY sheet and copies the formats (but not data) from the _PROTO sheet, including Conditional Formats.)
Comment 1 Jim Avera 2020-10-02 07:01:53 UTC
Created attachment 166022 [details]
t.ods (see Steps to Reproduce)
Comment 2 Jim Avera 2020-10-02 07:02:52 UTC
Created attachment 166023 [details]
Data file which must be in /tmp/tickers.csv (see Steps to Reproduce)
Comment 3 Xisco Faulí 2020-10-12 18:06:54 UTC
Clicking in the yellow button prompts the following message: No .csv files in /home/jima/jts/.
Please attach the correct file pointing to /tmp
Comment 4 Jim Avera 2020-10-12 18:54:16 UTC
Created attachment 166321 [details]
t.ods (corrected) (see Steps to Reproduce)
Comment 5 Jim Avera 2020-10-12 18:55:37 UTC
@Xisco - My apologies!   I updated the attachment (t.ods)
Comment 6 QA Administrators 2020-10-13 04:48:29 UTC Comment hidden (obsolete)
Comment 7 Buovjaga 2021-11-25 15:11:46 UTC
When I run the macro, in 7.3 I get:

BASIC runtime error.
Argument is not optional.

for the line:
if IsMissing(dirlist_arg) OR IsEmpty(dirlist_arg) or dirlist_arg = "" then

In 7.2 I get:

Inadmissible value or data type.
Data type mismatch.

for the line:
dim BidVal as double : BidVal = CDbl(bid)

Can you fix this/these and check with 7.3?
I guess Linux-rpm_deb-x86_64@tb87-TDF-dbg would be easiest to test from https://dev-builds.libreoffice.org/daily/master/current.html
Comment 8 Jim Avera 2021-11-29 17:56:14 UTC
> BASIC runtime error.  Argument is not optional.

Hi Buovjaga, Yes, that is a code bug but happens only if it can not find file tickers.csv in /tmp (actually it searches for *.csv).   Please download tickers.csv from this bug and place it in /tmp and try again.



I re-confirmed the bug on 7.4 alpha, downloaded 
from https://dev-builds.libreoffice.org/daily/master/Linux-rpm_deb-x86_64@tb87-TDF/2021-11-28_06.35.40/LibreOfficeDev_7.4.0.0.alpha0_Linux_x86-64_deb.tar.gz

P.S. I'll upload a revised test spreadsheet which prints a proper diagnostic if the .csv file can not be found.
Comment 9 Jim Avera 2021-11-29 17:57:39 UTC
Created attachment 176574 [details]
t.ods (corrected & fixed to diagnose missing .csv)(see Steps to Reproduce)
Comment 10 Buovjaga 2021-11-29 19:47:38 UTC
I did have it in /tmp. Tried again with your new file with 7.4 and now I run into

(In reply to Buovjaga from comment #7)
> Inadmissible value or data type.
> Data type mismatch.
> 
> for the line:
> dim BidVal as double : BidVal = CDbl(bid)
Comment 11 Xisco Faulí 2022-05-02 14:04:01 UTC
A new major release of LibreOffice is available since this bug was reported.
Could you please try to reproduce it with the latest version of LibreOffice
from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.
Comment 12 QA Administrators 2022-10-30 03:49:28 UTC Comment hidden (obsolete)
Comment 13 QA Administrators 2022-11-30 03:52:07 UTC Comment hidden (obsolete)
Comment 14 Jim Avera 2022-12-01 05:45:14 UTC
Created attachment 183930 [details]
Updated test file t.ods (fixes comment #10)(See Steps to Reproduce & Comment after #14)
Comment 15 Jim Avera 2022-12-01 05:54:20 UTC
With latest master the corrupted Conditional Formats no longer occur, but a crash occurs every time when starting LO the second time in the test sequence, i.e. after running the macros and doing a save and exit.

Since this looks like a different bug, I'm going to close this as WORKSFORME and open a new bug for that crash.

-Jim

Version: 7.5.0.0.alpha1+ (X86_64) / LibreOffice Community
Build ID: 98d8adc5b377039d5dee0d5046ece721010a960c
CPU threads: 12; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded