Ignore empty cells PHPExcel

If your problem is in getting empty columns that go after real data, and you would like to avoid these, you could do something like this: $maxCell = $sheet->getHighestRowAndColumn(); $data = $sheet->rangeToArray(‘A1:’ . $maxCell[‘column’] . $maxCell[‘row’]); This will return array representing only the area containing real data.

PHPExcel download using ajax call

PHP $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); ob_start(); $objWriter->save(“php://output”); $xlsData = ob_get_contents(); ob_end_clean(); $response = array( ‘op’ => ‘ok’, ‘file’ => “data:application/vnd.ms-excel;base64,”.base64_encode($xlsData) ); die(json_encode($response)); JS $.ajax({ type:’POST’, url:”MY_URL.php”, data: {}, dataType:’json’ }).done(function(data){ var $a = $(“<a>”); $a.attr(“href”,data.file); $(“body”).append($a); $a.attr(“download”,”file.xls”); $a[0].click(); $a.remove(); });

PHPExcel class not found in Zend Autoloader

Create an autoloader for PHPExcel and add it to the Zend autoloader stack. In library/My/Loader/Autoloader/PHPExcel.php: class My_Loader_Autoloader_PHPExcel implements Zend_Loader_Autoloader_Interface { public function autoload($class) { if (‘PHPExcel’ != $class){ return false; } require_once ‘PHPExcel.php’; return $class; } } And in application/configs/application.ini: autoloadernamespaces[] = “My_” Then, in application/Bootstrap.php: protected function _initAutoloading() { $autoloader = Zend_Loader_Autoloader::getInstance(); $autoloader->pushAutoloader(new My_Loader_Autoloader_PHPExcel()); … Read more

PHPExcel auto size column width

If a column is set to AutoSize, PHPExcel attempts to calculate the column width based on the calculated value of the column (so on the result of any formulae), and any additional characters added by format masks such as thousand separators. By default, this is an estimated width: a more accurate calculation method is available, … Read more

How to read large worksheets from large Excel files (27MB+) with PHPExcel?

It is possible to read a worksheet in “chunks” using Read Filters, although I can make no guarantees about efficiency. $inputFileType=”Excel5″; $inputFileName=”./sampleData/example2.xls”; /** Define a Read Filter class implementing PHPExcel_Reader_IReadFilter */ class chunkReadFilter implements PHPExcel_Reader_IReadFilter { private $_startRow = 0; private $_endRow = 0; /** Set the list of rows that we want to read … Read more

phpexcel to download

Instead of saving it to a file, save it to php://output­Docs: $objWriter->save(‘php://output’); This will send it AS-IS to the browser. You want to add some headers­Docs first, like it’s common with file downloads, so the browser knows which type that file is and how it should be named (the filename): // We’ll be outputting an … Read more

How to use phpexcel to read data and insert into database?

Using the PHPExcel library to read an Excel file and transfer the data into a database // Include PHPExcel_IOFactory include ‘PHPExcel/IOFactory.php’; $inputFileName=”./sampleData/example1.xls”; // Read your Excel workbook try { $inputFileType = PHPExcel_IOFactory::identify($inputFileName); $objReader = PHPExcel_IOFactory::createReader($inputFileType); $objPHPExcel = $objReader->load($inputFileName); } catch(Exception $e) { die(‘Error loading file “‘.pathinfo($inputFileName,PATHINFO_BASENAME).'”: ‘.$e->getMessage()); } // Get worksheet dimensions $sheet = $objPHPExcel->getSheet(0); … Read more

PHPExcel runs out of 256, 512 and also 1024MB of RAM

There’s plenty been written about the memory usage of PHPExcel on the PHPExcel forum; so reading through some of those previous discussions might give you a few ideas. PHPExcel holds an “in memory” representation of a spreadsheet, and is susceptible to PHP memory limitations. The physical size of the file is largely irrelevant… it’s much … Read more

Alternative for PHP_excel

For Writing Excel PEAR’s PHP_Excel_Writer (xls only) php_writeexcel from Bettina Attack (xls only) XLS File Generator commercial and xls only Excel Writer for PHP from Sourceforge (spreadsheetML only) Ilia Alshanetsky’s Excel extension now on github (xls and xlsx, and requires commercial libXL component) PHP’s COM extension (requires a COM enabled spreadsheet program such as MS … Read more