Description: FOSS software 'openxlsx' produces an XLSX workbook with non-table filters. The XML within the output file defines the autoFilter XML tag correctly in each spreadsheet, e.g. <autoFilter ref="A1:E11"/>. However, when the file is opened with Libreoffice Calc, only the last sheet presents the filters. (The filters present correctly when opened with Excel.) Steps to Reproduce: 1. Open the attachment. 2. Click "Sheet 1", to observe that it contains no filters. 3. Click "Sheet 2", to observe that it contains filters. 4. Review the XML in "/xl/worksheets/" for "Sheet1" and "Sheet2", and note that the files are nearly identical. Actual Results: Sheet 1 has no filters, but Sheet 2 has filters. (See the uploaded workbook.) Expected Results: Both Sheet 1 and Sheet 2 should have filters. (See the uploaded workbook.) Reproducible: Always User Profile Reset: No Additional Info: Tested on Ubuntu running Libreoffice 7.3, and on Windows running Libreoffice 7.6. I've also opened the file with MS Office Excel, which shows both sheets with auto filters. This bug occurs when there is more than one sheet. If 4 sheets, the first 3 will have no filters. (Also, it does not matter if the files contain identical data. The corresponding workbook contains identical data in each sheet for clarity.) Also, I love LibreOffice; thank you for making a great product.
Created attachment 190120 [details] An example showing two or more sheets in the workbook, where each sheet has identical XML, but only the final sheet presents filters. Example 1 To recreate the xlsx file, the `openxlsx` code is: library(openxlsx) wb = createWorkbook() addWorksheet(wb, "Sheet 1") addWorksheet(wb, "Sheet 2") writeData(wb, 1, x = iris, withFilter = TRUE) writeData(wb, 2, x = iris, withFilter = TRUE) saveWorkbook(wb, file = "FilterExample.xlsx")
Created attachment 190121 [details] An example showing two or more sheets in the workbook, where each sheet has identical XML, but only the final sheet presents filters. 2nd example
Created attachment 190122 [details] An example showing two or more sheets in the workbook, where each sheet has identical XML, but only the final sheet presents filters. 3rd example
Thank you Hayward for the report and the kind words! (And great to see fellow R users around here :)) I can confirm that: - the 3 example files only have the autofilter active in the last sheet - MS Office 365 shows the autofilter active on all sheets - this behaviour is an old regression: using the linux-64-releases bibisect repository, I could not reproduce in version 3.3.4 but I could in 3.4.0rc1. - still reproduced in a recent trunk build: Version: 24.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: fd69b546ad36452560cb11ccb28e78632d65f045 CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Looking at the release notes[1], I'm wondering if this is related to: commit 7df4c7feacf51c141a7beaa2a8404d932d7c2e86 Author: Markus Mohrhard Date: Thu Mar 24 23:14:28 2011 -0400 Support sheet-local anonymous database ranges. This is for an Easy Hack to allow one autofilter per sheet, not per document. Markus, I know this is from 12 years ago, but any idea here? [1]: https://wiki.documentfoundation.org/ReleaseNotes/3.4