FM / Database

/var/www/vhosts/nabawater/vendor/phpoffice/phpspreadsheet/samples/Calculations/Database/

/var/www/vhosts/nabawater/vendor/phpoffice/phpspreadsheet/samples/Calculations/Database

Name	Size	Mode	Actions
DAVERAGE.php	1827	0644	edit dl rm
DCOUNT.php	1805	0644	edit dl rm
DGET.php	1634	0644	edit dl rm
DMAX.php	1699	0644	edit dl rm
DMIN.php	1702	0644	edit dl rm
DPRODUCT.php	1645	0644	edit dl rm
DSTDEV.php	1875	0644	edit dl rm
DSTDEVP.php	1872	0644	edit dl rm
DVAR.php	1834	0644	edit dl rm
DVARP.php	1831	0644	edit dl rm

Edit: /var/www/vhosts/nabawater/vendor/phpoffice/phpspreadsheet/samples/Calculations/Database/DVARP.php (1831B)

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;

require __DIR__ . '/../../Header.php';

$helper->log('Calculates variance based on the entire population of selected database entries,');

// Create new PhpSpreadsheet object
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

// Add some data
$database = [['Tree', 'Height', 'Age', 'Yield', 'Profit'],
    ['Apple', 18, 20, 14, 105.00],
    ['Pear', 12, 12, 10, 96.00],
    ['Cherry', 13, 14, 9, 105.00],
    ['Apple', 14, 15, 10, 75.00],
    ['Pear', 9, 8, 8, 76.80],
    ['Apple', 8, 9, 6, 45.00],
];
$criteria = [['Tree', 'Height', 'Age', 'Yield', 'Profit', 'Height'],
    ['="=Apple"', '>10', null, null, null, '<16'],
    ['="=Pear"', null, null, null, null, null],
];

$worksheet->fromArray($criteria, null, 'A1');
$worksheet->fromArray($database, null, 'A4');

$worksheet->setCellValue('A12', 'The variance in the yield of Apple and Pear trees');
$worksheet->setCellValue('B12', '=DVARP(A4:E10,"Yield",A1:A3)');

$worksheet->setCellValue('A13', 'The variance in height of Apple and Pear trees');
$worksheet->setCellValue('B13', '=DVARP(A4:E10,2,A1:A3)');

$helper->log('Database');

$databaseData = $worksheet->rangeToArray('A4:E10', null, true, true, true);
var_dump($databaseData);

// Test the formulae
$helper->log('Criteria');

$criteriaData = $worksheet->rangeToArray('A1:A3', null, true, true, true);
var_dump($criteriaData);

$helper->log($worksheet->getCell('A12')->getValue());
$helper->log('DVARP() Result is ' . $worksheet->getCell('B12')->getCalculatedValue());

$helper->log('Criteria');

$criteriaData = $worksheet->rangeToArray('A1:A3', null, true, true, true);
var_dump($criteriaData);

$helper->log($worksheet->getCell('A13')->getValue());
$helper->log('DVARP() Result is ' . $worksheet->getCell('B13')->getCalculatedValue());

cmd: