Create Excel Files With Text Aligned Vertically In PHP Using PHPSpreadSheet

| August 2, 2020 | 1,887 views | PhpSpreadsheet

Set the vertical position of a text on a cell spreadsheet through coding with PhpSpreadsheet. Depending on the presentation need, it can be aligned at the top, middle, or the default bottom arrangement upon creating an xlsx file.

To further demostrate the changes, the tutorial Create Xlsx Files With Line Breaks will be used.

Requirements:

  • Composer
  • PHP 7.2 or newer

Step 1.

Setup dependencies.

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

Step 2.

Install phpspreadsheet.

$ composer install

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 with the 3 "Hello World !" string values with line breaks
$sheet->setCellValue('A1', 'Hello World !' . "\n" . 'Hello World !' . "\n" . 'Hello World !');

// Set 'wrap text' option to cell A1 
$sheet->getStyle('A1')->getAlignment()->setWrapText(true);

// Set cell B1 with the "Hello World !" string value
$sheet->setCellValue('B1', 'Hello World !');

// Set B1 vertical alignment to top
$sheet->getStyle('B1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

// Set B1 vertical alignment to center
// $sheet->getStyle('B1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

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

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-text-aligned-vertically.xlsx');

Test.

Run the following codes.

$ php create-xlsx-files-with-text-aligned-vertically.php

Result.

Open the generated file create-xlsx-files-with-text-aligned-vertically.xlsx.

// Set cell A1 with the 3 "Hello World !" string values with line breaks
$sheet->setCellValue('A1', 'Hello World !' . "\n" . 'Hello World !' . "\n" . 'Hello World !');

// Set 'wrap text' option to cell A1 
$sheet->getStyle('A1')->getAlignment()->setWrapText(true);

// Set cell B1 with the "Hello World !" string value
$sheet->setCellValue('B1', 'Hello World !');

// Set B1 vertical alignment to top
$sheet->getStyle('B1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

Test Again.

Center the text vertically.

// Set B1 vertical alignment to top
// $sheet->getStyle('B1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);

// Set B1 vertical alignment to center
$sheet->getStyle('B1')->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

Default.

References:

0 Comments

Leave a Reply

Your email address will not be published.