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

13 followers

This wiki article has not been tagged with a corresponding Yii version yet.
Help us improve the wiki by updating the version information.

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 !!

Total 4 comments

#16615 report it
codesutra at 2014/03/11 02:50am
@morteza toloo

Sorry, but i am not able to understand your comment. :) Just to let you know that this article is explaining about creating a new excel sheet.I hope this is what you are looking for.

#16584 report it
morteza toloo at 2014/03/07 02:00pm
Load excel

Thanks for your good and easy to use extension. How can I load an excel file (which is uploaded by the client)?

#14046 report it
codesutra at 2013/07/15 07:24am
@samilo

your welcome :) !!

#14045 report it
samilo at 2013/07/15 02:53am
thanks

Good , thank you so much

Leave a comment

Please to leave your comment.

Write new article