Populate dropdown with phpexcel

Question

I'm running into the following problem.

Im trying to get some results from my database and I want to insert the results into a dropdown list.

In the examle file was the following example:

$objValidation->setFormula1('"Item A,Item B,Item C"');

so the results have to be comma seperated and the total results have to be between "".

Here is the code so far:

$configurations = Db::getInstance()->queryResults('SELECT * FROM configurations', array($siteNumber));
$objPHPExcel->getActiveSheet()->setCellValue('B7', "List:");

$configs = '"';
foreach($configurations as $config) {
    $configs .= $config->configuration_name . ', ';
}
$configs .= '"';

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B8')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Value is not in list.');
$objValidation->setPromptTitle('Pick from list');
//$objValidation->setPrompt('Please pick a value from the drop-down list.');
$objValidation->setFormula1($configs);

Anybody has an idea why the dropdownlist does not get populated??

Thanks in advance!

Dump database results:

string(3100) "DUS800, DUG900+3xRRUS, DUW2100, 2xMU, SIU, DUS800+3xRRUS, DUG900+3xRRUS, DUW2100, 2xMU, SIU, DUS800+3xRRUS, DUG900+3xRRUS, DUW2100+3xRRUS, 2xMU, SIU, DUS800, DUG900+3xRRUS, DUW2100+3xRRUS, 2xMU, SIU, DUS800, DUS1800+3xRRUS, MU, SIU, DUS800+3xRRUS, DUS1800+3xRRUS, MU, SIU, DUS800, DUG900+3xRRUS, MU, SIU, DUS800+3xRRUS, DUG900+3xRRUS, MU, SIU, DUS800, MU, SIU, DUS800+3xRRUS, MU, SIU, DUS800, DUW2100, 2xMU, SIU, DUG900+3xRRUS, MU, DUS1800+3xRRUS, MU, SIU, DUW2100, MU, DUW2100+3xRRUS, MU, DUG900+3xRRUS, MU, DUG900+3xRRUS, DUW2100, 2xMU, DUG900+3xRRUS, DUW2100+3xRRUS, 2xMU, DUW2100, MU, DUW2100+3xRRUS, MU, RBS6102 DUS800+DUG900+DUW2100 outdoor+SIU, RBS6102hybrid DUS800+DUG900+DUW2100Remote outdoor+SIU, RBS6201 DUS800+DUG900+DUW2100 indoor+SIU, RBS6201hybrid DUS800+DUG900+DUW2100Remote indoor+SIU, RBS6102 DUS800+DUS1800 outdoor+SIU, RBS6201 DUS800+DUS1800 indoor+SIU, RBS6102 DUS800 outdoor+SIU, RBS6201 DUS800 indoor+SIU, RBS6102 DUS800+DUG900 outdoor+SIU, RBS6201 DUS800+DUG900 indoor+SIU, RBS6102 DUS1800 outdoor+SIU, RBS6201 DUS1800 indoor+SIU, RBS6102 DUG900+DUS1800 outdoor+SIU, RBS6201 DUG900+DUS1800 indoor+SIU, RBS6102 DUG900+DUW2100 outdoor, RBS6201 DUG900+DUW2100 indoor, RBS6102hybrid DUG900+DUW2100Remote outdoor, RBS6201hybrid DUG900+DUW2100Remote indoor, RBS6102 DUG900 outdoor, RBS6201 DUG900 indoor, RBS6102 DUW2100 outdoor, RBS6201 DUW2100 indoor, RBS6102 DUS1800+DUW2100 outdoor+SIU, RBS6201 DUS1800+DUW2100 indoor+SIU, Exp pakket G900 in RBS6102 UMTS, Exp pakket G900 in RBS6201 UMTS, Exp pakket G900 in RBS6102 LTE, Exp pakket G900 in RBS6201 LTE, Exp pakket U2100 in RBS6102 GSM, Exp pakket U2100 in RBS6201 GSM, Exp pakket U2100 in RBS6102 LTE, Exp pakket U2100 in RBS6201 LTE, Exp pakket U2100 in RBS6102 GSM/LTE, Exp pakket U2100 in RBS6201 GSM/LTE, Exp pakket U2100 OIL in RBS6102 GSM/LTE, Exp pakket L800 in RBS6102 GSM, Exp pakket L800 in RBS6201 GSM, Exp pakket L800 in RBS6102 LTE, Exp pakket L800 in RBS6201 LTE, Exp pakket L1800 in RBS6102 GSM, Exp pakket L1800 in RBS6201 GSM, Exp pakket L1800 in RBS6102 LTE, Exp pakket L1800 in RBS6201 LTE, expansie DUG1800+MU in Main-Remote, expansie DUG1800 in RBS6102, expansie DUG1800 in RBS6201, 3rd Carrier DUW2100 in bestaande Main-Remote met RBS6601 V1, 3th Carrier DUW2100 in bestaande Main-Remote met RBS6601 V2, 3rd Carrier DUW2100 in RBS6102, 3rd Carrier DUW2100 in RBS6201, Exp pakket add LTE1800 3x RRU, Exp pakket add LTE1800 3x RRU + 3x PSU 230V/48V, Exp pakket add LTE1800 3x RRU + 3x PSU 230V/48V + 1x DUS31, Exp pakket add LTE1800 3x RRU + 3x PSU 230V/48V + 1x DUS41, DU adapter frame t.b.v. RBS 6102, DU adapter frame t.b.v. RBS 6201, 3x DUMMY RUS t.b.v. ombouw macro naar OIL, 6x DUMMY RUS t.b.v. ombouw macro naar OIL, 1x PSU AC01 1,8kW  incl bekabeling, 4x PSU AC03 2,5kW incl bekabeling, Battery set complete 1x RBS 6102, Battery set complete 2x RBS 6102, Battery set complete 1x MMC, Battery set complete 2x MMC, Battery set complete 1x RBS6102+1x MMC, Battery set complete 2x RBS6102+1x MMC, Battery set complete indoor 1 set, Battery set complete indoor 2 sets, Battery set complete indoor 3 sets"

