创建Excel动态选项

Question

I am creating an excel with PhpSpreadsheet that contains 2 sheets. The first is the selection and insertion of data, the second is a list of options that feed combos of the first page, until now everything is working very well, but I am presented with a case and I do not know if it is possible to do it.

In my database I have a table called Base_type and it contains Id and Name

I also have a table called Type_Base_List and it contains Id, Id_Type_Base and Id_List

And finally, I have the Model table that contains Id, Name and Id_Type_Base

With this I get that when the user selects a specific model I can get with that id to the lists that have associated (the model has base_type_id, and in the table base_list_type I have all the list_id associated with base_type_id)

This logic works very well in my application but I do not know if it is possible to apply that logic in a generated excel. I mean, when the person goes to cell A1 (which is the model) once you select a value, in B1, all the LISTS associated with that model should be listed (with the logic explained above)

To generate the options in the excel:

//1 SHEET
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet(0);
$sheet->setTitle('Actividades');

$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(30);

$sheet->setCellValue('A1', 'Model');
$sheet->setCellValue('B1', 'List');

//2 sheet
$spreadsheet->createSheet(1);
$spreadsheet->setActiveSheetIndex(1);
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Combos');

//Models
$sheet->mergeCells('A1:B1');
$sheet->setCellValue('A1', "Models");
$i = 2;

foreach ($models as &$model) {
    $sheet->setCellValue('A'.$i, $model['name']);
    $sheet->setCellValue('B'.$i, $model['id']);
    $i++;
}

$sheet->mergeCells('C1:D1');
$sheet->setCellValue('C1', "Lists");
$i = 2;

foreach ($lists as &$list) {
    $sheet->setCellValue('C'.$i, $list['name']);
    $sheet->setCellValue('D'.$i, $list['id']);
    $i++;
}

$spreadsheet->setActiveSheetIndex(0);
$sheet = $spreadsheet->getActiveSheet();

$i = 2;
while ($i <= $minimoListas + 2) {

   //Options Models
   $objValidation = $sheet->getCell("A".$i)->getDataValidation();
   $objValidation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
   $objValidation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
   $objValidation->setAllowBlank(false);
   $objValidation->setShowInputMessage(true);
   $objValidation->setShowErrorMessage(true);
   $objValidation->setShowDropDown(true);
   $objValidation->setErrorTitle('error entrada');
   $objValidation->setError('Valor no es válido');
   $objValidation->setPromptTitle('Select Model');
   $objValidation->setFormula1('Combos!$A$2:$A$'.$countUnidades);

   //Options List
   $objValidation = $sheet->getCell("B".$i)->getDataValidation();
   $objValidation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
   $objValidation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
   $objValidation->setAllowBlank(false);
   $objValidation->setShowInputMessage(true);
   $objValidation->setShowErrorMessage(true);
   $objValidation->setShowDropDown(true);
   $objValidation->setErrorTitle('error entrada');
   $objValidation->setError('Valor no es válido');
   $objValidation->setPromptTitle('Select List');
   $objValidation->setFormula1('Combos!$C$2:$C$'.$countListas);


   $i++;
}