Saving XLSX: apros is changed to '

[cscrewsandcaptains]

Export XLSX and use this in R, than the content of the cells are wrong, becaues ' and not ' is used.
Workaround: Open xlsx in Excel and save it without a change. Excel will transform ' to '.

[oleibman]

I am sorry, I do not understand your use case. If you use PhpSpreadsheet to write a cell containing an apostrophe, PhpSpreadsheet will read the resulting cell as if it contained an apostrophe, not &#39. So will Excel. Why are you having a problem? What makes your case different from, say, a cell containing a less-than sign? Have you reported this problem to "R"?

[cscrewsandcaptains]

Yes, that is correct. R is causing the problem, not PHPSpreadsheet and not Excel. Therefore I cannot build a test script.
I did not report the problem to R (by the way, they could also say: why not fix it in PHP).
I think it's better to program so that other tools understand the content and don't have to implement all the special cases.
But certainly not a “must have”, but “nice to have”.
This would make the PHPSpreadsheet code more homogeneous, as the htmlspecialchars function is already called with the parameter in the “writeCellInlineStr” method in the Workbook class: Settings::htmlEntityFlags().
htmlspecialchars has changed the default parameter with PHP 8.1: https://www.php.net/manual/en/function.htmlspecialchars.php#refsect1-function.htmlspecialchars-changelog
so I assume that it is cleaner to always call htmlspecialchars with the parameter Settings::htmlEntityFlags().

[oleibman]

If R says "why not fix the problem in Php?", they are wrong. You are presenting them with well-formed XML, and they are not reading it correctly. That is a bug on their part, plain and simple.

I had thought I could suggest a workaround for you, namely explicitly setting your cell value as TYPE_INLINE. That would cause it to be written with htmlentityflags. BUT ... that section of code has a bug which my workaround exposed - it should not be calling htmlspecialchars at all. So, I am going to fix that first, and then return to your problem, probably within a couple of weeks.

As for your comment that it is cleaner to always call - when we make changes, we want to avoid breaking existing code if possible. So, cleaner or not, there will be a change in behavior, and we need to evaluate the effects of that change.

[oleibman]

Good news - my work on the TYPE_INLINE problem will fix this one as well. Xml Writer and Xlsx Writer should not be using htmlspecialchars at all - they should be using the native XML routines which do their own escaping (and leave apostrophes untouched).

[oleibman]

If you can test against PR #4540, please do so.

[cscrewsandcaptains]

Yes, with PR #4540 (and without changing my code) the xlsx could be read correctly by R.
Thank you very much for your support!