$configs look like this : "DUS800, DUG900+3xRRUS, DUW2100, 2xMU, SIU, DUS800+3xRRUS, DUG900+3xRRUS, DUW2100" — Frank W., Jul 04 '16 at 13:37

user5104026 · Accepted Answer · 2016-07-08T02:04:29.993

Here is the correctly working code:

<?php
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

require_once dirname(__FILE__) . './PHPExcel.php';


echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()
    ->setCellValue('B5', "SELECT ITEM")
    ;


$configs = "DUS800, DUG900+3xRRUS, DUW2100, 2xMU, SIU, DUS800+3xRRUS, DUG900+3xRRUS, DUW2100";

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Value is not in list.');
$objValidation->setPromptTitle('Pick from list');
$objValidation->setPrompt('Please pick a value from the drop-down list.');
$objValidation->setFormula1('"'.$configs.'"');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel 95 file
echo date('H:i:s') , " Write to Excel5 format" , EOL;
$callStartTime = microtime(true);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('populate.xls');
?>

It will output to populate.php in the same directory as this script.

First off, it's not setFormula1("'".$configs."'"). It's setFormula1('"'.$configs.'"').

Secondly, you might be missing something elsewhere, which is why you are getting ERR_CONNECTION_RESET. I posted a working example just in case you are missing something else in the code as well. If you had posted the entire code, I could have known for sure.

