Create an excel file in PHP

September 08, 2013 | 6383 views | Comments

Generate excel files in PHP using PHPExcel.

The script below is divided into two parts. The first script contains code to prepare and set up the data. Redirecting the output to the user's web browser to prompt a download file box, and saving the excel file to the server are what the code on the second script does.

For example

First part of the script.

Prepare and set up the data.

include_once('PHPExcel_1.8.0_doc/Classes/PHPExcel/IOFactory.php');

//set the desired name of the excel file
$fileName = 'create-an-excel-file-in-php';

//prepare the records to be added on the excel file in an array
$excelData = array(
	0 => array('Jackson','Barbara','27','F','Florida'),
	1 => array('Kimball','Andrew','25','M','Texas'),
	2 => array('Baker','John','28','M','Arkansas'),
	3 => array('Gamble','Edward','29','M','Virginia'),
	4 => array('Anderson','Kimberly','23','F','Tennessee'),
	5 => array('Houston','Franchine','25','F','Idaho'),
	6 => array('Franklin','Howard','24','M','California'),
	7 => array('Chen','Dan','26','M','Washington'),
	8 => array('Daniel','Carolyn','27','F','North Carolina'),
	9 => array('Englert','Grant','25','M','Delaware')
);

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

// Set document properties
$objPHPExcel->getProperties()->setCreator("Me")->setLastModifiedBy("Me")->setTitle("My Excel Sheet")->setSubject("My Excel Sheet")->setDescription("Excel Sheet")->setKeywords("Excel Sheet")->setCategory("Me");

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

// Add column headers
$objPHPExcel->getActiveSheet()
			->setCellValue('A1', 'Last Name')
			->setCellValue('B1', 'First Name')
			->setCellValue('C1', 'Age')
			->setCellValue('D1', 'Sex')
			->setCellValue('E1', 'Location')
			;

//Put each record in a new cell
for($i=0; $i<count($excelData); $i++){
	$ii = $i+2;
	$objPHPExcel->getActiveSheet()->setCellValue('A'.$ii, $excelData[$i][0]);
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$ii, $excelData[$i][1]);
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$ii, $excelData[$i][2]);
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$ii, $excelData[$i][3]);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$ii, $excelData[$i][4]);
}

// Set worksheet title
$objPHPExcel->getActiveSheet()->setTitle($fileName);

 

Second part of the script.

Prompt a download file window or save the excel file in the server.

The excel file can be generated into Excel2007(xlsx) or Excel5(xls) format, the former (Excel2007) is recommended for it is the latest version. There are four options in generating the actual excel file.

1. Prompt a download file box to save an Excel2007 file.

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

2. Prompt a download file box to save an Excel5 file.

// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

3. Save an Excel2007 file to the server.

//save the file to the server (Excel2007)
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('excel-files/' . $fileName . '.xlsx');

4. Save an Excel5 file to the server.

//save the file to the server (Excel5)
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('excel-files/' . $fileName . '.xls');