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

Tuesday, July 11, 2017

How to configure PHP with Mongodb and sample

Hi All,

It`s simple as mysql db configuration , do not worry about it , please follow below step to run demo application using mango db.

Step ->download  For window enable the driver "extension=mongo.so" 

configure for Ubuntu 14.04, : run "sudo apt-get install php5-mongo "

Step 2 : Download the sample PHP with mongo crud application

Step 3 : Extract CRUD folder into you xampp directory or /var/www/html/

finally run your sample : http://localhost/php-mongodb-crud

hope it will help you :) Enjoy Guys


Some of the below mongodb commands

Please read commands from official mongodb site