Xlsx writer does not escape or remove control characters that cannot be represented in XML

[athanasius-kircher]

This is:

• a bug report
• a feature request
• not a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

If you put a string containing control characters as vertical tabs into a cell, the resulting xlsx using the corresponding writer should not be corrupted.

What is the current behavior?

The xlsx file is corrupted when opening it in Excel.

What are the steps to reproduce?

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$worksheet = $spreadsheet -> getActiveSheet();
$excelCell = $worksheet -> getCellByColumnAndRow('A',1);
$excelCell -> setValueExplicit(
  'test '."\v" . 'test',
  \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING2
);
$excelWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$excelWriter -> save('test.xlsx');

There seems to be some escaping intended:

Compare \PhpOffice\PhpSpreadsheet\Shared\StringHelper::controlCharacterPHP2OOXML(). But even if I use the following code, the control character looks weird in Excel. But the file is not corrupted. If that is intended, then should the proper escaping not be active by default, without using \PhpOffice\PhpSpreadsheet\Shared\StringHelper::buildCharacterSets().

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
\PhpOffice\PhpSpreadsheet\Shared\StringHelper::buildCharacterSets();
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$worksheet = $spreadsheet -> getActiveSheet();
$excelCell = $worksheet -> getCellByColumnAndRow('A',1);
$excelCell -> setValueExplicit(
  'test '."\v" . 'test',
  \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING2
);
$excelWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$excelWriter -> save('test.xlsx');

Which versions of PhpSpreadsheet and PHP are affected?

"phpoffice/phpspreadsheet": "1.0.0-beta"
PHP 5.6/7.0/7.1

[thommyhh]

Actually the fix is not working in my case. I'm exporting json encoded data from a mysql database. In the database the text contains ^K, which should be vertical tab control character. The data is read from the database and then json-decoded. Created XLSX file is corrupted because of that character.

I'd like to provide more information, if you need that to investigate this further.

[PowerKiKi]

@thommyhh, StringHelperTest::testControlCharacterOOXML2PHP() seems to suggests that it should work. Would you be able to debug further and come up with a failing tests case that cover your case ?

[thommyhh]

I will try to do so, yes.