Description: Spreadsheet with Database Range is incorrect when the document is opened in Microsoft Excel. Steps to Reproduce: 1. Create a new spreadsheet in LibreOffice Calc. 2. Create a Database Range. (Data→Define Database Range) 3. Save the file as XLSX. 4. Open the same file in Microsoft Excel. Actual Results: Error message: Excel found unreadable content in filename... Excel was able to open the file by repairing or removing the unreadable content. Expected Results: Spreadsheet should open correctly in Excel. The first row of the database range shouldn’t be header row. Reproducible: Always User Profile Reset: No Additional Info:
Created attachment 148551 [details] Screenshot of the problem in Excel.
Created attachment 148552 [details] Example file from Calc with database range.
Thank you for reporting the bug. I can confirm this in Version: 6.3.0.0.alpha0+ Build ID: 3c964980da07892a02d5ac721d80558c459532d0 CPU threads: 2; OS: Windows 6.1; UI render: default; VCL: win; TinderBox: Win-x86@42, Branch:master, Time: 2018-12-12_02:07:45 Locale: en-US (en_US); UI-Language: en-US Calc: threaded
Also reproduced in Version: 5.2.0.0.alpha0+ Build ID: 3ca42d8d51174010d5e8a32b96e9b4c0b3730a53 Threads 4; Ver: 4.15; Render: default; but not in Version: 4.3.0.0.alpha1+ Build ID: c15927f20d4727c3b8de68497b6949e72f9e6e9e
NISZ Team: would it be possible for you to bibisect this as having MSO + bibisecting capabilities is a somewhat rare combination? You can follow this tutorial, if there is need for training: https://wiki.documentfoundation.org/QA/Bibisect/Bibisecting_tutorial
Created attachment 148708 [details] Bibisect log file Bibisected with: bibisect-win32-5.1 Regression introduced by: commit 61d203fab63ef4f4863e8b2308e62bd245aa62db [log] author Eike Rathke <erack@redhat.com> Mon Aug 31 20:55:18 2015 +0200 committer Eike Rathke <erack@redhat.com> Mon Aug 31 20:56:12 2015 +0200 tree 265ab6dc301f451ef57dce8f467110a2c310eb57 parent f284678e334b02808a6c2d473ce683745c99d08e [diff] But: In the previous versions the Database Range is lost, when the spreadsheet is saved in XLSX format.
(In reply to NISZ LibreOffice Team from comment #6) > But: > In the previous versions the Database Range is lost, when the spreadsheet is > saved in XLSX format. Ok, so it's not a regression after all, but an improvement.
This seems to have begun at the below commit. Adding Cc: to Eike Rathke; Could you possibly take a look at this one? Thanks f0f65f2aeef6afe9b25d9d7c97afc4f59539b6fe is the first bad commit commit f0f65f2aeef6afe9b25d9d7c97afc4f59539b6fe Author: Norbert Thiebaud <nthiebaud@gmail.com> Date: Tue Sep 1 16:44:44 2015 -0700 source 61d203fab63ef4f4863e8b2308e62bd245aa62db source 61d203fab63ef4f4863e8b2308e62bd245aa62db source f284678e334b02808a6c2d473ce683745c99d08e source 8709571dc5a595fbc51b25e159fbd944fcb2ebc1 source ef89f94b26e61b43451f1f4f685a55aaa959311c author Eike Rathke <erack@redhat.com> 2015-08-31 20:55:18 +0200 committer Eike Rathke <erack@redhat.com> 2015-08-31 20:56:12 +0200 commit 61d203fab63ef4f4863e8b2308e62bd245aa62db (patch) tree 265ab6dc301f451ef57dce8f467110a2c310eb57 parent f284678e334b02808a6c2d473ce683745c99d08e (diff) TableRef: adapt to ::std::vector<std::unique_ptr<ScDBData>> change Change-Id: I8f84bb5f4a988b5fb2b688e3c5be321c39818259 author Eike Rathke <erack@redhat.com> 2015-08-31 19:52:27 +0200 committer Eike Rathke <erack@redhat.com> 2015-08-31 19:59:38 +0200 commit f284678e334b02808a6c2d473ce683745c99d08e (patch) tree 0b83aa57871e3c57de2ca8fd60864b3101f5a10a parent 8709571dc5a595fbc51b25e159fbd944fcb2ebc1 (diff) TableRef: write OOXML table autoFilter fragment author Eike Rathke <erack@redhat.com> 2015-08-31 18:48:46 +0200 committer Eike Rathke <erack@redhat.com> 2015-08-31 19:59:38 +0200 commit 8709571dc5a595fbc51b25e159fbd944fcb2ebc1 (patch) tree 08d3b64931616c6842663743ef74a9edc6bf008e parent ef89f94b26e61b43451f1f4f685a55aaa959311c (diff) TableRef: write OOXML tableColumns,tableColumn author Eike Rathke <erack@redhat.com> 2015-08-31 15:53:33 +0200 committer Eike Rathke <erack@redhat.com> 2015-08-31 19:59:37 +0200 commit ef89f94b26e61b43451f1f4f685a55aaa959311c (patch) tree f8d705faf97cca6cf66244514a7830b153350e3e parent 3dabb0692d496684ba145cc95884a731ca1cd5b0 (diff) TableRef: first wave of writing ScDBData to OOXML tables
Created attachment 173380 [details] Example ods file to reproduce the problem Looks like we forgot about an ods reproducer. This file currently exports fine in LO 7.3 nightly: However, if you go to the Data - Define Database Range dialog, check the Contains totals row box and save the file as XLSX then Excel calls that broken. Contains totals row seems to be a half baked feature on its own: checking it is not doing anything visually (In Excel it would add a row to the bottom of the database range (Table) that contains a sum of the values, also a dropdown to choose other functions like min, max, avg, count), neither is it saved to ods.
Created attachment 173381 [details] Screenshot of the original document in Calc and its xlsx-version in Excel Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community Build ID: bd2f2273d83dcca43eb6b465308707efd45e7adf CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: default; VCL: win Locale: en-US (hu_HU); UI: en-US Calc: CL
Created attachment 173382 [details] Example file from Excel with Totals row in Table Another way to break this: enable Totals row in Excel for a table, save it in Calc (Calc imports the Totals row setting just fine) and reopen in Excel.
Created attachment 173383 [details] The previous file roundtripped in Calc
Created attachment 173384 [details] The Excel-made file and its roundtripped version reopened This says "There are one or more circular references in the formulae, i.e. a formula references directly or indirectly its own cell. This may lead to incorrect calculation. Remove or modify these references, or move the formulae to a different cell."
Dear NISZ LibreOffice Team, 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 https://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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
The export to Excel still produces a file that Excel wants to repair. On save to ODF, the setting "contains totals row" of the database range is lost. Tested with Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 976567aee323afd09629b6adf13537908f43d2a8 CPU threads: 32; OS: Windows 11 X86_64 (10.0 build 22631); UI render: Skia/Vulkan; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: threaded and Microsoft® Excel® for Microsoft 365 MSO (Version 2408 Build 16.0.17928.20114) 64-bit
(In reply to Regina Henschel from comment #15) > On save to ODF, the setting "contains totals row" of the database range is > lost. I have written bug 162963 for the lost of the setting when saving to ODF. Thus the bug here should only treat the problem, that the export to xlsx produces a file, that Excel considers as faulty.
(In reply to NISZ LibreOffice Team from comment #9) > Created attachment 173380 [details] > Example ods file to reproduce the problem > However, if you go to the Data - Define Database Range dialog, check the > Contains totals row box and save the file as XLSX then Excel calls that > broken. The last row has no formula at all. It seems Excel does not like that. Although I do not see in the OOXML standard that it is not allowed. I can not reproduce the problem with attachment 173382 [details]. The current LO-daily writes a totalsRowFunction="sum" and Excel opens the resaved file without problems. The problem I have seen in comment 15 is, that if the function in the totals row is e.g. "SUM", totalsRowFunction="sum" is written although it needs to be totalsRowFunction="custom". The enum "sum" can only be used for function SUBTOTAL(109;...), see table in 2.1.1094 in [MS-OI29500].
The export is in /core/sc/source/filter/excel/xedbdata.cxx and there I see a lot of OOXTODO.