Apr 28, 2018

Huge data Excel download using PHP

Ah! It was always a problem to download huge data in excel format. Tried the best library available 'PHPExcel' but the issue persists with memory limit and time taken to generate and download.

But here is the promising solution found after spending ages searching on Google. You can find it here. The author himself says "Never run out of memory with PHPExcel again".

You need to download it and include xlsxwriter.class.php where you want to fetch the data from DB and through it on to browser or save to a particular location.

Let's get started.
Below is the logic to generate the file and store in the current directory. If you want to store is at the desired location, specify the location for the method writeToFile.

include_once("xlsxwriter.class.php");
$writer = new XLSXWriter();
$data = array(
    array('year','month','amount'),
    array('2003','1','220'),
    array('2003','2','153.5'),
);

$writer = new XLSXWriter();
$writer->writeSheet($data);
$writer->writeToFile('output.xlsx');


In case if you want to download it on the fly use the below logic.
include_once("xlsxwriter.class.php");
$writer = new XLSXWriter();
$data = array(
    array('year','month','amount'),
    array('2003','1','220'),
    array('2003','2','153.5'),
);
$filename = 'output.xlsx';

$writer = new XLSXWriter();
$writer->writeSheet($data);

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0');

$writer->writeToStdOut();

Through this library, you can even set the column type. Please visit the official URL for more information.

No comments:

Post a Comment

Want to tell something about this post. Please feel free to write...