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.
/* if you want you can change 'iconv' to mb_convert_encoding:*/
* By default rows & cols indeces start with 1
* For change initial index use:
$index = 0;
/* setDefaultFormat - set format for columns with unknown formatting*/
/* setColumnFormat - set format for column (apply only to number fields)*/
$data->setColumnFormat(4, '%.3f');
/*Do the actual reading of file*/
return $data;

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

//count rows
//count columns
//data from $i-row $j-column
//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
//gets colspan value
//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.

You made it all the way down here so you must have enjoyed this post! You may also like:

How I learned to stop worrying and love 3rd party modules for Magento Luka Rajcevic
, | 2

How I learned to stop worrying and love 3rd party modules for Magento

Changing default category sort direction in Magento Marin Grizelj
Marin Grizelj, | 1

Changing default category sort direction in Magento

Using Magento >= 1.6 data install scripts Thomas Spigel
Thomas Spigel, | 6

Using Magento >= 1.6 data install scripts


  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
    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. 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)

  5. 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>.