Create Excel Files With Max Formula In PHP Using PHPSpreadSheet

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

You can code and write a max formula that would get the maximum value from a given specific range of cells, or get the max value between two cells, or max value of only selected cells.

This article will discuss the basic process of getting the maximum cell value using the max formula in PHP using PHPSpreadSheet. The goals of this tutorial are the following.

1. Get the max value of Cells B1 up to B10.

2. Get the max value between Cell B1 and B10.

3. Get the max value between Cell B1 and B2.

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

/**
 * Get the max value of Cells B1 up to B10.
 */
$sheet->setCellValue('A12', 'Max value of Cells B1 up to B10.'); // description
$sheet->setCellValue('B12', '=MAX(B1:B' . ($i-1) . ')'); // formula
$sheet->setCellValue('C12', '(' . implode(', ', $randArray) . ')'); // get the max value of B1 up to B10

/**
 * Get the max value of Cells B1 and B10 only.
 */
$sheet->setCellValue('A13', 'Max value of Cells B1 and B10 only.'); // description
$sheet->setCellValue('B13', '=MAX(B1,B' . ($i-1) . ')'); // formula
$sheet->setCellValue('C13', $randArray[0] . ($randArray[0]>$randArray[count($randArray)-1] ? '>' : '<') . $randArray[count($randArray)-1]); // get the max value of B1 and B10

/**
 * Get the max value of Cells B1 and B2 only.
 */
$sheet->setCellValue('A14', 'Max value of Cells B1 and B2 only.'); // description
$sheet->setCellValue('B14', '=MAX(B1,B2)'); // formula
$sheet->setCellValue('C14', $randArray[0] . ($randArray[0]>$randArray[1] ? '>' : '<') . $randArray[1]); // get the max value of B1 and B2

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

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

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

Test.

Run the following codes.

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

command line

Result.

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