Tag Archives: excel

Read and write Excel file using PHP (example)

This post will describe a basic usage of PHPExcel to read and write Excel file. Although in this demo I use .xls extension, PHPExcel really can write and read Excel 2007 (.xlsx) files too.

Download PHPExcel here (free of charge, it’s open source 😀 ).

Unzip the PHPExcel .zip file into your project destination. You only need to extract “Classes” folder.

Our scenario: user will fill some HTML form then from this input, we will populate a new Excel file (.xls) and send it to user as downloadable file.

Our Excel template screenshot

Excel template screen shot

We will fill C1 cell with the current date when the form submitted. C3 cell will be the user’s name and C4 cell will contain user’s message. Our template file –> http://demo.ahowto.net/phpexcel/template.xls

Full source code of our simple form can be seen by viewing source code (Ctrl+U on Firefox) our demo URL: http://demo.ahowto.net/phpexcel/

Form inputs will be processed by process.php below:


//processing form submitted

if (!isset($_POST['btnSubmit'])) exit;

//include PHPExcel library
require_once "Classes/PHPExcel/IOFactory.php";

//load Excel template file
$objTpl = PHPExcel_IOFactory::load("template.xls");
$objTpl->setActiveSheetIndex(0);  //set first sheet as active

$objTpl->getActiveSheet()->setCellValue('C2', date('Y-m-d'));  //set C1 to current date
$objTpl->getActiveSheet()->getStyle('C2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); //C1 is right-justified

$objTpl->getActiveSheet()->setCellValue('C3', stripslashes($_POST['txtName']));
$objTpl->getActiveSheet()->setCellValue('C4', stripslashes($_POST['txtMessage']));

$objTpl->getActiveSheet()->getStyle('C4')->getAlignment()->setWrapText(true);  //set wrapped for some long text message

$objTpl->getActiveSheet()->getColumnDimension('C')->setWidth(40);  //set column C width
$objTpl->getActiveSheet()->getRowDimension('4')->setRowHeight(120);  //set row 4 height
$objTpl->getActiveSheet()->getStyle('A4:C4')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP); //A4 until C4 is vertically top-aligned

//prepare download
$filename=mt_rand(1,100000).'.xls'; //just some random filename
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objTpl, 'Excel5');  //downloadable file is in Excel 2003 format (.xls)
$objWriter->save('php://output');  //send it to user, of course you can save it to disk also!

exit; //done.. exiting!

I already put a lot of comment on the codes. It should be clear. On the code above, I show you how to read Excel .xls file, change cell content, formatting a cell (alignment), change column width, set row height and finally “force” send result file to user.

The Excel result file should be like this:

Our Excel result from template created by PHP

You can do more than I showed here (such as setting cell color, rotation, inserting images, formula, printing, etc.)  by reading PHPExcel Documentation and see the codes in Tests folder (it is included in the PHPExcel download zip).