DGET.php 1.6 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152
  1. <?php
  2. use PhpOffice\PhpSpreadsheet\Spreadsheet;
  3. require __DIR__ . '/../../Header.php';
  4. $helper->log('Extracts a single value from a column of a list or database that matches conditions that you specify.');
  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 height of the Apple tree between 10\' and 16\' tall');
  24. $worksheet->setCellValue('B12', '=DGET(A4:E10,"Height",A1:F2)');
  25. $helper->log('Database');
  26. $databaseData = $worksheet->rangeToArray('A4:E10', null, true, true, true);
  27. var_dump($databaseData);
  28. // Test the formulae
  29. $helper->log('Criteria');
  30. $helper->log('ALL');
  31. $helper->log($worksheet->getCell('A12')->getValue());
  32. $helper->log('DMAX() Result is ' . $worksheet->getCell('B12')->getCalculatedValue());
  33. $helper->log('Criteria');
  34. $criteriaData = $worksheet->rangeToArray('A1:A2', null, true, true, true);
  35. var_dump($criteriaData);
  36. $helper->log($worksheet->getCell('A13')->getValue());
  37. $helper->log('DMAX() Result is ' . $worksheet->getCell('B13')->getCalculatedValue());