Create lightweight excel spreadsheet using PHP library

Last updated on July 31st, 2016 at 11:16 am

Excel is used to store large amount of information in the form of a spreadsheet. Which is easy to manage accounts information and other information. In todays time we save large amount of information online in database but some time we need to convert that data into excel spreadsheet and provide to user. In this post i will explain you how to create excel file online using PHP_XLSXWriter library available on github. You download this from this link : https://github.com/mk-j/PHP_XLSXWriter

Download

PHP_XLSXWriter use less memory to create excel file.

Create simple excel file

To create a simple excel file we need to add this library into our file and need to call some function and we done.

<?php
include_once("xlsxwriter.class.php");

$header = array(
    'Name'=>'string',
    'age'=>'string',
    'developer'=>'string',  
);
$data1 = array(
    array('Aneh Thakur','22','PHP, Android'),
    array('Amit Rana','22','PHP, iPhone'),
	
);

$writer = new XLSXWriter(); // Initialize XLSXWriter() class
$writer->setAuthor('Aneh Thakur'); // Set author name if you want
$writer->writeSheet($data1,'sheet1',$header); // write your data into excel sheet
$writer->writeToFile('example.xlsx'); // Name the file you want to save as

?>

If you want to add data into multiple sheet you simple need to recall writeSheet and add new variable and add them into function as shown below.

<?php
include_once("xlsxwriter.class.php");

$header = array(
    'Name'=>'string',
    'age'=>'string',
    'developer'=>'string',  
);
$data1 = array(
    array('Aneh Thakur','22','PHP, Android'),
    array('Amit Rana','22','PHP, iPhone'),
	
);
$header2 = array(
    'Status'=>'string',
);

$data2 = array(
    array('Love think code!!!'),
);

$writer = new XLSXWriter();
$writer->setAuthor('Aneh Thakur');
$writer->writeSheet($data1,'sheet1',$header); // Excel sheet 1
$writer->writeSheet($data2,'sheet2',$header2); // Excel sheet 2
$writer->writeToFile('example.xlsx');

?>

All these excel file are save in your server directory. If you want to make it download you need to add some header function in your file

<?php
include_once("xlsxwriter.class.php");

$filename = "sampleFilename.xlsx";
header('Content-disposition: attachment; filename="'.XLSXWriter::sanitize_filename($filename).'"');
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
header('Pragma: public');

$header = array(
    'Name'=>'string',
    'age'=>'string',
    'developer'=>'string',  
);
$data1 = array(
    array('Aneh Thakur','22','PHP, Android'),
    array('Amit Rana','22','PHP, iPhone'),
	
);
$header2 = array(
    'Status'=>'string',
);

$data2 = array(
    array('Love think code!!!'),
);

$writer = new XLSXWriter();
$writer->setAuthor('Aneh Thakur');
$writer->writeSheet($data1,'sheet1',$header);
$writer->writeSheet($data2,'sheet2',$header2);
$writer->writeToStdOut(); 

?>