Data Validation (Long drop/down list)

Topics: Developer Forum, User Forum
Jan 11, 2010 at 11:39 AM

I have had some problems with Data Validation during using PHPExcel lib. I wanted to create a simple drop/down list, but there was a limitation in this case.

The concrete problem is if you want to export a drop/down list with lots of items (example: 100items, each of them is longer than 25chars) -> Here PHPExcel

rough cut the string. But in OpenOffice or Excel i can create a long drop/down list. So it should be work, but it is not.


The solution is an another type of formula. PHPExcel is use a "native" formula: "listitem1,listitem2,...,listitemN". Theres a better method when you pre-store

the items which you want to see in drop/down lists. And when you just creating the drop/down list, only reference those pre-store items.


$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, 'item1');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 2, 'item2');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 3, 'item3');

Jan 14, 2010 at 3:50 AM

Agreed. Named ranges are better that using "listitem1,listitem2,...,listitemN". There is a 255 character count limit in formulas in Excel. I don't think we can blame PHPExcel.

Related thread:

Jan 14, 2010 at 6:43 AM
Edited Jan 14, 2010 at 7:22 AM


"There is a 255 character count limit in formulas in Excel." -> Yeah, i know that. This is the reason wherefore I started looking for a solution for my problem.

"I don't think we can blame PHPExcel" -> Let him be at a standstill far from me. PHPExcel is a great lib. But I didnt find any problem solving method in any PHPExcel documentation (My personally opinion this should be in the documentation [and the solution also]).


Jan 14, 2010 at 8:50 AM

Following your suggestion I have just added the following piece of text in section "Setting data validation on a cell" in the "PHPExcel developer documentation".

 It is important to remember that any string participating in an Excel formula is allowed to be maximum 255 characters (not bytes). This sets a limit on how many items you can have in the string "Item A,Item B,Item C". Therefore it is normally a better idea to type the item values directly in some cell range, say A1:A3, and instead use, say, $objValidation->setFormula1('Sheet!$A$1:$A$3');. Another benefit is that the item values themselves can contain the comma ',' character itself.

Let's see if it helps.

Jan 14, 2010 at 8:59 AM

Hopefully :)

Keep up the good work.


Feb 1, 2010 at 11:08 PM


Setting the data validation formula to a cell range doesn't seem to work with the Excel5 Writer.

When I set the formula to this:

I get a "File error: data may have been lost" message when I open the Excel file.

However if I include an equals symbol (=) and double quotes, like this:

I no longer get the error message, but it doesn't show me my list of values either.  However, from within Excel, if I manually highlight the cell, go to its Data Validation settings, and click "OK" to save its Data Validation settings -- without making any changes -- then the list of values is correctly displayed.

I'm using Excel 2007 to test it, but I need to create Excel files with long data validation lists, that can be opened in both Office 2003 and 2007.


Feb 2, 2010 at 6:31 AM


Bug with list type data validation verified in PHPExcel_Writer_Excel5

This is correct:


Although I don't get "File error: data may have been lost" message when I open the file in Microsoft Office Excel 2007, I instead see empty drop-down.

Two minor bugs fixed in PHPExcel_Writer_Excel5. 1) Option flag bug in datavalidation record. 2) Formula token bug for 3dArea

Can you test with latest source code in a couple of hours:

Feb 2, 2010 at 7:51 PM

Got the latest source code, it's working great. Thank you!


Sep 13, 2010 at 2:52 AM

I am attempting to use Named Ranges for Data Validation to work around the 255 char limit in formulas.
The generated excel file has UNPOPULATED (empty) drop downs (with no errors reported writing out or opening the xls)
I get the same result writing both excel5 and excel2007.

I am using PHPExcel @version 1.7.4, 2010-08-26 to generate  and excel2010Beta to open the files.

I have tried a number of different values for <SheetName>: Sheet, Sheet0, Sheet1.  All fail to populate dropdown
I have also tried using setCellValueByColumnAndRow(0, 1, 'item1') etc, fails to populated dropdown

Am I doing something incorrectly?  Any advice would be greatly appreciated.

Sample code follows:

    require_once 'lib/phpexcel/PHPExcel.php';
    $objPHPExcel = new PHPExcel();

    $objPHPExcel->getActiveSheet()->setCellValue("A1", "item1");
    $objPHPExcel->getActiveSheet()->setCellValue("A2", "item2");
    $objPHPExcel->getActiveSheet()->setCellValue("A3", "item3");

    $objValidation = $objPHPExcel->getActiveSheet()->getCell("B1")->getDataValidation();
    $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
    $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
    $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.');
    //Using a comma separated list here works, but using a range comes back empty

    //// Export as Excel5: fails to populate dropdown
    //header('Content-Type: application/');
    //header('Content-Disposition: attachment;filename="dropdown_test.xls"');
    //header('Cache-Control: max-age=0');
    //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

    // Export as Excel2007: fails to populate dropdown
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="dropdown_test.xlsx"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');