Create Excel Files With Sum Formula In PHP Using PHPSpreadSheet

Learn how to create excel files with SUM formula in PHP using PHPSpreadSheet.

You can code and write a summation formula that would add values of specific range of cells, or get the total values of only selected cells.

This article will discuss the basic process of adding cell values together through a sum formula in PHP using PHPSpreadSheet. Below are the goals we will try to achieve.

1. Add the values of Cells B1 up to B10.

2. Get the summation of the values of Cell B1 and B10 only.

3. Count the values of Cell B1 and B2 together.

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 the random values container
$randArray = [];

// Set cell B1-B10 values between 0,100 randomly
for ($i=1; $i<11; $i++) {
    // Generate random value
    $rand = rand(0, 100);

    // Set random value to the cell
    $sheet->setCellValue('B' . $i, $rand);

    // Store random value for reference later
    array_push($randArray, $rand);
}

/**
 * Sum the values of Cells B1 up to B10.
 */
$sheet->setCellValue('A12', 'Summation of Cells B1 up to B10.'); // description
$sheet->setCellValue('B12', '=SUM(B1:B' . ($i-1) . ')'); // formula
$sheet->setCellValue('C12', '(' . implode('+', $randArray) . ')'); // actual values to sum

/**
 * Sum the values of Cells B1 and B10 only.
 */
$sheet->setCellValue('A13', 'Summation of Cells B1 and B10 only.'); // description
$sheet->setCellValue('B13', '=SUM(B1,B' . ($i-1) . ')'); // formula
$sheet->setCellValue('C13', '(' . $randArray[0] . '+' . $randArray[count($randArray)-1] . ')'); // actual values to sum

/**
 * Sum the values of Cells B1 and B2 only.
 */
$sheet->setCellValue('A14', 'Summation of Cells B1 and B2 only.'); // description
$sheet->setCellValue('B14', '=SUM(B1,B2)'); // formula
$sheet->setCellValue('C14', '(' . $randArray[0] . '+' . $randArray[1] . ')'); // actual values to sum

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

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

create-excel-files-with-sum-formula.php

Test.

Run the following codes.

$ php create-excel-files-with-sum-formula.php

command line

Result.

Open the generated file create-excel-files-with-sum-formula.xlsx.