How to download excel file using phpExcel core and joomla

its easy way to download php with mysql data into custom excel file using phpExcel library.

Please check below code step by step :

download PHPExcel library



public function downloadPHPexcel()
{

require_once (JPATH_SITE.'/phpexcel/Classes/PHPExcel.php');
require_once (JPATH_SITE.'/phpexcel/Classes/PHPExcel/IOFactory.php');
ini_set('memory_limit','2048M');    

$jinput = JFactory::getApplication()->input;
$buildername = $jinput->get('buildername', '', 'STRING');
$date1 = $jinput->get('date1', '', 'STRING');
$date2 = $jinput->get('date2', '', 'STRING');
$db = JFactory::getDbo();
$query  = $db->getQuery(true);    
$query ="SELECT *
FROM #__splms_videohit_track AS
courseTitle ASC";

$db->setQuery($query);
$isbtotalResult = $db->loadAssocList();

// Set title and meta data//
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Creator")
            ->setLastModifiedBy("Modified")
            ->setTitle("Title")
            ->setSubject("Report")
            ->setDescription("ISB Report")
            ->setKeywords("report")
            ->setCategory("Report");
$rowNo = 1;    


$objPHPExcel->getActiveSheet()->setTitle(strtoupper($buildername.'_Members'));
// Set Header Column name//
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibiri')->setSize(11);
$objPHPExcel->getActiveSheet()->mergeCells('A1:F1');
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);    
$objPHPExcel->getActiveSheet()->getStyle('A1:F1')->getFont()->setBold(true);    
$objPHPExcel->getActiveSheet()->setCellValue('A'.$rowNo, 'Bk® Confidential');
$rowNo=3;    
$objPHPExcel->getActiveSheet()->getStyle('A3:F3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('A'.$rowNo, 'Course Title');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$rowNo, 'Registrant Name');
$objPHPExcel->getActiveSheet()->setCellValue('C'.$rowNo, 'Registrant Email');
$objPHPExcel->getActiveSheet()->setCellValue('D'.$rowNo, 'Register Date');

$objPHPExcel->getActiveSheet()->setAutoFilter('A3:D3');
//foreach loop for data get
$rowNo++;
foreach($isbtotalResult as $isbResultDetail){
   
   $objPHPExcel->getActiveSheet()->setCellValue('A'.$rowNo, $isbResultDetail['courseTitle']);
   $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowNo, $isbResultDetail['name']);    
   $objPHPExcel->getActiveSheet()->setCellValue('C'.$rowNo, $isbResultDetail['email']);
   $objPHPExcel->getActiveSheet()->setCellValue('D'.$rowNo, $isbResultDetail['redate']);

   $rowNo++;
}
//for loop end    
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
for( $i='C'; $i<= 'D'; $i++ ){
   $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(true);
}
$objPHPExcel->getActiveSheet()->getStyle('A'.$colHeder)->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('B'.$colHeder)->getAlignment()->setWrapText(true);

$styleArray = array(
 'borders' => array(
     'allborders' => array(
 'style' => PHPExcel_Style_Border::BORDER_THIN
     )
 )
);
$rowNo = $objPHPExcel->getActiveSheet()->getHighestRow();
$objPHPExcel->getActiveSheet()->getStyle(
   'A1:' .
   $objPHPExcel->getActiveSheet()->getHighestColumn() .
   ($rowNo-1)
)->applyFromArray($styleArray);    


$reportFormat    = 'xlsx';
$year  = date('Y',strtotime($date1));
$Month = date('M',strtotime($date1));
$fileName         = 'My_Company_Engagement_Report_'.$Month.'_'.$year.'.'.$reportFormat;
$csvfileName = $fileName;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setPreCalculateFormulas(true);
$objWriter->setIncludeCharts(TRUE);
header("Content-Description: File Transfer");
header("Content-Type:   application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=".$csvfileName);
header("Content-Transfer-Encoding: binary");
header("Expires: 0");
header("Cache-Control: must-revalidate");
header("Pragma: public");
$objWriter->save('php://output');
exit;
}

finally did then call the function : downloadPHPexcel();


ok, if you need to save the excel file into server Please refer below code

#################SAVE THE XLSX FILE INTO SERVER TOOT#######################

$fileName      = 'saveexcel.xlsx';
$objWriter      = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setPreCalculateFormulas(true);
$objWriter->setIncludeCharts(TRUE);
$DownLoadPath = ABS_PATH.'download/'.$csvfileName;
$objWriter->save($DownLoadPath);
#################SAVE THE XLSX FILE INTO SERVER TOOT#######################