Custom export into excel in Yii Framework

yii framework developer in mumbai india - ezeelive technologies

Custom export into excel in Yii Framework

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

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 function for export in excel. Remember for unset session variable into the end for you controller function before 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 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 problem in integration. Please put comment or mail us on info@ezeelive.com. We will feel happy to help you any problem relation with yii framework.

Comments (2)

  • Ecrva Reply

    I alաays spent my half an hour to read this webpage’ѕ content every day along with a cup of coffee.

    March 10, 2014 at 2:22 am
  • Kaceysaywel Reply

    Τhese are really impreѕsive ideas in on tҺe topic of bloggіng. You Һave toucҺed some fastidious points here. Any way keep up writing.

    March 11, 2014 at 8:59 pm

Leave a Reply

Your email address will not be published. Required fields are marked *