Load XLS files into Magento modules using PHP-ExcelReader class

Featured Image

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.


8 comments

  1. Hi,
    I’ve get an error like this:

    filename ...  is not readable

    Execution stop in oleread.inc especially here:

    if (substr($this->data, 0, 8) != IDENTIFIER_OLE) {

    I've debug and found that substr($this->data, 0, 8) = PK
    IDENTIFIER_OLE = ��ࡱ�
    It seem a kind of encoding problem but I don't know what to do actually.

  2. Hi Inchoo guys, would love to partner with you guys we have developed a platform to automate xls into magento when it needs to be updated regularly. Be keen to reach out to talk.

  3. Thankyou!! Your post is really helpful, but I am stuck at how can I show date in proper format? I tried setting difference date formats for that column but still unable to get the date in proper format.

  4. How can i ues this files in magento
    where i have to add file and that class in magento 1.8

  5. I get an ‘Zend_Log class not found’ error when trying to instantiate the Spreadsheet_Excel_Reader class of the latest version of ExcelReader (Version 2.21)

  6. I get an error when trying to open the zip file, stating that it is invalid. Would it be possible for you to upload a new version of the files with your changes? Or, if not, could you elaborate on what you had to change to get the reader working with Magento?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.