PHPExcel

PHPExcel is a PHP library to handle both read/write from/to an excel file. Below is a short tutorial to cover reading data from an excel file.

$filename = 'your_file.xls';

require_once 'PHPExcel/Classes/PHPExcel.php';
	
// Create new PHPExcel object
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($filename);

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
		
  $sheet_name = $worksheet->getTitle();

  foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if empty
    foreach ($cellIterator as $cell) {
      if (!is_null($cell)) {
        $cell_coord = $cell->getCoordinate();
        $cell_val = $cell->getCalculatedValue();
        if (preg_match('/^([a-zA-Z]+)([0-9]+)$/', $cell_coord, $matches)){
          $col = $matches[1];
          $row = (int) $matches[2];
          do_something_with_this_cell($sheet_name, $col, $row, $cell_val);
        }
      }
    }
  }

}		

For do_something_with_this_cell($sheet_name, $col, $row, $cell_val), you can either store the value into DB for later analysis, or look up a mapping array and determine what that cell carries.

Sometimes you may need to deal with cells that carry dates. If you simply make the getCalculatedValue() call you will get some strange integer back but not the actual date string. The reason for that is, the date object in excel is actually masked by a date formatter. In order to get the desired date string, do the following:

foreach ($cellIterator as $cell) {
  if (!is_null($cell)) {
    $cell_coord = $cell->getCoordinate();
    $cell_val = $cell->getCalculatedValue();
    if (preg_match('/^([a-zA-Z]+)([0-9]+)$/', $cell_coord, $matches)){
      $col = $matches[1];
      $row = (int) $matches[2];

      // check for date object
      if(PHPExcel_Shared_Date::isDateTime($cell)){
        $cell_val = PHPExcel_Style_NumberFormat::toFormattedString($cell_val, "YYYY-MM-DD");
      }

      do_something_with_this_cell($sheet_name, $col, $row, $cell_val);
    }
  }
}

One thought on “PHPExcel

  1. Richard Sweeney says:

    Awesome. Was looking for some decent documentation for this. Works great.

    Thanks for taking the time to post this 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s