PHP Export to Excel Example

It can become neccessary to export data to a browser in excel format or just create an excel document from data received from a database or any other source. In this example we will create a (PHP) web app that exports data to excel.

For this example we will use:

• A computer with PHP >= 5.5 installed
• notepad++
• Microsoft Excel to test the examples
• PHPExcel

Want to be a PHP master?

Subscribe to our newsletter and download the PHP programming Cookbook right now!

In order to get you familiarized with PHP development, we have compiled a comprehensive guide about all major aspects of PHP programming. The perfect tool to boost your WEB development career. Besides reading them online you may download the eBook in PDF format!

Download Now

Thank you!

We will contact you soon.

1. PHPExcel

According to the website, PhpExcel is a Project providing a set of classes for the PHP programming language, which allows you to write to, and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML, … This project is built around Microsoft’s OpenXML standard and PHP.

PHPExcel allows you to create an excel file and add spreadsheet metadata to your file, such as title, description, author, last modified by, category, subject e.t.c. We are going to use PHPExcel to create our Excel documents in this example.
Requirements for PHPExcel are

• PHP version 5.2.0 or higher
• PHP extension php_zip enabled
• PHP extension php_xml enabled
• PHP extension php_gd2 enabled (if not compiled in)

To use PHP Excel to create an Excel file on the fly,

1. You have to download it from github
2. Extract the contents to a suitable place on your computer.
3. Copy the classes folder in the extracted folder and put it in the location where your application resides. So your file structure resembles the one below:

   \var\www\Classes
   \var\www\your_app
   

index.php

 
<?php

/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';

// we Create a new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set the excel document properties or metadata
$objPHPExcel->getProperties()->setCreator(" john mark simeon ")
							 ->setLastModifiedBy("john mark simeon ")
							 ->setTitle("john mark simeon phpexcel document ")
							 ->setSubject("john mark simeon phpexcel document")
							 ->setDescription(" This is a test document for webcodegeeks.")
							 ->setKeywords("office PHPExcel php")
							 ->setCategory(" Test result file ");


// Add some data to the excel document
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B2', 'world!')
            ->setCellValue('C1', 'Hello')
            ->setCellValue('D2', 'world!');

// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A4', 'Miscellaneous glyphs')
            ->setCellValue('A5', 'test it');
$objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setWrapText(true);

$objPHPExcel->getActiveSheet()->setCellValue('A8',"Hello\nWorld");
$objPHPExcel->getActiveSheet()->getRowDimension(8)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setWrapText(true);


$value = "-ValueA\n-Value B\n-Value C";
$objPHPExcel->getActiveSheet()->setCellValue('A10', $value);
$objPHPExcel->getActiveSheet()->getRowDimension(10)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getStyle('A10')->getAlignment()->setWrapText(true);// we wrap the text in this column so it wouldnt cross it boundary, set this to false and see what happens
$objPHPExcel->getActiveSheet()->getStyle('A10')->setQuotePrefix(true);



// Rename worksheet
echo date('H:i:s') , " Rename worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel 2007 file
echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
?>

In the code above we use PHPExcel to create an excel document. Make sure to exactly follow the instructions above on how to get PHPExcel working.
In line 18 we create a PHPExcel object. In line 21 we set the document properties and we start adding data to the excel document in line 30.
The code above teaches you how to get started with PHPExcel. You should dive into the documentation to fully learn all the features of PHPExcel.

index2.php

 
<?php



<?php
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE); 
ini_set('display_startup_errors', TRUE); 
date_default_timezone_set('Europe/London');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';


// Create new PHPExcel object
echo date('H:i:s') , " Create new PHPExcel object" , EOL;
$objPHPExcel = new PHPExcel();

// Set document properties
echo date('H:i:s') , " Set document properties" , EOL;
// Set the excel document properties or metadata
$objPHPExcel->getProperties()->setCreator(" john mark simeon ")
							 ->setLastModifiedBy("john mark simeon ")
							 ->setTitle("john mark simeon phpexcel document ")
							 ->setSubject("john mark simeon phpexcel document")
							 ->setDescription(" This is a test document for webcodegeeks.")
							 ->setKeywords("office PHPExcel php")
							 ->setCategory(" Test result file ");

// Set default font
echo date('H:i:s') , " Set default font" , EOL;
$objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')
                                          ->setSize(13);

// Add some data, resembling some different data types
echo date('H:i:s') , " Add some data" , EOL;
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'String')
                              ->setCellValue('B1', 'Simple')
                              ->setCellValue('C1', 'PHPExcel');

$objPHPExcel->getActiveSheet()->setCellValue('A2', 'String')
                              ->setCellValue('B2', 'Symbols')
                              ->setCellValue('C2', '!+&=()~§±æþ');

$objPHPExcel->getActiveSheet()->setCellValue('A3', 'String')
                              ->setCellValue('B3', 'UTF-8')
                              ->setCellValue('C3', 'Создать MS Excel Книги из PHP скриптов');

$objPHPExcel->getActiveSheet()->setCellValue('A4', 'Number')
                              ->setCellValue('B4', 'Integer')
                              ->setCellValue('C4', 12);

$objPHPExcel->getActiveSheet()->setCellValue('A5', 'Number')
                              ->setCellValue('B5', 'Float')
                              ->setCellValue('C5', 34.56);

$objPHPExcel->getActiveSheet()->setCellValue('A6', 'Number')
                              ->setCellValue('B6', 'Negative')
                              ->setCellValue('C6', -7.89);

