Create Excel Files With Line Breaks In PHP Using PHPSpreadSheet

Posted

July 12, 2020

PhpSpreadsheet

admin

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.

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

composer.json

Step 2.

Install phpspreadsheet.

$ composer install

command line

Step 3.

Create a new PHP file, and start coding.

<?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');

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

Test.

Run the following codes.

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

command line

Result.

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

// 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);

References: