Create Xlsx Files With Cell Comments

Posted

August 8, 2020

admin

Code a remark to describe the contents of a cell when creating an xlsx file using PhpSpreadsheet. Certain font style formats can also be set, this includes the font weight, font color, and font style.

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

/**
 * Set the following initial comments
 * and comment styles to A1.
 *
 * Hello World ! (with line break - "\r\n")
 * - font weight = bold
 * - font color = red
 */
$sheet->getComment('A1')->getText()->createTextRun('Hello World !' . "\r\n")->getFont()->setBold(true)->getColor()->setARGB('FFFF0000');

/**
 * Add some more comments to A1 with
 * default the comment style.
 */
$sheet->getComment('A1')->getText()->createTextRun('Hello World ! Hello World !' . "\r\n");

// Set the author of the comment
$sheet->getComment('A1')->setAuthor('Author Name');

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

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

create-xlsx-files-with-cell-comments.php

Test.

Run the following codes.

$ php create-xlsx-files-with-cell-comments.php

command line

Result.

Open the generated file create-xlsx-files-with-cell-comments.xlsx.

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

/**
 * Set the following initial comments
 * and comment styles to A1.
 *
 * Hello World ! (with line break - "\r\n")
 * - font weight = bold
 * - font color = red
 */
$sheet->getComment('A1')->getText()->createTextRun('Hello World !' . "\r\n")->getFont()->setBold(true)->getColor()->setARGB('FFFF0000');

/**
 * Add some more comments to A1 with
 * default the comment style.
 */
$sheet->getComment('A1')->getText()->createTextRun('Hello World ! Hello World !' . "\r\n");

// Set the author of the comment
$sheet->getComment('A1')->setAuthor('Author Name');

References: