Create Excel Files With Sum Formula In PHP Using PHPSpreadSheet

Posted

in

by

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

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

Test.

Run the following codes.

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

Result.

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

References:


Posted

in

by

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

Latest Tutorials

Web Dev Tutorials