Missing validation with super simple example:

Topics: Developer Forum, User Forum
Oct 28, 2009 at 8:30 PM

I'm using 1.7.0 with php 5.2.11 (recompiled after same issue with 5.2.6).  Phpinfo() reports Zip version 1.8.11, Libzip version 0.9.0. Validation cells don't appear. I opened the sheet1.xml file contained in the XLSX zip, and there's no indication of any validation being included. The XLSX file opens, but is blank.

<?php
require('PHPExcel.php');
require('PHPExcel/Writer/Excel2007.php');

$objPHPExcel = new PHPExcel();

$objValidation = $objPHPExcel->getActiveSheet()->getCell('A1')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setFormula1('"Item A,Item B,Item C"');
$objPHPExcel->getActiveSheet()->getCell('A1')->setDataValidation($objValidation);

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save("validatetest.xlsx");

?>

Developer
Oct 29, 2009 at 12:42 AM
AnonymousPrime wrote:

I'm using 1.7.0 with php 5.2.11 (recompiled after same issue with 5.2.6).  Phpinfo() reports Zip version 1.8.11, Libzip version 0.9.0. Validation cells don't appear. I opened the sheet1.xml file contained in the XLSX zip, and there's no indication of any validation being included. The XLSX file opens, but is blank.

Yes, this is a bug in PHPExcel 1.7.0, but should not be happening with latest source code. Download from here.

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

(No issue was raised for this bug, because it was fixed 2009-09-25 as side-effect of minor performance tweak [r1166]).

Oct 29, 2009 at 5:34 PM

Got the newest snapshot, works great! Thanks.

Dec 10, 2009 at 7:25 AM

I have the same problem, ie no cell validation in the output file even with a very simple example (the one above, or the one taken from the manual).

I am using phpExcel 1.7.1 with php 5.3.1 (on Windows). And I am using the Excel5 writer.

Any clue?

Is it possible that the fix mentioned by koyama was not included in the 1.7.1 release?

Thanks for any help.

Developer
Jan 20, 2010 at 11:27 AM
jlb wrote:

I have the same problem, ie no cell validation in the output file even with a very simple example (the one above, or the one taken from the manual).

I am using phpExcel 1.7.1 with php 5.3.1 (on Windows). And I am using the Excel5 writer.

Any clue?

Is it possible that the fix mentioned by koyama was not included in the 1.7.1 release?

Thanks for any help.

The problem is that data validation is not supported in PHPExcel_Writer_Excel5.

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10300

I am looking at it currently. If everything goes well it may be in place soon.

Jan 25, 2010 at 1:54 PM

Thanks for your answer.

It would be really great if you can make it work for Excel5, because that's what my users mainly use...

Thanks in advance.

Developer
Jan 27, 2010 at 10:08 AM
jlb wrote:

It would be really great if you can make it work for Excel5, because that's what my users mainly use...

Data validation should be working in latest source code:

http://phpexcel.codeplex.com/SourceControl/list/changesets

Feb 6, 2010 at 9:00 AM

Great !!!

I tested it with PHPExcel-39223 that I loaded this morning. It works fine :-)

I have just a little issue: when I set data validation (list type) on a complete column in my template Excel file, and then in a php script I load the template file using PHPExcel_Reader_Excel5, I get a memory overflow error after a while (it looks like an infinite loop in the "load()" function!). Note that it's OK when I set the data validation on one or more cells individually in the template file, the problem is only when I set data validation on a whole column. Anyway I have a workaround for that: I do not set data validation in the template file, instead I set it programatically via setDataValidation() on each cell and it works OK for me. But I just wanted to mention this issue in case you see an easy fix that might be useful to other users...

I will wait for the next packaged release (1.7.3 ?) before I put it in my production environment, but at least I can now rely on this very useful feature in my development.

I know it may sound useless to port all the nice features that you develop for Excel2007 to Excel5, but for me it's a must as I cannot easily make my users upgrade their Office environment.

It's a good opportunity for me to say that I love PHPExcel. You did a great work with this lib and so far I have no complain or enhancement request ... put aside performance improvements, but I know you have this in mind too ;-)

Thanks again

 

Developer
Feb 19, 2010 at 12:28 AM
jlb wrote:

I have just a little issue: when I set data validation (list type) on a complete column in my template Excel file, and then in a php script I load the template file using PHPExcel_Reader_Excel5, I get a memory overflow error after a while (it looks like an infinite loop in the "load()" function!).

