Update Excel Files In PHP Using PHPSpreadSheet

Posted

June 27, 2020

admin

Edit texts, links, font colors, background colors, and many more. Learn how to update xlsx files in this PhpSpreadsheet tutorial. The generated file from Create Xlsx Files With Underlined Texts will be used as the sample xlsx file to be updated.

Requirements:

Composer
PHP 7.2 or newer

Before Xlsx file update.

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 IOFactory class
use \PhpOffice\PhpSpreadsheet\IOFactory;

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

// Full path of the file to be indentified
$inputFileName = 'create-xlsx-files-with-underlined-texts.xlsx';

// Identify the file type using the IOFactory object
$inputFileType = IOFactory::identify($inputFileName);

// Create the reader object
$reader = IOFactory::createReader($inputFileType);

// Load the file to read
$spreadsheet = $reader->load($inputFileName);

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

// Merge cells from A1 to E1
$sheet->mergeCells('A1:E1');

// Underline cell A1
$sheet->getCell('A1')->getStyle()->getFont()->setUnderline(true);

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

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

// Apply a solid type background to 'A3'
$sheet->getStyle('A3')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);

// Fill up the background of 'A3' with color red
$sheet->getStyle('A3')->getFill()->getStartColor()->setARGB('FFFF0000');

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

// Set cell A5 font color to blue
$sheet->getStyle('A5')->getFont()->getColor()->setARGB('FF0000FF');

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

// Save as the current input file to apply the updates
$writer->save($inputFileName);

update-xlsx-files.php

Test.

Run the following codes.

$ php update-xlsx-files.php

command line

Result.

Open the updated file create-xlsx-files-with-underlined-texts.xlsx.

References: