Php – Perserving newline characters in PHPExcel

PHPphpexcel

I am trying to create an Excel file using PHPExcel. The source data is an array of associative arrays, each of which stores a name and address:

Array
(
    [0] => Array
        (
            [name] => Joe Bloggs
            [address] => 10 Main St 
Bayside 
Big Town ABC123
    )

    [1] => Array
        (
            [name] => Mary Bloggs
            [address] => 18 Bridge St
Riverside
Small Town XYZ987
        )
    :
    :
)

As you can see, the addresses contain newline characters. I am writing this data to an Excel file using PHP using the following code:

$phpExcel = new PHPExcel();

$phpExcel->setActiveSheetIndex(0);
$phpExcel->getActiveSheet()->getCell('A1')->setValue("Name");
$phpExcel->getActiveSheet()->getCell('B1')->setValue("Address");
$index = 2;
foreach($rows as $row) {
    $phpExcel->getActiveSheet()->getCell('A'.$index)->setValue($row["name"]);
    $phpExcel->getActiveSheet()->getCell('B'.$index)->setValue($row["address"]);
    $index++;
}
$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel2007');
$filename = "outputs/excel/data.xlsx";
$objWriter->save($filename);

However, the resulting Excel file does not contain the newline characters, which I want to preserve. 

Name         Address
Joe Bloggs   10 Main St Bayside Big Town ABC123
Mary Bloggs  18 Bridge St Riverside Small Town XYZ987
...

From comments in the PHPExcel website, it seems that it should be possible to do this, but I cannot figure out how to do this.

Can anyone help me? THANKS.

Best Answer

Try using this instead of "\n":

$lfcr = chr(10) . chr(13);

then replace "\n" everywhere with $lfcr, like this:

setValue("First line". $lfcr . "second Line" . $lfcr . "Third Line");
Related Topic