PHPExcel: Advanced Read-Write Excel Made Simple

written by Arief Bayu Purwanto on July 16, 2010 in Tutorial with 36 comments

There are many excel reader / writer library all over the net. However, most of it only support partial features. Today, we’ll learn a php excel library that support almost all aspect of excel. Well, except VBS, I guess.


Step 1 Preparation

This step will tell you what we need to be able to use PHPExcel flawlessly.

Dependency

PHPExcel require PHP5 5.2.0 or higher. It also require php extension: php_xml, php_gd2, and optionally php_zip. Php_zip is optional because it is needed only if you want to handle .xlsx (excel 2007) and or .ods (OpenOffice Spreadsheet).

Script installation

If you’ve done with the dependency, now we can download the actual installation file here. Extract and copy Classes to your includes/libraries directory. On your script, the minimum code you need to do is:

require_once dirname(__FILE__) . '/PHPExcel/PHPExcel.php';

Then, you need to instantiate PHPExcel class:

$objPHPExcel = new PHPExcel();


Step 2 Reading Data

To read excel file, we need to instantiate the appropriate reader class. There are several class ready for reading Excel:

  • PHPExcel_Reader_Excel2007: For reading excel 2007 format (.xlsx)
  • PHPExcel_Reader_Excel5: For reading excel 2003 format (.xls)
  • PHPExcel_Reader_OOCalc: For reading OpenOffice.Org Spreadsheet format (.ods)

Note: You need php_zip in order to be able to read data from Excel 2007 or OOCalc format.

How to actually read xls data

After determining what file format we want to read, we instantiate reader class based on that. Then, we set it to only for reading and also load our xls file.

$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load( dirname(__FILE__) . '/xls/test_data.xls' );

We now have the reader object, next step is, instantiate the iterator.

$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();

After we have the iterator, we can loop through each and every row we have.

Oh wait, to better understand how it work, I think you should download the source file and see the xls file data first. It’s located in ‘xls/test_data.xls

$array_data = array();
foreach($rowIterator as $row){
	$cellIterator = $row->getCellIterator();
	$cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
	if(1 == $row->getRowIndex ()) continue;//skip first row
	$rowIndex = $row->getRowIndex ();
	$array_data[$rowIndex] = array('A'=>'', 'B'=>'','C'=>'','D'=>'');
	
	foreach ($cellIterator as $cell) {
		if('A' == $cell->getColumn()){
			$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
		} else if('B' == $cell->getColumn()){
			$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
		} else if('C' == $cell->getColumn()){
			$array_data[$rowIndex][$cell->getColumn()] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'YYYY-MM-DD');
		} else if('D' == $cell->getColumn()){
			$array_data[$rowIndex][$cell->getColumn()] = $cell->getCalculatedValue();
		}
	}
}
print_r($array_data);

Feeling a little confused? Don’t worry, I’ll explain it to you. The code work by looping through row iterator. Then, for each row, we instantiate cell iterator. And then, we execute code based on which column we are, right now.

Reading without cell iterator

There’s another alternative way to read data. Instead of using cell iterator, we simply read the data directly from currently active sheet.

$sheet = $objPHPExcel->getActiveSheet();
$array_data = array();
foreach($rowIterator as $row){
	$rowIndex = $row->getRowIndex ();
	$array_data[$rowIndex] = array('A'=>'', 'B'=>'','C'=>'','D'=>'');
	
	$cell = $sheet->getCell('A' . $rowIndex);
	$array_data[$rowIndex]['A'] = $cell->getCalculatedValue();
	$cell = $sheet->getCell('B' . $rowIndex);
	$array_data[$rowIndex]['B'] = $cell->getCalculatedValue();
	$cell = $sheet->getCell('C' . $rowIndex);
	$array_data[$rowIndex]['C'] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getCalculatedValue(), 'YYYY-MM-DD');
	$cell = $sheet->getCell('D' . $rowIndex);
	$array_data[$rowIndex]['D'] = $cell->getCalculatedValue();
}
print_r($array_data);

What method will you use? that’s depend on what situation you’re facing with.


Step 3 Writing Data

Writing excel data require a slightly different route. First, we need to instantiate PHPExcel() class and get currently active sheet in which we will write into.

require_once dirname(__FILE__) . '/PHPExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();

As a note, we will write data gathered from previous step.

Create row header

After instantiating the needed class, our next step is to create row header.

$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Name');
$sheet->setCellValue('C1', 'Date');
$starting_pos = ord('C');
$index_pos = 0;
foreach($array_distict_date as $date){
	$sheet->setCellValue(chr($starting_pos+$index_pos) . '2', $date);
	$index_pos++;
}
ugly header

