Bug 152805 - 'Excel A1' when creating files from XLSX / XLTX
Summary: 'Excel A1' when creating files from XLSX / XLTX
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.3.7.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-01-02 04:48 UTC by leander@wein-hilgert.de
Modified: 2023-01-05 09:19 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Excel file which was converted into an ods file (30.85 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.template)
2023-01-05 07:21 UTC, leander@wein-hilgert.de
Details
ODS file with bug (41.24 KB, application/vnd.oasis.opendocument.spreadsheet)
2023-01-05 07:30 UTC, leander@wein-hilgert.de
Details

Note You need to log in before you can comment on or make changes to this bug.
Description leander@wein-hilgert.de 2023-01-02 04:48:28 UTC
Description:
The Calc function INDIREKT() works incorrectly. If a document was imported from an xlsx file and saved as an ods file, these two problems then occur:
1. =INDIREKT("xyz.A1") produces #REF! with the separator "!" however not. => =INDIREKT("xyz!A1")
2. The table name cannot contain spaces, as this also results in #Ref! leads. => =INDIREKT("xyz xy!A1")

Steps to Reproduce:
1.xlsx document save as ods document
2.use INDIREKT()  with "."
3.

Actual Results:
345

Expected Results:
#REF!


Reproducible: Always


User Profile Reset: No

Additional Info:
Version: 7.3.7.2 (x64) / LibreOffice Community
Build ID: e114eadc50a9ff8d8c8a0567d6da8f454beeb84f
CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded
Comment 1 Mike Kaganski 2023-01-02 07:30:53 UTC
> 1. =INDIREKT("xyz.A1") produces #REF! with the separator "!" however not. =>
> =INDIREKT("xyz!A1")
> ...
> Steps to Reproduce:
> 1.xlsx document save as ods document
> 2.use INDIREKT()  with "."

Your description looks odd. Initially you write that the problem occurs when you use exclamation mark as separator; then in steps, you write about dot as separator.
Comment 2 Roman Kuznetsov 2023-01-04 22:16:05 UTC
PLease attach here your Excel (XLSX) document for testing
Comment 3 leander@wein-hilgert.de 2023-01-05 07:21:19 UTC
Created attachment 184492 [details]
Excel file which was converted into an ods file
Comment 4 leander@wein-hilgert.de 2023-01-05 07:30:58 UTC
Created attachment 184493 [details]
ODS file with bug
Comment 5 Mike Kaganski 2023-01-05 08:44:25 UTC
Aha. No, it doesn't "work incorrectly", and also, despite the original claim that it happens after "import from an xlsx", the described behavior occurs after creation from an *XLTX*.

In version 5.1, a new string reference syntax was introduced: "Calc A1 | Excel A1", available under Options->Calc->Formula->Detailed Calculation Syntax->Details (tdf#92256). It allows using mixed Calc / Excel string references in a single spreadsheet; and it is used by default now, when one opens XLSX files. (It re-introduced what was used in old OOo versions without any specific syntax.)

However, when creating files from an XLTX, the older (and stricter) "Excel A1" string reference mode is still used. It disallows the Calc-style string references.

*Possibly* using "Calc A1 | Excel A1" here would also be reasonable, because Excel syntax could be used in pre-existing formulas coming from XLTX, and Calc syntax could be expected by users in the new documents ... but OTOH, this mixed mode is not ideal anyway. Documents containing such mixed syntax would fail when opened in Excel, so would not be interoperable.

Why do you expect the mixed mode? IMO, you either should use ODS and its syntax (Calc A1), or you would need interoperability, and then use Excel A1 syntax - but not both.

And thus, I'd say this is not a bug, and this behavior is better than more liberal mixed mode, requiring user to decide on the syntax explicitly if they intend to use Calc syntax when they use Excel templates (wouldn't users better use OTS templates in this case?).

As for the space in the sheet name, the standards require that names containing special characters be enclosed into single quotes; so the proper reference would be

=INDIRECT("'test Mittwoch'!A1")
Comment 6 Mike Kaganski 2023-01-05 09:19:34 UTC
(In reply to Mike Kaganski from comment #5)
> Aha. No, it doesn't "work incorrectly", and also, despite the original claim
> that it happens after "import from an xlsx", the described behavior occurs
> after creation from an *XLTX*.

Likely I was confused, using XLSX in my Downloads folder for testing. It seems like tdf#92256 had introduced also a compatibility setting written to XLSX documents; and thus, *some* XLSX documents may have it and define the mixed mode. In the *clear* XLSX documents, coming from MS Excel, Calc also sets the proper strict "Excel A1" syntax.

So IMO: this is *not* a bug, but a proper behavior.