Data Validation - excel taking a long time to save

Topics: Developer Forum
Mar 13, 2012 at 11:23 AM

PHPExcel - 86490

Writer - 2007


I have a spread sheet with around 12,000 rows, I am setting data validation on 2 columns using the following code...

	$objValidation = $sheet->getCell('I2')->getDataValidation();
	$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_DECIMAL );
	$objValidation->setOperator( PHPExcel_Cell_DataValidation::OPERATOR_BETWEEN );		
	$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
	$objValidation->setAllowBlank(true);
	$objValidation->setShowInputMessage(true);
	$objValidation->setShowErrorMessage(true);
	$objValidation->setErrorTitle('Discount % Error');
	$objValidation->setError('Only numbers between 1 and 100 are allowed in this column.');
	$objValidation->setPromptTitle('Discount %');
	$objValidation->setPrompt('Enter a discount % between 1 and 100.');
	$objValidation->setFormula1(1.00);
	$objValidation->setFormula2(100.00);
	
	$objValidation1 = $sheet->getCell('J2')->getDataValidation();
	$objValidation1->setType( PHPExcel_Cell_DataValidation::TYPE_DECIMAL );
	$objValidation1->setOperator( PHPExcel_Cell_DataValidation::OPERATOR_GREATERTHAN );	
	$objValidation1->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
	$objValidation1->setAllowBlank(true);
	$objValidation1->setShowInputMessage(true);
	$objValidation1->setShowErrorMessage(true);
	$objValidation1->setErrorTitle('Nett Price Error');
	$objValidation1->setError('Only numbers greater than 0 allowed in this column.');
	$objValidation1->setPromptTitle('Nett Price');
	$objValidation1->setPrompt('Enter a number greater than 0.');
	$objValidation1->setFormula1(0);

------------------------------------

	$sheet->getCell('I' .$row)->setDataValidation(clone $objValidation);
	$sheet->getCell('J' .$row)->setDataValidation(clone $objValidation1);


The problem is that when I try to save in excel it is taking around a minute to save the file.

If I use Save As in Excel and select XLS 2003 then the file saves immediately.

If I remove the data validation that I have set in PHPExcel and add it using Excel itself then the file saves immediately.

Is there a way I can set the validation to a whole range instead of setting it cell by cell, I think this might be the problem?

Mar 14, 2012 at 12:30 PM
Edited Mar 14, 2012 at 12:30 PM

After looking at the XML file inside the different files, this is definitely due to me having to clone data validation per row instead of applying it to a range of cells.

In the file where I created the validation using Excel the XML is one entry;

 

<dataValidations count="2">
<dataValidation type="decimal" allowBlank="1" showInputMessage="1" showErrorMessage="1" errorTitle="Discount" error="Discount" promptTitle="Discount" prompt="Discount" sqref="I2:I12974"><formula1>1</formula1><formula2>100</formula2></dataValidation>
<dataValidation type="decimal" operator="greaterThan" allowBlank="1" showInputMessage="1" showErrorMessage="1" errorTitle="Nett" error="Nett" promptTitle="Nett" prompt="Nett" sqref="J2:J12974"><formula1>0</formula1></dataValidation>
</dataValidations>

 

In the file where I created the validation using PHPExcel the XML has two entries per ROW;

 

<dataValidation type="decimal" operator="greaterThan" allowBlank="1" showDropDown="1" showInputMessage="1" showErrorMessage="1" errorTitle="Nett Price Error" error="Only numbers greater than 0 allowed in this column." promptTitle="Nett Price" prompt="Enter a number greater than 0." sqref="J2"><formula1>0</formula1></dataValidation>
<dataValidation type="decimal" allowBlank="1" showDropDown="1" showInputMessage="1" showErrorMessage="1" errorTitle="Discount % Error" error="Only numbers between 1 and 100 allowed in this column." promptTitle="Discount %" prompt="Enter a discount % between 1 and 100." sqref="I2"><formula1>1</formula1><formula2>100</formula2></dataValidation>

 

 

This is causing the XML file to be 3 times as big.

Mar 21, 2012 at 1:09 AM
Edited Mar 21, 2012 at 1:11 AM

ale1981 - instead of getting each individual cell and looping to set each data validation, you should be able to use the worksheet object's setDataValidation method and pass to it your full range: 

  $xlReader =  \PHPExcel_IOFactory::createReader("Excel2007");
  $xl = $xlReader->load("../yourFile.xlsx");
  $sheet = $xl->getSheetByName("YourSheet");
  $sheet->setDataValidation("I2:I12974", $objValidation);

If you crawl through the classes you'll see that the Cell object's setDataValidation just calls the parent's (ie worksheet) setDataValidation method, passing the coordinate of the cell and the validation objects along:

 

public function setDataValidation(PHPExcel_Cell_DataValidation $pDataValidation = null)
	{
		if (!isset($this->_parent)) {
			throw new Exception('Cannot set data validation for cell that is not bound to a worksheet');
		}

		$this->_parent->setDataValidation($this->getCoordinate(), $pDataValidation);

		return $this->notifyCacheController();
	}

 

Looking at the worksheet's method you see:

 

public function setDataValidation($pCellCoordinate = 'A1', PHPExcel_Cell_DataValidation $pDataValidation = null)
	{
		if ($pDataValidation === null) {
			unset($this->_dataValidationCollection[$pCellCoordinate]);
		} else {
			$this->_dataValidationCollection[$pCellCoordinate] = $pDataValidation;
		}
		return $this;
	}

 

Lastly, looking at the Excel2007_Worksheet writer you find:

 

foreach ($dataValidationCollection as $coordinate => $dv) {
				$objWriter->startElement('dataValidation');

				if ($dv->getType() != '') {
					$objWriter->writeAttribute('type', $dv->getType());
				}

				if ($dv->getErrorStyle() != '') {
					$objWriter->writeAttribute('errorStyle', $dv->getErrorStyle());
				}

				if ($dv->getOperator() != '') {
					$objWriter->writeAttribute('operator', $dv->getOperator());
				}

				$objWriter->writeAttribute('allowBlank',		($dv->getAllowBlank()		? '1'  : '0'));
				$objWriter->writeAttribute('showDropDown',		(!$dv->getShowDropDown()	? '1'  : '0'));
				$objWriter->writeAttribute('showInputMessage',	($dv->getShowInputMessage()	? '1'  : '0'));
				$objWriter->writeAttribute('showErrorMessage',	($dv->getShowErrorMessage()	? '1'  : '0'));

				if ($dv->getErrorTitle() !== '') {
					$objWriter->writeAttribute('errorTitle', $dv->getErrorTitle());
				}
				if ($dv->getError() !== '') {
					$objWriter->writeAttribute('error', $dv->getError());
				}
				if ($dv->getPromptTitle() !== '') {
					$objWriter->writeAttribute('promptTitle', $dv->getPromptTitle());
				}
				if ($dv->getPrompt() !== '') {
					$objWriter->writeAttribute('prompt', $dv->getPrompt());
				}

				$objWriter->writeAttribute('sqref', $coordinate);

				if ($dv->getFormula1() !== '') {
					$objWriter->writeElement('formula1', $dv->getFormula1());
				}
				if ($dv->getFormula2() !== '') {
					$objWriter->writeElement('formula2', $dv->getFormula2());
				}

				$objWriter->endElement();
			}

 

That bold line is setting the sqref attribute of the dataValidation node to match whatever string (aka coordinate) you passed to the dataValidationCollection using the setDataValidation method of the worksheet.  So if you pass "I2:I12974" then that's what'll go into the sqref (Sequence of References).  By the way, if you have a non-continuous range you can also hit all of those with one validation by separating them with spaces (eg "I2:I6 I20:I30 K1:K3" etc).

Hope that helps!

Alex

Mar 26, 2012 at 10:40 AM

Thanks Alex, I did not know the method existed for the worksheet, this worked great and has solved the issue.

Dec 13, 2012 at 3:13 AM

please help me about to put formula on large row without using iteration, cant find it anywhere on mr google.

big thanks.

Mar 12, 2013 at 7:55 AM
I'm a chinese,Thanks a lot.It's awsome.