Create Excel Files With Formulas In PHP Using PHPSpreadSheet

Posted

July 11, 2020

admin

Write formulas in an xlsx file, and easily compute for an average, sum a range of cell values, and even set a conditional formula based on a given cell value.

These are just a few of the many formulas you can write in creating xlsx files with PhpSpreadsheet.

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-A10 value from 1-10 respectively
for ($i=1; $i<11; $i++) {
    $sheet->setCellValue('A' . $i, $i);
}

// Set cell A11 with a formula that will get the average value of cell A1 to cell A10
$sheet->setCellValue('A11', '=AVERAGE(A1:A' . ($i-1) . ')');

// Set cell A12 with a formula that will get the summation value of cell A1 to cell A10
$sheet->setCellValue('A12', '=SUM(A1:A' . ($i-1) . ')');

// Set cell B1-B10 with a formula that will check if it's corresponding A column value is odd or even
for ($i=1; $i<11; $i++) {
    $sheet->setCellValue('B' . $i, '=IF(MOD(A' . $i . ',2)=0,"Even","Odd")');
}

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

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

create-xlsx-files-with-formulas.php

Test.

Run the following codes.

$ php create-xlsx-files-with-formulas.php

command line

Result.

Open the generated file create-xlsx-files-with-formulas.xlsx.

// Set cell A1-A10 value from 1-10 respectively
for ($i=1; $i<11; $i++) {
    $sheet->setCellValue('A' . $i, $i);
}

// Set cell A11 with a formula that will get the average value of cell A1 to cell A10
$sheet->setCellValue('A11', '=AVERAGE(A1:A' . ($i-1) . ')');

// Set cell A12 with a formula that will get the summation value of cell A1 to cell A10
$sheet->setCellValue('A12', '=SUM(A1:A' . ($i-1) . ')');

// Set cell B1-B10 with a formula that will check if it's corresponding A column value is odd or even
for ($i=1; $i<11; $i++) {
    $sheet->setCellValue('B' . $i, '=IF(MOD(A' . $i . ',2)=0,"Even","Odd")');
}

References: