PHPExcel: Advanced Read-Write Excel Made Simple

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.


Author: Arief Bayu Purwanto

Hello, my name is Arief Bayu Purwanto, a 24 years old father of a beautiful daughter. Interested in online programming, linux, games, and reading. Currently working on kapanlagi.com as junior developer. I live in a relatively quite and cool place called Malang. I'm available for some freelance stuff as well as some consulting job. You can see my portofolio for some previous task I've finished and some other information related to my capability. Btw, I'm plurking here.

  • Jemeti

    wow… it`s very help full…

    tx bro..

  • Armen Markossyan

    Thank you very much, helped a lot!

    • Abc

      good

      • Abc

        good

        • Abc

          good

          • Abc

            good

          • Abc

            good

          • Abc

            good

          • Abc

            good

          • Abc

            good

          • Abc

            good

          • Abc

            good

          • Abc

            good

          • Abc

            good

          • Abc

            good

          • Abc

            good

          • Abc

            ood

          • Abc

            good

          • Abc

            good

  • MK

    You saved me a lot of time ! Thanks !!!

  • dima

    You are a hero!

  • Abc

    This is help ful

  • Abc

    Type your comment here.

    Type your comment here.Type your comment here.Type your comment here.Type your comment here.Type your comment here.Type your comment here.Type your comment here.

  • Francis-gonzales

    ‘Autofilter must be set on a range of cells.’what can i do?

  • Toti Ac Newsletter

    I am reading data from a dynamic html form. I do get the data from the form ok, but how do I write each line to an excel with PHPexcel?

    See my code that I receive from the form:

    $ids=explode(“,”,$_POST[‘count’]);for ($i=0;$i<count($ids);$i++){     $level=$_POST['level'.$ids[$i]]; $institution=$_POST['institution'.$ids[$i]]; $board=$_POST['board'.$ids[$i]]; $division=$_POST['division'.$ids[$i]]; echo "level: ".$level; echo " institution: ".$institution; echo " board: ".$board; echo " division: ".$division; echo "”; }

    In the PHPexcel I would like each record as per example:
    $objPHPExcel->getActiveSheet()->setCellValue(‘B’.$row, $dataRow[“$level”]);

    Any suggestions?

  • Pingback: Memberi style font, cell, dan merge di PHPExcel « DY Shared Anything……()

  • Nice, Thankyou… Can you tell me how to format fonts in PHPExcel such as BOLD….  etc?

  • adred

    Hi, I am kinda new to this library. I am writing an importer and I am kinda stuck on how set it up so it would detect the new line char from the xlsx file? Any help would be appreciated.

  • Loiphamle

    Thanks for your useful post :X

  • Thanks :D, I really appreciate your help, greetings from Bolivia

  • Ir_dev

    useful
    thanks.

  • PCK

    Thanks it helped me getting started.

  • karu

    Hi. I am able to store data into excel file. But when i do it again it is replacing previous data. How to append data into excel file??

  • This is the most clearly written post I think I have ever read.

    It’s possibly the most useful also.

    Thank you,

  • Bonie

    How can I work with large number of columns not limited to A, B, C and D

  • helpyou

    You can use ‘allborders’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN) instead of defining an array for each border