DVAR.php 1.8 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455
  1. <?php
  2. use PhpOffice\PhpSpreadsheet\Spreadsheet;
  3. require __DIR__ . '/../../Header.php';
  4. $helper->log('Estimates variance based on a sample from selected database entries.');
  5. // Create new PhpSpreadsheet object
  6. $spreadsheet = new Spreadsheet();
  7. $worksheet = $spreadsheet->getActiveSheet();
  8. // Add some data
  9. $database = [['Tree', 'Height', 'Age', 'Yield', 'Profit'],
  10. ['Apple', 18, 20, 14, 105.00],
  11. ['Pear', 12, 12, 10, 96.00],
  12. ['Cherry', 13, 14, 9, 105.00],
  13. ['Apple', 14, 15, 10, 75.00],
  14. ['Pear', 9, 8, 8, 76.80],
  15. ['Apple', 8, 9, 6, 45.00],
  16. ];
  17. $criteria = [['Tree', 'Height', 'Age', 'Yield', 'Profit', 'Height'],
  18. ['="=Apple"', '>10', null, null, null, '<16'],
  19. ['="=Pear"', null, null, null, null, null],
  20. ];
  21. $worksheet->fromArray($criteria, null, 'A1');
  22. $worksheet->fromArray($database, null, 'A4');
  23. $worksheet->setCellValue('A12', 'The estimated variance in the yield of Apple and Pear trees');
  24. $worksheet->setCellValue('B12', '=DVAR(A4:E10,"Yield",A1:A3)');
  25. $worksheet->setCellValue('A13', 'The estimated variance in height of Apple and Pear trees');
  26. $worksheet->setCellValue('B13', '=DVAR(A4:E10,2,A1:A3)');
  27. $helper->log('Database');
  28. $databaseData = $worksheet->rangeToArray('A4:E10', null, true, true, true);
  29. var_dump($databaseData);
  30. // Test the formulae
  31. $helper->log('Criteria');
  32. $criteriaData = $worksheet->rangeToArray('A1:A3', null, true, true, true);
  33. var_dump($criteriaData);
  34. $helper->log($worksheet->getCell('A12')->getValue());
  35. $helper->log('DVAR() Result is ' . $worksheet->getCell('B12')->getCalculatedValue());
  36. $helper->log('Criteria');
  37. $criteriaData = $worksheet->rangeToArray('A1:A3', null, true, true, true);
  38. var_dump($criteriaData);
  39. $helper->log($worksheet->getCell('A13')->getValue());
  40. $helper->log('DVAR() Result is ' . $worksheet->getCell('B13')->getCalculatedValue());