Load XLS files into Magento modules using PHP-ExcelReader class

Recently I had a small problem and it turned out that it wasn’t small as a initially thought. I had to read XLS (Microsoft Excel document) into PHP. After I spent almost 2 hours browsing through internet, I found myself most satisfied with open source project “PHP-ExcelReader class“. It has everything I needed (multiple sheets support and it could read data from both Excel 2000 and 2007). On the other hand, Magento doesn’t support XLS, and I decided to write a small method that you can implement in any Magento class.

So, here it goes:

public function loadXML($path_to_XML)
{
$include_path = dirname(__FILE__);
$path_to_PHP_ExcelReader = $include_path."/read_xls/Excel/reader.php";
 
require_once $path_to_PHP_ExcelReader;
 
// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
 
// Set output Encoding.
$data->setOutputEncoding('utf-8');
/* if you want you can change 'iconv' to mb_convert_encoding:*/
 
$data->setUTFEncoder('mb');
 
/*
* By default rows & cols indeces start with 1
* For change initial index use:
*/
$index = 0;
 
$data->setRowColOffset($index);
/* setDefaultFormat - set format for columns with unknown formatting*/
 
$data->setDefaultFormat('%.2f');
 
/* setColumnFormat - set format for column (apply only to number fields)*/
$data->setColumnFormat(4, '%.3f');
/*Do the actual reading of file*/
 
$data->read($path_to_XML);
return $data;
}

And here’s how you can access your Excel file data as PHP array after call on method:

$data->sheets[0]['numRows']
//count rows
 
$data->sheets[0]['numCols']
//count columns
 
$data->sheets[0]['cells'][$i][$j]
//data from $i-row $j-column
 
$data->sheets[0]['cellsInfo'][$i][$j]
//extended info about cell
 
$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
//if 'type' == "unknown" - use 'raw' value, because  cell contain value with format '0.00';
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value
//if cell without format
 
$data->sheets[0]['cellsInfo'][$i][$j]['colspan']
//gets colspan value
 
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan']
//gets rowspan value

All you need to do is extract this archive to same directory that contains your class that need XLS support. And a small notice – I did some small changes on original PHP-ExcelReader class because it caused an error on method call in that case.

I hope it’ll help someone, bye.