setCellValueTypeByColumnAndRow: new function to worksheet.php

May 19, 2010 at 6:25 PM
Edited May 20, 2010 at 12:41 PM

I suggest add one more function to Worksheet.php, wich can combine getCellByColumnAndRow and setCellValueByColumnAndRow, but more faster. When use this library for export large data, we need maximum speed. Here my sample: 

/**
* Custom function
*
* Set cell value at a specific coordinate by using numeric cell coordinates
*
* @param 	string $pColumn		Numeric column coordinate of the cell
* @param 	string $pRow		Numeric row coordinate of the cell
* @param	mixed  $pValue		Value of the cell
* @param	string $pDataType	Explicit data type
* @return	PHPExcel_Worksheet
*/
public function setCellValueTypeByColumnAndRow($pColumn = 0, $pRow = 0, $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
{
	$columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
	$coordinate = $columnLetter . $pRow;

	if (!$this->_cellCollection->isDataSet($coordinate))
	{
		$this->_cellCollection->addCacheData($coordinate, new PHPExcel_Cell($columnLetter, $pRow, $pValue, $pDataType, $this));
		$this->_cellCollectionIsSorted = false;

		if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < $pColumn)
			$this->_cachedHighestColumn = $columnLetter;

		if ($this->_cachedHighestRow < $pRow)
			$this->_cachedHighestRow = $pRow;
	}
	else
	{
		$cell = $this->_cellCollection->getCacheData($coordinate);
		$cell->setValue($pValue);
		$cell->setDataType($pDataType);
	}

	return $this;
}

Coordinator
May 19, 2010 at 10:18 PM

Is this measurably faster than the setCellValueExplicitByColumnAndRow() method?

May 20, 2010 at 12:41 PM
You can test on 7500 cells... Or you can compare count of actions in my function and count of actions in functions: getCell, setValueExplicit; P.S. this is not finished variant of function. It works on 1.7.2, but with 1.7.3 need more work... :)
Mar 19, 2011 at 7:02 PM

Almost year past from our discussion and looks like developers do not interested in hi-speed data set functions ;)

If any one require this function, here last version, compatible with last versions of PHPExcel (insert it into Worksheet.php): 

/**
* Custom function
*
* Set cell value at a specific coordinate by using numeric cell coordinates
*
* @param 	string $pColumn		Numeric column coordinate of the cell
* @param 	string $pRow		Numeric row coordinate of the cell
* @param	mixed  $pValue		Value of the cell
* @param	string $pDataType	Explicit data type
* @return	PHPExcel_Worksheet
*/
public function setCellValueTypeByColumnAndRow($pColumn = 0, $pRow = 0, $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING)
{
	$columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
	$coordinate = $columnLetter . $pRow;

	if (!$this->_cellCollection->isDataSet($coordinate))
	{
		$this->_cellCollection->addCacheData($coordinate, $cell = new PHPExcel_Cell($columnLetter, $pRow, $pValue, $pDataType, $this));
		$this->_cellCollectionIsSorted = false;

		if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < $pColumn)
			$this->_cachedHighestColumn = $columnLetter;

		if ($this->_cachedHighestRow < $pRow)
			$this->_cachedHighestRow = $pRow;
	}
	else
	{
		$cell = $this->_cellCollection->getCacheData($coordinate);
		$cell->setValueExplicit($pValue, $pDataType);
	}

	return $cell;
}

Jun 14, 2012 at 8:34 AM
Edited Jun 14, 2012 at 9:03 AM

Hi SailorMax,

I try this method and did some benchmarks:

3822 Cells: Time 3.5947 -> 3.3331

25382 Cells Time:  13.2808 ->12.5732

setCellValueExplicitByColumnAndRow -> setCellValueTypeByColumnAndRow

Testet version PHPExcel 1.7.7 with PHP 5.2

 

Greetings

Frank

Aug 21, 2012 at 11:29 AM
Edited Aug 21, 2012 at 12:09 PM

Here is my result of test (real life function with using styles and setWidth):

58100 Cells (83 x 700): Time 17sec -> 14sec (difference = 20%)  :: PHP 5.2.17, CPU Core 2 DUO 2.4Ghz, 2GB RAM

I think problem in getCell() - too many work with string of coordinate in our case. On input we have X and Y. We don't need in this case check it on "Worksheet reference" (strpos), "Named range" (2 x preg_match) and other exceptions (3 x strpos) + It use setXfIndex(). What I lost without it???

Dec 6, 2012 at 1:31 PM

I use this modifikation since June (in Production) and it works perfekt also with 1.7.8.

It safes arround 20% time for every Excel-File generation.

Lot auf my reports have over 100.000 cells so it safes a lot of time.

Jun 3, 2013 at 11:43 AM
Hi Sailormax,

do you have a Version of setCellValueTypeByColumnAndRow that works with the new Version 1.7.9?

Greetings

Frank
Jun 3, 2013 at 3:17 PM
Edited Jun 3, 2013 at 3:20 PM
I trying to change, but in my case version 1.7.9 is totaly broken :/ It doesn't work even with standart functions.

try put as first line of my function:
return $this->setCellValueExplicitByColumnAndRow($pColumn, $pRow, $pValue, $pDataType);
This is slower version, but can you test it? Does it work at you? I have many problems like infinite recursion somewhere: https://phpexcel.codeplex.com/workitem/19800

If at you it works, try to make next change in my function:
//$this->_cellCollection->addCacheData($coordinate, $cell = new PHPExcel_Cell($columnLetter, $pRow, $pValue, $pDataType, $this)); // deprecated
$this->_cellCollection->addCacheData($coordinate, $cell = new PHPExcel_Cell($pValue, $pDataType, $this));
May be this will fix it, but currently I can't test it :(
Jun 4, 2013 at 8:16 AM
Edited Jun 4, 2013 at 9:38 AM
Thanx SailorMax,

I don't use $this->setCellValueExplicitByColumnAndRow($pColumn, $pRow, $pValue, $pDataType); it is to slow, better is:
$sheet->getCell($coordinate)->setValueExplicit($cell_value, PHPExcel_Cell_DataType::TYPE_STRING);
It is much faster then any ByColumnAndRow, because there are some obsolete functions call if you have the coordinate.

But your Function will be a little bit faster then this, with my litte optimization around 10% faster then getCell()->setValueExplicit.
I do this:
public function setCellValueTypeByColumnAndRow($pColumn = 0, $pRow = 0, $pValue = null, $pDataType = PHPExcel_Cell_DataType::TYPE_STRING, $coordinate)
{
  $columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
  if(!isset($coordinate[0])) $coordinate   = "{$columnLetter}{$pRow}";

  if (!$this->_cellCollection->isDataSet($coordinate))
  {
    #$this->_cellCollection->addCacheData($coordinate, $cell = new PHPExcel_Cell($columnLetter, $pRow, $pValue, $pDataType, $this));// deprecated
    $this->_cellCollection->addCacheData($coordinate, $cell = new PHPExcel_Cell($pValue, $pDataType, $this));
    $this->_cellCollectionIsSorted = false;

    if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < $pColumn)
      $this->_cachedHighestColumn = $columnLetter;

      if ($this->_cachedHighestRow < $pRow)
        $this->_cachedHighestRow = $pRow;
      }
      else
      {
      $cell = $this->_cellCollection->getCacheData($coordinate);
        $cell->setValueExplicit($pValue, $pDataType);
    }

    return $cell;
}
With 753.454 Cells, Styles and Tooltips etc. it will be taken 1.000 Seconds, befor it was 1.700 Seconds (around 30 Minutes).
Great!
Jun 5, 2013 at 9:05 AM
Try changes described in this issue: https://phpexcel.codeplex.com/workitem/19816
With them speed of setCellValueExplicitByColumnAndRow() is almost same as mine function.