Writing To Excel With PHP

Today I searched how to write out an Excel file with PHP.  Keep in mind that this is being written by a PHP noob and I am a Java developer by trade.  This was my first project being built in PHP from the ground up, prior to this my only experience was tweaking a couple of our existing open source PHP apps.

OK, to the point….

My co-worker nalmf and I were searching for a library at the same time.  I actually have to give him the credit for stumbling across the solution we ended up using.  I will start by listing the 2 that I found and why we did not end up using them.

  • Pear Spreadsheet_Excel_WriterThis one looked really promising.  I had heard of Pear before but know nothing about it really.  I was looking for a library that I could just drop into our project and immedietly start working with it doing little or no configuration.  I took a peak at installing Pear and it seemed like more work than I wanted to do at the time.  This is for a very small project.  If you already have Pear or are familiar with how to install it, it appears this could be a very viable solution.
  • PHP Classes : MS-Excel Stream HandlerThis gives you a very bare bones way to dump data into Excel.  It provides no means to format the cell and no formulas.  I did not have need to use formulas for this project, but I thought I would throw that in there.  I came close to using this one and just have the user bold the header and freeze the top and left column.  I did have to make a couple of fixes to the source.  The script is broken by default on windows systems.  It drops out the ” : ” in the path to the Excel file to be written.  I found a fix for it in their forum.  There was also a $size variable that did not get initialized before it was read.  I initialized it to 0 before it attempted to do a += on it in a for loop.  I have already deleted my modifications to the source before posting this.  If anyone is interested in using it and needs help they can leave a comment.  I did not feel safe using the script in production without thorough testing.  I will say that it did write the Excel file out very fast for my 2,000 record test.  It was done in less than a second and I had about 5 columns of data.
  • PHPExcelThis is the library we ended up going with.  It does not have any dependencies, which was really nice, and I was able to drop it right into our project include it and run with it.  It is very extensive and can even create Excel 2007 documents.  I was looking to create an Excel 2000 compatible document, which it easily handled as well.  I was able to bold our headers and it handles the freeze pane as well. I was able to freeze the top header row and the left column which contained some time slots.  Below is the code I used to extract the data out of our db and dump it into Excel.  This library is very well documented and looks as if it will handle any of our PHP / Excel  needs for the future.
require_once $_SERVER["DOCUMENT_ROOT"] . '/classes/PHPExcel.php';
require_once $_SERVER["DOCUMENT_ROOT"] . '/classes/PHPExcel/IOFactory.php';
require_once($_SERVER["DOCUMENT_ROOT"] . '/classes/database.php');
/* Here there will be some code where you create $objPHPExcel */
// redirect output to client browser
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="appointment.xls"');
header('Cache-Control: max-age=0');

$objPHPExcel = new PHPExcel();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();
	try {
		$dbh = Database :: getConnection();

		$sql = 'select *,DATE_FORMAT(appoint_dob, \'%m/%d/%Y\') dobFormat from appoint a
				inner join onehourslots h on a.appoint_hour = h.onehourslots_hour
				left outer join language l on a.language_no = l.language_no
				left outer join sick s on a.sick_no = s.sick_no
				left outer join ethnicity e on a.ethnicity_no = e.ethnicity_no
				where a.event_no = :eventNo order by a.appoint_hour';

		//setup headers
		$sheet->setCellValue('A1', "Time");
		$sheet->setCellValue('B1', "First");
		$sheet->setCellValue('C1', "Last");

		$stmt = $dbh -> prepare($sql);
		$eventNo = $_GET['event_no'];
		$stmt -> bindValue(':eventNo', $eventNo, PDO :: PARAM_INT);
		$stmt -> execute();

		$i = 2;
		while ($r = $stmt->fetch()) {
			$sheet->setCellValue('A' . $i, $r['onehourslots_display']);
			$sheet->setCellValue('B' . $i, $r['appoint_first']);
			$sheet->setCellValue('C' . $i, $r['appoint_last']);
			$i ++;
		 }

		$sheet->getStyle('A1')->getFont()->setBold(true);
		$sheet->getStyle('B1')->getFont()->setBold(true);
		$sheet->getStyle('C1')->getFont()->setBold(true);

		//freeze the top header row and the left time column
		$sheet->freezePane('B2');			

		$dbh = null;
	} catch(PDOException $e) {
		echo $e -> getMessage();
		$dbh = null;
	}

$objWriter->save('php://output');

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Go back to top