$objPHPExcel->getActiveSheet()->setCellValue('A7', 'Boolean')
                              ->setCellValue('B7', 'True')
                              ->setCellValue('C7', true);

$objPHPExcel->getActiveSheet()->setCellValue('A8', 'Boolean')
                              ->setCellValue('B8', 'False')
                              ->setCellValue('C8', false);

$dateTimeNow = time();
$objPHPExcel->getActiveSheet()->setCellValue('A9', 'Date/Time')
                              ->setCellValue('B9', 'Date')
                              ->setCellValue('C9', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
$objPHPExcel->getActiveSheet()->getStyle('C9')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

$objPHPExcel->getActiveSheet()->setCellValue('A10', 'Date/Time')
                              ->setCellValue('B10', 'Time')
                              ->setCellValue('C10', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
$objPHPExcel->getActiveSheet()->getStyle('C10')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);

$objPHPExcel->getActiveSheet()->setCellValue('A11', 'Date/Time')
                              ->setCellValue('B11', 'Date and Time')
                              ->setCellValue('C11', PHPExcel_Shared_Date::PHPToExcel( $dateTimeNow ));
$objPHPExcel->getActiveSheet()->getStyle('C11')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);

$objPHPExcel->getActiveSheet()->setCellValue('A12', 'NULL')
                              ->setCellValue('C12', NULL);

$objRichText = new PHPExcel_RichText();
$objRichText->createText('你好 ');

$objPayable = $objRichText->createTextRun('你 好 吗？');
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );

$objRichText->createText(', unless specified otherwise on the invoice.');

$objPHPExcel->getActiveSheet()->setCellValue('A13', 'Rich Text')
                              ->setCellValue('C13', $objRichText);


$objRichText2 = new PHPExcel_RichText();
$objRichText2->createText("black text\n");

$objRed = $objRichText2->createTextRun("red text");
$objRed->getFont()->setColor( new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_RED  ) );

$objPHPExcel->getActiveSheet()->getCell("C14")->setValue($objRichText2);
$objPHPExcel->getActiveSheet()->getStyle("C14")->getAlignment()->setWrapText(true);


$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

$objRichText3 = new PHPExcel_RichText();
$objRichText3->createText("Hello ");

$objUnderlined = $objRichText3->createTextRun("underlined");
$objUnderlined->getFont()->setUnderline(true);
$objRichText3->createText(' World.');

$objPHPExcel->getActiveSheet()
    ->getCell("C15")
    ->setValue($objRichText3);


$objPHPExcel->getActiveSheet()->setCellValue('A17', 'Hyperlink');

$objPHPExcel->getActiveSheet()->setCellValue('C17', 'www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('C17')->getHyperlink()->setUrl('http://www.phpexcel.net');
$objPHPExcel->getActiveSheet()->getCell('C17')->getHyperlink()->setTooltip('Navigate to website');

$objPHPExcel->getActiveSheet()->setCellValue('C18', '=HYPERLINK("mailto:abc@def.com","abc@def.com")');


// Rename worksheet
echo date('H:i:s') , " Rename worksheet" , EOL;
$objPHPExcel->getActiveSheet()->setTitle('Datatypes');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel 2007 file
echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;

echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;


echo date('H:i:s') , " Reload workbook from saved file" , EOL;
$callStartTime = microtime(true);

$objPHPExcel = PHPExcel_IOFactory::load(str_replace('.php', '.xlsx', __FILE__));

$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo 'Call time to reload Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;


//var_dump($objPHPExcel->getActiveSheet()->toArray());


// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo done
echo date('H:i:s') , " Done testing file" , EOL;
echo 'File has been created in ' , getcwd() , EOL;

Compared to the first script the example above seems to be more complex as it uses more features of PHPExcel. We add primitive values of differeny types, boolean, Integer, float( line 48, 52, 60). We set cell values to an hyperlink, email, e.t.c. Using PHPExcel can be quite overwhelming for a beginner, but if you follow and read the instructions and scripts above you will grasp the concept fast enough.

Lets look at a very simple example with only the very basics.

index3.php

 
<?php
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/** Include PHPExcel */
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
							 ->setLastModifiedBy("Maarten Balliauw")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");



// Add some data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'just')
            ->setCellValue('B1', 'the')
            ->setCellValue('C1', 'basics')
            ->setCellValue('D1', '!!!!!');

// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A4', 'Basics Only');
            


$objPHPExcel->getActiveSheet()->setCellValue('A8',"Hello\nWorld");
$objPHPExcel->getActiveSheet()->getRowDimension(8)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setWrapText(true);


$value = "-ValueA\n-Value B\n-Value C";
$objPHPExcel->getActiveSheet()->setCellValue('A10', $value);
$objPHPExcel->getActiveSheet()->getRowDimension(10)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getStyle('A10')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('A10')->setQuotePrefix(true);



// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel 2007 file

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));



echo 'Files have been created in ' , getcwd() , EOL;

The script above goes straight to the point, it just adds sone data and creates the excel document(nothing fanciful or complicated).

2. Summary

In this example we learnt how to create an excel document in PHP with PHPExcel. We have also learnt how to use phpexcel functions to create an excel document.

3. Download the source code

Do you want to know how to develop your skillset to become a Web Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. Building web apps with Node.js

2. HTML5 Programming Cookbook

3. CSS Programming Cookbook

4. AngularJS Programming Cookbook

5. jQuery Programming Cookbook

6. Bootstrap Programming Cookbook

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.