01/09/2009 Written by Cyril GRANDJEAN

During my work for the company Planet Bourgogne, I had to create an XLS file using PHP and MySQL. I’ve also discovered a PHP library for reading and writing XLSX files.

The format XLSX is supported by Excel 2007 and later. For the previous versions, it requires the installation of the following module : http://www.microsoft.com/downloads/details.aspx?displaylang=fr&FamilyID=941b3470-3ae9-4aee-8f43-c6bb74cd1466.

Here are some lines of codes which are going to allow you to create your first file XLS by using PHP.

  1. Download and copy the library PHPExcel in your PHP Project
  2. Create a PHP file by using the following syntax :
/** Errors report */
error_reporting(E_ALL);

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . 'PHPExcel/Classes/');

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

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

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

//We add contents
//Warning, a utf8_encode() is necessary for the character like 'é', 'è', ..
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Ligne 1 Colonne 1');
$objPHPExcel->getActiveSheet()->setCellValue('A2', utf8_encode('Durée'));

//Manage the size of the column
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);

//Manage font style
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(10);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

//Make a border style
$objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

// Save our file xlsx
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('../data_xls/myfileXLS.xlsx');

This tutorial is only the bases necessary for the creation of a simple file XLSLX. For advanced used : click here.