Yii excel export

It is very simple and a only a matter of a few lines of code to export your data (for example from mysql) into excel. Follow the following steps:
1. Download PHP Excel Library from
https://github.com/PHPOffice/PHPExcel/tree/1.8/Classes
And extract into protected/extensions/PHPExcel

2. Use an action like the following in your controller

public function actionExcel($id) {
  $criteria = new CDbCriteria;
  $criteria->condition="winner_id=$id OR loser_id=$id";
  $criteria->order = "date DESC";
  $models= Match::model()->findAll($criteria);
        
  $objPHPExcel = new PHPExcel();

  $objPHPExcel->setActiveSheetIndex(0);
  // set header
  $data=['Winner','Loser','Result','Date'];
  $objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
  $i=2;
  // from db
  foreach ($models as $model) {
     $data = [
           CHtml::encode($model->getPlayerName($model->winner_id)),
           CHtml::encode($model->getPlayerName($model->loser_id)),
           $model->result,
           $model->date
      ];
      $objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A'.$i);
      $i++;
  }
        
      $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);
        
      $objPHPExcel->getActiveSheet()->setTitle('Matches list');
      foreach (range('A', $objPHPExcel->getActiveSheet()->getHighestDataColumn()) as $col) {
          $objPHPExcel->getActiveSheet()
              ->getColumnDimension($col)
              ->setAutoSize(true);
      }

      ob_end_clean();
      ob_start();

      header('Content-Type: application/vnd.ms-excel');
      header('Content-Disposition: attachment;filename="matches.xls"');
      header('Cache-Control: max-age=0');
      $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
      $objWriter->save('php://output');
        
    }


The phpexcel library can be dowloaded here PHPExcel.tar

Leave a Comment

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