Export to excel in php codeigniter

July 05, 2018170
Share Tweet Pin it

CodeIgniter: Export data to excel sheet. 


$fromdate=$this->uri->segment(3);
        $todate=$this->uri->segment(4);
        $message="Getting Sales report From date: ".date("d/m/Y",$fromdate)." to ".date("d/m/Y",$todate).".";
        $mdata=$this->Mainmodel->query_execution2("select * from bill_summary_vv where createdby='".$session_data['iddd']."' and deleted=0 and billdate between '".date("Y-m-d",$fromdate)."' and '".date("Y-m-d",$todate)."' order by billno");
        $datainfo="";
        $settings=$this->data['settings']=$this->Mainmodel->query_execution2("SELECT a.*,b.statename,b.statecode FROM `settings` a left JOIN masters_states b on a.stateid=b.id
 WHERE a.createdby='".$session_data["iddd"]."'");
        if(!empty($mdata)){
            $sr=1;
            $t_amount=0;
            $t_schemeamount=0;
            $t_discountamount=0;
            $t_taxableamount=0;
            $t_totaltax=0;
            $t_paybleamount=0;
            $heading=array('#','Bill No','Bill Date','Customer','Total','Tot Sch.','Tot Dis.','Taxable','Tot Tax','Payble');
            $t_cgst_tax2=0;
            $this->load->library('PHPExcel');
            //$this->load->library('PHPExcel/iofactory');
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->getActiveSheet()->setTitle("Sales Report");
            //Loop Heading
            $rowNumberH = 1;
            $columnarray=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
            $colH = $columnarray[0];
            foreach($heading as $h){
                $objPHPExcel->getActiveSheet()->setCellValue($colH.$rowNumberH,$h);
                $colH++;    
            }
            $i=2;
            $alp=0;
            $sr=0;
            foreach($mdata as $idata){
                $sr++;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,$sr);
                $alp++;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,$idata->billno);
                $alp++;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,date("d/m/Y",strtotime($idata->billdate)));
                $alp++;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,$idata->cname);
                $alp++;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,$idata->amount);
                $alp++;
                $t_amount+=$idata->amount;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,$idata->schemeamount);
                $alp++;
                $t_schemeamount+=$idata->schemeamount;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,$idata->discountamount);
                $alp++;
                $t_discountamount+=$idata->discountamount;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,$idata->taxableamount);
                $alp++;
                $t_taxableamount+=$idata->taxableamount;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,$idata->totaltax);
                $alp++;
                $t_totaltax+=$idata->totaltax;
                $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,$idata->paybleamount);
                $t_paybleamount+=$idata->paybleamount;
                $i++;$alp=0;
            }
            
            $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,'');
            $alp++;
            $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,'');
            $alp++;
            $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,'');
            $alp++;
            $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,number_format($t_amount,2));
            $alp++;
            $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,number_format($t_schemeamount,2));
            $alp++;
            $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,number_format($t_discountamount,2));
            $alp++;
            $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,number_format($t_taxableamount,2));
            $alp++;
            $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,number_format($t_totaltax,2));
            $alp++;
            $objPHPExcel->getActiveSheet()->setCellValue($columnarray[$alp].$i,number_format($t_paybleamount,2));
            $alp++;
            //Freeze pane
            $objPHPExcel->getActiveSheet()->freezePane('A2');
            //Save as an Excel BIFF (xls) file
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
            
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="'.$session_data["iddd"].'-Sales-report-from-'.date("d-m-Y",$this->uri->segment(3)).'-to-'.date("d-m-Y",$this->uri->segment(4)).'-'.date("D-d-m-Y-H-i-s").'.xlsx');
            header('Cache-Control: max-age=0');
    
            $objWriter->save('php://output');
            exit();
            
            //echo json_encode(array('status'=>1,'sdata'=>$message,'mdata'=>$datainfo));
        }
        else{
            echo json_encode(array('status'=>0,'sdata'=>'No Data Found'));
        }

Use PHPExcel this library.