Asudahlah.com

Programming, tutorials, mechatronics, operating systems, and other tech stuff

PHPExcel Generate Excel Spreadsheet Files in PHP with Different Formats

157 comments
PHPExcel is a powerful PHP library (class) which allows us to generate and read Excel spreadsheet file in PHP server-side. Developed by PHPOffice team.

Depending on the circumstances, generating an excel spreadsheet file as a reporting medium is a great way to present reporting data in offline, independent manner. Especially when dealing about printing and lots of numeric data such as financial report.

Reading an excel file using PHP is also a good way to automate data entry. Especially when dealing with batch data entries. And on the end-user side, this method will also ease the user as the interface for entering the data is an excel spreadsheet file which most people are familiar with. And absolutely more agile when compared to web-interfaced forms.

To use this library, simply download the library files here.

Extract it anywhere in your web root directories. Call the PHPExcel.php file in your script and you're good to go.

Here's a Hello World PHP script example to generate an excel file.
I think the comments in pretty self-explanatory.
<?php
require_once 'Classes/PHPExcel.php';

//create PHPExcel object
$excel = new PHPExcel();

//insert some data to PHPExcel object
$excel->setActiveSheetIndex(0)
 ->setCellValue('A1','Hello')
 ->setCellValue('B1','World');

//redirect to browser (download) instead of saving the result as a file

//this is for MS Office Excel 2007 xlsx format
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="test.xlsx"');

//this is for MS Office Excel 2003 xls format
//header('Content-Type: application/vnd.ms-excel');
//header('Content-Disposition: attachment; filename="test.xlsx"');


header('Cache-Control: max-age=0');

//write the result to a file
//for excel 2007 format
$file = PHPExcel_IOFactory::createWriter($excel,'Excel2007');

//for excel 2003 format
//$file = PHPExcel_IOFactory::createWriter($excel,'Excel5');

//output to php output instead of filename
$file->save('php://output');

?>

You can choose in what format the excel file will be generated by uncomment/comment the headers and IOFactory line.

Basically, what needs to be change to save it in different formats is:
for Excel 2007 or above
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="test.xlsx"');
$file = PHPExcel_IOFactory::createWriter($excel,'Excel2007');

and for Excel 2003
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="test.xls"');
$file = PHPExcel_IOFactory::createWriter($excel,'Excel5');

And here's the video if you need a step-by-step explanation.

Part #1 How to install and test php excel (basic usage).

Part #2 Download PHP Generated Excel File.

157 comments :

Post a Comment

Conditional Formatting in PHPExcel

2 comments
Doing conditional formatting in PHPExcel is pretty easy. First you have to define the formatting. Then apply it to a cell.

I hope this snippet could be useful.
<?php
...
$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_CELLIS)
                ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_LESSTHAN)
                ->addCondition('B2')
                ->getStyle()
                ->applyFromArray(
 array(
  'font'=>array(
   'color'=>array('argb'=>'FF000000')
  ),
  'fill'=>array(
   'type' =>PHPExcel_Style_Fill::FILL_SOLID,
   'startcolor' =>array('argb' => 'FFFF0000'),
   'endcolor' =>array('argb' => 'FFFF0000')
  )
 )
);
$conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle('C2')->getConditionalStyles();
array_push($conditionalStyles,$objConditional);
$objPHPExcel->getActiveSheet()->getStyle('C2')->setConditionalStyles($conditionalStyles);

B2 is the reference cell, and C2 is the cell for conditional formating to be applied to.

By the way, not everything went smooth when i did this, because the "fill" style doesn't works while the "font" style works well. I spend hour to scour the documentation, and search every possible keywords in google before i finally found what the problem is.
If you want to use SOLID fill, you need to use 'startcolor' instead of 'color'.
That's it. Simple matter but cost me my worthy minutes.
P.S: If you want to use textual conditional formatting insetad of numeric data. Check out Krazal's comment below.

2 comments :

Post a Comment