Create Excel File with PHPExcel Plugin Yii Framework

Create Excel File with PHPExcel Plugin Yii Framework:

public function actionCreateExcel() {
        Yii::import('ext.phpexcel.XPHPExcel');
        $objPHPExcel = XPHPExcel::createPHPExcel();
        $objPHPExcel->getProperties()->setCreator("Arunsri")
                ->setLastModifiedBy("Arunsri")
                ->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', 'Name')
                ->setCellValue('B1', 'Age')
                ->setCellValue('C1', 'Mark1')
                ->setCellValue('D1', 'Mark2')
                ->setCellValue('E1', 'Mark3');

        $sql = "select * from student";
        $datas = Yii::app()->db->createCommand($sql)->queryAll();        

        for ($i = 0; $i < count($datas); $i++) {
            $j = $i + 2;
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A' . $j, $datas[$i]['name'])
                    ->setCellValue('B' . $j, $datas[$i]['age'])
                    ->setCellValue('C' . $j, $datas[$i]['mark1'])
                    ->setCellValue('D' . $j, $datas[$i]['mark2'])
                    ->setCellValue('E' . $j, $datas[$i]['mark3']);
        }

        $styleArray = array(
            'font' => array(
                'bold' => true,
            ),
            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN
                ),
            ),
        );

        $objPHPExcel->getActiveSheet()->getStyle('A1:E1')->applyFromArray($styleArray);
        
        // uncomment the following to include image in sheet 2
        /*
          // Create new sheet in current excel file
          $objPHPExcel->createSheet(1);
          $objPHPExcel->setActiveSheetIndex(1);
          $objPHPExcel->getSheet(1)->setTitle('Image');

          //To Insert an image in excelsheet
          $objDrawingPType = new PHPExcel_Worksheet_Drawing();
          $objDrawingPType->setWorksheet($objPHPExcel->setActiveSheetIndex(1));
          $objDrawingPType->setName("Pareto By Type");
          $objDrawingPType->setPath(Yii::app()->basePath . DIRECTORY_SEPARATOR . "../images/logo.png");
          $objDrawingPType->setCoordinates('B2');
          $objDrawingPType->setOffsetX(1);
          $objDrawingPType->setOffsetY(5);
         */

        // Rename worksheet
        $objPHPExcel->getSheet(0)->setTitle('Data');

        // 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="GridData.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();
    }


Reference Documentation:

Click here for phpexcel reference documentation


Related Links:

Create Pdf file using Tcpdf plugin in php Yii framework

CGridView display data from database in Yii PHP Framework