Universal cell reading in PHPExcel

Hi, Habr!
I often need to import Excel documents using PHP.
For this, I use the PHPExcel library, which today is a very convenient tool.
But there are a few “slippery” moments related to reading data from cells, which I want to tell habrachitelemi, as well as to make out the function that solves these problems.

1. Addressing cells

You can address cells when working with excel in different ways:

• column and row as row: “A1”
• column letter, row number: ("A", 1)
• column and row number: (1, 1)

The first method is more convenient for static cells, and the second and third for cycles.

But in PHPExcel there is no universal function to get a cell in any of these ways, there are only separate functions. Well, we correct this omission:

public function getCellValue($cellOrCol, $row = null) { //column set by index if(is_numeric($cellOrCol)) { $cell = $this->activeSheet->getCellByColumnAndRow($cellOrCol, $row); } else { $lastChar = substr($cellOrCol, -1, 1); if(!is_numeric($lastChar)) { //column contains only letter, eg "A" $cellOrCol .= $row; } $cell = $this->activeSheet->getCell($cellOrCol); } $val = $cell->getValue(); return $val; } 

At once I will make a reservation that in the examples of code given by me there are references to $ this , since these are the methods of my wrapper class over PHPExcel. In this piece, all three ways of obtaining a cell object are implemented.

2. Combining cells

When reading merged cells, PHPExcel returns an empty value for all but the first .
Those. for the figure below, the values ​​of B3 and C3 will be empty lines:
')


I have always been uncomfortable with this behavior.
It is much more convenient (and more logical!) For any merged cell to return the total value of “mergedvalue” for them.
To do this, when requesting a value, you need to go through all the combined ranges of the sheet, and if the specified cell falls within the range, then return the first:

 $this->mergedCellsRange = $this->activeSheet->getMergeCells(); foreach($this->mergedCellsRange as $currMergedRange) { if($cell->isInRange($currMergedRange)) { $currMergedCellsArray = PHPExcel_Cell::splitRange($currMergedRange); $cell = $this->activeSheet->getCell($currMergedCellsArray[0][0]); break; } } 

3. Dates

As you know, Excel stores dates as the number of days from January 1, 1900. Therefore, when reading cell B2 in the screenshot above, we get a useless 41044. But there is also good news - PHPExcel has a convenient function PHPExcel_Shared_Date :: ExcelToPHP () , which turns the date into php format.
It remains only to apply this function at the right time:

 $val = $cell->getValue(); if(PHPExcel_Shared_Date::isDateTime($cell)) { $val = date($format, PHPExcel_Shared_Date::ExcelToPHP($val)); } 

4. Formulas

In most cases, the standard $ cell-> getValue () function correctly processes the formulas and returns the calculated value. But there are situations when a formula refers to a nonexistent sheet or other file that is locally stored by the person who sent you the excel document. Then getValue () will return an error, although visually in Excel you can see the correct value if there was no recalculation of the sheet. The fact is that Excel saves oldCalculatedValue , which is used if you do not recalculate the sheet. In the picture above, I showed this in cell B4 - it displays the old value, although the link in it is not working.
PHPExcel, fortunately, also knows how to store the old value of a formula. This is convenient to use when getValue () could not work and returned not the value, but the formula itself (the first "=" character):

 $val = $cell->getValue(); if((substr($val,0,1) === '=' ) && (strlen($val) > 1)){ $val = $cell->getOldCalculatedValue(); } 

Result

As a result, we obtained a function that allows us to universally read cell values:

  public function getCellValue($cellOrCol, $row = null, $format = 'dmY') { //column set by index if(is_numeric($cellOrCol)) { $cell = $this->activeSheet->getCellByColumnAndRow($cellOrCol, $row); } else { $lastChar = substr($cellOrCol, -1, 1); if(!is_numeric($lastChar)) { //column contains only letter, eg "A" $cellOrCol .= $row; } $cell = $this->activeSheet->getCell($cellOrCol); } //try to find current coordinate in all merged cells ranges //if find -> get value from head cell foreach($this->mergedCellsRange as $currMergedRange){ if($cell->isInRange($currMergedRange)) { $currMergedCellsArray = PHPExcel_Cell::splitRange($currMergedRange); $cell = $this->activeSheet->getCell($currMergedCellsArray[0][0]); break; } } //simple value $val = $cell->getValue(); //date if(PHPExcel_Shared_Date::isDateTime($cell)) { $val = date($format, PHPExcel_Shared_Date::ExcelToPHP($val)); } //for incorrect formulas take old value if((substr($val,0,1) === '=' ) && (strlen($val) > 1)){ $val = $cell->getOldCalculatedValue(); } return $val; } 

Test

To check, we can read the Excel from the screenshot in two ways: standard getValue ( # 1 ) and using the above function ( # 2 ):



Test # 1:

Test # 2:

As you can see, in the second case, everything was considered correct.

A spoon of tar

It is important to note that the use of clauses 2, 3 and 4 only works in ReadDataOnly = false mode. This is the default PHPExcel mode when it reads all the meta information about the book. Suitable for standard small documents, such as invoices, delivery notes, etc.
Enabling ReadDataOnly = true may be required for bulky files when only cell values are needed. In my practice, such files contain formatted tables and there is no such functionality needed.

Setting the read mode in PHPExcel is done like this:

 $objReader = PHPExcel_IOFactory::createReaderForFile($filename); $objReader->setReadDataOnly(false); $this->PHPExcel = $objReader->load($filename); 

Thanks for attention!