Download it now!
Bug 65371 - FILESAVE: Cell Styles applied on Conditional Formatting change after save as XLS & reopen file
Summary: FILESAVE: Cell Styles applied on Conditional Formatting change after save as ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.4.1 rc
Hardware: x86 (IA32) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 67645 (view as bug list)
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2013-06-04 19:03 UTC by dfrerichs
Modified: 2018-08-16 02:37 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet with Conditional Formatting (248.50 KB, application/vnd.ms-excel)
2013-06-04 19:03 UTC, dfrerichs
Details

Note You need to log in before you can comment on or make changes to this bug.
Description dfrerichs 2013-06-04 19:03:26 UTC
Created attachment 80306 [details]
Spreadsheet with Conditional Formatting

Windows XP SP3

LibreOffice 4.0.4.1 RC1


In a spreadsheet with conditional formatting that is saved as an .xls, the formatting adds twelve decimal points to the number when reopening the file. The original spreadsheet that was created in Excel 2003 shows conditional formatting style "Excel_CondFormat_1_2_1". If the file is saved as an .ods file it works fine.

I created a new conditional formatting style that had no decimal places with red text. I named this style "Format1". The cells changed as expected, but when the file was saved and reopened, the conditional formatting style returned to "Excel_CondFormat_1_2_1" with twelve decimal points added to the number.

Attached is a spreadsheet that can be used to verify this behavior.
Comment 1 ign_christian 2013-06-07 16:40:31 UTC
I think you should give step by step procedure to see that problem from scratch.
Comment 2 dfrerichs 2013-06-07 20:44:06 UTC
Ok, here are the steps to reproduce this.

Go to Calc -> Format -> Conditional Formatting -> Manage
1. Add -> Cell value is -> less than -> 84.5
2. Apply Style -> New Style
3. Organizer Tab -> Name -> Format1
4. Numbers Tab -> Category -> Number, Format -> -1,234 (with zero decimal places, Format Code #,##0)
5. Font Effects Tab ->Font Color -> Light Red -> OK
6. Add -> Condition 2 -> Cell value is -> greater than -> 115.5
7. Apply Style -> Format1
8. Range -> A1:A10 -> Click OK in Conditional Formatting Box, then Click OK in Manage Conditional Formatting Box

Highlight Column A and center it 
Go to cell A1 and type in 123.123
Go to cell A2 and type in 123.1234
Go to cell A3 and type in 123
Go to cell A4 and type in 13.123
Go to cell A5 and type in 13

You will see that all the numbers display without decimal places, just like they should, even though they read with the correct decimals in the formula bar.
Now save as "test sheet" in .xls format.
Close the sheet. Reopen the sheet and you will see that the decimals places are now showing in Column A.
Go to Format -> Conditional Formatting -> Manage
Highlight Range A1:A10 -> Click Edit
Under Condition 1, where it says "Apply Style", the style is no longer "Format 1", 
it now says "Excel_CondFormat_1_1_1" (which was never an option before)
Under Condition 2, the Apply Style has changed to "Excel_CondFormat_1_1_2"
Hit the drop down arrow and change the Apply Style back to "Format1" for both Condition 1 and Condition 2, then hit OK twice.
The decimals in column A are now gone, as they should be.

This time, save as "test sheet" in .ods format.
Close the sheet.
Reopen the .ods sheet and you will see that the formatting is correct and the "Apply Style" saves as "Format1", as it should.

So, it seems that conditional formatting is saved correctly when saved as .ods but not when saved as .xls
Comment 3 ign_christian 2013-06-08 05:11:30 UTC
Thanks for really clear description :)
I can confirm reproducible on LO 4.0.4.1 (Win7 32bit)

Cell Styles not remembered when applied on Conditional Formatting. It changes after saving to XLS & reopening that file.

Reopening previously saved file magically produces new Cell Styles:
(we can see that by hitting button 'Styles and Formatting' or F11)
- Excel_CondFormat_1_1_1
- Excel_CondFormat_1_1_2
Above styles replace previously saved styles applied on Conditional Formatting 
(in this test case: Format1)

That behavior not occured if saving as ODS (correctly saved). Saving as XLSX generates different behavior that should be reported on another bug report.
Comment 4 Markus Mohrhard 2013-06-27 18:19:12 UTC
> That behavior not occured if saving as ODS (correctly saved). Saving as XLSX
> generates different behavior that should be reported on another bug report.

Can you please link the bug for XLSX? I'm more motivated to fix the XLSX cases than looking again in the fun with the binary filters.
Comment 5 Jorendc 2013-08-03 09:25:52 UTC
*** Bug 67645 has been marked as a duplicate of this bug. ***
Comment 6 QA Administrators 2015-04-19 03:20:48 UTC Comment hidden (obsolete)
Comment 7 Buovjaga 2015-06-15 16:29:48 UTC
Repro per comment 2

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+
Build ID: 01a189abcd9a4ca472a74b3b2c000c9338fc2c91
TinderBox: Win-x86@39, Branch:master, Time: 2015-06-14_07:46:28
Locale: fi-FI (fi_FI)
Comment 8 QA Administrators 2016-09-20 10:01:30 UTC Comment hidden (obsolete)
Comment 9 tom1williams 2017-08-14 16:47:16 UTC
Today is the first day I learned about Bugzilla and this whole process. I put this same comment bellow into a different bug report. I hope that is not against the rules.

In my job I must add data to a corporate XLSX file with color related conditional formatting. Every time I saved that file as XLSX in any version of Libre Calc prior to 5.4, all of the colors and conditional formatting were lost.

My work around was/is awful. I have to download the file on my Linux system, yank out the cat5 cable, reboot into an unpatched copy of Windows, use an unpatched copy of MS Office to enter the data, reboot into Linux, re-insert the cat5 cable, upload the file, and repeat this process at least monthly.

In my daily work all other XLSX files have no issues, but then they have no colors related conditional formatting. Recently I upgraded to Libre Office 5.4. Then I accidentally edited and saved that one special corporate XLSX file. I was shocked to find that the colors related conditional formatting seems to be intact!

Could someone please confirm that there was a fix made to saving colors related conditional formatting in XLSX files between 5.3 and 5.4? If confirmed this may be my big chance to get rid of my old MS software.
Comment 10 Buovjaga 2017-08-14 17:17:59 UTC
(In reply to tom1williams from comment #9)
> Could someone please confirm that there was a fix made to saving colors
> related conditional formatting in XLSX files between 5.3 and 5.4? If
> confirmed this may be my big chance to get rid of my old MS software.

Well, here are changes with a commit message containing conditional format: https://cgit.freedesktop.org/libreoffice/core/log/?qt=grep&q=conditional+format
As you can see there have been several changes this year, so maybe you got lucky.
Comment 11 tom1williams 2017-08-15 13:50:11 UTC
(In reply to Buovjaga from comment #10)
> (In reply to tom1williams from comment #9)
> > Could someone please confirm that there was a fix made to saving colors
> > related conditional formatting in XLSX files between 5.3 and 5.4? If
> > confirmed this may be my big chance to get rid of my old MS software.
> 
> Well, here are changes with a commit message containing conditional format:
> https://cgit.freedesktop.org/libreoffice/core/log/
> ?qt=grep&q=conditional+format
> As you can see there have been several changes this year, so maybe you got
> lucky.

Thank you, that is very helpful!
Comment 12 QA Administrators 2018-08-16 02:37:32 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug