Wednesday, July 12, 2017

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();

1 comment:

  1. Just read your website. Good one. I liked it. Keep going. you are a best writer your site is very useful and informative thanks for sharing!
    Web Design Company in Chennai

    ReplyDelete