Create Excel Files With Multiple Cell Styles From An Array In PHP Using PHPSpreadSheet

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

When considering coding multiple styles on a cell of a spreadsheet, it would be a better approach to do it in group rather than setting the style individually. In PhpSpreadsheet, when creating an xlsx file, this option is possible through the applyFromArray() function, this function accepts an array parameter that contains the desired cell setting styles.

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 "Hello World !" string value
$sheet->setCellValue('A1', 'Hello World !');

/**
 * Prepare the following cell styles.
 *
 * Italic font style.
 * Bold font weight.
 * White font color.
 * Centered horizontal alignement
 * Thick bottom border.
 * Solid red background color.
 */
$styleArray = [
    'font' => [
        'italic' => true,
        'bold' => true,
        'color' => [
            'argb' => 'FFFFFFFF',
        ],
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'bottom' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => [
            'argb' => 'FFFF0000',
        ],
    ],
];

// Set cell A1 with the above prepared cell styles
$sheet->getStyle('A1')->applyFromArray($styleArray);

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

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-multiple-cell-styles-from-an-array.xlsx');

Test.

Run the following codes.

$ php create-xlsx-files-with-multiple-cell-styles-from-an-array.php

Result.

Open the generated file create-xlsx-files-with-multiple-cell-styles-from-an-array.xlsx.

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

/**
 * Prepare the following cell styles.
 *
 * Italic font style.
 * Bold font weight.
 * White font color.
 * Centered horizontal alignement
 * Thick bottom border.
 * Solid red background color.
 */
$styleArray = [
    'font' => [
        'italic' => true,
        'bold' => true,
        'color' => [
            'argb' => 'FFFFFFFF',
        ],
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
    'borders' => [
        'bottom' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
        ],
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
        'startColor' => [
            'argb' => 'FFFF0000',
        ],
    ],
];

// Set cell A1 with the above prepared cell styles
$sheet->getStyle('A1')->applyFromArray($styleArray);

References:

0 Comments

Leave a Reply

Your email address will not be published.