magento2: Excel writer produces invalid files for numbers preceded by spaces

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 (*)

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).
Call convert or write on the object.
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: https://github.com/magento/magento2/blob/bf4cdad2b1d0436be831c1f5c89c1adc77bfd1fc/lib/internal/Magento/Framework/Convert/Excel.php#L153

Workaround

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

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 1
Comments: 18 (7 by maintainers)

Commits related to this issue

magento/magento2#33422: Fixed excel writer produces invalid files for numbers preceded by spaces — committed to hutychivan/magento2 by hutychivan 2 years ago

Most upvoted comments

The issue is confirmed on 2.4-develop branch. Here are the steps to reproduce the issue.

Create an instance of Magento\Framework\Convert\Excel with an ArrayIterator over an array containing a field
Call convert or write on the object.
Try opening the resulting file with a current version of Microsoft Excel. It will refuse to open the file.

Sample code

$data[] = ['message' => " 1233"];
    $convert = new \Magento\Framework\Convert\Excel(new \ArrayIterator($data));
    $file = $convert->convert('single_sheet');
    $fileName = "test_without_space.xml";
    return $this->_fileFactory->create($fileName, $file, DirectoryList::VAR_DIR, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

Generated file should contain the encoded value as string but it is coming as number <Data ss:Type="Number"> 1233</Data>

engcom-Bravo on Aug 24, 2021