Like you write it, it works fine. But when I replace the '$configs' by `$configs = ''; foreach($configurations as $config) { $configs .= $config->configuration_name . ', '; } $configs = '"'; $count = 1; foreach($configurations as $config) { if($count == 1){ $configs .= $config->configuration_name; } else { $configs .= ', '.$config->configuration_name; } $count += 1; } ` So retreive records from the database, it doesnt work anymore — Frank W., Jul 09 '16 at 22:41
How about you echo the `$configs` variable before you pass it to the dropdown? Is it formatted EXACTLY like my `$configs`? What is the error exactly? Is it `ERR_CONNECTION_RESET` or is that one resolved? ALSO, where are you generating the SQL request in the code? I suggest you do the entire thing before `error_reporting(E_ALL);`. Generate the `$configs` variable beforehand and then close the connection to the MySQL database. — user5104026, Jul 10 '16 at 02:04
P.S. Your code suggests you aren't adding a `'"'`at the end @FrankW. — user5104026, Jul 10 '16 at 02:14
There is certainly a problem with the code you are using to generate `$configs`. I can see half of it here. Please export the `$configurations` variable to a file as suggested by [this page](http://stackoverflow.com/questions/2995461/save-php-variables-to-a-text-file) and attach it to your question. — user5104026, Jul 10 '16 at 16:27
@FrankW. please post up the `$configurations` variable by exporting it to a file. If you got the dropdown working, fetching stuff from the database and replacing the static `$configs` isn't a problem. — user5104026, Jul 12 '16 at 08:02

Arun · Answer 2 · 2016-07-04T14:02:47.583

3

Change the line

$objValidation->setFormula1($configs);

to

$objValidation->setFormula1("'".$configs."'");

Because in the structure the data is with in single quotes(').

Sample working code

$objValidation2 = $sheet -> getCell('E1') -> getDataValidation();
$objValidation2 -> setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
$objValidation2 -> setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
$objValidation2 -> setAllowBlank(true);
$objValidation2 -> setShowInputMessage(true);
$objValidation2 -> setShowErrorMessage(true);
$objValidation2 -> setShowDropDown(true);
$objValidation2 -> setErrorTitle('Invalid date');
$objValidation2 -> setError('Date is not in list.');
$objValidation2 -> setPromptTitle('Select DOB date');
$objValidation2 -> setPrompt('Please pick a date from the drop-down list.');
$dates = '"01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31"';
$objValidation2 -> setFormula1("'".$dates."'");

edited Jul 04 '16 at 14:02

answered Jul 04 '16 at 13:39

Arun

3,496
7
41
78

I tried this but I keep getting a `ERR_CONNECTION_RESET` – Frank W. Jul 04 '16 at 13:43
$objValidation -> setFormula1('"01,02,03,04,05,06,07,08,09,10,11,12"'); is this working for you? Try with it. – Arun Jul 04 '16 at 13:46
Works great. But that is the strange thing. When I add the variable as argument I can't get it to work. – Frank W. Jul 04 '16 at 13:50
Is there any value that start with an '='? – Arun Jul 04 '16 at 13:51
No only with a '-' – Frank W. Jul 04 '16 at 13:52
Can you get the error log? You may get some more detailed description. It may be due to the data. The code is working. – Arun Jul 04 '16 at 13:54
No, still clueless. No errors just doesn't populate the dropdown – Frank W. Jul 04 '16 at 14:04
@FrankW, I added a sample working code with variable assign. It is working on my end. If it is working with you, then the problem with the data. – Arun Jul 04 '16 at 14:05
What I did was Made a dump of the results and pasted it directly into the function, then i get the `ERR_CONNECTION_RESET` error again. That means indeed that it has to be within the results. I've have added the results to the question. Could i ask you to check it and see if you notice anything strange. – Frank W. Jul 04 '16 at 14:13
I think I got the issue. Check with `$objValidation->setFormula1("'\"".$configs."\"'");` – Arun Jul 04 '16 at 14:15
I really appreciate your time but unfortunately still not working :( still the `ERR_CONNECTION_RESET` – Frank W. Jul 04 '16 at 14:17
At the time of var dump, it should show like `""01,02,03,04,05,06""`. It have to show two double quotes at the start and end. – Arun Jul 04 '16 at 14:19
There has to be something wrong with this construction `$objValidation->setFormula1("'\"".$configs."\"'");` I tested it with config like this: `$configs = 'hello, hello2';` now it executes but It doesn't populate. – Frank W. Jul 04 '16 at 14:24
Try with `$objValidation->setFormula1("'".$configs."'");` and `$configs = '"hello, hello2"';` It should work. – Arun Jul 04 '16 at 14:26
No, it executes but It still won't populate – Frank W. Jul 04 '16 at 14:29
Sorry for that. It was wrong. Try `$objValidation->setFormula1($configs);` with the same `$configs` – Arun Jul 04 '16 at 14:34
That works, But when I replace the $configs with the loop It does not execute anymore: `$configs = '\'"'; foreach($configurations as $config) { $configs .= $config->configuration_name . ', '; } $configs .= '"\'';` – Frank W. Jul 04 '16 at 14:40
This will definitely work `$configs = '"'; $count = 1; foreach($configurations as $config) { if($count == 1){ $configs .= $config->configuration_name; } else{ $configs .= ', '.$config->configuration_name; } $count += 1; } $configs .= '"';` – Arun Jul 04 '16 at 14:43
The issue is, you are appending a comma in all the line. So after the last record, there will be a comma and it throw error. – Arun Jul 04 '16 at 14:46
This may not completely work. there is limitation in the number of content in a dropdown. the better way is to add the record in another column, hide it and show the content in it in the desired column. – Arun Jul 04 '16 at 14:49
I couldn't find out the issue. But I got something. Your first `DUS800, DUG900+3xRRUS, DUW2100, 2xMU, SIU, DUS800+3xRRUS, DUG900+3xRRUS, DUW2100, 2xMU, SIU, DUS800+3xRRUS, DUG900+3xRRUS, DUW2100+3xRRUS, 2xMU, SIU, DUS800, DUG900+3xRRUS, DUW2100+3xRRUS, 2xMU, SIU, DUS800, DUS1800+3xRRUS, MU, SIU, DUS800+3xRRUS` record is working. After that, not. I don't know why – Arun Jul 04 '16 at 15:00
This is so flippin frustrating – Frank W. Jul 04 '16 at 15:03
You can try one thing. Load the data in another column, and refer it to the desired column. Try if it works. I am not sure – Arun Jul 04 '16 at 15:04
@FrankW., Its nice to meet u. I have to leave now. Keep in touch – Arun Jul 04 '16 at 15:08
Thanks for your help! – Frank W. Jul 04 '16 at 15:09
It's not `setFormula1("'".$configs."'")`. It's `setFormula1('"'.$configs.'"')`. I posted a working example just in case you are missing something else in the code as well (which I think you are, since you are getting `ERR_CONNECTION_RESET` as well. – user5104026 Jul 07 '16 at 17:45

Populate dropdown with phpexcel

2 Answers2

Linked