processing spreadsheet saved as XLSX results in _x000D_ before line breaks, the same file saved as XLS does not

[0nobody0]

Hello, I'm processing a spreadsheet with line breaks in cells through Collective Access which appears to be using PhpSpreadsheet 1.30.1 for processing. PHP version is 8.3.28:

./composer.phar show:

...
phpoffice/common                   1.0.5               PHPOffice Common
phpoffice/phppresentation          1.0.0               PHPPresentation - Read, Create and Write Presentations docum...
phpoffice/phpspreadsheet           1.30.1              PHPSpreadsheet - Read, Create and Write Spreadsheet document...
phpoffice/phpword                  0.18.3              PHPWord - A pure PHP library for reading and writing word pr...
...

Processing a spreadsheet as an .xls file returns the cell contents as expected with line breaks as newlines (\n). Processing the same data as an .xlsx file returns the cell contents with the encoding for a carriage return (\r) _x000D_ before the newline line breaks. Searching the .xlsx file for Alt+0010 (newline) finds the line breaks, while Alt+0013 (carriage returns) shows no results.

From a brief review of what seems like the relevant Collective Access code at https://github.com/collectiveaccess/providence/blob/a0a24298d69a23e3f5e58f0a5eec7796e415a38a/app/helpers/importHelpers.php#L1584-L1715 I'm wondering if this might be a PhpSpreadsheet issue?

Here is an example cell:

Test line 1.
Test line number 2.
final test line.

Spreadsheet saved as XLS and processed the cell is returned as:

Test line 1.
Test line number 2.
final test line.

Spreadsheet saved as XLSX and processed the cell is returned as:

Test line 1._x000D_
Test line number 2._x000D_
final test line.

any help or recommendations on whether this might be user error, a PHPSpreadsheet issue, or issue with the CollectiveAccess code is greatly appreciated. thanks

[oleibman]

Although I will try to help, we are no longer updating the release of PhpSpreadsheet that you are using except for security patches, which clearly does not apply here.
_x000D_ is a valid encoding for carriage return in an Excel file. See the following file, and note that string occurs in SharedStrings.xml; but it does not show when you open the file in Excel.
issue.4724.xlsx

[oleibman]

Normally, PhpSpreadsheet does not output that string. Issue #347 is relevant here. PhpSpreadsheet replaced both cr/lf and cr alone with lf (i.e. newline) until PR #4536 and later #4619 earlier this year; neither PR was applied to your release. So, it might help if you show how you populated the cell in question in your code.

[0nobody0]

thanks very much for the info. on a Windows 11 25H2 machine the cells were populated by copying tabbed and quoted text from a .txt file in sublimetext v4200 and pasting it into excel for microsoft office 365 version 2510 build 16.0.19328.20244.

example .txt file content (only \n no \r characters, line 2 and 5 starting with \t ):

ID001
	"Test line A1.
Test line number A2.
final A test line."
ID002
	"Test line B1.
Test line number B2.
final B test line."

After pasting this block with cell A1 selected, the cells in column B were then formatted in excel with wrap text and copied and pasted up one cell:

[0nobody0]

oh - i had only reviewed the .xlsx file by exporting from excel to .csv where the new lines were only shown as \n's. i just exported the above test file from excel to .xml and ASCII code for carriage returns do show up: <Cell ss:StyleID="s63"><Data ss:Type="String">Test line A1.&#13;&#10;Test line number A2.&#13;&#10;final A test line.</Data></Cell>

[oleibman]

Thank you for the information. It appears that PhpSpreadsheet never expected, and never handled _x000D_. Well, it did briefly, I added support for it in PR #4536, but that caused some problems, so it had to be removed in PR #4619. I will work on another PR to re-add it, but, as noted above, that will not be applied to the release which you are using. I'm afraid you (or your framework) will have to handle this issue manually with a str_replace for each cell to which it might apply if you cannot convince your framework to move to a supported version. Sorry.

[0nobody0]

no problem at all, thanks very much for clarifying things