Create Excel File with PHPExcel Plugin (Insert Image in Excel Sheet ,Use Template to Create Excel File, Create New Sheet)

This is an example for how to create Excel File with PHPExcel library.This article is inspired by Wikki Article return by Antonio Ramirez and extension created by marcovtwout for PHPExcel library.

So, my sincere thanks to Antonio Ramirez and marcovtwout for their contribution and time which they have spent to fix this PHPExcel issue with the implementation in Yii.

In this Wikki Article i am trying to include few more example like inserting image in excel,Creating a new sheet, Use of Template to create new Sheet..

Here is the step by step tutorial for creating a PHP Excel file with the help of PHPExcel Library and extension created by marcovtwout

Step 1 : Download Yii-phpexcel extension

Step 2 : (As suggested on yii-phpextension extension download page)Unzip the contents of this directory to protected/extensions/phpexcel.

Step 3 : ( As suggested on yii-phpextension extension download page) Download Latest version of PHPExcel Library from this link

Step 4 : (As suggested on yii-phpextension extension download page) Unzip the contents of the folder Classes to a new folder protected/extensions/phpexcel/vendor

Step 5 : Create an Action in Controller file. with below given code

<?php

public function actionCreateExcel(){
  Yii::import('ext.phpexcel.XPHPExcel');    
	  $objPHPExcel= XPHPExcel::createPHPExcel();
	  $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
							 ->setLastModifiedBy("Maarten Balliauw")
							 ->setTitle("Office 2007 XLSX Test Document")
							 ->setSubject("Office 2007 XLSX Test Document")
							 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
							 ->setKeywords("office 2007 openxml php")
							 ->setCategory("Test result file");


// Add some data
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B2', 'world!')
            ->setCellValue('C1', 'Hello')
            ->setCellValue('D2', 'world!');

// Miscellaneous glyphs, UTF-8
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A4', 'Miscellaneous glyphs')
            ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
      Yii::app()->end();
}
?>

Step 6 : Now execute controller action. And That's it you will get excelsheet ready for download. :)

Create New Sheet in Current Excel File :-

To Create new Sheet in Current Excel File you can simply use below given code before exporting a document code in above above given example code for controller action.

<?php
$objPHPExcel->createSheet(1);
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle('Title');
?>
Insert Image In ExcelSheet :-

To Insert an image in excelsheet use below given code.

<?php
$objDrawingPType = new PHPExcel_Worksheet_Drawing();
$objDrawingPType->setWorksheet($objPHPExcel->setActiveSheetIndex(1));
$objDrawingPType->setName("Pareto By Type");
$objDrawingPType->setPath(Yii::app()->basePath.DIRECTORY_SEPARATOR."../images/img.jpg");
$objDrawingPType->setCoordinates('B2');
$objDrawingPType->setOffsetX(1);
$objDrawingPType->setOffsetY(5);
?>

Use template to create Excel Sheet :-

To Use Template to create a Excelsheet. change below given code in controller action. use below given code

<?php
Yii::import('ext.phpexcel.XPHPExcel');      
		$objPHPExcel = XPHPExcel::createPHPExcel();
		$objReader = PHPExcel_IOFactory::createReader('Excel5');
		$objPHPExcel = $objReader->load(Yii::app()->basePath . DIRECTORY_SEPARATOR . 'extensions' . DIRECTORY_SEPARATOR."phpexcel".DIRECTORY_SEPARATOR."dp-template".DIRECTORY_SEPARATOR."30template.xls");
??

Instead of

<?php
 Yii::import('ext.phpexcel.XPHPExcel');      
    $objPHPExcel= XPHPExcel::createPHPExcel();
?>

This code will load a template

$objPHPExcel = $objReader->load(Yii::app()->basePath . DIRECTORY_SEPARATOR . 'extensions' . DIRECTORY_SEPARATOR."phpexcel".DIRECTORY_SEPARATOR."dp-template".DIRECTORY_SEPARATOR."30template.xls");

Note : Make sure that it is pointing to the correct path where a template file is placed.

For Further References and examples you can refer PHPExcel Official Example Section.

I hope this article would be helpful for few people.:)

Thanks !!

3 0
12 followers
Viewed: 77 240 times
Version: 1.1
Category: Tutorials
Written by: codesutra
Last updated by: codesutra
Created on: Jul 10, 2013
Last updated: 3 years ago
Update Article

Revisions

View all history

Related Articles