CODEWITHSUNDEEP
phpphpexcel
Duleep
Duleep

Reputation: 587

Read xls file using PHPExcel lib and get cell type

I read XLS file using PHPExcel. There is cells date column. that data column has two type of cells (Date and Text type cells).

I need to get view value of the cells in the XLS file. So before get value of the cell i want to decided whether that cell is Date cell or Text cell.

How can i get view value of the XLS file cells. Is There method like getCellType(), getViewValueOfCell() or any other method for get view value of cell?

Note: $cell->getType is not a real method in PHPExcel. this pseudo method.

please suggest best logic/method for bellow cell read.

 // read view value form date column
 if($cell->getType == 'Date'){
  $array_data[$rowIndex][$cell->getColumn()] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'YYYY-MM-DD'); 
 }
 else if($cell->getType == 'Text'){
  $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
 }

this my all XLS read function

       $objReader = new PHPExcel_Reader_Excel5();
   $objReader->setReadDataOnly(true);
   $objPHPExcel = $objReader->load($file_path);
   $rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();

   $array_data = array();
   foreach ($rowIterator as $row) {
      $cellIterator = $row->getCellIterator();
      $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
      // if(1 == $row->getRowIndex ()) continue;//skip first row
      $rowIndex = $row->getRowIndex();
      $array_data[$rowIndex] = array('A' => '', 'B' => '', 'C' => '', 'D' => '');

      foreach ($cellIterator as $cell) {
         if ('A' == $cell->getColumn()) {
            $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
         } else if ('B' == $cell->getColumn()) {
            $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
         } else if ('C' == $cell->getColumn()) {
            // read view value form date column
             if($cell->getType == 'Date'){
              $array_data[$rowIndex][$cell->getColumn()] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'YYYY-MM-DD'); 
             }
             else if($cell->getType == 'Text'){
              $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
             }
         } else if ('D' == $cell->getColumn()) {
            $array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
         }
      }
   }

Upvotes: 1

Views: 3722

Answers (1)

Mark Baker
Mark Baker

Reputation: 212402

The cell's getDataType() will return the datatype of the value contained in a cell

Valid datatypes are defined in PHPExcel_Cell_DataType:

const TYPE_STRING2  = 'str';
const TYPE_STRING   = 's';
const TYPE_FORMULA  = 'f';
const TYPE_NUMERIC  = 'n';
const TYPE_BOOL     = 'b';
const TYPE_NULL     = 'null';
const TYPE_INLINE   = 'inlineStr';
const TYPE_ERROR    = 'e';

Note that there is no datatype for dates or times: dates/times are a datatype of float in MS Excel.

To identify whether a cell contains a date/time value you need to check the numberformat mask. To simplify this, the following methods are available in the PHPExcel_Shared_Date class to identify a numberformat mask as one that relates to a date/time

isDateTime()

/**
 * Is a given cell a date/time?
 *
 * @param    PHPExcel_Cell  $pCell
 * @return   boolean
 */

isDateTimeFormat()

/**
 * Is a given number format a date/time?
 *
 * @param    PHPExcel_Style_NumberFormat    $pFormat
 * @return   boolean
 */

isDateTimeFormatCode()

/**
 * Is a given number format code a date/time?
 *
 * @param    string $pFormatCode
 * @return   boolean
 */

The API documentation should have identified these for you

Upvotes: 1

Related Questions