See the details here
$user = JFactory::getUser();
/* get the menu Itemid and set the case function based on the menu item selected */
$menuId = $this->app->getUserStateFromRequest('Itemid','0',false,'menu');
$incfile = '';
if((int) $menuId > 0){
switch ($menuId) {
case '2205':
$incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_hospital.php';
// $filename will be used as name of the Excel file created (less extension)
$filename = 'hospital_'.$user->id;
$filetype = 'Hospital';
break;
case '2206':
$incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_nursing.php';
$filename = 'nursingl_'.$user->id;
$filetype = 'Nursing/Rehab';
break;
case '2207':
$incfile = JPATH_ROOT.'/plugins/fabrik_list/listcsv/scripts/csv_export_practice.php';
$filename = 'practice_'.$user->id;
$filetype = 'Medical Practice';
break;
default:
break;
}
}
/* Check that 2nd include file exists */
if(file_exists(JPATH_ROOT.$incfile)){
require JPATH_ROOT.$incfile;
}
/* If JPATH_ROOT.$incfile does not exist, the normal download will proceed */
?>
<?php
// Get upload temp path (containing the CSV export file)
// Needed when writing the saved file with a different filename
$config = JFactory::getConfig();
$tmp_path = $config->get( 'tmp_path' );
// Import PHPExcel library
jimport('phpexcel.library.PHPExcel');
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$objReader = PHPExcel_IOFactory::createReader('CSV');
// Load the CSV export file into the phpExcel Reader
$objPHPExcel = $objReader->load($filePath);
// assign the active sheet to var $sheet
$sheet = $objPHPExcel->getActiveSheet();
/* Set Excel document properties
* This section is optional. The values can either be hard-coded
* or insert php code here to query tables and pass the retrieved
* values to these phpExcel functions.
*/
$objPHPExcel->getProperties()->setCreator("My Project")
->setLastModifiedBy($user->name." [".$user->username."]")
->setTitle("My Project Report")
->setSubject("My Project - Type:".$filetype)
->setDescription("My Project Description")
->setKeywords("Project PHPExcel php")
->setCategory("Reports");
// sheet title
// shown in navigation tab at bottom of workbook - limit 31 characters
$sheet->setTitle(substr($filetype,0,31));
// save row and column count to variables
$highestRowCount = $sheet->getHighestRow();
$highestColumnCount = $sheet->getHighestColumn();
// Freezes row 1 and columns A-D
// so header and read-only columns remain fixed when scrolled
$sheet->freezePane( "E2" );
// Style row 1 (Heading)
$sheet->getStyle("A1:{$highestColumnCount}1")->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'FFFF00')
)
)
);
/* This is part of trick to use column 'D' cell like a tooltip.
* By setting the background color to the same as the foreground color it
* will appear to be empty - but it really contains the 'Job Description'
* (a detailed description of the row's content) and will display that
* description above the spreadsheet when column D is clicked in any row.
* (See attachments)
*/
$sheet->getStyle("D1:D{$highestRowCount}")->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => '000000')
)
)
);
/* This is styling for the 'read-only' columns of the spreadsheet */
$sheet->getStyle("A2:C{$highestRowCount}")->applyFromArray(
array(
'fill' => array(
'type' => PHPExcel_Style_Fill::FILL_SOLID,
'color' => array('rgb' => 'F9FAEB')
)
)
);
/* The export contains internal ID in 1st column A - Hide it */
$sheet->getColumnDimension("A")->setVisible(false);
/* Sets the width of various columns as needed to accommodate numeric data
* or to prevent characters from wrapping in words in the header columns
*/
$sheet->getColumnDimension("B")->setWidth(9.75);
$sheet->getColumnDimension("C")->setWidth(40);
$sheet->getColumnDimension("D")->setWidth(1.7);
$sheet->getColumnDimension("E")->setWidth(40);
$sheet->getColumnDimension("G")->setWidth(9.75);
$sheet->getColumnDimension("K")->setWidth(9.75);
$sheet->getColumnDimension("M")->setWidth(10);
/* Sets style and formatting for columns with numeric data */
$sheet->getStyle("F1:L{$highestRowCount}")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$sheet->getStyle("F1:K{$highestRowCount}")->getNumberFormat()->setFormatCode('#,##0.00');
$sheet->getStyle("L1:L{$highestRowCount}")->getNumberFormat()->setFormatCode('#,##0.0');
/* Creates a popup comment in column D of header to instruct how to use the column D 'tooltip' */
$sheet->getComment("D1")->getText()->createTextRun("Click in this column to view description of selected row");
$sheet->getComment("D1")->setWidth("350px");
$sheet->getComment("D1")->setHeight("24px");
/* Wordwrap the heading cells */
$sheet->getRowDimension(1)->setRowHeight(45);
$sheet->getStyle("A1:{$highestColumnCount}1")
->getAlignment()
->setWrapText(true);
/* Password protect sheet */
$sheet->getProtection()->setPassword('PHPExcel');
$sheet->getProtection()->setSheet(true);
/* Unprotect editable cells */
$sheet->getStyle("E2:{$highestColumnCount}{$highestRowCount}")->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
/* You could add some code here that sets the file extension
* (and which PHPExcel writer to use), based on user preference set elsewhere.
* But for this example it is just hard-coded as xlsx.
*/
/* Initialize the PHPExcel Writer */
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$fullFilename = $filename.'.xlsx';
$fullPathFile = $tmp_path.'/'.$fullFilename;
/* Write the formatted sheet to an Excel file */
$objWriter->save($fullPathFile);
/* disconnect worksheet and unload PHPExcel */
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
/* Delete original csv export file */
unlink($filePath);
/* Set HTML headers and force download of Excel file
* skip this if you don't want to immediately download
*/
header('Content-Description: File Transfer');
header('Content-type: application/vnd.ms-excel');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename={$fullFilename}");
header("Content-Transfer-Encoding: binary ");
header('Content-Length: ' . filesize($fullPathFile));
/* flush output before reading file */
while(ob_get_level()) ob_end_clean();
flush();
/* readfile (and force the download) */
readfile($fullPathFile);
/* This is where you could include some php to also optionally save
* the file to a special user folder or 'store' for future use or purchase.
*/
/* delete file then kill the php process to prevent 'normal' csv file from being downloaded */
unlink($fullPathFile);
die;
?>