Custom export into excel in Yii Framework

Nitin Bhardwaj · January 9, 2014

Custom export into excel in Yii Framework

ByNitin BhardwajCore Php and FrameworksYii Framework4 Comments

Custom export into excel in Yii Framework

Hello Friend, In one our project developed in Yii. I was struggling to export the cgridview data into excel. I searched many plugins and read the tutorial. But not get proper solution especially with relational data from one or many models (MySQL database tables). I try excelview, toexcel etc. Yii plugin but it was really slow especially on large data fetch and also not getting proper excel format. After spending some time, I planned to write my own custom export in excel code in Yii framework.

Creating a custom plugin in Yii framework involves creating reusable components that can be easily integrated into your application. Plugins in Yii are typically implemented as Yii extensions.

Here’s a step-by-step guide on how to develop a custom plugin in Yii:

1. In model search function define session global variable and store active search data (set pagination false so you will get all the record without paging)

public function search()
{
 $criteria=new CDbCriteria;
 $criteria->compare('vendor_debit_id',$this->vendor_debit_id);
 $criteria->compare('hotel_id',$this->hotel_id);
 $criteria->compare('supplier_name',$this->supplier_name,true);
 $criteria->compare('debit_amount',$this->debit_amount);
 $criteria->compare('bal_before_debit',$this->bal_before_debit);
 $criteria->compare('bal_after_debit',$this->bal_after_debit);
 $criteria->compare('debit_sheet_no',$this->debit_sheet_no,true);
 $criteria->compare('created_on',$this->created_on,true);
 $criteria->compare('admin_id',$this->admin_id);
 $data = new CActiveDataProvider($this, array(
 'criteria'=>$criteria,
 ));
 $_SESSION['vendor_debit_export_excel'] = new CActiveDataProvider($this, array('criteria' => $criteria, 'pagination' => false));;
 return $data;
}

2. Open controller class and define the function for export in excel. Remember for unset session variable into the end for you controller function before the end the function execution.

public function actionExport_excel()
{
 if(isset($_SESSION['vendor_debit_export_excel'])):
 header("Content-type: application/vnd.ms-excel");
 header("Content-Disposition: attachment;Filename=vendor-debit-report.xls");
 $dataProvider = $_SESSION['vendor_debit_export_excel'];
 $data = array();
 foreach($dataProvider->getData() as $record) {
 $data[] = array(
 'vendor_debit_id'=>$record->vendor_debit_id,
 'hotel_id'=>$record->hotel->name,
 'supplier_name'=>$record->supplier_name,
 'debit_amount'=>$record->debit_amount,
 'bal_before_debit'=>$record->bal_before_debit,
 'bal_after_debit'=>$record->bal_after_debit,
 'debit_sheet_no'=>$record->debit_sheet_no,
 'admin_id'=>Users::model()->usernameWithName($record->admin_id),
 'created_on'=>Yii::app()->dateFormatter->format("d/MM/y, h:m a", strtotime($record->created_on))
 );
 }
 echo $this->renderPartial('//hotelvendordebit/vendor_debit_excel',array('excel_data'=>$data));
 unset($_SESSION['vendor_debit_export_excel']);    
 Yii::app()->end();
 else:
 throw new CHttpException(500,'An error occurred in fetching data. please try later'); // Error message as per your convenient
 endif;
}

3. Now the final create and rederpartial view file into your relative view folder and pass the data(in the array).

<html>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<body>
 <table border="1" cellpadding="5" style="font-size:10px;font-family:tahoma,arial;" cellspacing="0" bordercolor="#666666">
 <tr>
 <td align="center"><strong>Debit ID</strong></td>
 <td align="center"><strong>Hotel</strong></td>
 <td align="center"><strong>Supplier Name</strong></td>
 <td align="center"><strong>Debit Amount</strong></td>
 <td align="center"><strong>Balance Before Debit</strong></td>
 <td align="center"><strong>Balance After Debit</strong></td>
 <td align="center"><strong>Debit Sheet No.</strong></td>
 <td align="center"><strong>Create/ Update User</strong></td>
 <td align="center"><strong>Created On</strong></td>
 </tr>
 <?php
 if(isset($excel_data)):
 for($i=0;$i<count($excel_data);$i++):
 print_r($data);
 ?>
 <tr>
 <td><?php echo $excel_data[$i]["vendor_debit_id"];?></td>
 <td><?php echo $excel_data[$i]["hotel_id"];?></td>
 <td><?php echo $excel_data[$i]["supplier_name"];?></td>
 <td><?php echo $excel_data[$i]["debit_amount"];?></td>
 <td><?php echo $excel_data[$i]["bal_before_debit"];?></td>
 <td><?php echo $excel_data[$i]["bal_after_debit"];?></td>
 <td><?php echo $excel_data[$i]["debit_sheet_no"];?></td>
 <td><?php echo $excel_data[$i]["admin_id"];?></td>
 <td><?php echo $excel_data[$i]["created_on"];?></td>
 </tr>
 <?php
 endfor;
 endif;
 ?>
 </table>
</body>
</html>

If you find any difficulty to the problem in integration. Please put the comment or contact us. We will feel happy to help you any problem related with Yii framework.