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.
9 comments
Exception #0 (Exception): Deprecated Functionality: Methods with the same name as their class will not be constructors in a future version of PHP; Spreadsheet_Excel_Reader has a deprecated constructor in
Hi,
I’ve get an error like this:
Execution stop in oleread.inc especially here:
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.
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.
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.
How can i ues this files in magento
where i have to add file and that class in magento 1.8
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)
Thank you,
This is really helpful for me.
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?
Thanks=)