Welcome Guest, Not a member yet? Register   Sign In
export to xls problem
#1

[eluser]Mohammed Zayan[/eluser]
hi there
I want to export data from the database into xls file
I use PHPexcel

this is my controller code
Code:
function download($type=""){
        $volunteers_data = $this->volunteer_model->get_all_volunteers();
        if($volunteers_data){
            $this->load->library("phpexcel");
            date_default_timezone_set("GMT");
            $filename = "filename".date("Y-m-d");

            $this->phpexcel->getProperties()->setCreator("Mohammed Zayan")
                ->setLastModifiedBy("Mohammed Zayan")
                ->setTitle("Volunteers Data")
                ->setSubject("Export Volunteers Data")
                ->setDescription("This is volunteers data")
                ->setKeywords("Volunteers")
                ->setCategory("Volunteers");

            $this->phpexcel->setActiveSheetIndex(0);

            $this->phpexcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
            $this->phpexcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
            $this->phpexcel->getActiveSheet()->getColumnDimension('C')->setWidth(35);
            $this->phpexcel->getActiveSheet()->getColumnDimension('D')->setWidth(35);
            $this->phpexcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);

            $this->phpexcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
            $this->phpexcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);

            $this->phpexcel->getActiveSheet()->getStyle('A1:G1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $this->phpexcel->getActiveSheet()->getStyle('A1:G1')->getFill()->getStartColor()->setARGB('FF808080');
            $this->phpexcel->getActiveSheet()->setCellValue('A1', "profileId");
            $this->phpexcel->getActiveSheet()->setCellValue('B1', "الاسم");
            $this->phpexcel->getActiveSheet()->setCellValue('C1', "الإيميل");
            $this->phpexcel->getActiveSheet()->setCellValue('D1', "print data");
            $this->phpexcel->getActiveSheet()->setCellValue('E1', "age");
            $this->phpexcel->getActiveSheet()->setCellValue('F1', "qualification");
            $this->phpexcel->getActiveSheet()->setCellValue('G1', "location");

            $therow = 1;

            foreach($volunteers_data as $row){
                ++$therow ;
                $this->phpexcel->getActiveSheet()->setCellValue('A'.$therow, $row->profileId);
                $this->phpexcel->getActiveSheet()->setCellValue('B'.$therow, $row->name);
                $this->phpexcel->getActiveSheet()->setCellValue('C'.$therow, $row->mail);
                $this->phpexcel->getActiveSheet()->setCellValue('D'.$therow, $row->printDate);
                $this->phpexcel->getActiveSheet()->setCellValue('E'.$therow, $row->age);
                $this->phpexcel->getActiveSheet()->setCellValue('F'.$therow, $row->qualification);
                $this->phpexcel->getActiveSheet()->setCellValue('G'.$therow, $row->location);

            }
            switch ($type){
                case "xlsx":
                    $ty = "Excel2007";
                    $fname = $filename.".xlsx";
                    $headertype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    break;

                case "csv":
                    $ty = "CSV";
                    $fname = $filename.".csv";
                    $headertype = "text/csv";
                    break;

                case "html":
                    $ty = "HTML";
                    $fname = $filename.".html";
                    $headertype = "text/html";
                    break;


                    break;

                default :
                    $ty = "Excel5";
                    $fname = $filename.".xls";
                    $headertype = "application/vnd.ms-excel";
            }

            header('Content-Type: '.$headertype);
            header('Content-Disposition: attachment;filename="'.$fname.'"');
            header('Cache-Control: max-age=0');

            $objWriter = PHPExcel_IOFactory::createWriter($this->phpexcel, $ty);
            ob_clean();
            $objWriter->save('php://output');
            exit;
        }else{
            echo "No data ". anchor(base_url(),"go back");
        }
    }

and this is my model code
Code:
function get_all_volunteers(){
        $this->db->select('profileId,name,mail,printDate,age,qualification,location');
        $query = $this->db->get('volunteers');
        if($query->num_rows > 0){
            return $query->result_object();
        }
    }

and this is the view code
Code:
echo   anchor(site_url()."/volunteers/download/xls/","download as xls", array('class'=>'export_link'));
echo "   ";
echo   anchor(site_url()."/volunteers/download/xlsx/","download as xlsx", array('class'=>'export_link'));
echo "   ";
echo   anchor(site_url()."/volunteers/download/csv/","download as csv", array('class'=>'export_link'));
echo "   ";
echo   anchor(site_url()."/volunteers/download/html/","download as html", array('class'=>'export_link'));

this code works in localhost and doesn't work online
what is the wrong with me???
#2

[eluser]Mohammed Zayan[/eluser]
Is there anyone knows???
#3

[eluser]solid9[/eluser]
Actually I tried this before many times and yes it's possible.
But I did not used PHPExcel.
I converted it from MySQL into .csv file.
And then the user will rename the .csv into excel file.

And I think it was the easiest conversion.
In your case what's the error?
#4

[eluser]Mohammed Zayan[/eluser]
In my localhost the file downloaded
but online it print the data in the page and isn't download
#5

[eluser]solid9[/eluser]
Yeah I think this is has to do with the MIME in your hosting server.
I think this was the reason I used .csv because before If I remember it won't download as well.
Thus I was forced to use .csv instead of directly converting it into .xls
You need to tell your hosting company to include .xls file in the MIME.
So you can download it.
#6

[eluser]JoostV[/eluser]
Have you tried adding the proper mime type to Apache config in an .htaccess file?
Code:
AddType application/excel .xls
AddType application/vnd.openxmlformats-officedocument.spreadsheetml.sheet .xlsx




Theme © iAndrew 2016 - Forum software by © MyBB