Preconditions (*)

Encountered in production with Magento 2.4.1, but this will happen with any Magento2 version, since the code hasn't changed in 7 years.

Steps to reproduce (*)

1. Create an instance of Magento\Framework\Convert\Excel with an ArrayIterator over an array containing a field with a value like ␣123 (i.e. space followed by digits, read ␣ as space).
2. Call convert or write on the object.
3. Try opening the resulting file with a current version of Microsoft Excel or LibreOffice Calc. They will refuse to open the file or display NaN for the value respectively.

Expected result (*)

The Excel XML should encode field as a String:

<Data ss:Type="String"> 123</Data>

Actual result

The value is encoded as Number:

<Data ss:Type="Number"> 123</Data>

Please provide Severity assessment for the Issue as Reporter. This information will help during Confirmation and Issue triage processes.

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

Note: For Microsoft Excel users this can be problematic, as Excel does not report the actual error. The Excel file needs to be opened as an XML file, and the value of ss:Type needs to be fixed manually to be able to import the file at all. LibreOffice Calc does open the file, but doesn't show a warning and silently replaces the cell's value with NaN, so people may not even be aware that some data is actually missing.

Cause

The Magento2 class mainly relies on PHP's is_numeric function to determine the ss:Type, which causes the bug:

Workaround

From a developer's perspective, the issue can be circumvented by triming all numeric values before passing them to the Excel class.