how could i possibly setting the reader to format certain data like data type of date(Microsoft Excel reader)

Topics: Developer Forum
Oct 5, 2012 at 1:01 PM

is there any way to change the format date type from Reader Object  into my desire like 'dd/mm/yyyy' or 'dd-mm-yyyy' . i mention this cause i try to logging the function of Worksheet->rangeToArray()  , i added my line

 

	public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
		// Returnvalue
		$returnValue = array();

		//	Identify the range that we need to extract from the worksheet
		list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange);
		$minCol = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] -1);
		$minRow = $rangeStart[1];
		$maxCol = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0] -1);
		$maxRow = $rangeEnd[1];

		$maxCol++;

		// Loop through rows
		$r = -1;
		for ($row = $minRow; $row <= $maxRow; ++$row) {
			$rRef = ($returnCellRef) ? $row : ++$r;
			$c = -1;
			// Loop through columns in the current row
			for ($col = $minCol; $col != $maxCol; ++$col) {
				$cRef = ($returnCellRef) ? $col : ++$c;
				//	Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
				//		so we test and retrieve directly against _cellCollection
				if ($this->_cellCollection->isDataSet($col.$row)) {
					// Cell exists
					$cell = $this->_cellCollection->getCacheData($col.$row);
					if ($cell->getValue() !== null) {
						if ($cell->getValue() instanceof PHPExcel_RichText) {
							$returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
						} else {
							if ($calculateFormulas) {
								$returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
							} else {
								$returnValue[$rRef][$cRef] = $cell->getValue();
							}
						}

						if ($formatData) {
						    
							$style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
                                                         //this is my line
							log_message('info', $returnValue[$rRef][$cRef].' format_code:'.$style->getNumberFormat()->getFormatCode());
							$returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style->getNumberFormat()->getFormatCode());
						}
					} else {
						// Cell holds a NULL
						$returnValue[$rRef][$cRef] = $nullValue;
					}
				} else {
					// Cell doesn't exist
					$returnValue[$rRef][$cRef] = $nullValue;
				}
			}
		}

		// Return
		return $returnValue;
	}

 and i always get lines like this:

NURHAYATI DG NURUNG format_code:General
INFO  - 2012-10-05 20:42:46 --> SINJAI format_code:General
INFO  - 2012-10-05 20:42:46 --> 26115 format_code:mm-dd-yy
INFO  - 2012-10-05 20:42:46 --> 38 format_code:General

 

let us focus in the line of INFO  - 2012-10-05 20:42:46 --> 26115 format_code:mm-dd-yy , i know it was MS Office Excel serial number of date.

the mention are, how could i possibly hook the Reader classes object to format date data type like the example format_code mm-dd-yy , i want to change it something like dd-mm-yyyy , so i can get my desire result, is there any way except hacking the core classes?

 

Oct 5, 2012 at 8:18 PM
Edited Oct 5, 2012 at 8:19 PM

Instead of getValue() use getFormattedValue().

- Christopher Mullins

Nov 23, 2012 at 4:35 AM

I am also looking for the best approach how to achieve this. I have tried to use getFormattedValue()  but to no avail. Does anyone know how exactly we can set the desired date format when reading excel files?

Nov 23, 2012 at 5:32 AM

