[FIXED] How to get excel sheet cell dataType in PHP using PHPExcel_IOFactory

Issue

Here I get all cell values from excel sheet(test.xlsx) using PHPExcel libraries. But I'm not getting the value of dataType. I need excel sheet data with this dataType(String or Number Or Date).

My Code:

require_once 'Classes/PHPExcel.php';
$file_path = "test.xlsx";
try {
    $objPHPExcel = PHPExcel_IOFactory::load($file_path);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

print_r($sheetData);

Advise Please!

Solution

You can retrieve the datatype for a cell by calling the getDataType() method for a cell:

$objPHPExcel->getActiveSheet()
    ->getCell('A1')
    ->getDataType();

This can return values of:

- TYPE_STRING2  = 'str';          // string
- TYPE_STRING   = 's';            // string
- TYPE_FORMULA  = 'f';            // formula
- TYPE_NUMERIC  = 'n';            // number
- TYPE_BOOL     = 'b';            // boolean
- TYPE_NULL     = 'null';         // null
- TYPE_INLINE   = 'inlineStr';    // rich text string
- TYPE_ERROR    = 'e';            // error

The constants for these datatypes are defined in the PHPExcel_Cell_DataType class

---

Date doesn't have a specific datatype, it's simply a number as far as Excel is concerned: you need to look at the number format mask for the cell to differentiate between a float and a date.

PHPExcel provides a method for this

PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell('A1'));

Answered By - Mark Baker

Answer Checked By - Katrina (PHPFixing Volunteer)