0

I have done ample research and got hopeless with PHPExcel output. When I use PHPexcel to list out the result, I get the output, but not always, in codeigniter. How do I rectify this issue? I am not very proficient with the Excel sheet coding. Here is my query:

 public function excel_customer_all($date, $fdate)
{
  if (isset($_SESSION['logged_in']) && $_SESSION['logged_in'] === true && $_SESSION['role'] && $_SESSION['role_des'])
    {
    $this->load->library('excel');
    $styleArray = array(
    'font'  => array(
    'bold'  => true,
    'color' => array('rgb' => '#000000'),
    'size'  => 10,
    'name'  => 'Verdana'
    ));
    $this->excel->setActiveSheetIndex(0);
    $this->excel->getActiveSheet()->setTitle('All Customer Details');
    .....
    SETTING CELL VALUES HERE
    .....



            //set cell A1 content with some text

    ................just ignoring these parts to shorten the code.........
            //retrive contries table data

     $rs=$this->Customer_model->list_all_customer_date($date, $fdate);

    $row = 4; // 1-based index
    $col = 0;

    $no = 1;    
    foreach($rs as $key=>$value) {

                   echo $row . ", ". $col . "<br>";
                    $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row,$no);
                    $col++;
                     $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value->cus_main_ph);
                    $col++;
                    $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value->cus_name);
                    $col++;$col++;
                    $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value->cus_add);

                    $col++; $col++;
                    $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value->cus_email);

                    $col++;
                    $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value->cus_main_location);
                    $col++;
                    $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value->category);
                    $col++;
                   if($value->used_devices&&$value->used_device_count){
        $z=array();
         $a=explode(',',$value->used_devices);
                     $b=explode(',',$value->used_device_count);
        foreach($a as $index=>$val){
           if($b[$index]=!0)
           {
           array_push($z,$a[$index].'-'.$b[$index]);
           }
        }
        $y='';
         $y=implode("  , ",$z);
        }
        if($value->other_device&&$value->other_device_count){
        $f=array();
         $c=explode(',',$value->other_device);
                     $d=explode(',',$value->other_device_count);
        foreach($c as $index=>$val){
           if($d[$index]=!0&&$c[$index])
           {
           array_push($f,$c[$index].'-'.$d[$index]);
           }
        }
         $g=implode("  , ",$f); 
        } 
        if($y || $g)
        {
                    $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $y.' , '.$g);
        }
                    $col++;

                    $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value->follow_up_dt);
                    $col++;

                 $row++;$col++;
                    echo $row . ", ". $col . "<br>";
                    $col = 0;
 $no++;
}
            $this->excel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $this->excel->getActiveSheet()->getStyle('B4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $this->excel->getActiveSheet()->getStyle('C4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

          $filename=mt_rand(1,100000).'.xls'; //just some random filename
          ob_clean();
    ob_start(); 
    header('Content-type: application/vnd.ms-excel; charset=UTF-8' ); 
    //header('Content-Type: application/xlsx');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($this->excel,'Excel5');
        $objWriter->save('php://output');
        }  else
    {
        redirect('Admin/index', 'refresh');
      }

}

When I tested the code with multiple selected data, I found that the issue is somewhere near the imploding part in the above function. Sometimes, the error shows "Undefined variable $y" and sometimes it shows "headers already sent " warning message with some numbers, for example: see the image below:

enter image description here

What should be done in order to get output always irrespective of the type of data present in the database.

Chris Happy
  • 7,088
  • 2
  • 22
  • 49
julie
  • 111
  • 1
  • 2
  • 13
  • Tip: Rather than repeatedly calling `$this->excel->getActiveSheet()->` capture that value to a variable - probably right after the call to `$this->excel->setActiveSheetIndex(0);` i.e. `$sheet = $this->excel->getActiveSheet();` then use `$sheet` i.e. `$sheet->setTitle('All Customer Details');` and `$sheet->setCellValueByColumnAndRow($col, $row, $y.' , '.$g);` – DFriend Mar 06 '18 at 20:20
  • Will update those lines as well according to your tip. @DFriend – julie Mar 08 '18 at 07:52

1 Answers1

2

To prevent the error "Undefined variable $y" initialize $y at the beginning of each iteration of your foreach loop.

foreach($rs as $key=>$value) {
    $y = '';
    ....

And check if it is empty

if(empty($y) || $g)
David Klotz
  • 21
  • 2
  • Its the same. When I choose only 60 records, I got the excel sheet output. But When the record number increased to 250, it did not give any output instead echoed the numbers list as before. – julie Mar 06 '18 at 08:55
  • 1
    Try to delete the line with the `echo` command --> https://stackoverflow.com/questions/8028957/how-to-fix-headers-already-sent-error-in-php – David Klotz Mar 06 '18 at 11:47