Description: LibreOffice supports empty values in array formulas, but Excel doesn't, this should be handled in XLSX export Example: we can insert "={1,,3}" as formula in LO but Excel throws a pop-up dialog saying it's not valid formula. When exporting array formulas containing empty values to XLSX, they are exported without any change, because of this Excel fails to open the file. Steps to Reproduce: 1. Open the attached XLSX file 2. Save it as XLSX 3. Resulting file can't be opened in Excel Actual Results: Excel opens the file without any problem Expected Results: Excel fails to open the file Reproducible: Always User Profile Reset: Yes Additional Info: Version: 25.8.3.2 (X86_64) Build ID: 8ca8d55c161d602844f5428fa4b58097424e324e CPU threads: 22; OS: Linux 6.12; UI render: default; VCL: gtk3 Locale: en-IN (en_IN); UI: en-US Calc: threaded
Created attachment 204900 [details] XLSX File
Excel opens the file. It explains, that it cannot read the content and replaces the array with constant values. What do you want to provide to Excel?
If the array as empty values, in XLSX export we replace them with 0. That way Excel doesn't give any errors. I submitted a patch in collabora branch https://gerrit.libreoffice.org/c/core/+/196421, will backport it to master
(In reply to Karthik from comment #3) > If the array as empty values, in XLSX export we replace them with 0. That > way Excel doesn't give any errors. But if other items in the inline array are strings, then an empty string would be better than 0.
Or use the string that is generated by the NA() function? That would make it visible to the user, that something could not be exported.
(In reply to Regina Henschel from comment #5) > Or use the string that is generated by the NA() function? That would make it > visible to the user, that something could not be exported. Yes, this seems like a better idea. Will implement this.
Karthik Godha committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/d6c5410ada718d19403cfeb427c3db0e50d40760 tdf#170201: Use #NA! err string for array formulas It will be available in 26.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b423455d30d24b43ed7bf8fbcd09e1a6da39b5ef tdf#170201: sc_subsequent_export_test2: Add test It will be available in 26.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Karthik Godha committed a patch related to this issue. It has been pushed to "libreoffice-26-2": https://git.libreoffice.org/core/commit/bc6d1bd72987c25d3a223f3e17cd6efe757b9a8d tdf#170201: Use #NA! err string for array formulas It will be available in 26.2.1. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.