Bug 145058

Summary:	FILESAVE XLSX Adding new sheet with conditional formatting loses autofilter condition
Product:	LibreOffice	Reporter:	Gabor Kelemen (allotropia) <kelemeng>
Component:	Calc	Assignee:	Samuel Mehrbrodt (allotropia) <samuel.mehrbrodt>
Status:	RESOLVED DUPLICATE
Severity:	normal	CC:	samuel.mehrbrodt, xiscofauli
Priority:	medium	Keywords:	filter:xlsx, implementationError
Version:	7.3.0.0 alpha0+
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	142076
Attachments:	Example file from Excel The example file modified and saved in Calc to XLSX Screenshot of the problem in Calc The example file modified and saved in Calc to ODS The modified ODS saved to XLSX – works For reference: the example file modified in Excel 13

Description Gabor Kelemen (allotropia) 2021-10-11 06:51:31 UTC

Created attachment 175642 [details]
Example file from Excel

When an XLSX file with autofilter set on a sheet gets a new sheet added and data is filtered using conditional formatting, the autofilters condition gets lost when saving the file as XLSX.
Starting from an ODS file this problem does not happen.

Steps to reproduce:
    1. Open the attached file which has an autofilter on Sheet1 but no conditions set
    2. Add a new sheet
    3. Fill the sheet with some data, and set up conditional formatting on this new data. I set green text color to text starting with M and blue text color on text starting with T in column B.
    4. Set an autofilter to the green text color.
    5. Save as XLSX and reload.

Actual results:
The autofilter dropdown does not indicate the green text color being applied to the column B. This happens both in Calc and Excel.

Expected results:
The autofilter should indicate the filter condition on the second sheet as well.

LibreOffice details:
Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: b1488cd6f008049a9aaff7350deeb73cbbd535b6
CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: hu-HU (en_US); UI: en-US
Calc: threaded

Comment 1 Gabor Kelemen (allotropia) 2021-10-11 06:51:53 UTC

Created attachment 175643 [details]
The example file modified and saved in Calc to XLSX

Comment 2 Gabor Kelemen (allotropia) 2021-10-11 06:52:14 UTC

Created attachment 175644 [details]
Screenshot of the problem in Calc

Comment 3 Gabor Kelemen (allotropia) 2021-10-11 06:52:35 UTC

Created attachment 175645 [details]
The example file modified and saved in Calc to ODS

Comment 4 Gabor Kelemen (allotropia) 2021-10-11 06:52:56 UTC

Created attachment 175646 [details]
The modified ODS saved to XLSX – works

Comment 5 Xisco Faulí 2021-10-22 09:28:09 UTC

hi Gabor,
is it a dupe of bug 145057 ?

Comment 6 Gabor Kelemen (allotropia) 2021-10-22 12:09:25 UTC

(In reply to Xisco Faulí from comment #5)
> hi Gabor,
> is it a dupe of bug 145057 ?

I wouldn't think. That one starts with a data range ("Format as Table" in Excel) and that's a different thing than a simple autofilter (although it may be filtered).

Comment 7 Samuel Mehrbrodt (allotropia) 2021-12-07 07:45:39 UTC

Confirmed.

Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: fd11f019e8b7253131bdb09f8023f15cd6525bf8
CPU threads: 8; OS: Linux 5.13; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: CL

Comment 8 Gabor Kelemen (allotropia) 2021-12-22 16:28:32 UTC

Created attachment 177100 [details]
For reference: the example file modified in Excel 13

Comment 9 Samuel Mehrbrodt (allotropia) 2021-12-22 19:28:23 UTC

(In reply to Gabor Kelemen (allotropia) from comment #6)
> (In reply to Xisco Faulí from comment #5)
> > hi Gabor,
> > is it a dupe of bug 145057 ?
> 
> I wouldn't think. That one starts with a data range ("Format as Table" in
> Excel) and that's a different thing than a simple autofilter (although it
> may be filtered).

So the root cause was indeed the same - when exporting, only the first data range was detected, all others were ignored.

Please retest once the fix from bug 145057 is merged.

*** This bug has been marked as a duplicate of bug 145057 ***