Running WorkBC Abilities quiz using PhpSpreadsheet

abilities.php

<?php

// Uses my fork of PhpSpreadsheet to add custom functions
// https://github.com/infojunkie/PhpSpreadsheet
//
// Usage:
// - Place spreadsheet "10-1. Quizzes - Algo Check - Shared.xlsx" in sheets/ subfolder
// - Run `php abilities.php`
// - Observe output:
/**
array(11) {
  [0]=>
  array(3) {
    [0]=>
    int(31110)
    [1]=>
    string(8) "Dentists"
    [2]=>
    float(0.8888888888888888)
  }
  [1]=>
  array(3) {
    [0]=>
    int(31101)
    [1]=>
    string(22) "Specialists in surgery"
    [2]=>
    float(0.8611111111111112)
  }
  [2]=>
  array(3) {
    [0]=>
    int(31209)
    [1]=>
    string(64) "Other professional occupations in health diagnosing and treating"
    [2]=>
    float(0.8333333333333334)
  }
  [3]=>
  array(3) {
    [0]=>
    int(31103)
    [1]=>
    string(13) "Veterinarians"
    [2]=>
    float(0.8055555555555556)
  }
  [4]=>
  array(3) {
    [0]=>
    int(31102)
    [1]=>
    string(43) "General practitioners and family physicians"
    [2]=>
    float(0.7777777777777778)
  }
  [5]=>
  array(3) {
    [0]=>
    int(21101)
    [1]=>
    string(8) "Chemists"
    [2]=>
    float(0.7777777777777778)
  }
  [6]=>
  array(3) {
    [0]=>
    int(22212)
    [1]=>
    string(38) "Drafting technologists and technicians"
    [2]=>
    float(0.75)
  }
  [7]=>
  array(3) {
    [0]=>
    int(72600)
    [1]=>
    string(51) "Air pilots, flight engineers and flying instructors"
    [2]=>
    float(0.75)
  }
  [8]=>
  array(3) {
    [0]=>
    int(72600)
    [1]=>
    string(51) "Air pilots, flight engineers and flying instructors"
    [2]=>
    float(0.75)
  }
  [9]=>
  array(3) {
    [0]=>
    int(72600)
    [1]=>
    string(51) "Air pilots, flight engineers and flying instructors"
    [2]=>
    float(0.75)
  }
  [10]=>
  array(3) {
    [0]=>
    int(31100)
    [1]=>
    string(47) "Specialists in clinical and laboratory medicine"
    [2]=>
    float(0.75)
  }
}
*/

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Calculation\LookupRef\RowColumnInformation;
use PhpOffice\PhpSpreadsheet\Calculation\Category;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
use PhpOffice\PhpSpreadsheet\Calculation\LookupRef\Matrix;

$spreadsheet = IOFactory::load("sheets/10-1. Quizzes - Algo Check - Shared.xlsx");
$calculation = $spreadsheet->getCalculationEngine();
$calculation->getDebugLog()->setWriteDebugLog(true);
//$calculation->getDebugLog()->setEchoDebugLog(true);
$calculation->setArrayReturnType(PhpOffice\PhpSpreadsheet\Calculation\Calculation::RETURN_ARRAY_AS_ARRAY);

$functions = &get_class($calculation)::getFunctions();
$functions['CHOOSECOLS'] = [
  'category' => Category::CATEGORY_MATH_AND_TRIG,
  'functionCall' => [Custom::class, 'choosecols'],
  'argumentCount' => '2+',
];

class Custom
{
  /**
   * CHOOSECOLS.
   *
   * Returns the specified columns from an array.
   *
   * @param mixed $cells The cells being searched
   * @param int $cols List of numeric column indexes to extract
   *
   * @return array|string The resulting array, or a string containing an error
   */
  public static function choosecols(mixed $cells, int ...$cols): array|string
  {
    $columns = RowColumnInformation::COLUMNS($cells);
    if (is_string($columns)) {
      return $columns;
    }
    $result = [];
    foreach ($cols as $col) {
      if (!$col || abs($col) > $columns) {
        return ExcelError::VALUE();
      }
      $result[] = array_column($cells, $col > 0 ? $col-1 : $columns-$col);
    }
    return Matrix::transpose($result);
  }
}

$sheet = $spreadsheet->setActiveSheetIndexByName('Abilities-NewQOrder');
$answers = '00444444444444444444444444444444444444';
$columns = 'JIHGF';
foreach (str_split($answers) as $a => $answer) {
  foreach (str_split($columns) as $c => $column) {
    $cell = $sheet->getCell($column . ($a + 5));
    $cell->setValue("$c" === $answer ? 1 : NULL);
  }
}
$cell = $sheet->getCell('O18');
$result = $calculation->calculate($cell);
var_dump(array_slice($result, 0, 11));