<?php

use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

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

// Create new Spreadsheet object
$helper->log('Create new Spreadsheet object');
$spreadsheet = new Spreadsheet();

// Set document properties
$helper->log('Set document properties');
$spreadsheet->getProperties()
    ->setCreator('PHPOffice')
    ->setLastModifiedBy('PHPOffice')
    ->setTitle('PhpSpreadsheet Test Document')
    ->setSubject('PhpSpreadsheet Test Document')
    ->setDescription('Test document for PhpSpreadsheet, generated using PHP classes.')
    ->setKeywords('Office PhpSpreadsheet php')
    ->setCategory('Test result file');
function transpose($value)
{
    return [$value];
}

// Add some data
$continentColumn = 'D';
$column = 'F';

// Set data for dropdowns
$continents = glob(__DIR__ . '/data/continents/*');
foreach ($continents as $key => $filename) {
    $continent = pathinfo($filename, PATHINFO_FILENAME);
    $helper->log("Loading $continent");
    $continent = str_replace(' ', '_', $continent);
    $countries = file($filename, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    $countryCount = count($countries);

    // Transpose $countries from a row to a column array
    $countries = array_map('transpose', $countries);
    $spreadsheet->getActiveSheet()
        ->fromArray($countries, null, $column . '1');
    $spreadsheet->addNamedRange(
        new NamedRange(
            $continent,
            $spreadsheet->getActiveSheet(),
            $column . '1:' . $column . $countryCount
        )
    );
    $spreadsheet->getActiveSheet()
        ->getColumnDimension($column)
        ->setVisible(false);

    $spreadsheet->getActiveSheet()
        ->setCellValue($continentColumn . ($key + 1), $continent);

    ++$column;
}

// Hide the dropdown data
$spreadsheet->getActiveSheet()
    ->getColumnDimension($continentColumn)
    ->setVisible(false);

$spreadsheet->addNamedRange(
    new NamedRange(
        'Continents',
        $spreadsheet->getActiveSheet(),
        $continentColumn . '1:' . $continentColumn . count($continents)
    )
);

// Set selection cells
$spreadsheet->getActiveSheet()
    ->setCellValue('A1', 'Continent:');
$spreadsheet->getActiveSheet()
    ->setCellValue('B1', 'Select continent');
$spreadsheet->getActiveSheet()
    ->setCellValue('B3', '=' . $column . 1);
$spreadsheet->getActiveSheet()
    ->setCellValue('B3', 'Select country');
$spreadsheet->getActiveSheet()
    ->getStyle('A1:A3')
    ->getFont()->setBold(true);

// Set linked validators
$validation = $spreadsheet->getActiveSheet()
    ->getCell('B1')
    ->getDataValidation();
$validation->setType(DataValidation::TYPE_LIST)
    ->setErrorStyle(DataValidation::STYLE_INFORMATION)
    ->setAllowBlank(false)
    ->setShowInputMessage(true)
    ->setShowErrorMessage(true)
    ->setShowDropDown(true)
    ->setErrorTitle('Input error')
    ->setError('Continent is not in the list.')
    ->setPromptTitle('Pick from the list')
    ->setPrompt('Please pick a continent from the drop-down list.')
    ->setFormula1('=Continents');

$spreadsheet->getActiveSheet()
    ->setCellValue('A3', 'Country:');
$spreadsheet->getActiveSheet()
    ->getStyle('A3')
    ->getFont()->setBold(true);

$validation = $spreadsheet->getActiveSheet()
    ->getCell('B3')
    ->getDataValidation();
$validation->setType(DataValidation::TYPE_LIST)
    ->setErrorStyle(DataValidation::STYLE_INFORMATION)
    ->setAllowBlank(false)
    ->setShowInputMessage(true)
    ->setShowErrorMessage(true)
    ->setShowDropDown(true)
    ->setErrorTitle('Input error')
    ->setError('Country is not in the list.')
    ->setPromptTitle('Pick from the list')
    ->setPrompt('Please pick a country from the drop-down list.')
    ->setFormula1('=INDIRECT($B$1)');

$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(30);

// Save
$helper->write($spreadsheet, __FILE__);