Create Excel Files With Line Breaks In PHP Using PHPSpreadSheet

Using PhpSpreadsheet, write text contents in an xlsx file with each words or paragraphs on it’s own line.

Requirements:

Composer
PHP 7.2 or newer

Step 1.

Setup dependencies.

composer.json

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.3"
    }
}

Step 2.

Install phpspreadsheet.

command line

$ composer install

Step 3.

Create a new PHP file, and start coding.

create-xlsx-files-with-line-breaks.php

<?php

// Autoload dependencies
require 'vendor/autoload.php';

// Import the core class of PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// Import the Xlsx writer class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet();

// Set cell A1 value with the three "Hello World !", one on it's own line
$sheet->setCellValue('A1', "Hello World !\nHello World !!\nHello World !!!");
$sheet->getStyle('A1')->getAlignment()->setWrapText(true);

// Using single quotes will not make the line break work
// Set cell B1 value with the three "Hello World !", one on it's own line
$sheet->setCellValue('B1', 'Hello World !\nHello World !!\nHello World !!!');
$sheet->getStyle('B1')->getAlignment()->setWrapText(true);

// Using single quotes and appending the line break with double quotes
// Set cell C1 value with the three "Hello World !", one on it's own line
$sheet->setCellValue('C1', 'Hello World !' . "\n" . 'Hello World !!' . "\n" . 'Hello World !!!');
$sheet->getStyle('C1')->getAlignment()->setWrapText(true);

// Write a new .xlsx file
$writer = new Xlsx($spreadsheet);

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-line-breaks.xlsx');

Test.

Run the following codes.

command line

$ php create-xlsx-files-with-line-breaks.php

Result.

Open the generated file create-xlsx-files-with-line-breaks.xlsx.

PHP

// Set cell A1 value with the three "Hello World !", one on it's own line
$sheet->setCellValue('A1', "Hello World !\nHello World !!\nHello World !!!");
$sheet->getStyle('A1')->getAlignment()->setWrapText(true);

PHP

// Using single quotes will not make the line break work
// Set cell B1 value with the three "Hello World !", one on it's own line
$sheet->setCellValue('B1', 'Hello World !\nHello World !!\nHello World !!!');
$sheet->getStyle('B1')->getAlignment()->setWrapText(true);

PHP

// Using single quotes and appending the line break with double quotes
// Set cell C1 value with the three "Hello World !", one on it's own line
$sheet->setCellValue('C1', 'Hello World !' . "\n" . 'Hello World !!' . "\n" . 'Hello World !!!');
$sheet->getStyle('C1')->getAlignment()->setWrapText(true);

References:

How to install PhpSpreadsheetOn this site

Creating a spreadsheetphpspreadsheet.readthedocs.io

Reading and writing to filephpspreadsheet.readthedocs.io

PhpSpreadsheet Recipesphpspreadsheet.readthedocs.io