Thanks for this info. I think I know what the problem is. In Excel, data validation is in general set for a range of cells, like A1:D20. When PHPExcel reads such file it explodes the area into single cells. This is not always optimal and can be done smarter. In the future we should try to fix this.

 

Mar 2, 2010 at 3:23 PM

Hi. I'm using PHPExcel-40449 but it doesn't seem to work if I specify the data validation as a range of cells rather than an explicit list, e.g:

$objValidation = $objPHPExcel->getActiveSheet()->getCell('F3')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$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->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setFormula1('L1:L4');
$objPHPExcel->getActiveSheet()->getCell('F3')->setDataValidation($objValidation);
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save("validatetest.xlsx");

In this example, when I click on F3, I see the drop down list symbol and the message saying 'Pick from list ...', but when I click on the drop down list, I don't see anything. Also if I go in to check the data validation formula, it doesn't appear to be set.

Any help would be greatly appreciated and keep up the great work with PHPExcel!

$objValidation = $objPHPExcel->getActiveSheet()->getCell('F3')->getDataValidation();
      $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
      $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->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
      $objValidation->setFormula1('L1:L4'); 
      $objPHPExcel->getActiveSheet()->getCell('F3')->setDataValidation($objValidation);$objValidation = $objPHPExcel->getActiveSheet()->getCell('F3')->getDataValidation();
      $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
      $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->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
      $objValidation->setFormula1('L1:L4'); 
      $objPHPExcel->getActiveSheet()->getCell('F3')->setDataValidation($objValidation);

 

Developer
Mar 16, 2010 at 8:21 AM
emma1098 wrote:

In this example, when I click on F3, I see the drop down list symbol and the message saying 'Pick from list ...', but when I click on the drop down list, I don't see anything. Also if I go in to check the data validation formula, it doesn't appear to be set.

Got it! It actually turned out to be an error in the Excel5 formula parser. Fixed now.

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10300

Download latest source tomorrow or look at the patch at link above.

http://phpexcel.codeplex.com/SourceControl/list/changesets


P.S: You can delete this line which is not necessary:

$objPHPExcel->getActiveSheet()->getCell('F3')->setDataValidation($objValidation);

Updated documentation & examples for next release.

Mar 17, 2010 at 3:12 PM

That's brilliant. Thanks koyama!

 

May 7, 2010 at 1:09 PM
Edited May 7, 2010 at 2:02 PM

Just to reopen this a bit:

It seems that the Excel5 writer will NOT properly write out a Named Range Data Validation dropdown list. For example:

  1. I have a workbook that has data entry fields on one sheet and validation lists on another.
    • Normally, you cannot place a ranged data validation dropdown on one sheet specifying a validation list on another sheet BUT you can with a named range.
    • On the data validation field the list source will be "=<NamedRange>" where <NamedRange> is the named range validation list you created.
  2. I load the XLS into PHPExcel, manipulate a few things and write out a new file.
    • NOTE: It doesn't matter if I manipulate the named ranges or not (which I would like to do to populate them with dynamic data BUT I've run tests with no manipulation with the same results).

The result is the data validation field(s) lose the "=<NamedRange>" in the source definition. The field(s) are still marked for list validation it's just that the source "=<NamedRange>" is now missing. If I manually place it back in all works as intend.

NOTE: This also happens with named ranges on the the SAME sheet as the data validation fields so it's not about cross sheet validation lists.

NOTE: I have not tested this yet in the Excel2007 writer but will shortly. Even so I'd prefer to use Excel5 for legacy users I know are out there. One less support headache.

NOTE: This was all tested using yesterday's code set so I'm up to date on the latest.

Thanks for listening!

PS: Just tested with Excel2007 (XLSX) and it worked fine (even when converted back to an XLS via Excel) so it appears it's the Excel5 writer.

Aug 10, 2012 at 7:44 PM

Any update on this? This report is a couple years old but the problem still seems to exist.

This seems to be it.


                // formula 1
                try {
                    $formula1 = $dataValidation->getFormula1();

                    if ($type == 0x03) { // list type
                        $formula1 = str_replace(',', chr(0), $formula1);
                    }
                    $this->_parser->parse($formula1);
                    $formula1 = $this->_parser->toReversePolish();
                   
                    $sz1 = strlen($formula1);

                } catch(Exception $e) {
                    $sz1 = 0;
                    $formula1 = '';
                }

At the top of the try block $formula1 holds the correct named range, but parse() throws an exception and it never gets stored.

Error message: Syntax error: @1), lookahead: , current char: 0

Any help?