I don't know if this is the right approach, but what I did is tweaked a little bit on the rangeToArray() function:

	public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
		// Returnvalue
		$returnValue = array();

		//	Identify the range that we need to extract from the worksheet
		list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange);
		$minCol = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] -1);
		$minRow = $rangeStart[1];
		$maxCol = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0] -1);
		$maxRow = $rangeEnd[1];

		$maxCol++;

		// Loop through rows
		$r = -1;
		for ($row = $minRow; $row <= $maxRow; ++$row) {
			$rRef = ($returnCellRef) ? $row : ++$r;
			$c = -1;
			// Loop through columns in the current row
			for ($col = $minCol; $col != $maxCol; ++$col) {
				$cRef = ($returnCellRef) ? $col : ++$c;
				//	Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
				//		so we test and retrieve directly against _cellCollection
				if ($this->_cellCollection->isDataSet($col.$row)) {
					// Cell exists
					$cell = $this->_cellCollection->getCacheData($col.$row);
					// if ($cell->getValue() !== null) {
					if ($cell->getFormattedValue() !== null) {
						// if ($cell->getValue() instanceof PHPExcel_RichText) {
						if ($cell->getFormattedValue() instanceof PHPExcel_RichText) {
							// $returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
							$returnValue[$rRef][$cRef] = $cell->getFormattedValue()->getPlainText();
						} else {
							if ($calculateFormulas) {
								$returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
							} else {
								// $returnValue[$rRef][$cRef] = $cell->getValue();
								$returnValue[$rRef][$cRef] = $cell->getFormattedValue();
							}
						}

						if ($formatData) {
							$style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
							/* Here is the line I changed */
							if(PHPExcel_Shared_Date::isDateTime($cell)) {
								$returnValue[$rRef][$cRef] = date('Y-m-d', strtotime($returnValue[$rRef][$cRef]));
							}else{
								$returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style->getNumberFormat()->getFormatCode());
							}
							/* End */
						}
					} else {
						// Cell holds a NULL
						$returnValue[$rRef][$cRef] = $nullValue;
					}
				} else {
					// Cell doesn't exist
					$returnValue[$rRef][$cRef] = $nullValue;
				}
			}
		}
		//print_r($returnValue);
		// Return
		return $returnValue;
	}

 

Hope this would help others.

Nov 23, 2012 at 7:09 AM
Edited Nov 23, 2012 at 7:09 AM

After a series of debugging I come up with the following:

	public function rangeToArray($pRange = 'A1', $nullValue = null, $calculateFormulas = true, $formatData = true, $returnCellRef = false) {
		// Returnvalue
		$returnValue = array();

		//	Identify the range that we need to extract from the worksheet
		list($rangeStart, $rangeEnd) = PHPExcel_Cell::rangeBoundaries($pRange);
		$minCol = PHPExcel_Cell::stringFromColumnIndex($rangeStart[0] -1);
		$minRow = $rangeStart[1];
		$maxCol = PHPExcel_Cell::stringFromColumnIndex($rangeEnd[0] -1);
		$maxRow = $rangeEnd[1];

		$maxCol++;

		// Loop through rows
		$r = -1; $k=0;
		for ($row = $minRow; $row <= $maxRow; ++$row) {
			$rRef = ($returnCellRef) ? $row : ++$r;
			$c = -1;
							$z=0;
			// Loop through columns in the current row
			for ($col = $minCol; $col != $maxCol; ++$col) {
				$cRef = ($returnCellRef) ? $col : ++$c;
				//	Using getCell() will create a new cell if it doesn't already exist. We don't want that to happen
				//		so we test and retrieve directly against _cellCollection
				if ($this->_cellCollection->isDataSet($col.$row)) {
					// Cell exists
					$cell = $this->_cellCollection->getCacheData($col.$row);
					
					if(PHPExcel_Shared_Date::isDateTime($cell)) {
						$this->_parent->getCellXfByIndex($cell->getXfIndex())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
					}
					if ($cell->getValue() !== null) {
						if ($cell->getValue() instanceof PHPExcel_RichText) {
							$returnValue[$rRef][$cRef] = $cell->getValue()->getPlainText();
						} else {
							if ($calculateFormulas) {
								$returnValue[$rRef][$cRef] = $cell->getCalculatedValue();
							} else {
								$returnValue[$rRef][$cRef] = $cell->getValue();
							}
						}

						if ($formatData) {
							$style = $this->_parent->getCellXfByIndex($cell->getXfIndex());
							/* Here is the line I changed */
								$returnValue[$rRef][$cRef] = PHPExcel_Style_NumberFormat::toFormattedString($returnValue[$rRef][$cRef], $style->getNumberFormat()->getFormatCode());
							/* End */
						}
					} else {
						// Cell holds a NULL
						$returnValue[$rRef][$cRef] = $nullValue;
					}
				} else {
					// Cell doesn't exist
					$returnValue[$rRef][$cRef] = $nullValue;
				}
			}
		}
		//print_r($returnValue);
		// Return
		return $returnValue;
	}

cheers!