Write To Excel Using PHP

PHPExcel project provides a set of classes, which allow us to Read, Write and Create Excel documents in PHP.

File formats, supported by PHPExcel are .xls, .xlsx, CSV, .ods, Gnumeric, PDF and HTML.

Requirements for PHPExcel:

PHP version >= 5.2.0
PHP extensions php_zip,php_xml,php_gd2 should be installed & enabled.

In this article we'll be looking at how to install PHPExcel and how to create an Excel file of the data fetched from MySQL.

Installating PHPExcel

• Download the PHPExcel 1.7.7 - PEAR from phpexcel.codeplex.com.
• Extract the downloaded file.
• Create a subfolder called 'Classes' under /var/php_files/ (or as you wish) and copy the contents into the folder like /var/php_files/Classes/ or if you are running Windows c:\xampp\htdocs\Classes\

Now we are ready to go...

Write New Excel File

Here I am using employee table with the following data.

Code:

mysql> select * from employee;
+------+------------+-----------------+--------------+
| id   | first_name | last_name       |    DOJ       |
+------+------------+-----------------+--------------+
|    1 | Ramesh     | Prasad          |  1996-09-17  |
|    2 | Dilip      | Das             |  1987-07-30  |
|    3 | Prakash    | Chandra         |  1982-10-24  |
|    4 | Sourav     | Roy             |  1996-07-25  |
|    5 | Vijay      | Kumar           |  1982-10-18  |
|    6 | Arun       | Kumar           |  1982-10-11  |
+------+------------+-----------------+--------------+

The PHP code to read from the MySQL table and write out the data in a Excel file

PHP:

<?php
/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
ini_set('include_path', ini_get('include_path').';../Classes/');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';

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

// Connect to MySQL
$conn = mysql_connect('localhost', 'root', '');

if (!$conn) {
    die('Could not connect: ' . mysql_error());
}
$db_selected = mysql_select_db('mydb', $conn);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}

$result = mysql_query("SELECT * from employee");

// Setting Excel file properties
// Change the properties detail as per your requirement
$objPHPExcel->getProperties()->setCreator("Jasbir");
$objPHPExcel->getProperties()->setLastModifiedBy("Pradeep");
$objPHPExcel->getProperties()->setTitle("Employee Details");
$objPHPExcel->getProperties()->setSubject("Employee DOJ Informaton");
$objPHPExcel->getProperties()->setDescription("Excel 2007 file generated using PHP.");

// Select current sheet
$objPHPExcel->setActiveSheetIndex(0);
// Add some data
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'EMP_ID');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'First Name');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Second Name');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Date of Join');

//column number, which we will be incrementing
$colnum=1;

while ($row = mysql_fetch_assoc($result))
{
    $colnum++;
    $objPHPExcel->getActiveSheet()->SetCellValue('A'."$colnum", $row["id"]);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'."$colnum", $row["first_name"]);
    $objPHPExcel->getActiveSheet()->SetCellValue('C'."$colnum", $row["last_name"]);
    $objPHPExcel->getActiveSheet()->SetCellValue('D'."$colnum", $row["DOJ"]);
}

// Optionally, set the title of the Sheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');
 
// Create a write object to save the the excel
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

// save to a file
$objWriter->save('/home/jasbir/Documents/Employees.xls');

// You may optionally output the data directly to a browser, here's how
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Employees.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');

?>

Please follow the PHPExcel Documentation for more information of all the functions used above or if you have trouble installing the class.