Incorrect handling of EOL characters in cell values

[PowerGamer1]

When spreadsheet is saved in OOXML file format CR character must be encoded according to ISO/IEC 29500-1 (Office Open XML File Formats Part 1) standard (as _x000D_) or XML standard (as 
).

When reading cell value from OOXML file PHPSpreadsheet file reader must decode _x000D_ into CR character (
 will be decoded automatically by PHP's XML reader used by PHPSpreadsheet file reader).

Microsoft Excel correctly handles CR character in cell values encoded either way (the character is not lost when resaving the XLSX file in Microsoft Excel).

What is the expected behavior?

CR character survive a round-trip through Xlsx and Open Office XML formats.

true
false

What is the current behavior?

CR character is lost after a round-trip through Xlsx and Open Office XML formats.

false
true

What are the steps to reproduce?

<?php
require __DIR__ . '/vendor/autoload.php';
$s1 = "AA\r\nAA";
$doc = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $doc->getSheet(0);
$sheet->getCellByColumnAndRow(1, 1)->setValueExplicit($s1, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($doc, 'Xlsx');
$writer->save('1.xlsx');

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$doc = $reader->load('1.xlsx');
$sheet = $doc->getSheet(0);
$s2 = $sheet->getCellByColumnAndRow(1, 1)->getValue();
var_dump($s2 === $s1);
var_dump($s2 === "AA\nAA");

Which versions of PhpSpreadsheet and PHP are affected?

1.0.0.

[PowerKiKi]

I can reproduce the described behavior. However the code specifically forbids the usage of \r in our model. See:

PhpSpreadsheet/src/PhpSpreadsheet/Cell/DataType.php

Lines 62 to 63 in 84f012f

	// we require that newline is represented as "\n" in core, not as "\r\n" or "\r"
	$pValue = str_replace(["\r\n", "\r"], "\n", $pValue);

This check was introduced a very long time ago, and the reason why it exists are not very clear. So I am not too keen on removing this check without a better understanding of the consequences it may have...

@MarkBaker, or @maartenba would you have any further knowledge about whether \r should or should not be allowed in strings ?

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[oleibman]

Just speculating. I have seen several examples of users loading large blocks of text into cells. If those blocks of text come from files with Windows (or, far less likely, Mac) line endings, Excel does not do a good job of rendering 0x0a. The current treatment would work around that problem. So it might be useful to some users.

OTOH, there is no reason why users who do not want that treatment should have it forced upon them. I will work on a PR to give them an option.