Look, it’s easy, right? But, it’s ugly, I know. We’ll fix that later. For now, let’s move on to how to write the actual content.

$rowcounter = 2;
foreach($array_data_user as $k1=>$v1){
	$rowcounter++;
	list($uid, $uname) = explode("|", $k1);
	$sheet->setCellValue('A' . $rowcounter, $uid);
	$sheet->setCellValue('B' . $rowcounter, $uname);
	$starting_pos = ord('C');
	$index_pos = 0;
	foreach($array_distict_date as $date){
		$vdate = isset($v1[$date]) ? $v1[$date] : '-';
		$sheet->setCellValue(chr($starting_pos+$index_pos) . $rowcounter, $vdate);
		$index_pos++;
	}
}

Haha, no difference, right? Yep, no difference. It’s just the same as how we write row header. Now we need to determine how we store the result.

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

You may notice that we don’t choose class. Instead, we determine the file type using string parameter. Here’s some acceptable value for PHPExcel_IOFactory::createWriter():

  • Excel2007
  • Excel5
  • CSV
  • HTML
  • PDF

It’s also worth to note that $objWriter->save() is also accept absolute file path for writing.

Now that we already know how to write excel, let’s tidy it up a bit to make a professionally looking xls report.

Merging columns

It’s easy to merge cell. All we have to do is, determine cell range we want to merge and send it to PHPExcel_Worksheet::mergeCells:

$sheet->mergeCells('A1:A2');
$sheet->mergeCells('B1:B2');
$sheet->mergeCells('C1:' . chr($starting_pos+($index_pos-1)) . '1');
merged cells

Resizing Cell Height / Width

Nice! Now we need to resize date column so that no ‘resize arrow‘ (I don’t really sure if it’s the right term) appeared. Here’s how we do it.

$sheet->getColumnDimensionByColumn(2+$i, 2)->setWidth(10);
merged cells

See? No more ‘resize arrow‘.

Setting text alignment

We now move on to how to setting text alignment.

$sheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
merged cells

Please note, on a merged cell, we need only the top-left coordinate of that cell.

Add Border And Fill Color

Cool. Lets add border and background fill to it to make a more professionally looking excel report.

$default_border = array(
	'style' => PHPExcel_Style_Border::BORDER_THIN,
	'color' => array('rgb'=>'1006A3')
);
$style_header = array(
	'borders' => array(
		'bottom' => $default_border,
		'left' => $default_border,
		'top' => $default_border,
		'right' => $default_border,
	),
	'fill' => array(
		'type' => PHPExcel_Style_Fill::FILL_SOLID,
		'color' => array('rgb'=>'E1E0F7'),
	),
	'font' => array(
		'bold' => true,
	)
);

$sheet->getStyle('A1:A2')->applyFromArray( $style_header );
$sheet->getStyle('B1:B2')->applyFromArray( $style_header );

merged cells

Bonus section: how to work with formula

Adding Summary / Total row

Working with formula is very easy. For example, we can to add TOTAL on each row, so that we know how many hour each person spent. This usually just fill certain cell with value ‘=SUM(C3:C7)’. How do we translate that in PHPExcel? well, guess what? fill that particular cell with whatever formula we want!

//Adding total column
$ranged_total_coordinate = chr($starting_pos+$index_pos) . '1:' . chr($starting_pos+$index_pos) . '2';
$sheet->setCellValue(chr($starting_pos+$index_pos) . '1', 'Total');
$sheet->mergeCells($ranged_total_coordinate);
$sheet->getStyle(chr($starting_pos+$index_pos) . '1')->getAlignment()
		->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)
		->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
$sheet->getStyle($ranged_total_coordinate)->applyFromArray( $style_header );
$rowcounter = 2;
foreach($array_data_user as $k1=>$v1){
	$rowcounter++;
	$sheet->setCellValue(chr($starting_pos+$index_pos) . $rowcounter, "=SUM(C{$rowcounter}:".chr(($starting_pos+($index_pos-1)))."{$rowcounter})");
	$sheet->getStyle(chr($starting_pos+$index_pos) . $rowcounter)->applyFromArray( $style_body_content );
}

Parsing Formula

What if we want to get value from certain cell, that is in formula, and we want to get the result? That’s easy too!

$sheet->getCell('K3')->getValue() . " -> " . $sheet->getCell('K3')->getCalculatedValue();

Summary

Working with Excel has never been this easy! we can manipulate ALMOST all aspect of an excel object. You can manipulate an excel object itself (adding properties, setting page, etc), worksheet (change column width, height), cell (playing with formula, cell styling, etc), and many more.