Bug 65371

Summary: FILESAVE: Cell Styles applied on Conditional Formatting change after save as XLS & reopen file
Product: LibreOffice Reporter: dfrerichs
Component: SpreadsheetAssignee: Not Assigned <libreoffice-bugs>
Status: NEW --- QA Contact:
Severity: normal    
Priority: medium CC: bdpita, dfrerichs, kdinse
Version: 4.0.4.1 rc   
Hardware: x86 (IA32)   
OS: Windows (All)   
Whiteboard:
i915 platform: i915 features:
Attachments: Spreadsheet with Conditional Formatting

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. ***

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.