Create Excel Files With Gradient Cell Background In PHP Using PHPSpreadSheet

Posted

August 2, 2020

admin

Apply cell background gradients on a spreadsheet when coding to create an xlsx file in PhpSpreadsheet, the result will have a smooth linear transition of two background colors from one to the other.

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

// Apply a linear gradient type background to 'A1'
$sheet->getStyle('A1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR);

// Fill up the background of 'A1' with start color blue
$sheet->getStyle('A1')->getFill()->getStartColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLUE);

// Fill up the background of 'A1' with end color white
$sheet->getStyle('A1')->getFill()->getEndColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);

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

// Apply a linera gradient type background to 'A5'
$sheet->getStyle('A5')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR);

// Fill up the background of 'A5' with start color white
$sheet->getStyle('A5')->getFill()->getStartColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);

// Fill up the background of 'A5' with end color blue
$sheet->getStyle('A5')->getFill()->getEndColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLUE);

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

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-gradient-cell-background.xlsx');

create-xlsx-files-with-gradient-cell-background.php

Test.

Run the following codes.

$ php create-xlsx-files-with-gradient-cell-background.php

command line

Result.

Open the generated file create-xlsx-files-with-gradient-cell-background.xlsx.

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

// Apply a linear gradient type background to 'A1'
$sheet->getStyle('A1')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR);

// Fill up the background of 'A1' with start color blue
$sheet->getStyle('A1')->getFill()->getStartColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLUE);

// Fill up the background of 'A1' with end color white
$sheet->getStyle('A1')->getFill()->getEndColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);

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

// Apply a linera gradient type background to 'A5'
$sheet->getStyle('A5')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR);

// Fill up the background of 'A5' with start color white
$sheet->getStyle('A5')->getFill()->getStartColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);

// Fill up the background of 'A5' with end color blue
$sheet->getStyle('A5')->getFill()->getEndColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLUE);

References: