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) "item1, item2, item3 etc etc"

$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,640
7
44
87

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