Update Excel Files In PHP Using PHPSpreadSheet

| June 27, 2020 | 1,131 views | PhpSpreadsheet

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"
    }
}

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

Test.

Run the following codes.

$ php update-xlsx-files.php

Result.

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

References:

0 Comments

Leave a Reply

Your email address will not be published.