Home » File Formats » Spreadsheet_Excel_Writer » Bug #2923

[2004-12-08 18:26 UTC] kgrigsby at cs dot ndsu dot nodak dot edu

Description:
------------
I am trying to enter 2-3 lines of text into a cell as shown in the Cell example below. The lines need to be separated by new line characters. In excel this is achieved by inserting the formula “CHAR(10)”.

The cell example below would have the cell formula
="line 1"&CHAR(10)&"line 2"&CHAR(10)&"much longer line 3"

It appears there is a problem with the handling of the & in the writer file, because the only thing that gets to the excel file is the first part of the string before the &.

Cell example:
------------------------
| line1
| line 2
| much longer line 3
------------------------

Reproduce code:
---------------
<?php
require_once( 'Spreadsheet/Excel/Writer.php' );

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();
$worksheet->hideGridLines();
$multipleLineDataFormat = &$workbook->addFormat( array( 'Border' => 1, 'Align' => 'left' ) );
$multipleLineDataFormat->setTextWrap();

$userString = '='. '"line 1"' . ' &char(10)&';
$userString .= '"line 2"' . '&char(10)&';
$userString .= '"Much longer line 3"';

$worksheet->write( 1, 1, $userString, $multipleLineDataFormat );
$workbook->send( 'test-excel.xls' );
$workbook->close();
?>

Expected result:
----------------
I expected to get this formula in cell (1,1)

="line 1" &char(10)&"line 2"&char(10)&"Much longer line 3"

Actual result:
--------------
All that is in cell (1,1) is

="line 1"

------------------------
Admittedly this may not be a bug.
But my search of the interweb was fruitless for a solution.

Thank you for your help,
------------------------

[2005-02-22 09:57 UTC] smith at backendmedia dot com

Have you tried "\n" instead of '&char(10)&' ?

[2005-03-03 21:33 UTC] kgrigsby at cs dot ndsu dot nodak dot edu

That worked.
Maybe if I used K.I.S.S. that would have helped.

Thank you,

[2005-06-08 21:09 UTC] bxny78 at yahoo dot com

Hi All, I'm having the same problem, what was the solution? the '\n' doesn't work, unless I'm doing something wrong

[2005-06-08 22:17 UTC] kgrigsby at cs dot ndsu dot nodak dot edu

This code worked for me

$multipleLineDataFormat = &$workbook->addFormat( array( 'Border'=> 1, 'Align' => 'left' ) );
$multipleLineDataFormat->setTextWrap();

$userString = 'line 1' . " \n ";
$userString .= 'line 2' . " \n ";
$userString .= 'Much longer line 3';

$worksheet->write( 0, 2, $userString, $multipleLineDataFormat );

[2005-06-09 14:10 UTC] bxny78 at yahoo dot com

Thanks!

[2005-06-28 15:33 UTC] tmiller at tobymiller dot com

I am trying to run the example code that kgrigsby wrote and it does not work. At least not the way I expected it to. The newline is inserted into the cell, but the cell itself is not formatted to wrap text. Is this a bug? Is this example code still working for everyone else?

[2005-06-28 20:29 UTC] tmiller at tobymiller dot com

Sorry, false alarm. I was applying the cell format to the cell hash instead of the cell value.