PHPExcel is pretty much cool tool which provide classes to generate spreadsheets or excel sheets in your PHP projects. Unfortunately Magneto doesn’t support for .xls or .xlsx file types for import and export. It only supports .csv format, but working with csv files is not an easy job as it seems.
You have to keep it in a properly formatted file structure, otherwise you may lose data. In case of product import you can not add “\n“(line-end) or “,” (comma) in values like description, short description or name. Some time it breaks if you didn’t kept a long string within “” (quotes).
To overcome this, many organisations as well as Magneto plugins adopted this awesome library to import/export in Magento backend.
This library provide almost all methods to work with a spreadsheet.
It is very easy to add PHPExcel library into Magento system like other libraries. It is very easy to install. You can download the files and extract it to magento folder or by using pear (For more information regarding PEAR link visit PHPExcel).
Install PHPExcel to Magento
- Download PHPExcel copy from the site.
- Extract the contents of Classes folder into magento-root/lib folder.
- Now create a script file inside magento root folder. e.g. magento-root/test.php. Add below code to create a new Excel file using library.
/// Create a new Excel file from an array using PHPExcel
$objPHPExcel = new PHPExcel();
$objPHPExcel -> getActiveSheet() -> fromArray($fileData, null, 'A1');
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$targetPath = Mage::getBaseDir() . DS ;
$objWriter -> save('new.xlsx');
This will create a new excel sheet inside Magento folder. Similarly library methods and functions can be used in Model or Controller of your custom module.
Piece of cake! Right ?
You can use any Class methods and objects like above to create or read excel sheet in PHP using PHP Excel Library. Enjoy Coding 🙂