CODEWITHSUNDEEP
phpexcelcodeigniter
Mootu Pattulu
Mootu Pattulu

Reputation: 33

How to export the data in excel and how to upload PHPExcel file into controller using codeiginter?

I have written the code to export the data into an excel sheet, but the PHPExcel  libray is not loaded into the code.It shows some error while clicking the button"Export".

The error is: enter image description here

 public function Arrear_Print(){

    $data['page'] = 'export-excel';
        $data['title'] = 'Export Excel data | TechArise';
        $data['startdate'] =$this->input->post('SDate');
        $data['enddate'] = $this->input->post('EDate');
                $this->load->model('Export');
        $data['PName'] = $this->Export->mobileList();
        // load view file for output
        //$this->load->view('header');
        $this->load->view('TipUp_Loan\Arrear_Print', $data);
       // $this->load->view('footer');
}
 public function createXLS() {
        // create file name
        $fileName = 'mobile-'.time().'.xlsx';  
        // load excel library
        $this->load->library('excel');
        $mobiledata = $this->model->Export->mobileList();
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        // set Header
       $objPHPExcel->getActiveSheet()->SetCellValue('A1', 'AcNo');
        $objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Mode');
        $objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Party Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Loan Date');
        $objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Loan Amt');  
        $objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Bal.Amt'); 
        $objPHPExcel->getActiveSheet()->SetCellValue('G1', 'Coll.Amt');  
        $objPHPExcel->getActiveSheet()->SetCellValue('H1', 'Rec.Amt');  
        $objPHPExcel->getActiveSheet()->SetCellValue('I1', 'Phone.No');
        // set Row
        $rowCount = 2;
        foreach ($mobiledata as $element) 
        {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $element['TAcNo']);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $element['TMode']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $element['TName']);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $element['TDate']);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $element['TAmt']);
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $element['total_sum']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $element['RLAmt']);
            $objPHPExcel->getActiveSheet()->SetCellValue('H' . $rowCount, $element['']);
            $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, $element['Phone']);
            $rowCount++;
        }

        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $objWriter->save($fileName);
        // download file
        header("Content-Type: application/vnd.ms-excel");
         redirect(site_url().$fileName);              
    }

This is a controller code:

 <?php

if (!defined('BASEPATH'))
exit('No direct script access allowed');


    class Export extends CI_Model {
    // get mobiles list
    public function mobileList() {

        $data['startdate'] =$this->input->post('SDate');
            $data['enddate'] = $this->input->post('EDate');
            $PName  = $this->input->post('TName');
            $SDate  = $this->input->post('SDate');
            $EDate  = $this->input->post('EDate');

            if($PName = $this->input->post('TName')){


            $query = $this->db->query("SELECT `TAcNo`,`TMode`,`TDate`,`TAmt`,`TName`, COALESCE(SUM(`RLAmt`) ,0) AS `RLAmt`,`Phone` FROM `tipup_payment`  LEFT OUTER JOIN `tipup_receipt` ON `tipup_receipt`.`TRAcNo` = `tipup_payment`.`TAcNo`  AND `TDate` >= '$SDate' AND `TDate` <= '$EDate' LEFT OUTER JOIN `parmaster` ON `parmaster`.`PName` = `tipup_payment`.`TName`  WHERE TName = '$PName' GROUP BY `TAcNo`ORDER BY `TAcNo` ASC");
               return $query->result_array(); 


            //$data['PName']=$query->result_array();
         //$this->load->view('TipUp_Loan\Arrear_Print',$data,FALSE);
        }

        else {
            $query = $this->db->query("SELECT `TAcNo`,`TMode`,`TDate`,`TAmt`,`TName`, COALESCE(SUM(`RLAmt`) ,0) AS `RLAmt`,`Phone` FROM `tipup_payment` LEFT OUTER JOIN `tipup_receipt` ON `tipup_receipt`.`TRAcNo` = `tipup_payment`.`TAcNo`  AND `TDate` >= '$SDate' AND `TDate` <= '$EDate' LEFT OUTER JOIN `parmaster` ON `parmaster`.`PName` = `tipup_payment`.`TName`  GROUP BY `TAcNo` ORDER BY `TAcNo` ASC");
            return $query->result_array();   
            //$data['PName']=$query->result_array();
          //$this->load->view('TipUp_Loan\Arrear_Print',$data,FALSE);
        }


    }
    }
    ?>

This is model code:

enter image description here This is input view page....While giving from and to date into the text box.It fetch the data between this date and show it in the view page correctly enter image description here

This is fetched data and shown in the view page while clicking the "Export" button.The error is displaying that I have never uploaded the picture.

I have downloaded the phpexcel library file in the codeiginter website.

This is link....enter link description here

Please help me to solve this problem.

Upvotes: 1

Views: 312

Answers (1)

Javier Larroulet
Javier Larroulet

Reputation: 3237

There's many ways to solve your problem. It all comes down to how (and where) your PHPExcel files are placed and invoked as from the error message we can assume that PHP is looking for the PHPExcel files in a different location than where they actually are.

YMMV, but this is my installation.

To output a XLS file I get the data arrays from my model (always use return $query->result_array(); instead of return $query->result(); in your model unless you want to convert the returned object to array later) and do:

    $this->excel->setActiveSheetIndex(0);
    $this->excel->getActiveSheet()->setTitle($report);

    $titles = array_keys($data[0]);
    $this->excel->getActiveSheet()->fromArray($titles, NULL, 'A1');
    $this->excel->getActiveSheet()->fromArray($data, NULL, 'A2');

    $filename='output.xls';

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

    $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
    $objWriter->save('php://output');

Note that I'm calling $this->excel which comes from application/libraries/Excel.php where this code lives:

<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');  

require_once APPPATH."/third_party/phpexcel/PHPExcel.php";

class Excel extends PHPExcel {
    public function __construct() {
        parent::__construct();
    }
}
?>

The core PHPExcel files are, as you can see from the require located on application/third_party/phpexcel/PHPExcel.php (always remember to respect case as routes are case-sensitive). On the same level where PHPExcel.php is, I have placed the directory with the core PHPExcel files -including autoloader.php-, named PHPExcel.

It's pretty straight-forward and has worked perfectly for me on several projects using CI 3.1.x

Upvotes: 1

Related Questions