0

I am trying to write a string that contains double quotes in a cell, write it to disk and read it again from an xls file. Example strings are:

  • single quote "
  • double quotes ""

I set the cell values via

// $page is of type PhpOffice\PhpSpreadsheet\Worksheet\Worksheet

$page->setCellValueExplicitByColumnAndRow($columnIndex, $rowIndex, $sanitizedValue, DataType::TYPE_STRING);
$page->getCellByColumnAndRow($columnIndex, $rowIndex)->getStyle()->setQuotePrefix(true);

Reading the value back at this stage (without writing to disk) works as expected via either getCalculatedValue(), getFormattedValue() or getValue(). However as soon as the file is written and read again via a Xls writer I get a String offset cast exception in PhpSpreadsheet\Reader\Xls.php:7863

Partial stack trace:

Stack trace:
#0 C:\temp\test\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\Xls.php(7863): Illuminate\Foundation\Bootstrap\HandleExceptions->handleError(8, 'String offset c...', 'C:\\temp\\files...', 7863, Array)
#1 C:\temp\test\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\Xls.php(3060): PhpOffice\PhpSpreadsheet\Reader\Xls::getUInt2d('\xAC\x01\x00\x00\xFF\x00\x00\x00\x17\x00\x01u\x00m\x00...', 16487)
#2 C:\temp\test\vendor\phpoffice\phpspreadsheet\src\PhpSpreadsheet\Reader\Xls.php(737): PhpOffice\PhpSpreadsheet\Reader\Xls->readSst()

I have tried

  • using DataType::TYPE_STRING2
  • with and without setQuotePrefix
  • escaping the string as formula single quote " => '= single quote "
  • searched if strings need to be escaped with phpSpreadsheet (not as far as I could find)
  • writing the sample texts directly via excel and importing that file (this works flawlessly)

but at this point I am out of Ides and would appreciate any hints.

phpoffice/phpspreadsheet: 1.18.0 php-version: 7.3.6 Excel Format: xls

Containt Letters
  • 1
  • 1
  • Hi, when people speak of single quotes and double quotes in PHP, it is usually referring to `'` and `"` respectively. An easier way to do what you're trying to achieve might be to covert the file to a comma separated type and use `file_get_contents()`/`file_put_contents()` to prove the principle (just use vanilla PHP). Converting to CSV: https://convertio.co/xls-csv/ - `file_get_contents()`: https://www.php.net/manual/en/function.file-get-contents.php - `file_put_contents()`: https://www.php.net/manual/en/function.file-put-contents – Shaun Bebbers Aug 23 '21 at 13:26
  • 1
    Unfortunately I have to stick to a `xls` as it is used also by other systems which I have no control over. Formulas are also no option as this again is picked up by other systems so there is really no other way than to find out how to write a simple string that contains `"` as text in an excel cell. – Containt Letters Aug 23 '21 at 13:58
  • You might have to stick with `xls` for production, but proof of concept code can be whatever you want; you can then take those concepts (not necessarily 1:1 code), along with the things that you have learnt, into prototyping and production. – Shaun Bebbers Aug 23 '21 at 14:17
  • 1
    The app is already in production ;) this issue arose through a recent update in which we also enabled free text fields (before that we only allowed numerical input). And the first thing some users did was to enter `"` which crashed the phpspreadsheet lib. This is why I am trying to solve this very specific case. – Containt Letters Aug 23 '21 at 14:21
  • Just to humour me, try entering this into an `xls` file for testing purposes only: `test \"\"` and see what happens (removing all other instances of the quotation marks) – Shaun Bebbers Aug 23 '21 at 15:35

0 Answers0