Monday, June 22, 2015

PHPExcel libraries using example for Codeigniter

public function export() {
        $this->load->library('PHPExcel');

        $where = array(
            'role.code' => 'user',
            'user_meta.key' => 'winner',
            'user_meta.value' => 'OK');
        $joins = array('user.role_id=role.id', 'user.id=user_meta.user_id');
        $fields = 'user.id,user.email,user.username,user.firstname,user.lastname';

        $winner_users = $this->user_model->get_list($where, null, $fields, $joins);

        $winner_list = array();
        foreach ($winner_users as $winner_user) {
            $user_meta = $this->user_model->get(
                    array('user_id' => $winner_user['id'], 'key' => 'article_name'), 'user_meta');
            $winner_user['article_name'] = $user_meta['value'];
            $winner_list[] = $winner_user;
        }

        $heading = array('ID', 'И-Мэйл', 'Хэрэглэгч', 'Нэр', 'Овог', 'Урамшуулал');

        //Create a new PHPExcel Object
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("Ялагч болсон хэрэглэгчид");

        //Loop Heading
        $rowNumberH = 1;
        $colH = 'A';
        foreach ($heading as $h) {
            $cell_name = $colH . $rowNumberH;
            $objPHPExcel->getActiveSheet()->setCellValue($cell_name, $h);
            // Make bold cells
            $objPHPExcel->getActiveSheet()->getStyle($cell_name)->getFont()->setBold(true);
            $colH++;
        }

        //Loop Result
        $no = 2;
        foreach ($winner_list as $winner) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $no, $winner['id']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $no, $winner['email']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $no, $winner['username']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $no, $winner['firstname']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $no, $winner['lastname']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $no, $winner['article_name']);
            $no++;
        }

        //Columns size by auto
        foreach (array('B', 'C', 'D', 'E', 'F') as $colname) {
            $col = $objPHPExcel->getActiveSheet()->getColumnDimension($colname);
            $col->setAutoSize(true);
        }
        $objPHPExcel->getActiveSheet()->calculateColumnWidths();

        //Freeze pane
        $objPHPExcel->getActiveSheet()->freezePane('A1');

        //Save as an Excel BIFF (xls) file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $filename = "winners_" . date('Ymd') . ".xls";
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$filename\"");
        header('Cache-Control: max-age=0');

        $objWriter->save('php://output');
        exit;
    }

No comments: