Bug 139536 - [XSLX] Conditional Formatting is lost on import
Summary: [XSLX] Conditional Formatting is lost on import
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.0.4.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX-Conditional-Formatting
  Show dependency treegraph
 
Reported: 2021-01-11 11:02 UTC by Dennis Roczek
Modified: 2021-09-08 09:37 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
file from excelvorlage.de (41.91 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-01-11 11:03 UTC, Dennis Roczek
Details
The original xlsx file in Excel and Calc 7.2 (195.44 KB, image/png)
2021-01-11 13:42 UTC, NISZ LibreOffice Team
Details
Good working example in MSO O365 (128.86 KB, image/png)
2021-01-11 14:34 UTC, Dennis Roczek
Details
Modified example file (65.04 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-09-08 09:28 UTC, NISZ LibreOffice Team
Details
Conditional formatting rules of the modified example in Excel and Calc (191.88 KB, image/png)
2021-09-08 09:30 UTC, NISZ LibreOffice Team
Details
Named ranges of the modified example in Excel and Calc (176.35 KB, image/png)
2021-09-08 09:37 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dennis Roczek 2021-01-11 11:02:14 UTC
Description:
1. get file at http://www.excelvorlage.de/entry/118/kreditberechnung-mit-sondertilgungen-2
2. Fill fields: D4=100.000; D5=2%; D6=5; D7=01.01.2021; D8=500
3. press F9
4. scroll down to line 65: everything 65 and following lines should be hidden per conditional formatting

Steps to Reproduce:
see above

Actual Results:
see above

Expected Results:
see above


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Version: 7.0.4.2 (x64)
Build ID: dcf040e67528d9187c66b2379df5ea4407429775
CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded
Comment 1 Dennis Roczek 2021-01-11 11:03:05 UTC
Created attachment 168814 [details]
file from excelvorlage.de
Comment 2 Dennis Roczek 2021-01-11 11:04:05 UTC
You might have to click F9 when being in cell G18. A separate ticket will be filled in a minute.
Comment 3 NISZ LibreOffice Team 2021-01-11 13:42:26 UTC
Created attachment 168817 [details]
The original xlsx file in Excel and Calc 7.2

I don't get the expected result in Excel 2013 either. All rows are visible between 18 and 377.

Nor in:
Version: 7.2.0.0.alpha0+ (x64)
Build ID: 8e691505d4675b878b30bd00cd2e4fb4f794f0ef
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: CL

Both Excel and Calc give and #N/A error in D13 when filled with the given numbers.
Comment 4 Dennis Roczek 2021-01-11 14:34:08 UTC
Created attachment 168820 [details]
Good working example in MSO O365

See line 64 and later: they are "hidden" by color. The second conditional formatting rule is not imported in Calc.
Comment 5 NISZ LibreOffice Team 2021-09-08 09:28:11 UTC
Created attachment 174879 [details]
Modified example file

I managed to get it visible in Excel by leaving the D8 field empty. Also changed the CF rule a bit, to make the invisible text formatting orange instead, so it's presence more clearly visible.

Can confirm that the formatting is not applied to the text in:

Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: c7b5e6566d9b24a0a996c739a945004d9aadee2f
CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: default; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: CL

The conditions look to be imported fine though.

To me it looks more like an issue with the forest of named ranges containing formulae, which is used in the condition. I could not minimize it enough yet to understand which step does not work.
Comment 6 NISZ LibreOffice Team 2021-09-08 09:30:19 UTC
Created attachment 174880 [details]
Conditional formatting rules of the modified example in Excel and Calc

These seem to be imported fine.
Comment 7 NISZ LibreOffice Team 2021-09-08 09:37:00 UTC
Created attachment 174883 [details]
Named ranges of the modified example in Excel and Calc

This part is more suspicious... the condition for the text coloring seems to be imported, but does not work:

IF(ROW(A18)>Last_Row;1; 0)

Last_Row = IF(Values_Entered;Header_Row+Number_of_Payments;Header_Row)
   Values_Entered = IF(Loan_Amount*Interest_Rate*Loan_Years*Loan_Start>0;1;0)
   Header_Row = ROW($Tilgungstabelle.$17:$17)
   Number_of_Payments = MATCH(0,01;End_Bal;-1)+1

Loan_Amount, Interest_Rate, Loan_Years, Loan_Start are references to D4:D7 cells.
End_Bal = $Tilgungstabelle.$I$18:$I$377