+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: export to xls problem (/showthread.php?tid=55907)
export to xls problem - El Forum - 11-16-2012
[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???
export to xls problem - El Forum - 11-17-2012
[eluser]Mohammed Zayan[/eluser]
Is there anyone knows???
export to xls problem - El Forum - 11-17-2012
[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?
export to xls problem - El Forum - 11-17-2012
[eluser]Mohammed Zayan[/eluser]
In my localhost the file downloaded
but online it print the data in the page and isn't download
export to xls problem - El Forum - 11-17-2012
[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.
export to xls problem - El Forum - 11-17-2012
[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